Filter subform with combo box using VBA (1 Viewer)

[hllary]

have a combo box on a form that I want to filter a subform (SubSearchMaster_frm). I getting a runtime error 3464: data type mismatch in expression. the code is below.

Private Sub CboNIIN_AfterUpdate()

Me.SubSearchMaster_frm.Form.Filter = "[NIIN] = " & Me.CboNIIN
Me.SubSearchMaster_frm.Form.FilterOn = True

End Sub

I have also tried:

Private Sub CboNIIN_AfterUpdate()
Dim sql As String
sql = "Select * from SubSearchMaster_frm where ([NIIN] = " & Me.CboNIIN & ") From subsearchmaster_frm"

Me.SubSearchMaster_frm.Form.RecordSource = sql
Me.SubSearchMaster_frm.Form.Requery

End Sub

But I'm getting an error on that too.

 

[Micron]

The message means a value that you are attempting to use/pass is of the wrong type. My guess is that the combo's data type is text, which would mean that you have to use text delimiters ( ' or ") so maybe not
"[NIIN] = " & Me.CboNIIN

but

"[NIIN] = ' " & Me.CboNIIN & " ' "

I added the extra spaces to make it easier to see the difference. Would actually be

"[NIIN] = '" & Me.CboNIIN & "'"

 

[jdraw]

In your SELECT statement this (SubSearchMaster_frm ) should be a table.
Is this the name of your table or the name of a form?

What is the data type of NIIN? and what is the value of NIIN in your combobox?

 

[hllary]

The data type in the combo box is number while in the table it's text. It's part number, some start with zero and I have to keep them that way. Is there a way to change the combo box data type?

I'm not getting an error now, but it's not returning any values. Just to cover all bases, I check to see if the items listed in the combo box are in the table. They are.

 

[jdraw]

Are you working with Nato Stock Numbers?

What is the VALUE of NIIN in the table?

 

[hllary]

No, I'm not working with nato stock numbers. the values of niin is like these:

0000000046
0055668355
0090206281

 

[Micron]

The data type in the combo box is number while in the table it's text. It's part number, some start with zero and I have to keep them that way.

This is not possible, thus may be the cause, thus the fix is probably what I already suggested. What looks like 123 could be "data typed" in a table as text or numbers. Actual numbers behave differently than text that looks like numbers. F'rinstance, you know that the number 123 comes after 22. If the field property is text, 123 comes before 22.

 

[jdraw]

Numbers do not have leading 000...
You have a Text string that has numeric characters.
NIIN should be declared as short text and that will help.

 

[hllary]

NIIN is declared as a short text. The combo box format is number.

 

[jdraw]

How did/do you format the combobox?
You can try
"[NIIN] = ' " & Cstr(Me.CboNIIN) & " ' "

 

[Micron]

Might need to see the combo row source. It's possible that a number is being passed to a text field and that number is an ID but you think it's the value you see in the combo after selecting.

 

[hllary]

When I use the master and child, the ID is given for the NIIN field.

 

[Micron]

When I use the master and child, the ID is given for the NIIN field.

If that was directed to me, it doesn't help and isn't really relevant to what I was saying. After a dozen or so posts for what should be a fairly simple problem, I'd say it's time to post a copy of your db. Note that I don't say that to criticize your knowledge because we've probably all been stumped by something simple at least once as we learned. I think you might learn something faster if we had a db to look at, that's all.

 

[hllary]

Sorry for the delay on uploading my database. I had to make a sample db where I recreated the issue.

 

[jdraw]

In your combobox, you have the bound column = 1 (which is ID). I think you want column 2 (NIIN). Try changing bound column to 2 and let us know........

 

[hllary]

I had a moment where my sample db worked but my main did not. But I recreated the form and it worked.

Thanks for the help.