AUTOFILL of related form fields

[Stan_B]

When using a form I wish to call up the record of an instrument by it's asset
number - which is a unique identifier and, thus, my record key - from a combo
box. Once I scan in this asset number (it's barcoded onto an instrument), I
wish to AUTO FILL into this form the following related fields (already
contained in an "Assets" table) of:

Model Number,
Serial Number, and
Nomenclature.

If I use the =comboboxname.column(#) it will fill in the fields but for
VIEWING only. I need this data (Model, Serial, Nomenclature) also entered
into another underlying table. How do I do this so I can call up the barcode
and have the data from the "Assets" table self populate into the related
fields on this form?

On the form I am attempting to fill, should my RECORD SOURCE (in the form
properties) be the table I am drawing information from, or the table that I
wish to save data to?

I apologize for my lack of skills and gratefully send Thanks for any and all
help. Regards,

 

[Steve McLeod]

This is the sort of thing that drove me nuts when first learning Access.

Bind your form to the table or query that you are updating.
Bind the combo box source to the table or query for the "Assets."
Use the comboBoxClick event to use a parameter query to retrieve the info
you need from the "Assets"

table into a recordset and copy the info into form fields bound to the
corresponding columns in the

table you want to update.

*********qryDocReferenceUpdate******************
PARAMETERS TBL Text ( 255 ), ExtKey Text ( 255 ), LAN Long;
SELECT tblDocReferencesEst.*
FROM tblDocReferencesEst
WHERE (((tblDocReferencesEst.txtTableName)=[TBL]) AND
((tblDocReferencesEst.txtExternalKey)=[ExtKey]) AND
((tblDocReferencesEst.enmLAN)=[LAN]))
ORDER BY HyperlinkPart([hyperDescription],1);


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Use a parameter in the where clause for your asset key then set that
parameter to the value of the combo box.

Set db = CurrentDb
Set qdf = db.QueryDefs("qryDocReferenceUpdate")
qdf.Parameters("TBL") = vTableName
qdf.Parameters("ExtKey") = vEntityKey
qdf.Parameters("LAN") = enLAN
Set rst = qdf.OpenRecordset

rst contains the stuff you want to copy to the form.
The order of parameters is real important. It must be the same in
PARAMETERS, SELECT, and qdf.Parameters.

 

[Stan_B]

Thank You, Steve

I will give this a try.

This is the sort of thing that drove me nuts when first learning Access.

Bind your form to the table or query that you are updating.
Bind the combo box source to the table or query for the "Assets."
Use the comboBoxClick event to use a parameter query to retrieve the info
you need from the "Assets"

table into a recordset and copy the info into form fields bound to the
corresponding columns in the

table you want to update.

*********qryDocReferenceUpdate******************
PARAMETERS TBL Text ( 255 ), ExtKey Text ( 255 ), LAN Long;
SELECT tblDocReferencesEst.*
FROM tblDocReferencesEst
WHERE (((tblDocReferencesEst.txtTableName)=[TBL]) AND
((tblDocReferencesEst.txtExternalKey)=[ExtKey]) AND
((tblDocReferencesEst.enmLAN)=[LAN]))
ORDER BY HyperlinkPart([hyperDescription],1);


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Use a parameter in the where clause for your asset key then set that
parameter to the value of the combo box.

Set db = CurrentDb
Set qdf = db.QueryDefs("qryDocReferenceUpdate")
qdf.Parameters("TBL") = vTableName
qdf.Parameters("ExtKey") = vEntityKey
qdf.Parameters("LAN") = enLAN
Set rst = qdf.OpenRecordset

rst contains the stuff you want to copy to the form.
The order of parameters is real important. It must be the same in
PARAMETERS, SELECT, and qdf.Parameters.

--
Pictou

When using a form I wish to call up the record of an instrument by it's asset
number - which is a unique identifier and, thus, my record key - from a combo
box. Once I scan in this asset number (it's barcoded onto an instrument), I
wish to AUTO FILL into this form the following related fields (already
contained in an "Assets" table) of:

Model Number,
Serial Number, and
Nomenclature.

If I use the =comboboxname.column(#) it will fill in the fields but for
VIEWING only. I need this data (Model, Serial, Nomenclature) also entered
into another underlying table. How do I do this so I can call up the barcode
and have the data from the "Assets" table self populate into the related
fields on this form?

On the form I am attempting to fill, should my RECORD SOURCE (in the form
properties) be the table I am drawing information from, or the table that I
wish to save data to?

I apologize for my lack of skills and gratefully send Thanks for any and all
help. Regards,