best way to populate multiple Unbound text boxes?

[Barry A&P]

I have a form with multiple unbound text boxes and a bunch of dlookups in VBA
to populate them all when the afterupdate of my SerialNumberIDcombo fires..
Since i am only working with two tables there must be a better more efficient
way to get the values i am after..

here is a query that gets me what i want but i dont know how to set multiple
unbound boxes from the result maybe i need an intro to arrays or something??

SELECT T_SerialNumbers.SerialNumberID, T_PartNumbers.[(NSN)],
T_PartNumbers.PartNumber, T_PartNumbers.Description,
T_SerialNumbers.SerialNumber, T_SerialNumbers.SNNotes FROM T_PartNumbers
INNER JOIN T_SerialNumbers ON
T_PartNumbers.PartNumberID=T_SerialNumbers.PartNumberID WHERE
(((T_SerialNumbers.SerialNumberID)=Me!SerialNumberIDCombo));

in the after update of Me!SerialNumberIDCombo i would like to set;
UnboundNSN = [NSN]
UnboundPartNumber = [partnumber] .ect

Maybe a single refined Dlookup can get me there..

Any help would be greatly appreciated

Barry

 

[Marshall Barton]

I have a form with multiple unbound text boxes and a bunch of dlookups in VBA
to populate them all when the afterupdate of my SerialNumberIDcombo fires..
Since i am only working with two tables there must be a better more efficient
way to get the values i am after..

here is a query that gets me what i want but i dont know how to set multiple
unbound boxes from the result maybe i need an intro to arrays or something??

SELECT T_SerialNumbers.SerialNumberID, T_PartNumbers.[(NSN)],
T_PartNumbers.PartNumber, T_PartNumbers.Description,
T_SerialNumbers.SerialNumber, T_SerialNumbers.SNNotes FROM T_PartNumbers
INNER JOIN T_SerialNumbers ON
T_PartNumbers.PartNumberID=T_SerialNumbers.PartNumberID WHERE
(((T_SerialNumbers.SerialNumberID)=Me!SerialNumberIDCombo));

in the after update of Me!SerialNumberIDCombo i would like to set;
UnboundNSN = [NSN]
UnboundPartNumber = [partnumber] .ect

Maybe a single refined Dlookup can get me there..

Forget about using DLookup for this.

You could use a bunch of code to open a recordset on that
query and copy the fields to the text boxes.

However, if the SerialNumberID combo box's row source query
can be made to include the fields you want to display, then
you can do it without any code by using text box expressions
like:
=combobox.Column(N)
where N is the zero based number of the field in the row
source query.

You can use the combo box's ColumnWidths property to hide
any fields you do not want to see in the drop down list.

If you need help doing that, please provide specific details
about the combo box's properties (RowSource query,
BoundColumn, ColumnCount and ColumnWidths), the text box
names and the field names that are to be display in each
text box.

 

[Barry A&P]

Exactly...

Life (or a MVP) usually provides a much easier path than the one i seem to
be following..

Thank you so much

Barry

I have a form with multiple unbound text boxes and a bunch of dlookups in VBA
to populate them all when the afterupdate of my SerialNumberIDcombo fires..
Since i am only working with two tables there must be a better more efficient
way to get the values i am after..

here is a query that gets me what i want but i dont know how to set multiple
unbound boxes from the result maybe i need an intro to arrays or something??

SELECT T_SerialNumbers.SerialNumberID, T_PartNumbers.[(NSN)],
T_PartNumbers.PartNumber, T_PartNumbers.Description,
T_SerialNumbers.SerialNumber, T_SerialNumbers.SNNotes FROM T_PartNumbers
INNER JOIN T_SerialNumbers ON
T_PartNumbers.PartNumberID=T_SerialNumbers.PartNumberID WHERE
(((T_SerialNumbers.SerialNumberID)=Me!SerialNumberIDCombo));

in the after update of Me!SerialNumberIDCombo i would like to set;
UnboundNSN = [NSN]
UnboundPartNumber = [partnumber] .ect

Maybe a single refined Dlookup can get me there..

Forget about using DLookup for this.

You could use a bunch of code to open a recordset on that
query and copy the fields to the text boxes.

However, if the SerialNumberID combo box's row source query
can be made to include the fields you want to display, then
you can do it without any code by using text box expressions
like:
=combobox.Column(N)
where N is the zero based number of the field in the row
source query.

You can use the combo box's ColumnWidths property to hide
any fields you do not want to see in the drop down list.

If you need help doing that, please provide specific details
about the combo box's properties (RowSource query,
BoundColumn, ColumnCount and ColumnWidths), the text box
names and the field names that are to be display in each
text box.