Enforced data type change causes problem with searching 2

[Robbo1974]

I have been forced to change the data type of a field which I use to find records on a subform (too complicated to explain, but not uncommon for my employer to change their mind about what they want) from number to text to allow the use of alphanumeric codes. This has now produced a problem in searching the database.

The field in question lives in a table called tblOrders1 and holds the (manually generated, again dont ask) order number for that order ([OldOrderNumber]). The information from this table is displayed in a subform, which is on a mainform which holds client contact information.

My search function runs from a form launched from my switchboard (frmFindOrder) which prompts for an order number and then opens the mainform at the client record which 'owns' that order. When I was just using numbers things were fine but now I have to use the format R### or T### or C### for order numbers depending on what the order is for.

This code worked fine before:

Public....

dim intCustomer as integer
dim intOrderNum as integer

intOrderNum = me!txtOrderNum 'The control on the search form

intCustomer = DLookup("CustomerID","tblOrders1",&_
"OldOrderNumber =" & intOrderNum

DoCmd.OpenForm "frmContactInformation",,,"[CustomerID] =" & intCustomer

DoCmd.Close acForm, "frmFindOrder"

the sub continues with a section to move the subform to the right record.

This code now breaks instantly producing the following error if I try to find order T66 say:

Type Mismatch

Obviously I've got some sort of syntax error but I'm buggered if I can find it - something to do with the quotes in the DLookup expression I think. I do need some help with this one - if you've got any ideas I'll be happy to hear them.

Robbo ;-)

 

[Maquis]

if your ordernumber is now a text field then you definitely have a type mismatch since you are still using an integer variable to search it.

You need to change your intOrderNum variable to a string variable and also put some quotes into your dlookup statement.


intCustomer = DLookup("CustomerID","tblOrders1",&_
"OldOrderNumber ='" & strOrderNum & "'"
Maq B-)
<insert witty signature here>

 

[Robbo1974]

Oops, pasted the old code! Have changed to a string variable but wasn't sure which quote arrangement! Anyhow, that produces a very cryptic error message:

2471 The expression you entered as a query parameter produced this error: 'The object doesn't contain the automation object 'OrderNumber.&quot;

When I tried myself earlier I got the same error but with 'OrderNumber' above being replaced by the value I was searching on.

The quotes on the error message are exactly as above - seems a bit odd! Robbo ;-)

 

[Robbo1974]

BTW, the star was an accident - no offence! ;-) Robbo ;-)

 

[Maquis]

Well, let's see if I can earn the star.

Where is this code located? Is it in the form that contains the txtOrderNum textbox?

If so double check the spelling of all your field and table names. A query parameter error seems to suggest that Access cannot find a field that you have referenced. Most common reason would be a typo somewhere.

If none of the above is helpful, post your code again here and highlight the line giving you the error and perhaps something will &quot;jump out&quot; at me. Maq B-)
<insert witty signature here>

 

[Robbo1974]

Field and table names are all fine, the code worked perfectly before the change of data type.

Heres the code, it does run from the search form (on clicking a command button):

Public Sub Command0_Click()

On Error GoTo err_command0_click

Dim intCustomer As Integer
Dim strOrderNum As String

strOrderNum = Me.txtOrderNum

intCustomer = DLookup("CustomerID", "tblOrders1", "OrderNumber='" & strOrderNum & "'")

DoCmd.OpenForm "frmContactInformation", , , "[CustomerID]=" & intCustomer

DoCmd.Close acForm, "frmFindOrder"

Forms![frmContactInformation].frmOrders1subform.RecordsetClone.FindFirst "[OrderNumber]=" & strOrderNum

Forms![frmContactInformation].frmOrders1subform.Bookmark = Forms![frmContactInformation].frmOrders1subform.RecordsetClone.Bookmark

GoTo Exit_command0_click

err_command0_click:

'err 94 is improper use of null value
If Err.Number = 94 Then

MsgBox "There is no order corresponding to the number you have entered." & vbCrLf & vbCrLf & "Please check and try again.", vbExclamation, "Order Number Not Found"

Me!txtOrderNum.Value = Null

Me!txtOrderNum.SetFocus

Else:

MsgBox Err.Number & " " & Err.Description

End If

Exit_command0_click:

End Sub

Am dead confused me, it should work. It breaks on the DLookup action for some reason, I have a gut feeling that somewhere along that line I have one of those silly little syntax/punctuation errors that you need somone else to spot!

Thanks for helping,

Robbo ;-)

 

[Maquis]

Hmmm, looking at your Dlookup command I don't see anything wrong with the syntax in it, but I know what you mean about a 2nd pair of eyes. After staring at code for 3 hours I can't spot syntax errors to save my life.

Some things to check:

Is CustomerID an integer field in the table? If it's now a text field then assign it to a string.

&quot;OrderNumber&quot; is a text field in the table, right?

The table is named &quot;tblOrders1&quot;, correct?

Honestly, those are the only things I see that could possibly be wrong with the syntax. You've already said that you double-checked the spelling of the field names and since this exact code worked before the data type changes the error must be something to do with data types. If all else fails try this code on the Dlookup. It shouldn't make a difference, but you never know.

Const dQuote = &quot;&quot;&quot;&quot; 'that's 4 double quotes
Dim intCustomer As Integer
Dim strOrderNum As String
Dim Criteria as string

strOrderNum = Me.txtOrderNum
Criteria = &quot;OrderNumber = &quot; & dquote & strOrderNum & dquote
intCustomer = DLookup(&quot;CustomerID&quot;, &quot;tblOrders1&quot;,Criteria)

Maq B-)
<insert witty signature here>

 

[CosmoKramer]

Robbo,

Check out the variable you are assigning the DLookup to. It should be a string....


Dim strCustomer As String
Dim strOrderNum As String

strOrderNum = Me.txtOrderNum

strCustomer = DLookup(&quot;CustomerID&quot;, &quot;tblOrders1&quot;, &quot;OrderNumber='&quot; & strOrderNum & &quot;'&quot

 

[Robbo1974]

Cosmo, this just produces the same error. [CustomerID] in the underlying table is number data type anyway, but anything's worth a try.

Maq, everything is as you say it is. The change to the code produces the same error.

Thanks for persevering, Robbo ;-)

 

[Maquis]

What error is it currently giving you on the Dlookup line? Maq B-)
<insert witty signature here>

 

[Robbo1974]

2471 The expression you entered as a query parameter produced this error: 'The object doesn't contain the automation object 'OrderNumber.&quot;

strOrderNumber is assigning fine, as soon as I try to step the procedure on it breaks with the message above. Robbo ;-)

 

[Maquis]

Well, I think the issue is going back to just checking spelling errors and field name errors. That error implies that OrderNumber is not a field in your table, &quot;tblOrders&quot;. Maq B-)
<insert witty signature here>

 

[Robbo1974]

D'oh! The field has been newly renamed OldOrderNumber. I bin spending too long at the screen....

Works perfect now, thanks for your help. Some days I just need hitting with something heavy and blunt.... Robbo ;-)