Cannot get the Form to update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I wonder if someone can please help me. I have the following scenario.

[Customers] - A table that contains customer information, name, address,
ZIP, etc.

[Customer Info] - A Select Query that only selects those records in the
"Customers" table, WHERE ZIP = "something". The query works fine.

Now, I have a form "Find Customers" that has a combo box [Pick_ZIP], with a
Select Query populating the drop down list, so that I can pick from a list of
ZIP codes. This works fine.

Also on the [Find Customers] form is a subform object [ZIP Query], that is
bound to Query.[Customer Info]

Now, I have written some VBA code that upon user selection from the
[Pick_ZIP] combo list, the SQL query in the [Customer Info] query is updated
to reflect the selected ZIP code, basically by modifying the WHERE statement.

The SQL change works fine, and the [Customer Info] query is updated to
refect the change.

The problem is that the [Find Customers]![ZIP Query] subform is not updated
to reflect the changes of the query unless I close the [Find Customers] form
and re-open it.

Any suggestions of how to force an update on the subform?

I have tried to "requery" it, refresh it, but with no success.

Thank you in advance for your help

Mark
 
Hi Chris,

Thanks for the feedback, unfortunatley it did not work.

The subform does not have a recordsource. Instead of using an intermediate
form it is dynamically linked to the query directly.

Thus I was expecting changes in the SQL of the Query to automatically update
any dependent forms.

Any other suggestions?

Thanks

Mark


ChrisJ said:
try resetting the subfrom's recordsource before requerying

mqshaw said:
Hello,

I wonder if someone can please help me. I have the following scenario.

[Customers] - A table that contains customer information, name, address,
ZIP, etc.

[Customer Info] - A Select Query that only selects those records in the
"Customers" table, WHERE ZIP = "something". The query works fine.

Now, I have a form "Find Customers" that has a combo box [Pick_ZIP], with a
Select Query populating the drop down list, so that I can pick from a list of
ZIP codes. This works fine.

Also on the [Find Customers] form is a subform object [ZIP Query], that is
bound to Query.[Customer Info]

Now, I have written some VBA code that upon user selection from the
[Pick_ZIP] combo list, the SQL query in the [Customer Info] query is updated
to reflect the selected ZIP code, basically by modifying the WHERE statement.

The SQL change works fine, and the [Customer Info] query is updated to
refect the change.

The problem is that the [Find Customers]![ZIP Query] subform is not updated
to reflect the changes of the query unless I close the [Find Customers] form
and re-open it.

Any suggestions of how to force an update on the subform?

I have tried to "requery" it, refresh it, but with no success.

Thank you in advance for your help

Mark
Click to expand...
Click to expand...
 
Something *must* be using query [Customer Info] or else I am missing something

Here is some code I put together as an example

Private Sub Combo1_Click()

Dim db As database
Dim qdf As querydef
Dim strsql As String

strsql = "SELECT Sheet1.* FROM Sheet1"

strsql = strsql & " Where Code = '" & Combo1.Column(0, Combo1.ListIndex) &
"';"

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.SQL = strsql
Me.Query1_subform1.Form.RecordSource = "Query1" '*******
Me.Query1_subform1.Requery
End Sub

If I omit the asterisked line, I get the same problem you describe

Does this help???


mqshaw said:
Hi Chris,

Thanks for the feedback, unfortunatley it did not work.

The subform does not have a recordsource. Instead of using an intermediate
form it is dynamically linked to the query directly.

Thus I was expecting changes in the SQL of the Query to automatically update
any dependent forms.

Any other suggestions?

Thanks

Mark


ChrisJ said:
try resetting the subfrom's recordsource before requerying

mqshaw said:
Hello,

I wonder if someone can please help me. I have the following scenario.

[Customers] - A table that contains customer information, name, address,
ZIP, etc.

[Customer Info] - A Select Query that only selects those records in the
"Customers" table, WHERE ZIP = "something". The query works fine.

Now, I have a form "Find Customers" that has a combo box [Pick_ZIP], with a
Select Query populating the drop down list, so that I can pick from a list of
ZIP codes. This works fine.

Also on the [Find Customers] form is a subform object [ZIP Query], that is
bound to Query.[Customer Info]

Now, I have written some VBA code that upon user selection from the
[Pick_ZIP] combo list, the SQL query in the [Customer Info] query is updated
to reflect the selected ZIP code, basically by modifying the WHERE statement.

The SQL change works fine, and the [Customer Info] query is updated to
refect the change.

The problem is that the [Find Customers]![ZIP Query] subform is not updated
to reflect the changes of the query unless I close the [Find Customers] form
and re-open it.

Any suggestions of how to force an update on the subform?

I have tried to "requery" it, refresh it, but with no success.

Thank you in advance for your help

Mark
Click to expand...
Click to expand...
Click to expand...
 
Excellent, thank you for the help.

That worked.

Mark


ChrisJ said:
Something *must* be using query [Customer Info] or else I am missing something

Here is some code I put together as an example

Private Sub Combo1_Click()

Dim db As database
Dim qdf As querydef
Dim strsql As String

strsql = "SELECT Sheet1.* FROM Sheet1"

strsql = strsql & " Where Code = '" & Combo1.Column(0, Combo1.ListIndex) &
"';"

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.SQL = strsql
Me.Query1_subform1.Form.RecordSource = "Query1" '*******
Me.Query1_subform1.Requery
End Sub

If I omit the asterisked line, I get the same problem you describe

Does this help???


mqshaw said:
Hi Chris,

Thanks for the feedback, unfortunatley it did not work.

The subform does not have a recordsource. Instead of using an intermediate
form it is dynamically linked to the query directly.

Thus I was expecting changes in the SQL of the Query to automatically update
any dependent forms.

Any other suggestions?

Thanks

Mark


ChrisJ said:
try resetting the subfrom's recordsource before requerying

:

Hello,

I wonder if someone can please help me. I have the following scenario.

[Customers] - A table that contains customer information, name, address,
ZIP, etc.

[Customer Info] - A Select Query that only selects those records in the
"Customers" table, WHERE ZIP = "something". The query works fine.

Now, I have a form "Find Customers" that has a combo box [Pick_ZIP], with a
Select Query populating the drop down list, so that I can pick from a list of
ZIP codes. This works fine.

Also on the [Find Customers] form is a subform object [ZIP Query], that is
bound to Query.[Customer Info]

Now, I have written some VBA code that upon user selection from the
[Pick_ZIP] combo list, the SQL query in the [Customer Info] query is updated
to reflect the selected ZIP code, basically by modifying the WHERE statement.

The SQL change works fine, and the [Customer Info] query is updated to
refect the change.

The problem is that the [Find Customers]![ZIP Query] subform is not updated
to reflect the changes of the query unless I close the [Find Customers] form
and re-open it.

Any suggestions of how to force an update on the subform?

I have tried to "requery" it, refresh it, but with no success.

Thank you in advance for your help

Mark
Click to expand...
Click to expand...
Click to expand...
 
Back
Top