Filling multiple fields from a multiple field look-up

[RMac49]

My brain tells me this should be simple, perhaps it is, but I cannot make it work.

I have a look-up database with two fields, Model and chassis. I have a main database that has many fields including these fields.

The "Model" field in the main data base is a look-up field using the aforementioned look-up database. I want to also fill in the "chassis" field in the main database from the look-up database simultaneously. Primarily, this is important in the data entry form for the user ie., he pulls down the model number and the chassis is automatically filled in for him. I am able to have the form work properly by merely using the look-up data base "chassis" field, but I am unable to get this data (Chassis) back into the main database.

I have read many (not all) of the posts similar to this, but none of the solutions given resolve this issue.

ANy help is very much appreciated,

Roger

 

[LittleSmudge]

Point One - If I understand what you're asking for then YOU SHOULDN'T BE DOING THIS - it is against the Normalisation rules
You should populate an unbound field on the User data input form using DLookup function and do exactly the same anytime you need the data subsequently.
Having said the .. .. to the answer..

In the AfterUpdate event on the Model control ( Lets call it cboModel) you put in some code that takes the Chassis data and places it into the Chassis field ( Actually all of this is true even if you heed the comments above. )

cboModel
ControlSource = tblMainTable!Model
RowSource = "SELECT Model,Chassis FROM tblLookUp"
BoundColumn = 1
Column Count = 2


Private Sub cboModel_AfterUpdate()
txtChassis = cboModel.Column(1)
End Sub

If you make the control txtChassis bound to the tblMainTable!Chassis field then you break the rules - but it does what you're asking for. If you leave the field unbound and populate it as required using
txtChassis = DLookup("Chassis","tblLookup","Model = " & cboModel)
then the purests are satisfied, your data integrity improves .. etc .. etc..


G LS

 

[RMac49]

Thanks Littlesmudge, I want to do it correctly, therefore I will learn to use the DLookup function. A prior post recommended using the Access help file for additional information on DLookup however, I found the help file referred only to troubleshooting expressions (using 'DLookup' as the search string). The books I have leave much to be desired regarding this subject. Still on the shallow knee of a learning curve, I appreciate your assistance.

RMac49

 

[LittleSmudge]

Good luck RMac49

The general form of DLookup is

xxx = DLookup( fieldname as string, tablename as string, WHEREclause as string)

fieldname is the name of the field that must be in the table identified in tablename
WHEREclause is a standard SQL WHERE clause withOUT the word WHERE
If the Where clause matches to more than one record in tablename then DLookup will pick one at random and return the value in fieldname ( USUALLY it is the first record in the table but the sort is NOT guaranteed.
If there are no record that match then DLookup returns NULL so you might need to protect for Nulls entering your data.
If all is well DLookup returns the value in fieldname so you need to make sure that the variable xxx is of the same type as fieldname ( or at least can cope with fieldname's type )

You can use DLookup in VB code or in the Control Source of controls on a Form or Report.

Even for the more experienced amongst us complex, WHEREclauses with a mixture of text and variables can be a matter of trial and error to get right so practice and see how you get on.


G LS