DLookup problem

[TomJamieson]

I'm not sure if I'm being really stupid here or not, but I'm rather stuck. I have a report that is created from a query. One of the fields is "Quantity" which is a number between 1 and 500,000. I have a custom label on the form which I want to display a certain number depending on the Quantity. So the user types in the ID of a record, the report opens showing details of that record, and the label automatically works out what number it should display depending on the Quantity.

I just can't figure out how to reference the Quantity field from my vba module. Here's my code:



Private Sub Report_Open(Cancel As Integer, qty As Integer)

qty = DLookup("[Quantity]", qryQCSheet)

If qty >= 2 And qty <= 8 Then
lblNoIns.Caption = 2
Exit Sub
End If
If qty >= 9 And qty <= 15 Then
lblNoIns.Caption = 3
Exit Sub
End If
If qty >= 16 And qty <= 25 Then
lblNoIns.Caption = 5
Exit Sub
End If
If qty >= 26 And qty <= 50 Then
lblNoIns.Caption = 8
Exit Sub
End If


End Sub


It's just the defining of the qty variable that it gets stuck on, the rest is all fine. Any ideas?

Many thanks.

 

[KenHigg]

It looks like your dlookup() is not formed correctly...

???

 

[TomJamieson]

I've tried forming it in all the different ways that I can see it explained but nothing works. If anyone else thinks this is the problem, do you wanna tell me how it should be?

 

[KenHigg]

You need a the name of the field you want it to return (say fld1), the name of the table where you want it to look (say tbl1) and an expression to test on (say where fld2 = 'abc' where fld1 and fld2 are both fields in tbl1)

So this would be something like:

dlookup("[fld1]", "tbl1", "fld2 ='abc'")

Hope this helps...

 

[TomJamieson]

Nope

I get this error on the dLookup line:

Run-time error '2001':

You canceled the previous operation.

 

[KenHigg]

If we can back up a bit...

So you have a form where the user types in a record number. Then you press a button that opens/prints a report that displays information on this one record. Is this correct so far?

 

[TomJamieson]

That's right. The information displayed in the report is a mixture of fields from two tables, that are gained using a query.

 

[KenHigg]

And one of the fields available in one of the two tables is 'Quantity'? So instead of displaying the actual value in this field, you want display other information as in the example you gave in your first post?

 

[TomJamieson]

Yep. Quantity is one of the fields. I want the qty variable in my vba module to equal the quantity of the chosen record, so that I can then use it in the rest of my code, as shown in my first post.

 

[KenHigg]

Can you do this in the underlying query? Like:

MyNewFld: iif([quantity]<9,2,iif([quantity]<16,3,iif([quantity]<26,5,8)))

And simply use this new dynamic field in your report?

 

[Mile-O]

You should not be using the Report_Open event, for a start, but anoterh. On Data, if I remember correctly, seems more appropriate as the Open events occur before data has been loaded into the report/form

Also, regarding your lengthy code, this can be rewritten thus:

Select Case qty
Case Is >= 50
  Me.lblNoIns.Caption = 0
Case Is >= 26
  Me.lblNoIns.Caption = 8
Case Is >= 16
  Me.lblNoIns.Caption = 5
Case Is >= 9
  Me.lblNoIns.Caption = 3
Case Is >= 2
  Me.lblNoIns.Caption = 2
Case Else
  Me.lblNoIns.Caption = 0
End Select

 

[TomJamieson]

Can you do this in the underlying query? Like:

MyNewFld: iif([quantity]<9,2,iif([quantity]<16,3,iif([quantity]<26,5,8)))

And simply use this new dynamic field in your report?

Good idea. I'm on my lunch now but I'll try it and post back and let you know in an hour or so.

Thanks

 

[Bodisathva]

an observation:

The criteria expression within DLookup is optional, so if it is omitted, the statement DLookup("[Quantity]", qryQCSheet) should return the contents of [Quantity] from the first record it encounters. The fact that you are getting nothing seems to indicate a problem with the recordset as opposed to the statement itself...parameter query that is not initialized in this context? A query that is filtered by a field on another database object which is now closed and therefore the query statment is no longer valid?