Problem with understanding Type mismatch error?

Hi all

I am counting the days until Tsjallie returns from his vacation (He helps me on this project). In the mean time I am wondering if anyone can help me in my understanding of the Type mismatch Error? I understand that I need to match txt and numbers. I kinda understand that the data needs to come from like tables. I am wondering why I get the error on a seemingly simple sub as below;

Screen Shot 2016-03-21 at 11.34.42 AM.png


Screen Shot 2016-03-21 at 11.35.19 AM.png

Maybe someone could suggest some reading that I could do that might clear it up in my head?

I will attach the file if you want to take a look?

A lot of us are not able to see images. Can you let us know which routine you are running when you get the error and on what line the error is.

gmr4evr1

Sorry I didn't know that, the red code is where the error type mismatch is and it happens when it tries to fill in data in a new Booking Form. Hope that helps, if not let me know and I will try again.

Private Sub txtPropIdSA_Change()

Dim tblProperties As ListObject
Dim tblGuestStays As ListObject
Dim RecIdx As Integer

      FormEventsEnabled = False 'To prevent firing control events and looping
      
      Set tblGuestStays = Worksheets("GuestStays").ListObjects("tblGuestStays")
      Set tblProperties = Worksheets("Properties").ListObjects("tblProperties")
      
      With tblGuestStays
      
      RecIdx = Application.Match(CDbl(Me.txtPropIdSA), .ListColumns("PropertyId").DataBodyRange, 0)

            Me.txtStayAmtSA = .ListColumns("MonthlyRent").DataBodyRange(RecIdx)
            Me.txtProAmtSA = .ListColumns("ProrateAmt").DataBodyRange(RecIdx)
            Me.txtDepAmtSA = .ListColumns("SecurityDeposit").DataBodyRange(RecIdx)
      End With
      
      FormEventsEnabled = True
      Set tblGuestStays = Nothing 'Clean up
      Set tblProperties = Nothing 'Clean up
      
End Sub

Getting warmer. You have a train-load of code in that workbook. I looked through it and can not find that code. Where exactly is it located and how is it triggered?

gmr4evr1

Its triggered from the module mdlESReservation Sub and Case below.

Sub HandleRightClick(ByVal Target As Range)
'---------------------------------------------------------------------------------------
' Procedure : HandleRightClick  (1)
' Author    : Tsjallie
' Date      : 10/25, 2015
' Purpose   : Handles rightclicking a cell in the calendar
' History   : 10/28, 2015: Overhaul existing code
'---------------------------------------------------------------------------------------

Starts here and has a few cases

Case Not Intersect(Target, ActiveSheet.ListObjects("tblCalendar").DataBodyRandg) Is Nothing

Don't give up on me, I am a an advanced beginner!lool!

It is triggered by picking vacant months in a property row and right clicking on it in the Calendar (Tsjallie Calendar) Sheet.

I hate to do this to you, but I get errors in other areas when I try to do things with your workbook (and there is A LOT going on in that workbook) so I can't even get to the error you are asking about. Maybe someone else can help, or you may have to wait until Tsjallie gets back.

gmr4evr1

Thanks for trying! I am not much help as I don't know how to strip it down! Do you know anyplace I can get information on matching code in that way that might help in my general understanding of VBA?

Thanks again!

You've declared RecIdx as Integer, if Application.Match fails it will return an error value.

So if Application.Match fails you will get a type mismatch because you are trying to assign an error value to an integer variable.

Try declaring RecIdx as Variant and add code to check for an error.

Norie


I will try that! Why is it that the number that shows when I hover over the match Id # is the correct Id number?

Thanks

Hard to tell without running the code and there is no sub named txtPropIdSA_Change in the workbook you uploaded.

Norie

Shoot I did remove for a bit but thought I attached version before I deleted sorry!

There's still a lot of stuff missing, not just code either as there appears to be a lot of missing (or misnamed/misspelled?) controls etc.

Also, in the sub txtPropIdSA_Change you have this,

         RecIdx = Application.Match(CDbl("mdlESReservation[PropertyId]"), .ListColumns("PropertyId").DataBodyRange, 0)

which is kind of different to the code you've posted here.

Norie

I was trying many things from many locations to match that Id number, that one didn't work and I forgot I didn't change it back to the original !:roll eyes:

Sorry let me clean thing up a bit and I will put up a new version soon!

No problem, try compiling (Debug>Compile VBAProject) it before uploading.

When you compile all the undeclared/misnamed controls/variables will be highlighted, one by one unfortunately, and you can deal with them.

Norie

Thanks, I was going to ask how I might clean up the mess!

Norie

Here is a cleaned up version

Thanks for any help!

Just had a quick look and I notice your property codes are alphanumeric, not numeric, eg x BR 7650.

In your code you are using CDbl on the textbox txtPropIdSA, CDbl is used to convert a text value to a double value, if possible.

If you use CDbl with an alphanumeric value it will return an error, try removing CDbl.

Something like this.

Private Sub txtPropIdSA_Change()
    '---------------------------------------------------------------------------------------
    ' Procedure :txtPropIdSA_Change
    ' Author    : Tsjallie/Rick
    ' Date      :3/21/2016
    ' Purpose   : To fill frmStayActivity Accounting frame
    ' History   : New Code
    '---------------------------------------------------------------------------------------
    '
Dim tblGuestStays As ListObject
Dim RecIdx As Variant

FormEventsEnabled = False    'To prevent firing control events and looping

    Set tblGuestStays = Worksheets("GuestStays").ListObjects("tblGuestStays")

    With tblGuestStays

        RecIdx = Application.Match(txtPropIdSA, .ListColumns("PropertyCode").DataBodyRange, 0)
        If Not IsError(RecIdx) Then
            Me.txtStayAmtSA = .ListColumns("MonthlyRate").DataBodyRange(RecIdx)
            Me.txtProAmtSA = .ListColumns("ProrateAmt").DataBodyRange(RecIdx)
            Me.txtDepAmtSA = .ListColumns("SecurityDeposit").DataBodyRange(RecIdx)
        End If
    End With

    FormEventsEnabled = True
    Set tblGuestStays = Nothing    'Clean up

End Sub

Note I've changed MonthlyRent to MonthlyRate as there's no MonthlyRent column in the table tblGuestStays.

Norie

Thanks will work on this in the AM!

You should declare RecIdx as Variant so if there is a problem with Application.Match the code would break.

Norie and Kalithro

Thanks for both your input. I have figured out what was causing the problem. I was calling the wrong sheet! the info exists on two sheets, but the propertyId I was matching was not on the sheet I was checking. This caused it to error 2042. Thats why I could see the right Id when I watch, but it still error or show type mismatch.

Would have never figured that without your guys help!

Kalithro

Yes, I should change the form, I make that mistake every time I use that textbook!

Thanks for your help!