473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 3215
PEB
1,418 Expert 1GB
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
isetea
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
1,418 Expert 1GB
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

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

Similar topics

3
by: Kevin Pedersen | last post by:
Hello, I am using an editable datagrid. After I save the changes the datagrid shows the old values. I've read the posts about the Page_Load and not binding the datagrid each time. The SQL that...
5
by: keri | last post by:
Hi, I am struggling to learn access and VBA whilst creating a db, progress is slow! So far I have managed to run cascading combo boxes on a form by using a sub in the after update procedure of...
5
by: njb35 | last post by:
Hi all I'm beginning my foray from VBA into VB 2005 Express, and enjoying some of the efficiencies it provides! I'm stuck with some dataset handling however that I _think_ can be automated but...
1
by: bjgdal | last post by:
I am currently trying to create an application which will help me in my work. To do this I need to update a field called "Installations" in a table with the same value for all records. The value...
4
by: Swinky | last post by:
I am trying to make a combo box dependent on a text box. I cannot get the combo box to pull the related data from the text box. I have been all over this user group & have tried several versions...
7
by: Rotsey | last post by:
Hi, I am having a problem trying to select an item with the enter key. I want to work the combo with the keyboard. So when I use the down arrow to browse the list I want to then hit the...
0
by: XenReborn | last post by:
Ok this should be simple. I made a form, added a combobox (for selecting items to edit, not for updating fields), several textboxes, a few checkboxes etc. On formshow it connects to my database,...
2
by: sirdavethebrave | last post by:
Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to...
2
by: gagandeepsngh | last post by:
'Code for selecting a particular data from database '***** START ***** sSQL = "SELECT name FROM trans" cmd = New OleDbCommand(sSQL, con) Dim reader As OleDbDataReader = cmd.ExecuteReader()...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.