DLookup code not working in VBA

[abzalali]

Dear All,
When I write this code then it shows in Red Color, Pleas help me.

ElseIf DLookup("BinNumber", "tblGoingOut", "[Status]='"&Orders Not Received &"'") > 0 Then
        MsgBox "Bin already Going Out but not received"

Thanks
Mir

 

[WayneRyan]

Mir,

ElseIf DLookup("[BinNumber]", "tblGoingOut", "[Status]='" & [SIZE="4"][[/SIZE]Orders Not Received[SIZE="4"]] [/SIZE]& "'") > 0 Then
        MsgBox "Bin already Going Out but not received"

Wayne

 

[abzalali]

Dear WayneRyan,

It shows Run-time error 2465

Please help me.

 

[WayneRyan]

Mir,

What is [Orders Not Received] ?

Are [Status] and [BinNumber] in tblGoingOut ?

Wayne

 

[abzalali]

Wayne,

Table Name: tblGoingOut

[BinNumber] and [Status] are Field (column)

"Orders Not Received" is [Status] field value.

Thanks
Mir

 

[WayneRyan]

Mir,

If 'Orders Not Received' is a constant, then:

ElseIf DLookup("[BinNumber]", "tblGoingOut", "[Status]='Orders Not Received'") > 0 Then
        MsgBox "Bin already Going Out but not received"

DLookup returns only 1 value (at best).

If none are found, try:

ElseIf Nz(DLookup("[BinNumber]", "tblGoingOut", "[Status]='Orders Not Received'"), 0) = 0 Then
   MsgBox "Bin ..."

Or, Maybe you just want to do a DCount.

Need more info.

Wayne

 

[WayneRyan]

Mir,

The DLookUp will return one value:

DLookup("[BinNumber]", "tblGoingOut", "[Status]='Orders Not Received'")

If the criteria is not met it will return a Null. Not good for VBA, which is
why I used the NZ function to force a value.

If you just want to check if *any* rows match the criteria, use a DCount.

If DCount("[BinNumber]", "tblGoingOut", "[Status]='Orders Not Received'") > 0 Then
   MsgBox("There are records.")
Else
   MsgBox("There are no records.")
End If

Wayne

 

[abzalali]

Wayne,
I'm really sorry that I distrub another more:
When I enter a BinNumber which Status is "Orders Not Received" then open the report

And when I enter BinNumber which Status is "Received" then also open the report

I really don't understand the matter.

    ElseIf DCount("[BinNumber]", "tblGoingOut", "[Status]='Orders Not Received'") > 0 Then
        DoCmd.OpenReport "rptLabelRe-Print", acPreview
        DoCmd.ShowToolbar "Ribbon", acToolbarYes
    Else
        MsgBox "No data found", vbExclamation, "Bin Number"
    End If

if possible, please help me.

Mir

 

[WayneRyan]

Mir,

The DCount will just return how many BinNumbers have the status
of 'Orders Not Received'. If there are ANY, then it will open
the report.

You say "And when I enter BinNumber which Status is "Received" then also open the report".

Any number that you may have entered IS NOT being referenced in the DCount.

What is it that you want to do after you've entered a BinNumber?
Where are you entering the BinNumber?

Wayne

 

[abzalali]

Wayne,

I entered "BinNumber" in a Form 'textbox', then I clicked a command button to preview report, and the 'textbox' is also using as report criteria. Report data source "tblGoingOut", BinNumber is field of table.

Thanks
Mir

 

[WayneRyan]

Mir,

It's been a long week. If BinNumber is a string, this should be what you want.
It it isn't remove the single-quotes.

DCount("[BinNumber]", "tblGoingOut", "[Status] = 'Orders Not Received' And [BinNumber] = '" & Me.txtBox & "'")

Wayne