listbox recordset criteria based on field on same form

[jonathonoss]

I am using Access 2000 and SQL Server 2000.

I have seen another post that deals with using a field on a form as the criteria on a listbox's recordset, but the recordset was a stored procedure.

Can I have criteria in the recordset of a listbox be a field's value (e.g. ... from table1 where myfield = [comboboxonmyform]) on the same form without using stored procedures? This is very easy to do with plain access, but I am new to projects and am embarrased to say I have been searching for this answer for hours today.

The Northwind database examples only show opening another form/report based fields on a different form. I want the criteria on a recordset to reflect a field's value on the same form.

I don't think input parameters are a solution. Must I somehow use stored procedures?

Thanks,

Jon

 

[Nowell]

Hi,

You could use a string of sql for the list boxes record source. something like this. I am going to assume the value in your xombo box is an number for now.

Dim strSQL as String
Dim combo as Integer

combo = forms!YourFormName!YourComboBox
strSQL = SELECT * FROM table WHERE field = '" & combo & "'
listbox.rowsource = strSQL

 

[jonathonoss]

Thanks Nowell.

That did it! I use vba to change SQL statements of listboxes in other plain Access DB's, so I don't know why I didn't think of it for this problem.

Jon