Call Parameter Query from VB

[Guest]

I have a parameter query that gets user input, and then displays the query
result in a Form. The Query selects and combines data from multiple tables
onto the Form. This is a read only form, since it is not displaying the
actual fields from the data base.

If the user needs to change something, I have an "Edit This" command button
that opens the specific data element in a new form which is based on the
original table.

When the user makes a change and closes the "Edit This" form, the new data
is not reflected in the original query form. I know that the original form
needs to requery, but I am not sure how to do that without the user having to
enter the original parameter again.

How do I get that original form, to rerun the query without having the user
reenter the original parameter?

Thanks,
Steve

 

[Michel Walsh]

Hi,


To "filter" in addition to what is already initially restricted by a
criteria, use


Me.Filter = "FieldName=what"
Me.FilterOn = true


Is it what you want?


Hoping it may help
Vanderghast, Access MVP

 

[Guest]

I don't think so. Let me try to explain it again.

I have a form that has its Source set to a query that asks the user to enter
criteria (lets just say a last name), and then displays data from several
tables.

For simplicity, I have placed some ccommand buttons on the form, so that if
the user wants to edit some of the data, it opens a new form with the
specific data in it so that it can be edited.

When the user closes the "editing" form, the original form is still open,
but is now displaying old data.

What I want to do, is have the original form re-query the data, but without
asking the user for the criteria again.

Does that make sense?

Steve

 

[Michel Walsh]

Hi,


If you don't need to see NEW records, just the existing one, but with their
modified values,


Me.Refresh


won't ask for the parameters.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I tried that, but it doesn't work. I think it is becasue the form is not
display data directly from the table, but from a query which is combining and
calculating the various fields, so the "refresh" doesn't work. ReQuery will,
but it prompts the user, so I was looking for a way to supply the parameter
that the user already supplied and do a ReQuery.

Steve

 

[Michel Walsh]

Hi,

If you have your hands on the value of the parameter, then:



Option Compare Database
Option Explicit
Dim qdf As QueryDef
Dim rst As DAO.Recordset

Private Sub Command2_Click()

Set qdf = CurrentDb.QueryDefs("MyQuery")
qdf.Parameters(0) = 2 '<---- the parameter
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set Me.Recordset = rst
End Sub




To get the values of the parameter(s), someone can include them in the
SELECT list:


SELECT f1, f2, f3, param1
FROM myTable
WHERE f5>= param1



and thus, they will be in the recordset of the form that has been open on
the said query.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

That's exactly what I was looking for.

Thanks a bunch.

Steve