bind listbox to parameterized query?

  • Thread starter Thread starter Craig Buchanan
  • Start date Start date
C

Craig Buchanan

i would like to bind a form's listbox to a query that has a parameter
(defined in the query). what's the best way to do this? do i need to
explicity set the parameter in the form_current event and then bind the
query to the rowsource?

Thanks,

Craig
 
If I understand correctly, you want to use the parameter query as the Row
Source for the listbox, not use the listbox as the value for the parameter.

What does this parameter represent? Where should its value come from? Could
you place another control on the form that you could type in or select the
parameter value then requery the listbox?
 
Wayne-

The parameter in the query is a GUID. It should be populated from the value
contained in a text field on the form. Originally, I tried to use a sql
statement in the rowsource, referencing the form's field directly
(forms!frmGroup.GroupId), but I was getting an error message about No
current row.

As a result, I changed the code to set the listbox's recordset property to
the results of the parameterized query. This works, but it requires quite a
bit more coding.

Is there a way to get the sql statement/row source method to work w/ GUIDs?
That seems to be the central issue.

Craig
 
Have you tried creating a stored query that uses the parameter? If so, if
you open the query, does it work? Try setting the parameter in the stored
query to the control on the form. The syntax you indicated should work. I
just want to check to make sure the "form" isn't a subform. If the query is
working, set the listbox's Row Source Type to Table/Query and the Row Source
to the stored query. You will need to requery the listbox whenever the value
in the form control that the parameter is based on is changed.

Me.lstMyListbox.Requery
or
Forms!frmMyForm!lstMyListbox.Requery

If the stored query doesn't work, please post back with the SQL view of the
query copied and pasted into the message. I've read of potential problems
with GUIDs in how they are handled. From what I remember they are sometimes
handled as Text and other times they are handled differently. If the stored
query doesn't work, I'll look up the information and post it.
 
Wayne-

I tried a parameterized query that referenced form fields--it didn't work.
I requeried on the form_current event.

At this point, i'm running the 'stored procedure' via the querydef interface
and assigning the recordset to the listbox.

Here's the proc:

PARAMETERS [Guid] Guid;
SELECT Person.Id, [LastName] & ", " & [FirstName] AS FileAs
FROM Person
WHERE (((Person.Id) Not In (SELECT PersonId FROM GroupPerson WHERE GroupId
=[Guid])))
ORDER BY Person.LastName, Person.FirstName;

Thanks,

Craig
 
With what you have, if you open the query by double clicking it in the
Queries window it should prompt you for [Guid]. If you type it in, does it
work? To point it to a value in a control on a form you would replace [Guid]
(in both locations) with

[Forms]![frmFormName]![ctlControlName]

Also, it is recommended to not use reserved words as variable or field
names. As you can see, you are using GUID as a data type in the PARAMETERS
statement. With the brackets around the other usage, you should be ok, but
it's not recommended.

I'm not at home. When I get home I'll look up the reference I mentioned
previously on GUIDs.
 
wayne-
With what you have, if you open the query by double clicking it in the
Queries window it should prompt you for [Guid]. If you type it in, does it
work? works

To point it to a value in a control on a form you would replace [Guid] (in
both locations) with [Forms]![frmFormName]![ctlControlName]
Click to expand...
doesn't work

thanks,

craig
 
I just want to verify that the form is open and the value is in the control
listed in the parameter (if the form isn't open, it won't work). Also, that
you did change the names in what I typed to the actual names of your form
and control.

What type of control is it? If it is a multicolumn control, such as a
listbox or combo box, the value comes from the bound column. To get the
value of a different column, you have to specify which column you want it
from. If it is a listbox, is the listbox set for Simple or Extended
multi-select? If so, then more work will need to be done. The "value" of a
multi-select listbox is gotten differently.

Did you read the information in the web link supplied in my 2nd reply?

What happens if you wrap the second "[Guid]" with Cstr?

WHERE GroupId = CStr([Forms]![frmFormName]![ctlControlName])
or
WHERE CStr([GroupId]) = CStr([Forms]![frmFormName]![ctlControlName])

Have you tried rewriting the query in the AfterUpdate event of the control
that you enter the GUID?

Example:
strSQL = "SELECT Person.Id, [LastName] & ", " & [FirstName] AS FileAs" & _
" FROM Person" & _
" WHERE (((Person.Id) Not In (SELECT PersonId FROM GroupPerson WHERE
GroupId" & _
"=" & Me.ctrControlName & ")))" & _
" ORDER BY Person.LastName, Person.FirstName;"

CurrentDb.QueryDefs("qryNameOfQuery").SQL = strSQL

There will be no need for a "parameter" because you will be placing the
value directly in the WHERE clause.

--
Wayne Morgan
MS Access MVP


Craig Buchanan said:
wayne-
With what you have, if you open the query by double clicking it in the
Queries window it should prompt you for [Guid]. If you type it in, does
it work? works

To point it to a value in a control on a form you would replace [Guid] (in
both locations) with [Forms]![frmFormName]![ctlControlName]
Click to expand...
doesn't work
Click to expand...
 
wayne-

the form is open. the guid is contained in a textbox. the query references
the textbox with the syntax [forms]![frmGroup]![GroupId], where GroupId is
the name of the textbox.

I haven't tried cstr() function. i tried using the stringfromguid() and
guidfromstring() functions without success.

at this point, i've create a class named 'Group' which returns a recordset
via the 'Members' and 'NonMembers' properties. i then set the recordset
properties of the two listboxes to these properties.

my plan is to change these methods return values to collections instead of
recordsets to further abstract the middle tier.

i'll send you the MDBs if you want.

craig

Wayne Morgan said:
I just want to verify that the form is open and the value is in the control
listed in the parameter (if the form isn't open, it won't work). Also, that
you did change the names in what I typed to the actual names of your form
and control.

What type of control is it? If it is a multicolumn control, such as a
listbox or combo box, the value comes from the bound column. To get the
value of a different column, you have to specify which column you want it
from. If it is a listbox, is the listbox set for Simple or Extended
multi-select? If so, then more work will need to be done. The "value" of a
multi-select listbox is gotten differently.

Did you read the information in the web link supplied in my 2nd reply?

What happens if you wrap the second "[Guid]" with Cstr?

WHERE GroupId = CStr([Forms]![frmFormName]![ctlControlName])
or
WHERE CStr([GroupId]) = CStr([Forms]![frmFormName]![ctlControlName])

Have you tried rewriting the query in the AfterUpdate event of the control
that you enter the GUID?

Example:
strSQL = "SELECT Person.Id, [LastName] & ", " & [FirstName] AS FileAs" & _
" FROM Person" & _
" WHERE (((Person.Id) Not In (SELECT PersonId FROM GroupPerson WHERE
GroupId" & _
"=" & Me.ctrControlName & ")))" & _
" ORDER BY Person.LastName, Person.FirstName;"

CurrentDb.QueryDefs("qryNameOfQuery").SQL = strSQL

There will be no need for a "parameter" because you will be placing the
value directly in the WHERE clause.

--
Wayne Morgan
MS Access MVP


Craig Buchanan said:
wayne-
With what you have, if you open the query by double clicking it in the
Queries window it should prompt you for [Guid]. If you type it in, does
it work? works

To point it to a value in a control on a form you would replace [Guid]
(in both locations) with [Forms]![frmFormName]![ctlControlName]
Click to expand...
doesn't work
Click to expand...
Click to expand...
 
Back
Top