By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Update a select query code by selecting an item in a combo box ?

P: 44
Hi,
I've got the follwing issue.
I have a command button on a form, which selects a query after beeing pressed. The data from the query is a combination of data from 3 tables.
Now I want to make a step in between. I want to give the users the possibility to restrict the output of the query.
E.g. The query previously showed all staff. Now the user selects only one department (maybe a combo box having an event procedure...) and when they press the button the query shows only the staff from that department.
I understand that that means I want to enter a criteria in the query with the help of the form.
The department actually is already part of the output from the query.
I really don't know if this is possible and how to do it?
Anybody out there with an idea? :)
Cheers
Sep 28 '06 #1
Share this Question
Share on Google+
3 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

It's possible - simply you have to delete your old query and recreate it with the respective criteria!

How to do it....

The query creation can be done using this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set mydb = CurrentDb()
  3. Set Myquery = mydb.CreateQueryDef(names, SQLS)
  4.  
  5.  
And the query del...

Expand|Select|Wrap|Line Numbers
  1. Set mydb = CurrentDb()
  2. mydb.QueryDefs.Delete names
  3.  
  4.  
And I don't mention something between the 2 operations...

The SQL that U have to Save to your query! It's up to You to invent it! ;)

But not hesitate to ask if pb! :)

Best regards

:)
Sep 30 '06 #2

P: 44
I'm not sure, if I understood your correct. I should create a query with a criteria and than delete it again. So always if somebody selects a department from the combo box this will be the new criteria in the code / query ? But how can I connect a combo box (where somebody selects any of 12 values) and a command button (or something else), which runs the code and selects all the records from the tables where the criteria fits?

I mean, I have a combo box, which shows all the departments in the list and where somebody can select the dept which is wanted. And I have a command button which opens an existing query.
Now I need to combine both to run the query with the selected dept as the criteria.

Or I'm totally wrong and there is an easier way to get it. But I'm still very lost...

Hi,

It's possible - simply you have to delete your old query and recreate it with the respective criteria!

How to do it....

The query creation can be done using this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set mydb = CurrentDb()
  3. Set Myquery = mydb.CreateQueryDef(names, SQLS)
  4.  
  5.  
And the query del...

Expand|Select|Wrap|Line Numbers
  1. Set mydb = CurrentDb()
  2. mydb.QueryDefs.Delete names
  3.  
  4.  
And I don't mention something between the 2 operations...

The SQL that U have to Save to your query! It's up to You to invent it! ;)

But not hesitate to ask if pb! :)

Best regards

:)
Oct 3 '06 #3

PEB
Expert 100+
P: 1,418
PEB
Hi icetea,

Imagine that you have the SQL of your query with the 3 tables without conditions..

Now try to vizualize 1 departement by manually typing the respective departement!

Pass in SQL Query view and Copy this SQL!

So in your combobox you need to create an After Update Event procedure...


In this procedure needed to type

Me.recordsource="PASTE YOUR SQL WHERE [Departement]='"+Me!Combobox1+"''"

You need to replace in the expression... And you will have the needed result!

Best regards
Oct 7 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.