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

[exipnakias]

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.

[ADezii]

In matters such as you describe, I like to use this logic whereby a Query is dynamically created, as opposed to populating Parameter Values:

Dim qdfTest As DAO.QueryDef, strSQL As String
 
On Error Resume Next
 
If Not IsNull(Me![cboTest]) Then
  CurrentDb.QueryDefs.Delete "Test Query"
  strSQL = "Select * From Employees Where [LastName] = '" & Me![cboTest] & "'"
  Set qdfTest = CurrentDb.CreateQueryDef("Test Query", strSQL)
    DoCmd.OpenQuery "Test Query", acViewNormal, acReadOnly
End If

[NeoPa]

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.

[exipnakias]

In matters such as you describe, I like to use this logic whereby a Query is dynamically created, as opposed to populating Parameter Values:

Dim qdfTest As DAO.QueryDef, strSQL As String
 
On Error Resume Next
 
If Not IsNull(Me![cboTest]) Then
  CurrentDb.QueryDefs.Delete "Test Query"
  strSQL = "Select * From Employees Where [LastName] = '" & Me![cboTest] & "'"
  Set qdfTest = CurrentDb.CreateQueryDef("Test Query", strSQL)
    DoCmd.OpenQuery "Test Query", acViewNormal, acReadOnly
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 ?

[NeoPa]

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.

[exipnakias]

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:

...
strSQL = "Select * From [Group] Where [Group] = '" comboX.column(1) "'"
...

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.

http://rapidshare.com/files/77275426/ex1.zip.html

Thanks in advanced.

[NeoPa]

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.

[NeoPa]

...

...
strSQL = "Select * From [Group] Where [Group] = '" comboX.column(1) "'"
...

...

This looks pretty good except you've left out the ampersand (&) characters. Try :

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.

[ADezii]

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:

strSQL = "Select * From <Your Table Name> Where [Group] = '" ComboX.column(1) "'"

[NeoPa]

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)?

[ADezii]

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:

strSQL = "Select * From Group Where Group.[Group] = '" ComboX.COLUMN(1) "'"

[ADezii]

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:

strSQL = "Select * From Group Where Group.[Group] = '" ComboX.COLUMN(1) "'"

Forgot those little doohickies (&)

strSQL = "Select * From Group Where Group.[Group] = '" & ComboX.COLUMN(1) & "'"

[NeoPa]

...
Forgot those little doohickies (&)
...

And I missed the "fix" to a circular reference.
The SQL in my post (#8) should have read :

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.

[ADezii]

And I missed the "fix" to a circular reference.
The SQL in my post (#8) should have read :

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.

[NeoPa]

Hah!!
Well, I suppose I can't expect to stay perfect forever. Age catches up with us all eventually :D

[ADezii]

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).

[exipnakias]

I added " & " to the code

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.

[NeoPa]

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 ;)

[NeoPa]

I added " & " to the code

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) :

strSQL = "Select * From Group Where Group.[Group] = '" & Combo19.Column(1) & "'"
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.

[exipnakias]

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:

Private Sub cmdquery_Click()
Dim qdfTest As DAO.QueryDef, strSQL As String
 
 
On Error Resume Next
 
 
If Not IsNull(Me![Combo2]) Then
  CurrentDb.QueryDefs.Delete "GroupQuery"
  strSQL = "Select * From [Stoixeia Mathitwn] where [GROUP/ΩΡΕΣ] = '" & Combo2.Column(1) & "'"
 Call MsgBox(strSQL)
  Set qdfTest = CurrentDb.CreateQueryDef("GroupQuery", strSQL)
    DoCmd.OpenQuery "GroupQuery", acViewNormal, acReadOnly
 
 
Else
    MsgBox ("Please choose a group.")
End If
 
 
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 ;)

[NeoPa]

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.