Table not updating after Form input

[jono]

I've imported a table showing 2 fields: "Order no" and "Code x-Code y-Code z", eg, Order No 1, x-y-z

Then created a form that has an Order no, 3 combo boxes, one each for Code x, Code y, Code z, with the intention of being able to add a new order together with selections for Code x, Code y, Code z.

Also on the form I've created a text box with the control source =[Code x] & "-" & [Code y] & "-" & [Code z], eg, x-y-z

However when I then try inputting a new Order on my form, the table shows the new order no but doesn't populate the newly concatenated field.

Any ideas would be helpful.

jono

[Robeen]

Are you trying to put the contents of your Text Box

 
control source =[Code x] & "-" & [Code y] & "-" & [Code z]

into a particular field in your Table . . . automatically?

If so - I think the control source has to be set to the Name of that Table field.

Once you changed the control source to
=[Code x] & "-" & [Code y] & "-" & [Code z]
it is no longer connected [bound] to that field in your table.

What you might try is setting the control source of the text box to the name of the Table field that you want x-y-z to go into . . .

and then

have a control on your Form [a button?] that you click when you are ready to insert data into your table.

In the code for the button, you can concatenate the three values and put the concatenated value into the Text Box.
The button can also do the actual inserting of the entire record if you want.

OR

in the Lost Focus events of the three combo boxes, you can have code that puts the concatenated value of all three combo boxes into the text box [which still has the table field name as its control source].

Now when you move to another record - the value in your text box will be inserted into the table.

I hope this helps.

[jono]

Hi Robeen,

Thanks for that, I understand how to bound the field to the table which now works, and I think the next steps will work but am being really thick when I get to:

[and then

have a control on your Form [a button?] that you click when you are ready to insert data into your table.

In the code for the button, you can concatenate the three values and put the concatenated value into the Text Box.
The button can also do the actual inserting of the entire record if you want.]


Do you know what the code would be to insert the record into the table? Do I run a 'RunCommand' via a macro?

[Robeen]

Here's an example of how you might insert data into your table.
You'll have to replace the table & field names.
My command button is called Login.

In this example, an integer, three String fields, a Date & a Time are inserted into a Table named Users.

 
Private Sub Login_Click()
 
Dim Date_Worked, Strt_Time As Date
Dim intID As Integer
Dim StrUser_Name, StrEmail, StrUser_Type As String
 
Me.txtDate.SetFocus
Date_Worked = Me.txtDate
 
Me.txtTime.SetFocus
Strt_Time = Me.txtTime
 
Me.ID.SetFocus
intID = Me.ID
 
Me.User_Name.SetFocus
StrUser_Name = Me.User_Name
 
Me.EMail.SetFocus
StrEmail = Me.EMail
 
Me.User_Type.SetFocus
StrUser_Type = Me.User_Type
 
StrSQL = "INSERT INTO Users (ID, User_Name, EMail, User_Type, Date_Worked, Strt_Time) "
StrSQL = StrSQL & "VALUES (" & intID & ", " & "'" & StrUser_Name & "'" & ", " & "'" & StrEmail & "'" & ", " & "'" & StrUser_Type & "'" & ", " & "#" & Date_Worked & "#" & ", " & "#" & Strt_Time & "#" & "); "
 
'MsgBox StrSQL
 
DoCmd.RunSQL StrSQL
 
End Sub