Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-25-2019, 09:56 AM   #1
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
DLookup with criteria into vba

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

gstylianou is offline   Reply With Quote
Old 10-25-2019, 10:05 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,410
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DLookup with criteria into vba

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
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 10-25-2019 at 10:40 AM.
theDBguy is online now   Reply With Quote
Old 10-25-2019, 10:09 AM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,418
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: DLookup with criteria into vba

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?

plog is offline   Reply With Quote
Old 10-25-2019, 10:09 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DLookup with criteria into vba

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gstylianou (10-25-2019)
Old 10-25-2019, 10:13 AM   #5
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,506
Thanks: 439
Thanked 837 Times in 808 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: DLookup with criteria into vba

@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?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 10-25-2019 at 12:11 PM.
Gasman is online now   Reply With Quote
Old 10-25-2019, 10:24 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DLookup with criteria into vba

mr.gasman, is that for me? remember he is only searching on Same table.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gstylianou (10-25-2019)
Old 10-25-2019, 10:27 AM   #7
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
Re: DLookup with criteria into vba

Quote:
Originally Posted by arnelgp View Post
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
Attached Images
File Type: jpg error.JPG (90.6 KB, 5 views)

gstylianou is offline   Reply With Quote
Old 10-25-2019, 10:29 AM   #8
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
Re: DLookup with criteria into vba

Quote:
Originally Posted by arnelgp View Post
mr.gasman, is that for me? remember he is only searching on Same table.
Yeap, correct..!!
gstylianou is offline   Reply With Quote
Old 10-25-2019, 10:33 AM   #9
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
Re: DLookup with criteria into vba

Quote:
Originally Posted by theDBguy View Post
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
gstylianou is offline   Reply With Quote
Old 10-25-2019, 10:38 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,410
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: DLookup with criteria into vba

Quote:
Originally Posted by gstylianou View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-25-2019, 11:19 AM   #11
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
Re: DLookup with criteria into vba

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
gstylianou is offline   Reply With Quote
Old 10-25-2019, 12:09 PM   #12
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,506
Thanks: 439
Thanked 837 Times in 808 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: DLookup with criteria into vba

Quote:
Originally Posted by arnelgp View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 10-25-2019 at 12:27 PM.
Gasman is online now   Reply With Quote
Old 10-25-2019, 09:11 PM   #13
gstylianou
Newly Registered User
 
Join Date: Dec 2013
Posts: 218
Thanks: 40
Thanked 5 Times in 5 Posts
gstylianou is on a distinguished road
Re: DLookup with criteria into vba

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

gstylianou is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Help with Dlookup with 3 criteria lookforsmt Modules & VBA 19 09-16-2019 11:40 AM
DLookUp as criteria within DLookup chuckcoleman Modules & VBA 15 04-10-2019 11:39 AM
Help With Dlookup Criteria prashant2002 General 12 09-10-2010 08:02 AM
dlookup - no criteria deekras Modules & VBA 3 10-27-2003 07:33 AM
DLookup with 2 criteria. hooi General 5 10-22-2003 01:01 AM




All times are GMT -8. The time now is 10:22 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World