gstylianou
Registered User.
- Local time
- Today, 14:35
- 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
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