Combo box event generates a Type mismatch error 1

[paulrmiller2000]

I am stumped! I have a form built on this table:

tbl_Leads
pk_lonLeadID
fk_lonClientID
fk_lonLeadSourceID
fk_lonListingTypeID
fk_lonMaketingTypeID
fk_lonReferralTypeID
fk_lonAgentID

Each field is a Long integer.

The form has several combo boxes, populated by queries, that are bound to these fields.

I have cboListingType, cboMarketingType, cboReferralType, and cboAgent set to invisible, and I only want them visible based on certain matches from table tbl_LeadSource. That table has two fields, pk_lonLeadSourceID and txtDescription. 2 = Listing, 4 = Marketing, 5 = Referral. If the value returned in the cboLeadSource combo box is a 5 for a "referral," then cboReferralType then becomes visible. If the value for 4 (or "marketing") is returned, then cboMarketingType becomes visible.

Everything works fine, except for one combo box I added called cboAgent. If cboLeadSource returns a 5 for "referral," then cboReferralType becomes visible as appropriate. However, I want to drill down a bit further, so I want cboAgent (bound to fk_lonAgentID) to become visible if cboReferralType is populated with a 1 which equals an "Agent" (I'll then select the agent's name).

I cannot for the life of me make cboAgent visible on cboReferralType equaling 1 -- all I receive is a type mismatch error.

I have double-checked table structures, queries, and bound fields. I have deleted and reinserted the combo box. I even changed cboAgent to become visible on cboLeadSource meeting miscellaneous criteria, and that seems fine. It only seems to have a problem when being fed from cboReferralType. Here's my code:

Private Sub Form_Current()

    Me!cboListingType.Visible = (Me!cboLeadSource.Column(0) = 2)
    Me!cboMarketingType.Visible = (Me!cboLeadSource.Column(0) = 4)
    Me!cboReferralType.Visible = (Me!cboLeadSource.Column(0) = 5)
    Me!cboAgent.Visible = (Me!cboReferralType.Column(0) = 1)
        
End Sub
Private Sub cboLeadSource_AfterUpdate()

    Me!cboListingType.Visible = (Me!cboLeadSource.Column(0) = 2)
    Me!cboMarketingType.Visible = (Me!cboLeadSource.Column(0) = 4)
    Me!cboReferralType.Visible = (Me!cboLeadSource.Column(0) = 5)
    Me!cboAgent.Visible = (Me!cboReferralType.Column(0) = 1)

End Sub

I must be missing something painfully obvious! Can anyone please advise?

Thanks,
Paul

 

[MajP]

what is the rowsource for the cboreferral?

 

[paulrmiller2000]

Hi MajP,

Rowsource for cboReferralType is

SELECT pk_lonReferralTypeID, txtDescription FROM qry_ReferralTypes;

I am convinced the problem originates with the cboReferralType combo box, either from the way it is structured or from the way the event is written. I also tried deleting it, then copying cboLeadSource and then just replacing the rowsource, bound field, and of course the name, and the error persists. Since table structure is fine (from what I can tell), that leads me to believe that maybe there's a problem with how I'm initiating the change from invisible to visible...?

 

[MajP]

did you double check that pk_lonReferralTypeID is a number field and not accidently a text field?

 

[paulrmiller2000]

Yes... but I did just double-check again, because you never know! pk_lonReferralTypeID is a long integer (and auto-number) in tbl_ReferralTypeID. It's corresponding field in tbl_Leads, fk_lonReferralTypeID, is also a long integer.

 

[kjv1611]

Have you tried commenting out the errant line just to see if the code runs fine w/o that one line? Just wanting to make sure it's dealing with one control/field rather than possibly a logic error..

--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

How are ya paulrmiller2000 . . .

I believe your problem revolves around [blue]Nulls![/blue] ... the value returned when no selection is made. Remember your code is controlling the visible prperty, [blue]not making selections![/blue] When you 1st open the form I query how it works at all as no selections have yet been made ... unless your setting defaults.

Your Thoughts?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kjv1611]

Ah, [blue]Nulls[/b]. That's what I was thinking originally, but just didn't say it. I was afraid that perhaps I was wrong.

--

"If to err is human, then I must be some kind of human!" -Me

 

[paulrmiller2000]

Hi everyone, thanks for the replies! kjv1611, yes I have commented out those two lines and have had no issues. By process of elimination I am confident there's some issue with the combo box itself or the way I'm trying to handle it.

TheAceMan1, I don't think that's possible, since each row in the table has a value, even if it is just a zero.

This is such a weird problem. The funny thing is, about three years ago I was an Access/VB pro. Haven't touched it since 2006 and I feel I've lost a LOT of ground!

 

[kjv1611]

Well, what are you waiting for?! Go find that ground!
It must be 'round there somewhere!


--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

[blue] ... I don't think that's possible, since each row in the table has a value, even if it is just a zero.[/blue]

[blue]I believe your problem revolves around Nulls! ... [purple]the value returned when no selection is made.[/purple][/blue]

The question for you is ... [blue]in youe code, what does a combobox return when no selection is made?[/blue] ... You Tell Us!

Also ... according to you, making [blue]cboAgent[/blue] visible is dependent on [blue]cboReferralType[/blue]. But all you've done in your code is make [blue]cboReferralType[/blue] visible! ... No selection has been made! Now, since [blue]cboAgent[/blue] is dependent on selection in [blue]cboReferralType[/blue], shouldn't the following line ...

[blue]Me!cboAgent.Visible = (Me!cboReferralType.Column(0) = 1)[/blue]

... reside in the [blue]After Update[/blue] event of [blue]cboReferralType[/blue]?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[paulrmiller2000]

AHA! See, I just KNEW I had gotten stupid after taking a few years off!

That's so simple I can't believe I didn't see it. Making your change did it for the update procedure.

However, one problems till remains, and I'm sure it's just as obvious a solution. Say my current record does NOT require cboAgent to be visible. If I move to the next existing record, I want cboReferralType to be visible and, given certain criteria, cboAgent to be visible as well. However, if I move to a form in which cboAgent should be visible, I still receive the Debug error of Type Mismatch. Following is the code as it is now written:

Private Sub Form_Current()

    Me!cboListingType.Visible = (Me!cboLeadSource.Column(0) = 2)
    Me!cboMarketingType.Visible = (Me!cboLeadSource.Column(0) = 4)
    Me!cboReferralType.Visible = (Me!cboLeadSource.Column(0) = 5)
    Me!cboAgent.Visible = (Me!cboReferralType.Column(0) = 1)
        
End Sub
Private Sub cboLeadSource_AfterUpdate()

    Me!cboListingType.Visible = (Me!cboLeadSource.Column(0) = 2)
    Me!cboMarketingType.Visible = (Me!cboLeadSource.Column(0) = 4)
    Me!cboReferralType.Visible = (Me!cboLeadSource.Column(0) = 5)
    
End Sub
Private Sub cboReferralType_AfterUpdate()

    Me!cboAgent.Visible = (Me!cboReferralType.Column(0) = 1)

End Sub

On moving to an existing record in which cboAgent should be available, I'll receive a type mismatch error and VB Debugger will highlight the fifth line down, or the Me!cboAgent.Visible statement in sub Form_Current().

Now what am I doing wrong (that is painfully obvious I'm sure)?

 

[TheAceMan1]

paulrmiller2000 . . .

Copy the form under another name so you can come back to square one if desired. Now ...
[ol][li]Copy the following common routine to the forms code module.

[blue]Public Sub CBxCtl()
   Dim cbxLS As ComboBox, cbxLT As ComboBox, cbxMT As ComboBox
   Dim cbxRT As ComboBox, cbxAg As ComboBox
   
   Set cbxLS = Me!cboLeadSource
   Set cbxLT = Me!cboListingType
   Set cbxMT = Me!cboMarketingType
   Set cbxRT = Me!cboReferralType
   Set cbxAg = Me!cboAgent
   
   If cbxLS.ListIndex <> -1 Then [green]'A LeadSource selection has been made![/green]
      cbxLT.Visible = (Me!cbxLS.Column(0) = 2)
      cbxMT.Visible = (Me!cbxLS.Column(0) = 4)
      cbxRT.Visible = (Me!cbxLS.Column(0) = 5)
      cbxAg.Visible = (Me!cbxRT.Column(0) = 1)
   Else [green]'No LeadSource selection so clear all and hide![/green]
      cbxLT = ""
      cbxMT = ""
      cbxRT = ""
      Me!cbxAg = ""
      cbxLT.Visible = False
      cbxMT.Visible = False
      cbxRT.Visible = False
      cbxAg.Visible = False
   End If
   
   Set cbxLS = Nothing
   Set cbxLT = Nothing
   Set cbxMT = Nothing
   Set cbxRT = Nothing
   Set cbxAg = Nothing

End Sub[/blue]

[/li]
[li]Next change your three routines as follows:

[blue]Private Sub Form_Current()
   Call CBxCtl        
End Sub

Private Sub cboLeadSource_AfterUpdate()
   Call CBxCtl
End Sub

Private Sub cboReferralType_AfterUpdate()
   Call CBxCtl
End Sub[/blue]

[/li]
[li]Thats it ... perform your testing![/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[paulrmiller2000]

AceMan,

Thank you for taking the time to write that out! I did as you instructed, and received the following error when attempting to open the form: "Microsoft Access can't find the field 'cbxLS' referred to in your expression," and debugger highlighted the following line in the public sub:

cbxLT.Visible = (Me!cbxLS.Column(0) = 2)

I find that strange since we have cbxLS defined as a ComboBox and set to cboLeadSource. Replacing the three cbxLS.Column statements with cboLeadSource.Column and the cbxRT.Column statement with cboReferralType eliminated that error.

I then received another one! "Invalid use of Null," highlighted the line

      cbxAg.Visible = (Me!cboReferralType.Column(0) = 1)

Funny since we're not assigning a null value, and the character "1" we're trying to insert is allowed in the numeric long integer data field in the table.

I commented out that line and the rest of the program now runs fine. Of course, the Agent combo box never becomes visible. Here's what your sub looks like now:

Public Sub CBxCtl()

    Dim cbxLS As ComboBox, cbxLT As ComboBox, cbxMT As ComboBox
    Dim cbxRT As ComboBox, cbxAg As ComboBox
   
    Set cbxLS = Me!cboLeadSource
    Set cbxLT = Me!cboListingType
    Set cbxMT = Me!cboMarketingType
    Set cbxRT = Me!cboReferralType
    Set cbxAg = Me!cboAgent
   
    If cbxLS.ListIndex <> -1 Then 'A LeadSource selection has been made!
        cbxLT.Visible = (Me!cboLeadSource.Column(0) = 2)
        cbxMT.Visible = (Me!cboLeadSource.Column(0) = 4)
        cbxRT.Visible = (Me!cboLeadSource.Column(0) = 5)
        'cbxAg.Visible = (Me!cboReferralType.Column(0) = 1)
    Else 'No LeadSource selection so clear all and hide!
        cbxLT = ""
        cbxMT = ""
        cbxRT = ""
    Me!cbxAg = ""
        cbxLT.Visible = False
        cbxMT.Visible = False
        cbxRT.Visible = False
        cbxAg.Visible = False
    End If
   
    Set cbxLS = Nothing
    Set cbxLT = Nothing
    Set cbxMT = Nothing
    Set cbxRT = Nothing
    Set cbxAg = Nothing

End Sub

I'm going to keep plugging away, see if I can figure out what I'm missing here. Any thoughts would (continue to) be appreciated!

Thanks,
Paul

 

[TheAceMan1]

paulrmiller2000 . . .

Hmmm strange indeed

Check the name property of cboLeadSource.

If there are no proprietary concerns how about uploading a scaled down db for troubleshooting! You can use 4Shared for this ... [blue]its free![/blue] Convert to access 2k before uploading if you have a later version.

[blue]Your Thoughts? . . .[/blue]

BTW what version are you using?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[paulrmiller2000]

Hi AceMan,

I am using Access 2000k. Assuming I did everything correctly, here's the link:

http://www.4shared.com/file/115968063/ffa4e1d1/Prospecting_-_Shared.html.

Try to keep the laughter to a minimum, it is far from complete and definitely an amateur's! ;-) But hey, I figure it's a good way to dust off those old skills and bring them back up to date... and thanks for your continued help on this!

 

[TheAceMan1]

paulrmiller2000 the 4Shared link certainly is not the db we've dicussed here! Please check it . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[paulrmiller2000]

Hi Ace, it sure is! I just downloaded it to make sure I didn't upload the wrong file. The module you've been graciously helping me with corresponds to frm_Leads.

Thanks,
Paul

 

[TheAceMan1]

paulrmiller2000 . . .

I took care of any null problems with the [blue]Nz[/blue] function. All appears to be ok. Here's the code:

[blue]   Dim cbxLS As ComboBox, cbxLT As ComboBox, cbxMT As ComboBox
   Dim cbxRT As ComboBox, cbxAg As ComboBox
   
   Set cbxLS = Me!cboLeadSource
   Set cbxLT = Me!cboListingType
   Set cbxMT = Me!cboMarketingType
   Set cbxRT = Me!cboReferralType
   Set cbxAg = Me!cboAgent
   
   If cbxLS.ListIndex <> -1 Then [green]'A LeadSource selection has been made![/green]
      cbxLT.Visible = Nz((cbxLS.Column(0) = 2))
      cbxMT.Visible = Nz((cbxLS.Column(0) = 4))
      cbxRT.Visible = Nz((cbxLS.Column(0) = 5))
      cbxAg.Visible = Nz((cbxLS.Column(0) = 5), 0) And Nz((cbxRT.Column(0) = 1), 0)
   Else [green]'No LeadSource selection so clear all and hide![/green]
      cbxLT = ""
      cbxMT = ""
      cbxRT = ""
      '    Me!cbxAg = ""
      cbxLT.Visible = False
      cbxMT.Visible = False
      cbxRT.Visible = False
      cbxAg.Visible = False
   End If
   
   Set cbxLS = Nothing
   Set cbxLT = Nothing
   Set cbxMT = Nothing
   Set cbxRT = Nothing
   Set cbxAg = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PRMiller]

Hi AceMan,

I tried those changes and it works perfectly, can't believe I forgot about the Nz function! Thank you very much for all of your help, it is greatly appreciated!