I am getting a Run-time error 13: Type-mismatch

[jlathem]

I am getting a Run-time error 13: Type-mismatch with A Better Mouse Trap code - Help

Hi,

I am getting a Run-time error 13: Type-mismatch on the section of code below. The code that is in RED below is what the error is highlighting.

ghudson pointed me to his code (A Better Mouse Trap? sample) but I am getting the error and don’t know enough about VBA to get past this issue.

Can anyone help me?

Thanks,
James

[SIZE=3][FONT=Times New Roman]Private Sub bSave_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]On Error GoTo Err_bSave_Click[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  Me.tbHidden.SetFocus[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  If IsNull(Year) Or IsNull(Week_Number) Or IsNull(Payee_ID) Or IsNull(Payee_Name) Or IsNull(Billed_Amount) Or IsNull(Requested_Amount) Or IsNull(Calc_Pmt_Amount) Or IsNull(Assigned) Or IsNull(Email_Receipt_Date) Or IsNull(Form_Tracking_Number) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Beep[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  Beep[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & "  Yes:         Saves Changes" & vbCrLf & "  No:          Does NOT Save Changes" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Case vbYes: 'Save the changes[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Me.tbProperSave.Value = "Yes"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          DoCmd.RunCommand acCmdSaveRecord[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      Case vbNo: 'Do not save or undo[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          'Do nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      Case vbCancel: 'Undo the changes[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          DoCmd.RunCommand acCmdUndo[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          Me.tbProperSave.Value = "No"[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      Case Else: 'Default case to trap any errors[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          'Do nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  End Select[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Exit_bSave_Click:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Exit Sub[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Err_bSave_Click:[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  If Err = 2046 Then 'The command or action Undo is not available now[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][B][COLOR=red]      MsgBox Err.Number, Err.Description[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Resume Exit_bSave_Click[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  End If[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[Poppa Smurf]

You can not use a comma to append you must use & as in err.number & err.description or err.number & " " & err.description.

Try msgbox "Error Number " & err.number & "Description " & err.description. This is handy when debugging and it can be changed to a more useful display to the user by using a select case on the err.number.

 

[jlathem]

You can not use a comma to append you must use & as in err.number & err.description or err.number & " " & err.description.

Try msgbox "Error Number " & err.number & "Description " & err.description. This is handy when debugging and it can be changed to a more useful display to the user by using a select case on the err.number.

Hey Poppa Smurf

I can see why you are the “Pappa”! You are the MAN!!! Thank you so much for the help. It’s working with one little issue.

As you can tell, I am very new to VBA and learning so much every time I make a posting and get help from good people like you guys.

I changed all the lines that had the same code and it gets past the #13 error.

Do you have a second for a couple questions?

1. I notice 2 more MsgBox that has commas in the code and wondered if I should rewrite it as the others?

[SIZE=3][FONT=Times New Roman] Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Beep[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     MsgBox "There were no modifications made to the current record."[B][COLOR=red], vbInformation, "[/COLOR][/B]Invalid Undo"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] End If [/FONT][/SIZE]

2. I have a field called Payment_Year (combo box) that I am getting a Compile error: Variable not defined in this code. When I take the IsNull(Payment_Year) out of the code it works fine.

Is there an issue with the name or something?

[SIZE=3][FONT=Times New Roman] If IsNull([B][COLOR=red]Payment_Year[/COLOR][/B]) Or IsNull(Week_Number) Or IsNull(Payee_ID) Or IsNull(Payee_Name) Or IsNull(Billed_Amount) Or IsNull(Requested_Amount) Or IsNull(Calc_Pmt_Amount) Or IsNull(Assigned) Or IsNull(Email_Receipt_Date) Or IsNull(Form_Tracking_Number) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Beep[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     MsgBox "All required fields must be completed before you can save a record[COLOR=red][B].", vbCritical,[/B][/COLOR] "Invalid Save"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] End If[/FONT][/SIZE]

Any suggestions on how I can fix this?

Thanks again,
James

 

[Poppa Smurf]

James

You need code similar to this make sure you have Resume Exit as shown below.

Err_bSave_Click:
If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
Else
MsgBox "Error NUmber " & Err.Number & " Description " & Err.Description
Resume Exit_bSave_Click
End If

MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"

This the correct format, I use the following layout as it is easier to read the code
strmsg = "Surname is required"
msg_title = "Surname"
MsgBox strmsg, vbOKOnly + vbCritical, msg_title

At the start of the code I put Dim strmsg as string and Dim msg_title as String.

ISNULL problem
Check the field payment_year is spelt correctly on the form.

 

[jlathem]

Hey Poppa,

Thank you for your help. I can tell you are a seasoned VBA coder!

IsNull problem:
During some trouble earlier I changed the name of the control from Payment_Year to cmb_Payment_Year and forgot to change it back. After correcting the name the code worked perfect!

MsgBox
My question was on the MsgBox that had the ", vbInformation, "Invalid Undo" but I figured it out.

The , vbInformation gives the "i" inside the balloon on the box and the , "Invalid Undo" is the caption of the msgbox.

Every time I chat with you guys I learn a lot!

I have been at this computer of nearly 20 hours today. I think I am starting to make forgetful errors now.

Thank for all your help. Maybe one day I can return the favor.

Respectfully,
James

 

[Poppa Smurf]

I am glad to assist. I too started like you a novice and learnt a lot from forums like this from experts willing to share their experience. So now I am returning the favours and also learning at the same time.

 

[jlathem]

I understand completely.

I spent 20+ years hiring, training, and mentoring photographers before getting into the technical field. After learning BASIC on a TRS80 I started working in the Tech field and helpdesk.

At one point I picked up HTML and then some JavaScript and eventually web developing with ColdFusion. I went back to teaching the beginners and intermediate CF and we used Access as a backend to the web applications.

My boss thinks that because I can build a basic Access table I should be able to develop a DB using all the nice VBA code! Well, it has been a learning experience for sure. My first project is in its final testing and I will be putting a SQL backend on it in the next two weeks.

I am on my second project for the State of Georgia.

Maybe one day, and I look forward to that day, I can give back to the group like you are doing.

Take care and thank you again for all your assistance!

James