Help merging a single record from one table to another.

  • Thread starter Thread starter Ynot
  • Start date Start date
Y

Ynot

I have a small application that uses a form to update a massive table to
maintain information about construction jobs. When adding new records, very
often, much of the information is the same, say it is for the same model
house so things like Square feet, number of windows, etc are the same. What
I would like to do is make a second table similar to my existing table. I
would like to be able to pick plan # 405A and use that to populate the
fields in a new record, then add that record to my exiting table, or better
yet, bring up the form used to add a record to the exiting table with all of
the fields populated with the data selected from the new table. Is this
possible, and can anyone point me in the right direction for how to do this?
 
Here is one ways to do what you want.

Create your 'lookup' table (I'll call it tblPlans). You'll need to make
another form to add new plans to this table.

Open the form used to add a record to the exiting table in design mode.
Add an unbound combo box in the form header (I'll call it
cboPlanLookUp). Open the properties for the combo box. Click on the
build button (...)(the three dots at the right) for the ROW SOURCE. Add
tblPlans and drag down the fields to the grid. Select ascending in the
Sort row for the plan number column. Close and save the query builder.

Click on the Format Tab. Set the Column Count to the number of columns
that you added to the query builder for the Row Source. Set the column
widths to 0 (zero) for all of the columns that you don't want to see
when the dropdown is open. It will look something like
0.5;0.9;0;0;0;0;0;0;0;0;0;0;0;0;0

Change the List Rows if you want to see more than 8 rows.


Next, click on the event tab (the combo box 'cboPlanLookUp' should still
be selected). In the After Update event, make sure [Event Procedure] is
selected, then click on the build button (...) to open the VBA editor.
This is where the magic happens.

Start out with

'if no plan selected, exit sub
If IsNull(Me.cboPlanLookUp) then
Exit Sub
End If

Then, add the data for each control on the form
Remember, the combo box row source columns are numbered starting with 0

'plan was selected - update controls on form with the combo box values
'important!! - match the control to the data column!!!
'control for Plan# = lookup column for plan #
Me.control_1 = Me.cboPlanLookUp.Columns(0)
Me.control_2 = Me.cboPlanLookUp.Columns(1)
..
..
..
Me.control_N = Me.cboPlanLookUp.Columns(N-1)

'clear the combo box
Me.cboPlanLookUp = Null


Change control_1 is the name of the controls on the form.


All that is left to do is add the est of the data to the form and save it.

HTH
 
Back
Top