Autopopulate

[Richnep]

Hi all,

Here is one I have been banging my head against the wall to solve.

1 table, 1 form bound to that table, 5 fields.

Primary Key is "Production Number" which is a combo box on the form.
All oterh boxes are text boxes on the form.

When a user picks a Production Numberr from the combo box field I
would like the other 4 boxes to auto fill with the data from the other
4 fields that correspond to that production number. It seems like it
would be easy to do but searching around the web I haven't had much
luck. Any help would be appreciated.

 

[George Nicholson]

Depending on exactly what *else* you want to do on the form after solving
the specific question posted, one of the following should help, in order of
increasing complexity:

How to populate text boxes with multicolumn Combo Box selections
http://support.microsoft.com/kb/319482/en-us

How to move to a specific record from a Combo Box selection in Microsoft
Access
http://support.microsoft.com/kb/287658/en-us

How to synchronize two combo boxes on a form in Access 2002 or in Access
2003
http://support.microsoft.com/kb/289670/en-us

 

[Richnep]

Depending on exactly what *else* you want to do on the form after solving
the specific question posted, one of the following should help, in order of
increasing complexity:

How to populate text boxes with multicolumn Combo Box selectionshttp://support.microsoft.com/kb/319482/en-us

How to move to a specific record from a Combo Box selection in Microsoft
Accesshttp://support.microsoft.com/kb/287658/en-us

How to synchronize two combo boxes on a form in Access 2002 or in Access
2003http://support.microsoft.com/kb/289670/en-us

--
HTH,
George










- Show quoted text -

Thanks for responding George. All of the links you sent are good
solutions.

The solution that works best for me was to use the DLOOKUP funtion in
the After Update event of the combo box.

 

[Anthos]

As a much better access Developer then I once said to me.

Dont use dlookups, they are the devil

They are very slow and expensive in quering the database

You are better off havng the combo box have all values in it, but
hidden
(ie Column widths for 5 fields would be ;0cm;0cm;0cm;0cm)

That way you will only see the value for the Primary key Column
(Production Number)
But on the "After Update" event of the Combo run this code

Me.textbox1.value = Me.combobox.Column(1)
Me.textBox2.value = Me.comboBox.column(2)
Me.textBox3.value = Me.ComboBox.column(3)
Me.textBox4.value = Me.ComboBox.column(4)

Because you have already queried the database once to populate the
drop down, you may as well pull the information from this drop down,
and save another query to the database.

Hope this help, or at least doesn't confuse the situation..

Kind Regards
Anthony Moore

IT Excellence

 

[Richnep]

As a much better access Developer then I once said to me.

Dont use dlookups, they are the devil

They are very slow and expensive in quering the database

You are better off havng the combo box have all values in it, but
hidden
(ie Column widths for 5 fields would be ;0cm;0cm;0cm;0cm)

That way you will only see the value for the Primary key Column
(Production Number)
But on the "After Update" event of the Combo run this code

Me.textbox1.value = Me.combobox.Column(1)
Me.textBox2.value = Me.comboBox.column(2)
Me.textBox3.value = Me.ComboBox.column(3)
Me.textBox4.value = Me.ComboBox.column(4)

Because you have already queried the database once to populate the
drop down, you may as well pull the information from this drop down,
and save another query to the database.

Hope this help, or at least doesn't confuse the situation..

Kind Regards
Anthony Moore

IT Excellence

Thanks for the tip. In a med/large db query cost may be a factor but
<250mb db and under 15 users at once. Further the DL lookup only runs
to populate 1 field if 1 record so the return recordset is 1 field of
1 record.

Just for others out there in the same situation, the real issue was
that I have tabbed subforms.
1 Main Table (Main Form) -PK is ProjectNumber with a 1:N
relationship with subtables
5 Subtables (Tabbes Subforms)- PK is autonummber field called
counter not seen by the users on the form.


So in each tab I have the subforms set up with the fields in a 1 row
tabular format, so that each record in subforms appears on a seperate
line.
In the subforms we use Lot number. So lot number 1234 is in each tab
but the lot numbers have no relationship to each other. So my problem
was I needed to copy a field called "CONC" from 1 tab to a field
called "CONCET" on another tab but under the same lot number. I wanted
to do this in a on change event so the user could see it was there and
it would save the record.


I orginally wrote an SQL statment but I keept getting "The record has
changed, do you want to drop changes" dialog box. Thats becuase the
SQL statment was making a change to the underlying table rather than
entering it through the form.

DL Lookup
Me.Concent = DLookup("[Conc]", "[tblAPResults]", "[LotNumber] = Forms!
[frmAntibodySheet]![SubstorageshipSheet22window]![CurrentLotNum] ")

SQL CODE
DoCmd.RunSQL "UPDATE tblAPResults INNER JOIN tblStorageShip ON
tblAPResults.LotNumber = tblStorageShip.OrigLotNum SET
tblStorageShip.Concent = [tblAPResults]![Conc]WHERE
(((tblAPResults.LotNumber)=[Forms]![frmAntibodySheet]!
[subStorageShipSheet22window]![CurrentLotNum]) AND
((tblAPResults.Conc) Is Not Null));"

The DLookup was easy to use and it didn't create the "The record has
changed" error and hence solved my problem with what seems like no
diffrence than the sql statment.

I wrote the post the way I did becuase every time I wrote it this way,
people went way OT. I was going to take the answer I got and see if I
could mod it to how I needed it and then found DLookup. Just like many
other things in life, it can work fine if you use it right

Thanks all for your help.

 

[Larry Linson]

If you use the Combo Box wizard to create the Combo, in any recent version
of Access, what you want to do is one of its options -- it'll write the code
for you. It is often more productive to just _try_ in Access, than to ask
in a newsgroup, or look things up in a book.

Larry Linson
Microsoft Access MVP