Run Time Error 3075 in Access 2007

[jblank65]

Hello,


I am a beginner with using Microsoft Access and with visual basic coding and I was wondering if I could get some help with some issues I have encountered. I would post a zip file of my entire database on here, but I am having issues loading it. I am trying to build a search option for my company's inventory of filters, but when I click the button to perform the search, "Run Time Error 3075 - Missing ), ] in query expression. The code is as follows.

Option Compare Database
Option Explicit

Private Sub cmdUnfilter_Click()

Dim intIndex As Integer

Me.cboType = Null
Me.cboMake = Null
Me.txtModel = ""
Me.cboLocation = Null

End Sub

Private Sub cmdFilter_Click()

Dim strInv As String
Dim lngLen As Long

If Len(Me.cboType & vbNullString) <> 0 Then
strInv = strInv & "[Type] = " & Chr(34) & Me.cboType & Chr(34)
End If

If Len(Me.cboMake & vbNullString) <> 0 Then
If Len(strInv) <> 0 Then
strInv = strInv & " AND [Make] = " & Chr(34) & Me.cboMake & Chr(34)
Else
strInv = "[Make] = " & Chr(34) & Me.cboMake & Chr(34)
End If
End If

If Len(Me.txtModel & vbNullString) <> 0 Then
If Len(strInv) <> 0 Then
strInv = strInv & " AND [Model] = " & Chr(34) & Me.txtModel & Chr(34)
Else
strInv = "[Model] = " & Chr(34) & Me.txtModel & Chr(34)
End If
End If

If Len(Me.cboLocation & vbNullString) <> 0 Then
If Len(strInv) <> 0 Then
strInv = strInv & " AND [Location] = " & Chr(34) & Me.cboLocation & Chr(34)
Else
strInv = "[Location] = " & Chr(34) & Me.cboLocation & Chr(34)
End If
End If

lngLen = Len(strInv) - 10
If lngLen <= 0 Then
MsgBox "No Records Found. Try Again", vbInformation, "Not Valid."

Else
strInv = Left$(strInv, lngLen)
Debug.Print strInv

Me.Filter = strInv
Me.FilterOn = True
End If
End Sub

In my "Filter Inventory" form, I have 3 combo boxes(cboMake, cboModel, and cboLocation) 1 text box (txtModel), 2 buttons (cmdFilter, cmdUnfilter) and I have one subform with information from my filter inventory table. Upon trying to run this information with just an item chosen in the Location combo box, the error is displayed and the immediate window in code builder displays [Location] = ". Any solutions would be greatly appreciated.

Also, in my sub-form, I have 200+ records, but it only shows one at a time. Is there any way I can get all of my records to show up just like my table shows, so that it is all on the form at first, then upon entering information into my search fields, the records are consolidated to only show information that matches all criteria I have entered. I just want to see all the results at once, and not have to scroll through with the arrows at the bottom of the sub form.

If anybody has the time to help me with any of these issues, that would be absolutely amazing. As of right now, I just don't know where to look to confront the issue and have run out of ideas as to how to fix the problem. Thanks a lot.

James

[ajetrumpet]

James,

I didn't read through this whole thing, but one thing you need to know about an error like that is that it cannot really be narrowed down to one specific area of code, although it helps to know how it works.

For instance, if the error messages starts out with:

missing operator in expression "[ yada yada.."

it is telling you that the error is AFTER the opening bracket. I don't know for sure, but it is reasonable to assume, based on the way Access error messages work, that the engine breaks out code and/or sql into chunks of tasks. Thus, if you have this:

SELECT [my field]] from table

it will probably show you:

missing operator in expression "[my field..."

instead of:

missing operator in expression "SELECT [my ...."

Info for you...it's good to have, to narrow the problem.

The other thing to note about these kinds of errors is that most of the time, the symbol and/or word in the error message doesn't at all mean that the actual error has anything to do with it. It simply means that the statement is breaking around the area where that symbol and/or word is at.

If you're experienced with server scripting, it's just like that. A script will die on an error, but no error line is given or anything sometimes.

[ajetrumpet]

I did just browse the code. don't see anything. can you LEN a vbnullstring though? IDK.

what line does it break on?

[jblank65]

I think the only break I have is before the cmdFilter line. Is it possibly that if my tables are not named correctly, related correctly, have issues in the properties section, etc that it would cause that error?

[ajetrumpet]

I think the only break I have is before the cmdFilter line. Is it possibly that if my tables are not named correctly, related correctly, have issues in the properties section, etc that it would cause that error?

anything causes an error, but that one is not related to tables.

and you can't have a break before cmdfilter. that's the first line in your subroutine.

[jblank65]

Ok, I have no idea what the issue could be then. My unfilter command works fine, and the program works correctly by displaying my own error when no information is entered, but as soon as I enter information, the errors occur. I am not to familiar with the terminology so I don't know where the breaks would be or what you mean. When I bring up the dubugger though, "Me.Filter = strInv" is what is highlighted. I hope that helps.

[ajetrumpet]

what is CHR(34)?? i forget. a double quote, right?? that's probably what's causing the mess. use quotes, not chr()'s.

all statements look similar to this:

strInv = strInv & " AND [Make] = " & Chr(34) & Me.cboMake & Chr(34)

first of all, if the control values are text values and not numeric, the concatenation is incorrect. should be:

strInv = strInv & " AND [Make] = '" & Me.cboMake & "'"

CHR(34)'s not withstanding.

so...if 34's are double quotes, then they're not even needed.