How to open a record that has two keys, one is a Text and the other is a Number

[bell]

Hi all,



This is probably related to my last question...
I had a database working with two keys, both numbers (Part Number and Serial Number), but I had to change Part Number to Text to allow for dashes

I was using the following code to find a specific record (it finds the last Serial Number I have in the database for a particular Part Number) but my code now does work when I have the Part Number as a Text (I get the error: Data type mismatch in criteria expression)

Private Sub Last_Record_Click()On Error GoTo Err_Last_Record_Click


    Dim criteria As String
    Dim lastSerial As Integer
    
    lastSerial = Me![Serial Number]
    
    criteria = "[Part Number]=" & Me![Part Number] & " AND [Serial Number]=" & lastSerial
    
    Do While (DCount("*", "Route Table", [critieria]) = 1)
        lastSerial = lastSerial + 1
        criteria = "[Part Number]=" & Me![Part Number] & " AND [Serial Number]=" & lastSerial
    Loop
    
    lastSerial = lastSerial - 1
    criteria = "[Part Number]=" & Me![Part Number] & " AND [Serial Number]=" & lastSerial
    Me.Form.Recordset.FindFirst [criteria]
    Me.[Operation Form].Form.Recordset.FindFirst [critieria]
    Me.SetFocus


Exit_Last_Record_Click:
    Exit Sub


Err_Last_Record_Click:
    MsgBox Err.Description
    Resume Exit_Last_Record_Click

In my last question, the mod posted:
Parameters for text field need apostrophe delimiters, date fields use #, numbers don't have delimiters.

So I am assuming the problem is because my Part Number is a Text and my Serial Number is a Number

I tried doing

criteria = "[Part Number] = ' " & Me![Part Number] & " ' " & " AND [Serial Number]=" & lastSerial

but that also doesn't work

[pbaldy]

Try

criteria = "[Part Number] = '" & Me![Part Number] & "' AND [Serial Number]=" & lastSerial

[ItsMe]

Edit: I was looking at the wrong variable, nevermind

The following is only good for numbers -32,000 to 32,000 (give or take several hundred)
Dim lastSerial As Integer

So maybe
Dim lastSerial As String

[ranman256]

1st yes, data type error means youre trying to put a string in a number field.

2nd Dont use FINDFIRST , just open a query , say qsFindData. in it is your where clause.
select * from [Route Table] where [Part Number]=" & Me![Part Number] & " AND [Serial Number]=" & lastSerial

IF you have lastSerial defined as number in the table, and part = text in the table.

[CJ_London]

try removing the me!

if you want to use me, it should be me.

also, tho' not sure if it is relevant

Me.[Operation Form].Form.Recordset.FindFirst [critieria]

has a typo - and you shouldn't need the square brackets

[ranman256]

you cant use ME in a query. you must use the entire path of forms..
forms!frmMyForm!partnum

[bell]

you cant use ME in a query. you must use the entire path of forms..
forms!frmMyForm!partnum

Thanks for the reply

I am close to getting the solution I want

I got the code down to just this

DoCmd.OpenForm "Route and Rework Form", , , "[Part Number]='" & Forms![Route and Rework Form]![Part Number] & "'" & " AND [Serial Number]= (SELECT Max([Serial Number]) FROM [Route Table] WHERE [Part Number]='" & Forms![Route and Rework Form]![Part Number] & "')"

So if I have the form "Route and Rework Form" opened on a particular Part Number and Serial Number, and I click a this button, it will open up the form to the last Serial Number for that given Part Number...
Which is good, but I wanted it to just bring focus to the last Serial Number, but still have the other records with the same Part Number still opened, so I could potentially switch back to them...

How can I do this?

[bell]

Never mind, I ended up doing this

lastSerial = DMax("[Serial Number]", "[Route Table]", "[Part Number]='" & Forms![Route and Rework Form]![Part Number] & "'")
    Call DoCmd.GoToRecord(acDataForm, "Route and Rework Form", acGoTo, lastSerial)

This finds the max serial number for that part number, and opens the form to that record