Refresh form based on parameter query from VBA

[Matthias Claes]

Access 2000
I have a form that has a pre-defined parameter query (qry_UserMasterRecord)
set as RecordSource.
The query is expected to select only one single row. (The parameter is set
on the primary key field)
The form has a non data-bound listbox (lbUsers) on it containing possible
values values for the parameter.
(The rowsource is another query on the same table)

I'd like the form to refresh it's contents everytime the users selects a
value in the listbox.
For now, I tried the OnClick event of the listbox, and changing the
parameter's value from VBA:

Private Sub lbUsers_Click()
'select the user in the form's query
Dim qd As QueryDef

Set qd = CurrentDb.QueryDefs("qry_UserMasterRecord")

With qd
.Parameters("[ID:]").Value = Me!lbUsers.Value
End With


Me.Refresh -> data on form doesn't change
Me.Requery -> causes the form the show the parameter dialog
End Sub

I don't get any errors, but the form doesn't refresh it's fields.
I somehow have to find a way to execute the qry_UserMasterRecord query
again, with the new parameter value.
And for the form to refresh the data from the parameter query.
Any help would be appreciated.


Matthias Claes

 

[John Griffiths]

RecordSource="SELECT * FROM qry_UserMasterRecord WHERE [ID:] =
FORMS!YOURFORMNAME!lbUsers"

In case of line breaks
RecordSource="SELECT * " & _
"FROM qry_UserMasterRecord " & _
"WHERE [ID:] = FORMS!YOURFORMNAME!lbUsers"

Regards John

 

[Matthias Claes]

RecordSource="SELECT * FROM qry_UserMasterRecord WHERE [ID:] =

FORMS!YOURFORMNAME!lbUsers"

In case of line breaks
RecordSource="SELECT * " & _
"FROM qry_UserMasterRecord " & _
"WHERE [ID:] = FORMS!YOURFORMNAME!lbUsers"

That's exactly what I want to avoid.
In your example, you simply reset the query from code.
I was under impression that using parametrized queries offer greater benefit
and performance,
especially if you intend to migrate your app to a SQL Server backend later
on.
However, not a single example I found on the web uses a parameter query to
update form contents,
they all do it the way you've shown: by rewriting the SQL statement from
code.
If this is 'the way to do it' in Access, then how do you maintain all your
VBA based SQL statements in a large project?

Matthias Claes

 

[John Griffiths]

RecordSource="SELECT * FROM qry_UserMasterRecord WHERE [ID:] =
FORMS!YOURFORMNAME!lbUsers"

In case of line breaks
RecordSource="SELECT * " & _
"FROM qry_UserMasterRecord " & _
"WHERE [ID:] = FORMS!YOURFORMNAME!lbUsers"

That's exactly what I want to avoid.
In your example, you simply reset the query from code.

No I bound the RecordSource parameter to a specific control on the form,
requery of the form ie using OnClick of lbUsers does't require any further
coding.

Copy and paste the first RecordSource string without the " quotes into the
RecordSource property using the user interface (property inspector).

IE :-
"SELECT *
FROM qry_UserMasterRecord
WHERE [ID:]=FORMS!YOURFORMNAME!lbUsers"

Then :-
Private Sub lbUsers_Click()
Me.Requery
End Sub
:- Will have the desired effect.

The code you had posted could not work as it didn't change the underlying
recordset of the form.

I was under impression that using parametrized queries offer greater benefit
and performance, especially if you intend to migrate your app to a SQL Server
backend later on.

A parameterized query on Access is not compiled in the same way as
Oracle/SQL Server.

There are many advantages to using Views, Parameterized queries,
stored procedures on the back end; however Access will still have
to use the returned recordset.

IE Changing the user in the list would require creating a new recordset from
the backend,
Access would still use the FORMS!YOURFORMNAME!lbUsers to populate
the parameter to generate the recordset.

However, not a single example I found on the web uses a parameter query to
update form contents, they all do it the way you've shown:
by rewriting the SQL statement from code.

If this is 'the way to do it' in Access, then how do you maintain all your
VBA based SQL statements in a large project?

Lot's of people do misuse the recordsource property in the way you describe,
however check out
the Filter property.
Me.Filter = "[ID:] = '" & lbUsers & "'"
Me.FilterOn = True

Matthias Claes

Regards John

 

[duncanjeff]

Try this after defining your parameters..

Set rst = qd.openrecordset
Set me.recordset = rst
me.repaint

Hope this helps!!
Jeff