combo box selection returns empty string 2

[cdgeer]

I have a form with a combo box. I have a cmd button on the form that is supposed to use whatever I have selected in the combo box for a string value in some SQL code. However, it's coming up as an empty string but I can't figure out why. I done this hundreds of times before and it worked fine. Please help.
Private Sub cmdFindMinNecessary_Click()
Dim db As Database
Dim rs As Recordset
Dim strJobTitle As String
Dim SQL As String
Dim strEEID As String

strJobTitle = Forms![MinimumNecessary]![cmbJobTitle]

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT All_Personnel.Active, All_Personnel.LastName, All_Personnel.FirstName, All_Personnel.EEID, All_Personnel.JobTitle, All_Personnel.Department, All_Personnel.RollUp, All_Personnel.Supervisor, All_Personnel.PersonnelType FROM All_Personnel WHERE All_Personnel.JobTitle = "" & strJobTitle & "";")

Do Until rs.EOF
strEEID = rs![EEID]
strJobTitle = Forms![MinimumNecessary]![cmbJobTitle]

SQL = "INSERT INTO y_tmpMinNecCol( DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID)" & _
"SELECT tmpCollector.DataSource, tmpCollector.AccessLevel, tmpCollector.AccessCapability, tmpCollector.Status, tmpCollector.AccountExpiration, tmpCollector.EEID FROM tmpCollector WHERE tmpCollector.EEID = " & strEEID & ";"

db.Execute SQL
rs.MoveNext

Loop

MsgBox "A table has been created."

End Sub

 

[dhookom]

Have you attempted to set a break point and step through your code to see what's happening?

I think you might start by adding a couple quotes:

    Set rs = db.OpenRecordset("SELECT Active, LastName, FirstName, EEID, " & _
        "JobTitle, Department, RollUp, Supervisor, PersonnelType " & _
        "FROM All_Personnel WHERE JobTitle = [COLOR=red yellow]"[/color]"" & strJobTitle & ""[COLOR=red yellow]"[/color];")

Your code also suggests strEEID should be a number.

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Thanks Duane. I've set breakpoints by accident before but never really understood how they work. How do you mean, "Your code also suggests strEEID should be a number. "

When I hover over strEEID in the WHERE statement, it shows that the correct value is being captured but the SQL still gives me a "Too few parameters. Expected 1." message. I would think that the WHERE statement is supplying the parameter but it doesn't seem to be working.

Here's the latest revision of the code:

Private Sub cmdFindMinNecessary_Click()
Dim db As Database
Dim rs As Recordset
Dim strJobTitle As String
Dim SQL As String
Dim strEEID As String


strJobTitle = Forms![MinimumNecessary]![cmbJobTitle]

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [tmpAll_Personnel_cmbJobTitle].[EEID] FROM [tmpAll_Personnel_cmbJobTitle]")

Do Until rs.EOF
strEEID = rs![EEID]

SQL = "INSERT INTO y_tmpMinNecCol( DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID) SELECT DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID FROM tmpCollector WHERE tmpCollector.EEID = [" & strEEID & "];"

db.Execute SQL

rs.MoveNext

Loop

MsgBox "A table has been created."

End Sub

 

[PHV]

You may try either this:

SQL = "INSERT INTO y_tmpMinNecCol( DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID) SELECT DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID FROM tmpCollector WHERE tmpCollector.EEID = '" & strEEID & "'"

or this:

SQL = "INSERT INTO y_tmpMinNecCol( DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID) SELECT DataSource, AccessLevel, AccessCapability, Status, AccountExpiration, EEID FROM tmpCollector WHERE tmpCollector.EEID = " & strEEID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdgeer]

Thanks PH! The first suggestion worked fine.