Syntax Error 3464 Need help

[draalderks]

Hello, I am trying to create a form with a sub form that filters table results based on what is selected in a combo box. I have been fighting with this for a bit and I after changing some things around I am finally stuck with a Syntax error 3464. Why would I be getting this? The code I am using is below. Thanks!

Private Sub comboTower_AfterUpdate()
Dim myTower As String
myTower = "Select * from BuildingBlocks where ([Tower] = " & Me.comboTower & ")"
Me.BuildingBlocksSubform.Form.RecordSource = myTower
Me.BuildingBlocksSubform.Form.Requery
End Sub

[draalderks]

My mistake, it was a Runtime error.

[Micron]

What are the data types? I guess myTower is text because you Dim'd it as a string. You are missing quotes around it in the construct.

([Tower] = '" & Me.comboTower & "')"

Researching the error will tell you it is a data type mismatch problem. It's always a good idea - it can put you on the right track.

[draalderks]

Great, thanks! I'll give this a try. I have not coded in a while so I am very rusty with this. I'll give this a try and see if that helps.

[CJ_London]

I guess myTower is text because you Dim'd it as a string. You are missing quotes around it in the construct.

Not saying your suggestion is wrong, it is most likely right, but myTower is the whole sql string

Dim myTower As String
myTower = "Select

If Micron's suggestion does not solve the problem, it would help if we knew what the error description was. - I'm wondering if [Tower] is a multivalue field

[Micron]

Good catch! I scrolled back to the top and grabbed the first thing that resembled what I had in mind. The error description for this number is "Data type mismatch in criteria expression.", hence me focusing on the missing quotes around a text data type.

[draalderks]

The full message is "Data type mismatch in criteria expression." Could I just be referencing the wrong field?

[draalderks]

Sorry, I forgot to mention that it didn't seem to do anything.

[draalderks]

I tried with a new set of tables and built a new form, all in the same general layout hoping that I might have done something in the background. Doesn't seem to be the case. I have the following attached at the combo box. The debugger is highlighting the "Me.SimpleBlockForm.Form.RecordSource = myTower" line. What can be happening. Runtime error 3464 - "Data type mismatch in criteria expression"

Private Sub Combo18_AfterUpdate()
Dim myTower As String
myTower = "Select * from BuildingBlocks where ([Tower] = " & Me.Combo18 & ")"
Me.SimpleBlockForm.Form.RecordSource = myTower
Me.SimpleBlockForm.Form.Requery
End Sub

[Micron]

I asked what the data types are but I do not see where you answered the question.
Given the simplicity of your sql, the error should mean you are trying to use the wrong data type for the criteria. It wants a number OR text OR a date OR ... and you are supplying something else (whatever is in the control). Also, Ajax speculated that you may be dealing with a multi-value field, but no comment from you on that either. I say this without malice: if you don't answer the questions from the people who are trying to help, you are contributing to the problem.

[draalderks]

Sorry about that. I appreciate your help on this matter and I am not trying to be aggravating at all. Let me try to respond to both: This is a pretty simple database build and most of this data types - including Tower are simple text data types and BuildingBlocks is actually the name of the table that Tower is being pulled from. In the database, there is no other field with the name Tower. Does that help? Again, thanks for time and help, I do appreciate it and I am not trying to be difficult.

[Micron]

OK, no worries. If this does not do the trick there must be something going on that's not obvious because on the face of it, it should be simple. Here are some things you should try - probably in this order:
1) Make the code line "Select * from BuildingBlocks where ([BuildingBlocks].[Tower] = '" & Me.Combo18 & "');" assuming I have the table and field name right.
The difference now is that there is an explicit reference to the table and field, the quotes you are still missing are there, and I added the semi colon which you dropped again in your new example. Likely it is only the missing single quotes that could cause a "Data type mis-match" error. Try that line and if it gives you the same error then I suggest this, not knowing if you are familiar with the immediate window or code stepping. If not, please Google it (gotta make you work too!):
2) Put a break on that line in your code, click whatever calls the code and the VBE editor will highlight that line as it stops there. Press F8 once to move to the next line. Then type this in the immediate window: "?myTower" (without the quotes) and hit Enter. A line will appear in the immediate window below that and should be:
Select * from BuildingBlocks where ([Tower] = 'the combo value'); Obviously, the combo value should be replaced by whatever is in the combo.
NOTE: I forget if it will surround this whole thing with single quotes in spite of the hundreds of times I have done this, however, the value of the combo box must have ' on each side of it if the value is text. If it looks good, press F8 to move to the next line. If no error, press again. If no error, the problem is somewhere else, not where you think it is. In that event, I think it is time you uploaded a zipped copy of the db with a short explanation of what to do to re-create the problem. Maybe it is in the relationship between the parent and child of what looks like a form / subform setup you are using.

[John_G]

The issue might be how you have the combo box set up. What you see in the combo box on the screen might not be what the value of the combo box actually is. This is often the case in a combo box that is listing values from a lookup table; the combo box displays a text value, but actually has a numeric reference (key) as its value.

How many columns does your combo box have, and is the width of one of them (usually the first column) 0?

[Micron]

Agreed. That's why I suggested trying to find out what the string is by looking at the immediate window. It might be more than one column, bound to the first column, and the first column is an number field (such as an auto number) while the second column is the visible one. If poster sees a number where the value of the control is within the string instead of text, this will surely cause a data type mis-match error.