DLookup Help

[rlmorgan]

Ok, I spent the better part of four hours looking at previous posts and trying various combinations of code and can’t come up with a solution.

I am using it for Asset Management and tracking of the warranty parts ordered for, installed, shipped back to the manufacturer. My form has two command buttons that lookup the Asset by item service tag or the RMA # from the manufacturer. The main form has all the information about the Asset in question (model, retire date, etc.). The subform shows all parts that have been ordered it, including all of the RMA numbers for the parts on order or previously installed. As the parts come in, I would like to scan the box and have it bring up the Asset it is assigned to showing all the parts in the subform.

My problem: I my have more than one RMA number in the subform and can’t figure out how to look up the Asset with the RMA#. I am trying to use DLookup to return the value of the service tag and put it into the Service Tag combo box lookup, but can’t get it work.

     Dim varX As Variable
     DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = " & Forms("frmParts")("[RMA_DPS]"))
     Forms!frmParts![cboFindService] = varX

 

[Remou]

I am not sure from the above where your textbox is, but unbound boxes don't work in continuous subforms.

 

[rlmorgan]

Both of the combo boxes are unbound. That is why I am trying to use DLookup to fill in the second combo box that will fill the main form thus filling in the subform at the same time.

 

[Remou]

Try:
DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = " & Me.RMA_DPS
If RMA_DPS is on your main form and is numeric;

DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.RMA_DPS & "'"
If RMA_DPS is on your main form and is text;

DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.RMA_DPS.Columns(i) & "'"
If RMA_DPS is on your main form and is text, but is not the first (0) column of a combo, where i is the relevant column;

DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.[Subform control name].Form.RMA_DPS & "'"
If RMA_DPS is on a sub form and is text.

 

[rlmorgan]

RMA_DPS is on the main form and is text. I have tried

DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.RMA_DPS & "'"

With this code, I get a Compile Error:
Expected: list separator or )

 

[randy700]

So, add the ).....

[COLOR=red]=[/color]DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.RMA_DPS & "'[COLOR=red])[/color]"

Randy

 

[rlmorgan]

Sorry for being ignorant, still a little new to the VB side of things. I am now getting a

Compile error:
Expected: line number or label or statement or end of statement

What I think I need to make happen is…. When I input data into the [cboFindRMA] combo box, under the “After Update” Event, I would like to have a DLookup look at the underlying table and return the “ServiceTag” field form the first record the has a matching RMA number , then put that “ServiceTag” data into the [cboFindService] combo box. This way it can update the subform as needed.

 

[Remou]

Try:
[tt]Me.cboFindService = DLookup("[ServiceTag]", "[tblPartsOrdered]", "[RMA_DPS] = '" & Me.cboFindRMA & "')"[/tt]

But I think I would go about this a different way. Can you base cboFindRMA on a query that includes ServiceTag? That way, you can simply reference the relevant column. Furthermore, you may need to update the subform at some stage depending on how you have set this up (link fields etc).

It may be best to post the code you have so far.