Problem with VB/Dlookup filling fields

[mattdube]

Hello.

I have given a lot of information here, possibly more than needed to
help me with the problem.

I am trying to populate 3 fields on a subform from one dropdown menu.
The fields are named LaborDept, LaborCode, and LaborDesc. The dropdown
has a select query pulling columns from a different table. I wrote the
following vb code to get the fields filled. Note that the dropdown is
associated with the first field, LaborDept, so it does not need vb code
to fill it.

Private Sub LaborDropDwn_Click()
LaborCode = Me.LaborDropDwn.Column(1)
LaborDesc = Me.LaborDropDwn.Column(2)
End Sub

This code *almost* does what it is supposed to do. The problem with
this is that the LaborDesc field changes for every single field
displayed on the form, presumably because the underlying table does not
have a LaborDesc field, and therefore isn't associated with a
particular record. The table that this form is associated with does
have a LaborDept and LaborCode field, but, again, no LaborDesc field.
LaborDesc is a string that provides description so the users know what
the codes mean, so I don't want to have to have that as another column
in the underlying table because it's not necessary.

I have also tried to populate the LaborDesc field using DLookUp, but
have not had success. An example of my DLookup is below. Note that
the primary key in tbl_ProjectCodes is a composite of LaborDept and
LaborCode, requiring the 2 field criteria.

= DLookup("[LaborDesc]", "tbl_ProjectCodes", "[LaborDept] = " &
[Forms]![fsub_StaffTimeDetails]![LaborDept] And "[LaborCode] = " &
[Forms]![fsub_StaffTimeDetails]![LaborCode])

Please offer help or ask for clarification if needed.

Thanks

-Matt

 

[Joan Wild]

You could go with just a single combobox. Set the rowsource of the combo to

SELECT [LaborDept] & " - " & [LaborCode] & " - " & [LaborDesc] AS Expr1,
LaborDept, LaborCode, LaborDesc
FROM SomeTable;

Set the column widths to
0.001";0.5";1";1"

When the list drops down, you'll see the three columns, after selecting
you'll
see the first which is a concatenation of the three.

I'm not clear from your description whether you might need two comboboxes,
but you should be able to adjust the above to suit.