Query that prompts with a listbox 1

[lck092580]

Hi,

Can anyone help me with a function that prompts the user with a listbox listing some items?

What I need to do is to insert the field of a table into a query.

For example:

select * from contacts
where contacts.[someInput] <> null

I want to incorporate this into a form so the user can just select a listbox and then the bottom returns results.

Thanks.

 

[lck092580]

Hi Duane,

Sorry about the late reply. I've checked all the name properties but the code still failes on compile stating that the data member isn't found (sfrmResults). I've tried using different names and it still doesn't work so I have to be missing something here.

 

[lck092580]

Also, go to the After Update event of the combo box and type in:
Me.sfr
You should see a drop down of your subform name if it is named correctly."

I GOT IT GOING!!! Thanks Daune! Turns out I didn't rename it properly so when I started typing str.. it gave me the name with UNDERSCORES. Whew! Thanks again! You had great patience with me!

 

[lck092580]

I'm not trying to update via command button for another form but it keeps saying I canceled the last operation. I put essentially the same code into the on click field.

 

[dhookom]

I don't understand your most recent posting.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lck092580]

Hi Duane,

I think it has something to do with my WHERE clause because if I take it out, everything works:

strSQL = "SELECT contact.ID, contact.RespondentID, contact.FirstName, contact.LastName, " & _
"contact.Availability, contact.Area1, contact.Area2, " & _
"contact.Group, contact.Supervisor " & _
"FROM (contact LEFT JOIN area1 ON contact.Area1=area1.ID) LEFT JOIN supervisor " & _
"ON contact.Supervisor=supervisor.ID " & _
"where (((contact.area1) = (select id from area1 where area1.name = " & Me.cboArea1Supervisor & ")) " & _
"and ((contact.supervisor) = (select id from supervisor where supervisor.name = " & _
Me.cboSupervisorArea1 & ")));"
Me.Area1Supervisor.Form.RecordSource = strSQL

If I take out the WHERE clause it works when I click on the Command button.

However, once I put in the WHERE clause I get a:

Run-tine error '2001':

You canceled the previous operation

 

[PHV]

Perhaps this ?[tt]
"WHERE contact.area1=(select id from area1 where area1.name=[!]'[/!]" & Me.cboArea1Supervisor & "[!]'[/!]) " & _
"and contact.supervisor=(select id from supervisor where supervisor.name=[!]'[/!]" & _
Me.cboSupervisorArea1 & "[!]'[/!])"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

lck092580,
You need to learn more about debugging. You could place this line in your code:

    strSQL = "SELECT contact.ID, contact.RespondentID, contact.FirstName, contact.LastName, " & _
    "contact.Availability, contact.Area1, contact.Area2, " & _
    "contact.Group, contact.Supervisor " & _
    "FROM (contact LEFT JOIN area1 ON contact.Area1=area1.ID) LEFT JOIN supervisor " & _
    "ON contact.Supervisor=supervisor.ID " & _
    "where (((contact.area1) = (select id from area1 where area1.name = " & Me.cboArea1Supervisor & ")) " & _
    "and ((contact.supervisor) = (select id from supervisor where supervisor.name = " & _
    Me.cboSupervisorArea1 & ")));"
debug.Print strSQL

This would allow you to copy and paste the strSQL into the SQL view of a query for testing. You might then readily see your issue or get a better error message.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]