DLookup with criteria into vba (1 Viewer)

gstylianou

Registered User.
Local time
Today, 15:41
Joined
Dec 16, 2013
Messages
357
Good Evening,

I'm trying to write a DLookup code with AND criterion but unfortunately i cannot. The code must look into Order table if the Supplier_id code exist, and then, to gives the OrderID related to the supplier_id with Status: 4 only.

The second problem after the above issue, is I would like to return the OrderID corresponding to the above criteria.

Please note:
1. The OrderID (into Order table) is Autonumber field
2. the Status is number (Integer / fixed format)
3. The Supplier_id is also number (Long Integer)


Private Sub txtOrder_Click ()

Dim SubID As String
Dim OrderStatus As String
Dim OrderCode As String


If Me.Supplier_id = DLookup ("Supplier_id", "ORDERS", "[Supplier_id] =" & Supplier_id AND DLookup ("OrderStatus", ORDERS "," [OrderStatus] = "& 4) Then

MsgBox "Found the Supplier" & Me.Supplier_id
SubID = Me.Supplier_id
OrderCode = DLookup ("OrderID", ORDERS "," [OrderStatus] = "& 4)

DoCmd.OpenForm "Orders", acNormal,, "OrderID =" & OrderCode

Else
MsgBox "Not Found the Supplier" & Me.Supplier_id

End If

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess, but how about?
Code:
If Me.Supplier_id = DLookup ("Supplier_id", "ORDERS", "[Supplier_id] ="  & Supplier_id _
    & " AND OrderStatus='" & DLookup ("OrderStatus", "ORDERS","[OrderStatus] = 4") & "'" Then
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:41
Joined
May 11, 2011
Messages
11,613
If your american, you can't just throw hail maries of code from your goaline and hope it works for a touchdown. If your English, you can't just have your keeper always boot code up field as hard as he can and hope it goes in.

In all cases, including coding, you have to get soemthing working and add to that until you reach the goal. You've thrown a ton of code with a ton of different errors and error types at Access and us and are asking for help. Here's that help:

Throw all your code away and get that first Dlookup to work. What is it returning? Is it even returning anything?
hint #1: Syntatically its incorrect--count your left and right parenthesis
hint #2: Logically, why look up supplier id when you know the supplier id?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,175
Code:
Dim strValue As String
strValue = Nz(DLookup ("Supplier_id & '/' OrderCode", "ORDERS", "[Supplier_id] =" & Supplier_id & " AND [OrderStatus] = 4"), "")
If Len(strValue)>0 Then

MsgBox "Found the Supplier" & Me.Supplier_id
SubID = Trim(Split(strValue, "/")(0))
OrderCode = CLng(Trim(Split(strValue, "/")(1)))

DoCmd.OpenForm "Orders", acNormal,, "OrderID =" & OrderCode

Else
MsgBox "Not Found the Supplier" & Me.Supplier_id

End If

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:41
Joined
Sep 21, 2011
Messages
14,052
@gstylianou

I do not think that is going to do what you think it is?

I *think* it will find a record for your supplierID and then find a record for Orderstatus = &4.

The two might well not be linked.?, ie you will get some orderstatus=&4 linked to another supplierID? :confused:
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,175
mr.gasman, is that for me? remember he is only searching on Same table.
 

gstylianou

Registered User.
Local time
Today, 15:41
Joined
Dec 16, 2013
Messages
357
Code:
Dim strValue As String
strValue = Nz(DLookup ("Supplier_id & '/' OrderCode", "ORDERS", "[Supplier_id] =" & Supplier_id & " AND [OrderStatus] = 4"), "")
If Len(strValue)>0 Then

MsgBox "Found the Supplier" & Me.Supplier_id
SubID = Trim(Split(strValue, "/")(0))
OrderCode = CLng(Trim(Split(strValue, "/")(1)))

DoCmd.OpenForm "Orders", acNormal,, "OrderID =" & OrderCode

Else
MsgBox "Not Found the Supplier" & Me.Supplier_id

End If

End Sub


Dear arnelgp first of all i would like to thank you very much for your help. But, still i have error (see picture). Can you please check it?

Thanks again
 

Attachments

  • error.JPG
    error.JPG
    90.6 KB · Views: 78

gstylianou

Registered User.
Local time
Today, 15:41
Joined
Dec 16, 2013
Messages
357
Hi. Just a guess, but how about?
Code:
If Me.Supplier_id = DLookup ("Supplier_id", "ORDERS", "[Supplier_id] ="  & Supplier_id _
    & " AND OrderStatus='" & DLookup ("OrderStatus", ORDERS "," [OrderStatus] =  "& 4) & "'" Then

theDBguy after than (if it works for me) i must take the OrderID from the Table ORDERS which the criteria are correct
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:41
Joined
Oct 29, 2018
Messages
21,358
theDBguy after than (if it works for me) i must take the OrderID from the Table ORDERS which the criteria are correct
Hi. Glad to hear you got it fixed. Sorry for the mistake. Good luck with your project.
 

gstylianou

Registered User.
Local time
Today, 15:41
Joined
Dec 16, 2013
Messages
357
My friends, I want to make clear what I need to do

1. The first issue is to check into ORDER table if an order with Supplier_id has already been created so that I don't create a new order for the same supplier.

2. All created orders in the ORDER table have an Order Status so that to know which ones have been completed (OrderStatus: 1), or which ones are still open (OrderStatus: 4) etc.

3. After the above checking I need to know which OrderID still open (OrderStatus: 4) for the specific Supplier (Supplier_id) so to use it for completion.

I would like to emphasize that:

1. Since a supplier may have more than one (Open: 4) order which the user has not closed (Completed: 1) I would like to use the oldest OrderID of the current supplier with Status: 4. If for example there are two open orders for the supplier A (OrderID: 10, OrderID: 5) I would like to use the order with the smallest ID so that no old IDs remain open.

I wish I became more understanding
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:41
Joined
Sep 21, 2011
Messages
14,052
mr.gasman, is that for me? remember he is only searching on Same table.

No arnelgp, the o/p. I've edited my post to make it clearer. Apologies for the confusion.
I can see that you got in before me and corrected the o/p's code
 
Last edited:

gstylianou

Registered User.
Local time
Today, 15:41
Joined
Dec 16, 2013
Messages
357
For those interested, I solved the problem as follows

Private Sub txtOrder_Click()

Dim SubID As String
Dim OrderStatus As String
Dim OrderCode As String
Dim conStr As String

' Check if the order exist
If (DLookup("Supplier_id", "CONSUMABLE ORDERS", "[Supplier_id]=" & Supplier_id & " AND [OrderStatus]= 4")) Then
conStr = 1
MsgBox "conStr" & conStr
Else
conStr = 0
MsgBox "conStr" & conStr

End If


If MsgBox("Would you like to order this product?", vbYesNo Or vbQuestion, "Order Product") = vbYes Then

If conStr = 1 Then
OrderCode = DLookup("Supplier_id", "CONSUMABLE ORDERS", "[Supplier_id]=" & Supplier_id & " AND [OrderStatus]= 4")

If Me.txtOrder = "Order Now" Then
Me.Grabb = True
Me.Order = True

'Create the order
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qryAutoOrder"
'DoCmd.SetWarnings True

'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qryAutoOrderDetails"
'DoCmd.SetWarnings True
DoCmd.OpenForm "Orders", acNormal, , "OrderID =" & OrderCode
Else
If IsNull(Me.txtOrder) Or Me.txtOrder = "" Then
MsgBox "The desired stock for this item is correct", vbCritical, "Order Fail"

End If
End If

Else
If conStr = 0 Then

If Me.txtOrder = "Order Now" Then
Me.Grabb = True
Me.Order = True

'Create the order
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qryAutoOrder"
'DoCmd.SetWarnings True

'DoCmd.SetWarnings False
'DoCmd.OpenQuery "qryAutoOrderDetails"
'DoCmd.SetWarnings True
DoCmd.OpenForm "Orders", acNormal, , "OrderID =" & OrderCode
Else
If IsNull(Me.txtOrder) Or Me.txtOrder = "" Then
MsgBox "The desired stock for this item is correct", vbCritical, "Order Fail"
End If
End If


End If
End If

Else
DoCmd.CancelEvent
End If
End Sub
 

Users who are viewing this thread

Top Bottom