Type Mismatch

[raweber]

Hi, all

I'm working on my db for tracking studies. Some studies need a Work Order number and some don't. I'm working on a VBA button that will populate the W/O field with a number one higher than the highest W/O Number on record.

Working through it, I've set up a messagebox to make sure first that I've got the correct value, and then I'll tackle writing that value to the field fom VBA.

However, I'm getting a Type Mismatch Error when I try to run the routine. Here's the code:

Private Sub btnWorkOrderQry_Click()
On Error GoTo Err_btnWorkOrderQry_Click
    Dim MaxWONumber As Integer
    Dim NewWONumber As Integer
    Dim Msg As String
 
    MaxWONumber = "Max(StudyData.WorkOrderNumber) FROM StudyData"
    NewWONumber = MaxWONumber + 1
    Msg = CStr(NewWONumber)
    MsgBox (Msg)
 
Exit_btnWorkOrderQry_Click:
    Exit Sub
 
Err_btnWorkOrderQry_Click:
    MsgBox Err.Description
    Resume Exit_btnWorkOrderQry_Click
 
End Sub

The field from the query is a "Number" field. Any help appreciated.

Thanks, Rob

p.s. Is there any way to stop the debugger without exiting the VBA window?

 

[pbaldy]

Try

MaxWONumber = Nz(DMax("WorkOrderNumber", "StudyData"), 0)

 

[raweber]

Thanks, Paul - I have no idea what you just did, but it worked!

Rob

 

[pbaldy]

Happy to help. The type mismatch was because you were trying to set a numeric variable to a text/string value. What you had inside the string wouldn't have worked anyway. To get a value from a table you'd either need to open a recordset or use a domain aggregate function like DMax().