Using If statement with Dlookup based on form entries

[dmeyring]

I have a form that auto-populates 10 fields using a Dlookup in the got focus event of each field. The dlookup searches the "DNM TEST Form Query" query based on two criteria: Sales Order # and SKU. This works perfectly, but... the end user would also like 5 of the fields to autopopulate when only SKU is entered. If only SKU is entered, I have to dlookup using only SKU as criteria from a different query- "Product Info Query".

I'm trying this as an If statement on the first autopopulate field only, but it's not working - I get no autopopoulate results and no error message.
Here's the code I'm trying:

Private Sub Pattern_Name_GotFocus()

If (Not (IsNull(Sales_Order)) Or (Sales_Order <> "")) Then
Me.Pattern_Name = DLookup("[PATTERN]", "DNM TEST Form Query", "[Sales Order Number]=Forms![Backorder Inquiry Form]![Sales Order]And[SKU]=Forms![Backorder Inquiry Form]![SKU]")
Else
Me.Pattern_Name = DLookup("[Pattern]", "Product Info Query", "[SKU]=Forms![Backorder Inquiry Form]![SKU]")
End If

End Sub

 

[Remou]

I do not think the Got Focus event is quite right, have you considered the After Update event and the On Current event for the form?

'Assumes sales order is a text field, 
'use Nz if it is not.
'
If Trim(Sales_Order & "") <> "" Then
'Assumes that both SKU and Sales Order are numeric,
'use single quotes if they are not.
Me.Pattern_Name = DLookup("[PATTERN]", "DNM TEST Form Query", "[Sales Order Number]=" & Forms![Backorder Inquiry Form]![Sales Order] & " And [SKU]= " & Forms![Backorder Inquiry Form]![SKU])
Else
Me.Pattern_Name = DLookup("[Pattern]", "Product Info Query", "[SKU]=" & Forms![Backorder Inquiry Form]![SKU])
End If

 

[dmeyring]

SKU is text, Sales Order is numeric. I get some compile errors with the first line of the If statement, by trial and error I am at:

If (Trim(Sales_Order&("")) <> "") Then

I receive a "Compile error: Type-declaration character does not match declared data type" which I'm guessing would refer to the text/numeric issue. However, when I try to change from the double quotes to single quotes I get a compile error: Expected: expression

 

[PHV]

SKU is text

Me!Pattern_Name = DLookup("Pattern", "[Product Info Query]", "SKU='" _
 & Replace(Forms![Backorder Inquiry Form]![SKU], "'", "''") & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

How about:

'Assumes sales order is a text field, 
'[b]use Nz if it is not[/b].
'
If Nz(Sales_Order,0) <> 0 Then
'Assumes that both SKU and Sales Order are numeric,
'use single quotes if they are not.
Me.Pattern_Name = DLookup("[PATTERN]", "DNM TEST Form Query", "[Sales Order Number]=" & Forms![Backorder Inquiry Form]![Sales Order] & " And [SKU]= [red]'[/red]" & Forms![Backorder Inquiry Form]![SKU] [red]& "'"[/red])
Else
Me.Pattern_Name = DLookup("[Pattern]", "Product Info Query", "[SKU]=[red]'[/red]" & Forms![Backorder Inquiry Form]![SKU] [red]& "'"[/red])
End If

 

[dmeyring]

Also had a question for you on the Event type - would I put all of the Dlookups in the After Update event of the SKU field? We would want the field to autopopulate once SKU has been entered. I currently have this code in the On Exit of the SKU field, so that once SKU is updated and exited, it sets focus to each of the fields for autopopulate, then the got focus event for each one is triggered:

Private Sub SKU_Exit(Cancel As Integer)

Me.Pattern_Name.SetFocus
Me.Color1.SetFocus
Me.Order_Date1.SetFocus
Me.Account1.SetFocus
Me.Account_Name1.SetFocus
Me.Mill1.SetFocus
Me.Contact_Resp1.SetFocus
Me.Buying_Trend1.SetFocus
Me.Country_of_Origin1.SetFocus
Me.Default_Whse1.SetFocus
Me.Source_of_Call.SetFocus

End Sub

Would it be better to use the AfterUpdate for all of the dlookups?

Thank you so much for your help- I am so new to this-

 

[Remou]

I think it would be better to put all the DLookups in an After Update event. If the Got Focus event of a control changes its contents, it can be quite annoying.

 

[dmeyring]

Hmm, I think my problem may lie outside of the syntax. I have been testing the suggestions above, but am still getting no data when I only enter a SKU #

Test 1- I started by going back to basics and changing all of the original Dlookups (based on two criteria) to the After Update event of the SKU field. This then worked beautifully to populate all fields.
Test 1 Dlookup:
Me.Pattern_Name = DLookup("[PATTERN]", "DNM TEST Form Query", "[Sales Order Number]=Forms![Backorder Inquiry Form]![Sales Order]And[SKU]=Forms![Backorder Inquiry Form]![SKU]")

Test2- Removed the first Dlookups entirely and tested my second Dlookup (based on one criteria and a different query) in the same way as the first Dlookup. It does not work and doesn't give me any error message - this might be my problem.
Test 2 Dlookup:
Me.Pattern_Name = DLookup("[Pattern]", "Product Info Query", "[SKU]=" & Forms![Backorder Inquiry Form]![SKU])

Test 3- I tried the If statement again using the Dlookup from test 1 as the first part of the statement and the Dlookup from test 2 and the second part.
Test 3 statement:
If ((Nz(Sales_Order, 0)) <> 0) Then
Me.Pattern_Name = DLookup("[PATTERN]", "DNM TEST Form Query", "[Sales Order Number]=Forms![Backorder Inquiry Form]![Sales Order]And[SKU]=Forms![Backorder Inquiry Form]![SKU]")
Else
Me.Pattern_Name = DLookup("[Pattern]", "Product Info Query", "[SKU]='" & Forms![Backorder Inquiry Form]![SKU] & "'")
Endif

If I only enter a SKU, nothing happens. If I enter a Sales Order # and a SKU, I get a 3184 Run-time error that can't find the linked table used in the first part of the statement - which is the same part of the statement from Test 1 which actually worked.

I'm sorry to be so long-winded in this, but why would that be? Why would the first Dlookup work on it's own,but not as part of an if statement?