DLookup problem (1 Viewer)

[mlopes1]

I have the following in an AfterUpdate event of a combo box:

Shelf = DLookup("[Shelf_Life]", "Shelf_Life_Tbl", ("[Stock_Number] = " & Me![stock].Column(0) & " And [Company] = " & Me![Shelf_Company].Column(0)))

Basically I want to look up the shelf life in another table based on the values selected in 2 combo boxes. This code is triggered after [Shelf_Company] is updated AND [Stock_Number] is not null.

I seem to have worked out the syntax correctly (I think!) but every time I run this I am told that "You cancelled the previous operation." Access help indicates that this means I need to update the recordset.
So I added:
set db = CurrentDb
Set rs = db.OpenRecordset("coa_select")
With rs
.Update
End With

Now I am told that I can't update the table without an addNew or Edit. I am totally lost here. All I want is to select 2 values seperately in the combo boxes and then for a variable to retireve its value by looking up the first 2 in the table....

any help here???

 

[AncientOne]

Forget the update, it's not relevant. Why are you using the column property of your combos instead of the combo name itself? Which is the bound column of these combos?

Are both the combo values referenced actually numbers or are they strings or dates?

 

[mlopes1]

I used the column numbers because the combo boxes have multiple columns and I thought that I had to reference a specific column. Both referenced are the bound columns.

The company name is text and the stock number is a double.

I also tried setting two variables equal to the values in the combo boxes and then put the variable names in the DLookup formula in place of the combo box reference. This did not change the Update error.

I will remove the update piece of it... but that should lead me back to the "You cancelled the previous operation" message that I receive when the code reached the DLookup, right?

It seems as if you are hinting that I am going about this the wrong way... any more direct ways to look up this value?

Thanks for your help!

 

[AncientOne]

If both references are to the bound columns, you need only refer to the control name. It would be less confusing if you used the prefix cbo (eg cboStock) to make it clear exactly what the control is. Assuming you do this, the following syntax should work:

Shelf = DLookup("[Shelf_Life]", "Shelf_Life_Tbl", "[Stock_Number] = " & Me!cbostock & " And [Company] = '" & Me!cboShelf_Company & "'")

The inverted commas are necessary to define the CompanyName string . There is a single quote folloWed by a double quote after= and a single quote enclosed within a pair of double quotes at the end.

 

[mlopes1]

AncientOne,

Your solution worked exactly right. I was totally unaware of the quote differences based on variable type. Once I made those changes, the value was successfully retireved with no problems.

Thanks for your help!

Marco