Cannot get the Form to update

[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

 

[Guest]

try resetting the subfrom's recordsource before requerying

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[Guest]

Excellent, thank you for the help.

That worked.

Mark

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

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

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