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

1) Query Parameter From ListBox? 2) Combo Box Query Parameter?

P: 17
Hello Guys. In a form I created a listbox which looks up the values of a table.

I want:

1)
..to create a query where a parameter will be needed in order to be loaded.
But I do not want to write the parameter, I want to select a row from the listbox and after clicking in a button, the query to automatically take the current value of listbox as the needed parameter and then to load the query.
(The Inputbox for "Enter Parameter" will not be showed as the db will automatically takes the listbox value for the required parameter).

Is this possible ? And How can I do it ?
Please tell me the whole procedure to do this (from the query creation to VBA code).

2) Then I created a form based on this query.
I want to have a combo box to look up the current records of the form.
As I realised, If I inserted a combo box in a "form query" that ask for a parameter before it loads, you need to re-write the parameter when you click to combo box in order for the current values to be showed.
So I would like to know if there is a way, after loading the "Form query", the combo box to take the parameter of the chosen row in the listbox in order not to be needed to write the parameter.

If someone can help me, I would really appreciate it.
Thanks in advanced.
Dec 16 '07 #1
Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,638
In matters such as you describe, I like to use this logic whereby a Query is dynamically created, as opposed to populating Parameter Values:
Expand|Select|Wrap|Line Numbers
  1. Dim qdfTest As DAO.QueryDef, strSQL As String
  2.  
  3. On Error Resume Next
  4.  
  5. If Not IsNull(Me![cboTest]) Then
  6.   CurrentDb.QueryDefs.Delete "Test Query"
  7.   strSQL = "Select * From Employees Where [LastName] = '" & Me![cboTest] & "'"
  8.   Set qdfTest = CurrentDb.CreateQueryDef("Test Query", strSQL)
  9.     DoCmd.OpenQuery "Test Query", acViewNormal, acReadOnly
  10. End If
Dec 16 '07 #2

NeoPa
Expert Mod 15k+
P: 31,492
Two ways, that should work both for your main form and your ComboBox, are :
  1. As ADezii has explained, build the SQL on-the-fly.
  2. Include a reference to the Listbox in the query (or SQL string). The currently selected item's value will be the value returned by the reference.
Dec 17 '07 #3

P: 17
In matters such as you describe, I like to use this logic whereby a Query is dynamically created, as opposed to populating Parameter Values:
Expand|Select|Wrap|Line Numbers
  1. Dim qdfTest As DAO.QueryDef, strSQL As String
  2.  
  3. On Error Resume Next
  4.  
  5. If Not IsNull(Me![cboTest]) Then
  6.   CurrentDb.QueryDefs.Delete "Test Query"
  7.   strSQL = "Select * From Employees Where [LastName] = '" & Me![cboTest] & "'"
  8.   Set qdfTest = CurrentDb.CreateQueryDef("Test Query", strSQL)
  9.     DoCmd.OpenQuery "Test Query", acViewNormal, acReadOnly
  10. End If

this is does not work correct. " Me![cboTest] " returns the number or row and not the combo contents in order to compare it with the values of field..so the results are wrong

I know that the command Combo1.Column (1) returns the content of combo but when I write it to the given code it ask to enter for a parameter first.
What to do ?
Dec 17 '07 #4

NeoPa
Expert Mod 15k+
P: 31,492
this is does not work correct. " Me![cboTest] " returns the number or row and not the combo contents in order to compare it with the values of field..so the results are wrong
If you configure cboTest to return the number or row instead of the visible data, then that's what it will return to you. This is not related to the code.
I know that the command Combo1.Column (1) returns the content of combo but when I write it to the given code it ask to enter for a parameter first.
What to do ?
You can explain your problem a bit more clearly.
We can't see what else you've got wrong so it's a bit difficult to tell you what to fix.
Dec 17 '07 #5

P: 17
If you configure cboTest to return the number or row instead of the visible data, then that's what it will return to you. This is not related to the code.

You can explain your problem a bit more clearly.
We can't see what else you've got wrong so it's a bit difficult to tell you what to fix.

I would like to first thank you for your care.

You are right I was not as clear as clear as I have to.

Lets take it from the beginning:

Lets say that in a database:
I have a table called Group. This table has three Fields. "ID", "Group" and "Teacher".
I now want to create a query which will show me the teacher of a selected group. The mask of Group Field is someting like that "ΔΕΤ-ΠΕΜ/14:30-16:30" (this is in greek).
The problem is that, I do not want to create a query which will ask for a parameter for group as it is too long (its size) and so very easy for mistakes.

I then thought to create a form. In this form, I put a combobox which looks up the values of "Group" field in the "Group" table.

Now my problem is that I dont know how to do the following:
To click in a button and then a query to take the content of the combobox and use it as the needed parameter in order to present the right results.


You have already given me a solution but when I tried it, It creates a query and takes the VALUE of combobox (I mean the number of row 1,2,etc..).
This has as a result the query not to have any records in the query.


I know that the command "ComboX.Column(1)" returns the content of the combobox, so I tried to change the code to this:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. strSQL = "Select * From [Group] Where [Group] = '" comboX.column(1) "'"
  3. ...
But when I click on the button, it askes to write a parameter for Combox.column(1) or it opens the query without records.


Why is this happening? Do you believe that there is an easier way to solve my problem (which is not to write the parameter but to choose it from somewhere)?

This the quection 1)

I believe that question 2) is clearly given. :)


I uploaded an example which it shows what I tried to you.
Expand|Select|Wrap|Line Numbers
  1. http://rapidshare.com/files/77275426/ex1.zip.html
Thanks in advanced.
Dec 17 '07 #6

NeoPa
Expert Mod 15k+
P: 31,492
For your first question, look in the properties of your ComboBox [comboX]. There will be two important ones that effect this issue, ColumnCount & BoundColumn. If ColumnCount is greater than one (if I understand you correctly then it is) then you need to set the BoundColumn to that which returns the data you want. I don't know what the RowSource is for comboX so I can't help much more with that but I'm sure you understand what to do here. Let me know if not.
Dec 17 '07 #7

NeoPa
Expert Mod 15k+
P: 31,492
...
Expand|Select|Wrap|Line Numbers
  1. ...
  2. strSQL = "Select * From [Group] Where [Group] = '" comboX.column(1) "'"
  3. ...
...
This looks pretty good except you've left out the ampersand (&) characters. Try :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From [Group] Where [Group] = '" & comboX.Column(1) & "'"
I can't tell you if Column(1) is correct, but if it is the changed code should work. Clearly [Group] must be a string (text) field.

Try this out and see where that leaves you. If you still need question two answered then try to express it again. I'm sorry but I'm not comfortable uploading files onto my system when I don't know who they're from.

** As Admin **
We don't expect our experts to need to upload files, but they can choose to if they are happy to.

ADMINISTRATOR.
Dec 17 '07 #8

ADezii
Expert 5K+
P: 8,638
this is does not work correct. " Me![cboTest] " returns the number or row and not the combo contents in order to compare it with the values of field..so the results are wrong

I know that the command Combo1.Column (1) returns the content of combo but when I write it to the given code it ask to enter for a parameter first.
What to do ?
Your problem may reside in the SQL Statement, try:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From <Your Table Name> Where [Group] = '" ComboX.column(1) "'"
Dec 17 '07 #9

NeoPa
Expert Mod 15k+
P: 31,492
From post #6 ADezii, Table = [Group] & one field = [Group].
Also, am I missing something here? Can the ampersands be omitted with no effect (I'd hate to tell the OP it's a problem just to find out I had it wrong)?
Dec 17 '07 #10

ADezii
Expert 5K+
P: 8,638
From post #6 ADezii, Table = [Group] & one field = [Group].
Also, am I missing something here? Can the ampersands be omitted with no effect (I'd hate to tell the OP it's a problem just to find out I had it wrong)?
Thanks NeoPa, I missed the obvious. Could it possible be something similar to a Circular Reference? In that case, a simple change should fix it:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" ComboX.COLUMN(1) "'"
Dec 18 '07 #11

ADezii
Expert 5K+
P: 8,638
Thanks NeoPa, I missed the obvious. Could it possible be something similar to a Circular Reference? In that case, a simple change should fix it:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" ComboX.COLUMN(1) "'"
Forgot those little doohickies (&)
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" & ComboX.COLUMN(1) & "'"
Dec 18 '07 #12

NeoPa
Expert Mod 15k+
P: 31,492
...
Forgot those little doohickies (&)
...
And I missed the "fix" to a circular reference.
The SQL in my post (#8) should have read :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From [Group] Where Group.Group = '" & comboX.Column(1) & "'"
Although I'm still not convinced that the added complication of the comboX where ".Column(1)" is included should be necessary. That's for the OP to sort out though.
Dec 18 '07 #13

ADezii
Expert 5K+
P: 8,638
And I missed the "fix" to a circular reference.
The SQL in my post (#8) should have read :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From [Group] Where Group.Group = '" & comboX.Column(1) & "'"
Although I'm still not convinced that the added complication of the comboX where ".Column(1)" is included should be necessary. That's for the OP to sort out though.
You're missing a lot these days, feeling OK lately? (LOL). I always miss a lot, so it's SOP for me, but for you, it is a different story.
Dec 18 '07 #14

NeoPa
Expert Mod 15k+
P: 31,492
Hah!!
Well, I suppose I can't expect to stay perfect forever. Age catches up with us all eventually :D
Dec 18 '07 #15

ADezii
Expert 5K+
P: 8,638
Hah!!
Well, I suppose I can't expect to stay perfect forever. Age catches up with us all eventually :D
I'm 58, are you catching up to me yet? BTW, how old is Mary, or is that a sensitive Topic? (LOL).
Dec 18 '07 #16

P: 17
I added " & " to the code

Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" & Combo19.Column(1) & "'"
...but now when I click to the button it happens nothing. The query does not open.
Dec 18 '07 #17

NeoPa
Expert Mod 15k+
P: 31,492
I'm 58, are you catching up to me yet? BTW, how old is Mary, or is that a sensitive Topic? (LOL).
I know ADezii. It's there in your profile for all (who think to look) to see :)
I don't think giving out a lady's age would be my place. I will only say that she's younger than both of us ;)
Dec 18 '07 #18

NeoPa
Expert Mod 15k+
P: 31,492
I added " & " to the code

Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" & Combo19.Column(1) & "'"
...but now when I click to the button it happens nothing. The query does not open.
Why does the name of the control keep on changing. Can we not work on the one problem without the changes being introduced all the time. Not a good idea.
Anyway, try the following (use the current name for your ComboBox) :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select * From Group Where Group.[Group] = '" & Combo19.Column(1) & "'"
  2. Call MsgBox(strSQL)
When you run it, think about what the message means that you see. If it doesn't help you then post it in here and we'll see if it makes sense to us. We will probably need to know the sort of thing you expect to see here though if you can't see the problem from there.
Dec 18 '07 #19

P: 17
I first tried the code in a trial database and then I tried to embody it to my main database. Thats why names are different.

Original Names are:

Table: Stoixeia Mathitwn
Parameter Field: [GROUP/ΩΡΕΣ] (second word is a greek one)
Query name: GroupQuery
Name of form where Combo box and command button is: SeletQuery
Command Button: cmdquery

so code is now that:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdquery_Click()
  2. Dim qdfTest As DAO.QueryDef, strSQL As String
  3.  
  4.  
  5. On Error Resume Next
  6.  
  7.  
  8. If Not IsNull(Me![Combo2]) Then
  9.   CurrentDb.QueryDefs.Delete "GroupQuery"
  10.   strSQL = "Select * From [Stoixeia Mathitwn] where [GROUP/ΩΡΕΣ] = '" & Combo2.Column(1) & "'"
  11.  Call MsgBox(strSQL)
  12.   Set qdfTest = CurrentDb.CreateQueryDef("GroupQuery", strSQL)
  13.     DoCmd.OpenQuery "GroupQuery", acViewNormal, acReadOnly
  14.  
  15.  
  16. Else
  17.     MsgBox ("Please choose a group.")
  18. End If
  19.  
  20.  
  21. End Sub
When I click on the button, msgbox says:

Select * From [Stoixeia Mathitwn] where [Groups/ΩΡΕΣ]=' '

It only reads the ' ' why? In SQL View says the same, and when I go to Design View of GroupQuery, [Groups/ΩΡΕΣ] has as a criteria ' ' .

Another strange thing is that:

I have a table called "Set Groups". In this table I set the groups that I need.
[Groups/ΩΡΕΣ] field from [Stoixeia Mathitwn] is set to look up the values of "Set Groups" table so as I do not have to right again and again the group but just to choose it.

Combo2 (which is on "SelectQuery" form) is also set to look up the values of "Set Groups" table for the same reason.

When I code the button cmdquey with this given code, the query it opens without results as there is a problem with SQL code (does not recognized the content of combo2 and takes only ' ' as the parameter).


Strange thing is that: In the trial database I made a table called Group which had 3 fields ID, Group, Teacher. In Group field I wrote a different group in every different record (it did not set to look up the values from another table).

In combobox I chose to look up the values of Group field from Group table and not for a third table. When I used the above code everything was OK. Query was run correclty with the correct results.
Whenever I chose a group from combo, and I clicked to button, the old query was deleted and another one is created which had as a parameter in Group Field the content that I chose from combobox. (I checked it from Query Design View and its SQL Code). Everytime this parameter was being changed.

Now, I can not understand why this is happended, why in my main database, where the [GROUP/ΩΡΕΣ] field and combobox looks up the value from the same (third) tablel, the program is confused. The problem was created because of the third table ?



I hope all of this to be helpful. I have to say that you are very patient persons ;)
Dec 19 '07 #20

NeoPa
Expert Mod 15k+
P: 31,492
Just posting to remind myself that this has not been completed yet.
Sorry, I must have overlooked it. I'll see what I can do to provide a response sometime soon.
Jan 6 '08 #21

Post your reply

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