Error:No value given for one or more required parameters

[shanl]

I'm new to VBA and this may be very basic so bear with me. I'm trying to develop a process where I have multiple list boxes. I want to populate the first list box and be able to select records from the first list box and move the selected records to a second list box for additional processing.

I'm having problem with populating the first list box. I can get this working using a straight selection from a sql server table but the minute I change it to use columns using a query I've created it fails with Run time error -2147217904 (No value given for one or more req parameters)

My query does use fields filled in from the form but by the time I execute my list box, all those fields should be available. Is this something that can't be done? Or does anyone else have any other suggestions on how to accomplish this when you need to compare values from the Form to narrow down your criteria. In order for this process to work my Row Source Type has to be set as a VALUE LIST.

Private Sub Employeelist_Enter()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String, strItem As String

strSQL = "SELECT EmployeeDept_qry.zLName, EmployeeDept_qry.zFName, EmployeeDept_qry.BadgeID FROM EmployeeDept_qry Where EmployeeDept_qry.zDeptdesc = [Forms]![Main_Frm]![DepartmentList];"

Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

Do Until rs.EOF
strItem = rs.Fields("zLname").Value & ";" & rs.Fields ("zfName").Value & ";" & rs.Fields("zBadgeId").Value
Me.Employeelist.AddItem strItem
'Row Source Type must be Value List
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Set cn = Nothing

End Sub

 

[Sweenster]

Hi,

It looks to me like it’s the SQL statement that you have after strSQL =. You have enclosed everything in quotes including [Forms]![Main_Frm]![DepartmentList]. Try changing it so that actual dept value is used rather than a ref to the object and then join that to the rest of the SQL statement. e.g.

strSQL = "SELECT EmployeeDept_qry.zLName, EmployeeDept_qry.zFName, EmployeeDept_qry.BadgeID FROM EmployeeDept_qry Where EmployeeDept_qry.zDeptdesc = " & [Forms]![Main_Frm]![DepartmentList]
'(not sure what Dept list is so you may need [DepartmentList].Text or whatever?)

Hope this helps..

Regards
Mark

 

[Andrzejek]

For future reference, try this with your SQLs:

 strSQL = "SELECT EmployeeDept_qry.zLName,   EmployeeDept_qry.zFName, EmployeeDept_qry.BadgeID FROM EmployeeDept_qry Where EmployeeDept_qry.zDeptdesc = [Forms]![Main_Frm]![DepartmentList];"

[blue]Debug.Print strSQL[/blue]

and see your SQL in Imidiete Window.

Most of the time you can see what's wrong with it.


Have fun.

---- Andy

 

[shanl]

Andy and Mark,
Thank you very much for your suggestions. Both were helpful.
I wasn't familiar with the Debug.Print option and Immediate window so that's been helpful to see how its interrogating my code.

I tried Mark's suggestion on reworking the statement and by reworking the statement it showed me that my Where clause was picking up information from the Main form.

I created a new query so that my code wasn't using a query that had "Where" criteria in the Query but only consisted of joined sql tables but I'm still unable to get the Where clause to work. This is what shows up in the Immediate window when I run the query. I'm wondering if the field it takes from the [FORMs]![Main_Frm]![DepartmentList] needs to be in quotes somehow in the comparison but I don't know how to do that.

Do you know how to take a field from the main Access form and enclose it in quotes? I've tried many combinations that don't seem to work.

Here are the results from my Immediate window that still giving me the same "No value given for one or more req parameters 'error. If I take the Where clause off it runs fine but I need to narrow my selection down to only people from a certain dept.

SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc = Patrol << I'm wondering if Patrol should be in quotes?

The code for the above looks like this
strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc = " & [Forms]![Main_frm]![DepartmentList]

 

[Sweenster]

Hi,

Just add the extra single quotes around the dept by enclosing these in double quotes and using ampersands to build up your string

 strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc = " & "'" & [Forms]![Main_frm]![DepartmentList] & "'"

Regards
Mark

 

[shanl]

Mark,
Thanks, you're the greatest. This was exactly what I needed to get it to work. Thanks for all your help on this.

 

[Andrzejek]

I would put the quote inside your Statement like this:

strSQL = "SELECT DISTINCT EmployeeDept_qry2.zLName, EmployeeDept_qry2.zFName, EmployeeDept_qry2.zBadgeID FROM EmployeeDept_qry2 WHERE EmployeeDept_qry2.zDeptDesc =[blue] '" & [/blue][Forms]![Main_frm]![DepartmentList] & "'"

but that's just personal preference.


Have fun.

---- Andy