Saving field values to a table

[SltPhx]

Hello All,
I have a question - regarding forms.

I have a Table ALPHA with fields A, B, ,C ,D, E , F, G, H and I - say 200 rows of data.
Columns A through D have values in it.
Columns E through I do not have any values.

I have a form connected to Table ALPHA - showing ALL the fields.

What I want is in the form; When User selects A (drop down list - 200 rows) - Fields B through D will automatically populate (I used D-LOOKUP, let me know if there is any other better way)
User has to manually enter values for fields E through I.


When user clicks SAVE (a button) - in the table corresponding to Field A values E though I should be saved.

Right now, all the fields are bound to the table. And when I hit save, a NEW LINE is saved i.e. Row 201. What I want is, the empty values of E through I for Row 1 should be filled - directly from the form.
Isn't that possible?

Please let me know as soon as possible. Thank You!

Thank You so much!

[June7]

Alternatives to DLookup:

1. make A a multi-column combobox, textboxes B,C,D reference columns of combobox

2. include the lookup table of A,B,C,D fields in the form RecordSource, join type 'Include all records from {primary table} and only those from {lookup} that match', bind textboxes to B,C,D fields and set them as Locked Yes, TabStop No.

Record is committed to table when form closes, move to new record, or run code (DoCmd.RunCommand acCmdSaveRecord).

What is your code that 'saves' record?

If you want to enter data into fields E-I of row/record 1 then form needs to be bound to table and record 1 has focus.

[SltPhx]

Hey June,
Thank you for your reply.

For Step 1 - how do you reference columns of the combo box?
And last step - how can we bring Record 1 to focus?
My code that saves record is - RunMenuCommand - Save Record

[June7]

In textbox ControlSource:

=[comboboxname].[Column](x)

x is column index. Index begins with 0 so column 2 is index 1.


Open form filtered to record 1 or navigate to the record and make sure it is selected. This can be done manually with intrinsic Access tools or write code behind custom buttons.


But what do you consider 'record 1'? An unfiltered bound form should open displaying the first record of the dataset unless the form DataEntry property is set to yes, then the form opens only to a new record row.


The code I show for saving record is recommended over the old RunMenuCommand method.


If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved.