"Auto-fill" on data entry form - need append query to save?

[kagoodwin13]

I have figured out "auto-fill" on a form, as referenced here: https://www.accessforums.net/forms/c...uto-31874.html



I am looking to add to this capability though. I need a data entry form to input data into a table, but use "auto-fill" fields for user validation. Some suppliers have the same name, but different locations, and therefore different supplier numbers.

The easiest method to make a data entry form is just to use the form generated by the table, but implementing auto-fill requires referencing other tables. Would I use unbound fields on my form, and then submit the form input data (not auto-filled) to the table via an append query? Or am I going about this the wrong way?

[June7]

By 'auto-fill' you mean a textbox referencing value from combobox? What data do you need to save? The textbox values? Why? Will this result in data saved in multiple tables?

[kagoodwin13]

Yes, I need a text box to reference a combo box value. I need to save the data entered into the combo box values. The text box "auto-fill" is a verification that the user has entered the correct information, and will flag when there is not a match.

[June7]

Use VBA code to save the values from combobox. The real trick is figuring out what event to put the code in.

If the field you want to update is included in the form RecordSource:

Me!fieldname = Me.comboboxname.Column(x)
or
Me!fieldname = Me.textbox.name

If the field is not in the RecordSource then use INSERT or UPDATE sql action:

CurrentDb.Execute "INSERT INTO ...."

[Missinglinq]

The text box "auto-fill" is a verification that the user has entered the correct information, and will flag when there is not a match

Or am I going about this the wrong way?

Taking your post at face value, you're actually going about it backwards! Comboboxes are used to facilitate the entering of correct/proper data, not to verify that data already entered is correct! What you would normally do is have the Combobox display the suppliers names, locations, and supplier numbers. Once a selection was made, you'd populate Textboxes on the Form with whichever values you need to save in the underlying Table, using a method like this:

Set up your Combobox using the Wizard and include the Fields you need, from Left-to-Right.

If in the Combobox they appear as

Field1 | Field2 | Field3

the code would be

Private Sub YourComboBox_AfterUpdate()
   Me.txtField1 =  Me.YourComboBox.Column(0)
   Me.txtField2 = Me.YourComboBox.Column(1)
   Me.txtField3= Me.YourComboBox.Column(2)
End Sub

Notice that the column index is Zero-based.

Linq ;0)>

[kagoodwin13]

Thanks to both of you. Solved!

I put the VBA code to save the auto-filled text field in the "save button" event. It would not work in After Update on the text box because it was automatically updated and not manually.