Can someone tell me what is wrong here?

[Anthonydb]

I am trying to build a select case on a combo box that is executed by a find button. I'm getting a syntax error with the following:

mstrSQL = "SELECT * FROM Employees WHERE " _
& "EmployeeID In (SELECT DISTINCTROW " _
& "EmployeeID FROM Employees WHERE [LastName] & ", " & [FirstName] Like '*" _
& DoubleQuote(Me![cbosSelect]) & "*')"


Here is the complete code:

Private Sub cmdGo_Click()

On Error GoTo HandleErr

DoCmd.OpenForm "Tape", acFormDS
With Forms!Tape
If Len(Me!cboSelect & "") > 0 Then

Select Case optChoose
Case 1

mstrSQL = "SELECT * FROM Employees WHERE " _
& "EmployeeID In (SELECT DISTINCTROW " _
& "EmployeeID FROM Employees WHERE [LastName] & ", " & [FirstName] Like '*" _
& DoubleQuote(Me![cbosSelect]) & "*')"


Case Else
End Select
.RecordSource = mstrSQL
!cmdFind.Caption = "&Show All"
If optChoose = 1 Then
!TabEmployees.Value = 0
Else
!TabEmployees.Value = 1
End If
Else
.RecordSource = "Employees"
End If
End With
DoCmd.Close acForm, "FindForm"

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub

Thanks

 

[dcx693]

What does this part of your statement do?
DoubleQuote(Me![cbosSelect])

 

[Anthonydb]

I'm using code from Northwinds to try and manipulate for my use. The combo box has a concatenated query which is the first case I'm trying to build. If I ever get the 1st case to work I will then try to include cases for dates, Serial # and Job ID. I don't know what the "DoubleQuote" represents.

 

[dcx693]

OK, first make sure Me![cbosSelect] is not a typo. cboSelect seems like a logical name, but not cbosSelect.

If that first part is OK, then you might want to consider removing the DoubleQuote function. I don't know what it's doing, but if the name is any indication, it's placing double quotation marks around the Me![cbosSelect] value. That, by itself, would not be a problem, but I see that your code is also placing single quotation marks around it. Is the cbosSelect field a numeric or text value?

 

[Anthonydb]

I corrected the cboselect and removed the "DoubleQuotes". No I get "expected end of statement at this point;

mstrSQL = "SELECT * FROM Employees WHERE " _
& "EmployeeID In (SELECT DISTINCTROW " _
& "EmployeeID FROM Employees WHERE [LastName] & ", " & [FirstName] Like '*" _
& (Me![cboSelect]) & "*')"

The , between [LastName] &" "&[FirstName]

 

[dcx693]

Ah, I think I see what you're trying to do. Try this:
& "EmployeeID FROM Employees WHERE [LastName] & ', ' & [FirstName] Like '*" _

 

[Anthonydb]

Okay the killed the error but, the concatenated query for the combo box is now not appearing. Could you explain the double vs single quotes distinction. Also, maybe I'm going about this the wrong way, ultimately, I want to be able to scroll to a name and have a datasheet popup and show the appropriate records. As stated before, the other cases would include Serial#, Tape ID, and JobID. Maybe the combo should be blank whatever the search criteria is, that is what is searched on.

 

[dcx693]

What do you mean here:

the concatenated query for the combo box is now not appearing

Your query itself seems a bit convoluted. How about trying this simplified version?
"SELECT DISTINCT * FROM Employees WHERE " & _
"[LastName] & ', ' & [FirstName]=" & Me.cbosSelect & ";"
This takes all fields from the Employees table where the LastName and FirstName fields match the selection the user makes in the cboSelect combo box.

Quotiation marks are used to delineate text in Access. However, what do you do when you need to have quotes within quotes? Like this: "[Name]="Jones"" The solution is to use single quotes within the double quotes like this:"[Name]='Jones'" Of course, you can run into all sorts of issues if your name includes a quote like O'Connor. So placing that comma and space in this part of the statement:
"[LastName] & ', ' & [FirstName]=" tells Access that I want the entire string of [LastName]&", " & [FirstName] treated as one unit.

 

[Anthonydb]

The cboselect has a query statement in the control properties that combines the first and last names of employees.

 

[dcx693]

And it's not loading up now? What is the SQL code for that query?

 

[Anthonydb]

When you told me to try the single quotes instead of the double quotes, that is when the cboselcet went blank. If I go from [LastName] & "," & [FirstName] to [LastName] & ',' & [FirstName] the cboselect becomes empty. The code is as follows:

mstrSQL = "SELECT * FROM Employees WHERE " _
& "EmployeeID In (SELECT DISTINCTROW " _
& "EmployeeID FROM Employees WHERE [LastName] & ", " & [FirstName] Like '*" _
& DoubleQuote(Me![cbosSelect]) & "*')"


Here is the complete code:

Private Sub cmdGo_Click()

On Error GoTo HandleErr

DoCmd.OpenForm "Tape", acFormDS
With Forms!Tape
If Len(Me!cboSelect & "") > 0 Then

Select Case optChoose
Case 1

mstrSQL = "SELECT * FROM Employees WHERE " _
& "EmployeeID In (SELECT DISTINCTROW " _
& "EmployeeID FROM Employees WHERE [LastName] & ", " & [FirstName] Like '*" _
& DoubleQuote(Me![cbosSelect]) & "*')"


Case Else
End Select
.RecordSource = mstrSQL
!cmdFind.Caption = "&Show All"
If optChoose = 1 Then
!TabEmployees.Value = 0
Else
!TabEmployees.Value = 1
End If
Else
.RecordSource = "Employees"
End If
End With
DoCmd.Close acForm, "FindForm"

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub

 

[dcx693]

The code you posted could does not set the rowsource of the cboSelect button, so I'm not sure how it's affecting it. Did you try replacing that code for mstrSQL with this simplified version:
"SELECT DISTINCT * FROM Employees WHERE " & _
"[LastName] & ', ' & [FirstName]=" & Me.cbosSelect & ";"

 

[MartijnR]

maybe not relevant anymore, but you can use something like

dim DoubleQuote as String
DoubleQuote = chr(34)

to use this variable in your statements so that it will place an " somewhere you want it and not interfere with the syntax of your code

 

[Anthonydb]

Forgive guys but my code module is so congested with active and commented out code that I can't make heads or tails of it anymore so let me try starting from scratch. What would be the best approach to the following:

I need a search form to produce a datasheet of records. I want to be able to search by name (which is concatenated, LastName FirstName), Serial #, TapeID and dates. As described above, I would require a combo box (cboselect) and an unbound combo or text box to type the other search criteria. Once the criteria is selected a command button (cmdOK) would perform the search and produce the datasheet.

Can you possibly show me the correct code or format to produce this or come up with a more efficient process? If there is an alternative, please demonstrate in VBA, I need all of the code exsposure I can possibly get.

Thanks again

 

[Rich]

You may find this example useful

 

[dcx693]

Rich's code is very nice. You can learn a lot from it.

I created a very simple database which mimics the example you've posted. It contains just one table and one form. Open the form, select a user from the list, and then click the Search button. It should bring up the selected user. See if this makes sense to you. Let me know if you have questions.

It's in Access 2000 format. Let me know if you need it in Access 97 format.