By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Requery subform

P: n/a
I have an unbound main form with an unbound subform.

frmProjects
fsubProjectList

Using combo boxes, the user can select several search criteria on
frmProjects and then click a command button. The command button passes
the criteria as a string to a query. A subform, which is based on this
query, shows the results of the search.

The query is properly updated but the subform won't requery.

I've tried every imaginable variation to refer to the subform but when
the command button is clicked, the subform just blinks and nothing
changes (but if I close and reopen the form the subform is properly
updated - proof that the query was correctly updated.)

To requery the subform, I've tried:

Me!fsubProjectList.Form.Requery
Me.fsubProjectList.Requery

.... and about 500 other different ways.

Any ideas?

Thanks.

Kurt

Jul 4 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
The subform is actually held by a child object in the form. The child
object may have a different name than the actual subform. Have you tried
examining the name of the object which holds the subform and requery that?

Linda

"Kurt" <kh*******@cox.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>I have an unbound main form with an unbound subform.

frmProjects
fsubProjectList

Using combo boxes, the user can select several search criteria on
frmProjects and then click a command button. The command button passes
the criteria as a string to a query. A subform, which is based on this
query, shows the results of the search.

The query is properly updated but the subform won't requery.

I've tried every imaginable variation to refer to the subform but when
the command button is clicked, the subform just blinks and nothing
changes (but if I close and reopen the form the subform is properly
updated - proof that the query was correctly updated.)

To requery the subform, I've tried:

Me!fsubProjectList.Form.Requery
Me.fsubProjectList.Requery

... and about 500 other different ways.

Any ideas?

Thanks.

Kurt

Jul 4 '06 #2

P: n/a
I've double checked and the child object and subform have the same
name.

Any other ideas?

Linda Burnside wrote:
The subform is actually held by a child object in the form. The child
object may have a different name than the actual subform. Have you tried
examining the name of the object which holds the subform and requery that?

Linda

"Kurt" <kh*******@cox.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
I have an unbound main form with an unbound subform.

frmProjects
fsubProjectList

Using combo boxes, the user can select several search criteria on
frmProjects and then click a command button. The command button passes
the criteria as a string to a query. A subform, which is based on this
query, shows the results of the search.

The query is properly updated but the subform won't requery.

I've tried every imaginable variation to refer to the subform but when
the command button is clicked, the subform just blinks and nothing
changes (but if I close and reopen the form the subform is properly
updated - proof that the query was correctly updated.)

To requery the subform, I've tried:

Me!fsubProjectList.Form.Requery
Me.fsubProjectList.Requery

... and about 500 other different ways.

Any ideas?

Thanks.

Kurt
Jul 4 '06 #3

P: n/a
When I refer to the subform this way:

[Forms]![fsubProjectList].Form.Requery

I get a run-time error 2450:

MS Office Access can't find the form 'fsubProjectList' referred
to in a macro expression or Visual Basic code.

I understand this error is usually caused by not properly referring to
the name of the subform control, but I've double checked and the only
name in use - both for the subform and it's source object - is
fsubProjectList.

Not sure if this matters: Everything is on a tabbed control. In other
words, the unbound main form has four tabs, and the search combo boxes,
the command button (which updates the query and requeries the subform),
and the subform are all on the first tab.

Kurt

Kurt wrote:
I've double checked and the child object and subform have the same
name.

Any other ideas?

Linda Burnside wrote:
The subform is actually held by a child object in the form. The child
object may have a different name than the actual subform. Have you tried
examining the name of the object which holds the subform and requery that?

Linda

"Kurt" <kh*******@cox.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>I have an unbound main form with an unbound subform.
>
frmProjects
fsubProjectList
>
Using combo boxes, the user can select several search criteria on
frmProjects and then click a command button. The command button passes
the criteria as a string to a query. A subform, which is based on this
query, shows the results of the search.
>
The query is properly updated but the subform won't requery.
>
I've tried every imaginable variation to refer to the subform but when
the command button is clicked, the subform just blinks and nothing
changes (but if I close and reopen the form the subform is properly
updated - proof that the query was correctly updated.)
>
To requery the subform, I've tried:
>
Me!fsubProjectList.Form.Requery
Me.fsubProjectList.Requery
>
... and about 500 other different ways.
>
Any ideas?
>
Thanks.
>
Kurt
>
Jul 4 '06 #4

P: n/a

"Kurt" <kh*******@cox.netwrote in message
news:11********************@m73g2000cwd.googlegrou ps.com...
When I refer to the subform this way:

[Forms]![fsubProjectList].Form.Requery

I get a run-time error 2450:

MS Office Access can't find the form 'fsubProjectList' referred
to in a macro expression or Visual Basic code.

I understand this error is usually caused by not properly referring to
the name of the subform control, but I've double checked and the only
name in use - both for the subform and it's source object - is
fsubProjectList.

Not sure if this matters: Everything is on a tabbed control. In other
words, the unbound main form has four tabs, and the search combo boxes,
the command button (which updates the query and requeries the subform),
and the subform are all on the first tab.
You need to reference the main form and then the subform...

Forms![MainFormName]![fsubProjectList].Form.Requery

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #5

P: n/a
I had tried that as well. The results are the same. The subform blinks,
the word "Calculating ..." briefly appears in the status bar, but the
subform is not requeried.

This is driving me nuts.

Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11********************@m73g2000cwd.googlegrou ps.com...
When I refer to the subform this way:

[Forms]![fsubProjectList].Form.Requery

I get a run-time error 2450:

MS Office Access can't find the form 'fsubProjectList' referred
to in a macro expression or Visual Basic code.

I understand this error is usually caused by not properly referring to
the name of the subform control, but I've double checked and the only
name in use - both for the subform and it's source object - is
fsubProjectList.

Not sure if this matters: Everything is on a tabbed control. In other
words, the unbound main form has four tabs, and the search combo boxes,
the command button (which updates the query and requeries the subform),
and the subform are all on the first tab.

You need to reference the main form and then the subform...

Forms![MainFormName]![fsubProjectList].Form.Requery

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #6

P: n/a
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
>I had tried that as well. The results are the same. The subform blinks,
the word "Calculating ..." briefly appears in the status bar, but the
subform is not requeried.

This is driving me nuts.
Try it without the .Form. Just Requerying the subform control usually works for
me. You still need the main form reference though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #7

P: n/a
That didn't change anything.

Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
I had tried that as well. The results are the same. The subform blinks,
the word "Calculating ..." briefly appears in the status bar, but the
subform is not requeried.

This is driving me nuts.

Try it without the .Form. Just Requerying the subform control usually works for
me. You still need the main form reference though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #8

P: n/a
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@l70g2000cwa.googlegr oups.com...
That didn't change anything.
How do you know the subform is not being Requeried? If you execute that code
and you don't get an error then the Requery should be taking place.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #9

P: n/a
How do you know the subform is not being Requeried?

For example, if I select "Manuscript" for the ProjectType, and click
the command button, the subform should update to list only those
records for which "Manuscript" is the ProjectType.

But the subform doesn't update/requery to show the results of this
search.
(It still shows the results of the last search.) Of course, if I close
and reopen the form, the subform will properly show only manuscripts.
This tells me that the code which creates the query which the subform
is based on is working.

I'm completely stumped.

Kurt

Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@l70g2000cwa.googlegr oups.com...
That didn't change anything.

How do you know the subform is not being Requeried? If you execute that code
and you don't get an error then the Requery should be taking place.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #10

P: n/a
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>How do you know the subform is not being Requeried?

For example, if I select "Manuscript" for the ProjectType, and click
the command button, the subform should update to list only those
records for which "Manuscript" is the ProjectType.

But the subform doesn't update/requery to show the results of this
search.
(It still shows the results of the last search.) Of course, if I close
and reopen the form, the subform will properly show only manuscripts.
This tells me that the code which creates the query which the subform
is based on is working.

I'm completely stumped.
This is an embedded subform right? Not one opened separately? If embedded are
you using the MasterLink and ChildLink properties to display the related records
or does the subform's RecordSource reference the ProjectType on the main form in
its query criteria?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #11

P: n/a
This is an embedded subform right?

Yes.

Not one opened separately?

Correct.
If embedded are you using the MasterLink and ChildLink properties
to display the related records or does the subform's RecordSource
reference the ProjectType on the main form in its query criteria?
I'm not using the MasterLink and ChildLink properties. Instead, when
the user selects the search criteria (up to three criteria can be
selected using 3 combo boxes, one of which is ProjectType), the code
under the command button:

1) creates a query (called qryProjectList) (only does this if the query
doesn't exist),
2) gets the values from the combo boxes,
3) builds a SQL string,
4) passes the string to the query, and
5) requries the subform, which is based on this query.

Every step by the last one is working.

Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
How do you know the subform is not being Requeried?
For example, if I select "Manuscript" for the ProjectType, and click
the command button, the subform should update to list only those
records for which "Manuscript" is the ProjectType.

But the subform doesn't update/requery to show the results of this
search.
(It still shows the results of the last search.) Of course, if I close
and reopen the form, the subform will properly show only manuscripts.
This tells me that the code which creates the query which the subform
is based on is working.

I'm completely stumped.

This is an embedded subform right? Not one opened separately? If embedded are
you using the MasterLink and ChildLink properties to display the related records
or does the subform's RecordSource reference the ProjectType on the main form in
its query criteria?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 4 '06 #12

P: n/a
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@v61g2000cwv.googlegr oups.com...
>This is an embedded subform right?

Yes.

Not one opened separately?

Correct.
>If embedded are you using the MasterLink and ChildLink properties
to display the related records or does the subform's RecordSource
reference the ProjectType on the main form in its query criteria?

I'm not using the MasterLink and ChildLink properties. Instead, when
the user selects the search criteria (up to three criteria can be
selected using 3 combo boxes, one of which is ProjectType), the code
under the command button:

1) creates a query (called qryProjectList) (only does this if the query
doesn't exist),
2) gets the values from the combo boxes,
3) builds a SQL string,
4) passes the string to the query, and
5) requries the subform, which is based on this query.

Every step by the last one is working.
If the form is bound to the query how can you be "creating it"? Do you mean you
are just changing the SQL?

Have you tried using the MasterLink and ChildLink properties? They should allow
all of this without any code at all.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 5 '06 #13

P: n/a
If the form is bound to the query how can you be "creating it"? Do you mean you
are just changing the SQL?
Yes. Just changing the SQL.
Have you tried using the MasterLink and ChildLink properties? They should allow
all of this without any code at all.
I can't use those because the main form is unbound. (It's just a place
holder basically to hold the tabbed pages.)

By the way, here's the full code behind the command button. It involves
calling a function (QueryExists) which I didn't post. I could easily
remove this function and relevent code because the query always exists.
Again, all of this code works perfectly except the

' Requery the subform
Forms![frmProjects]![fsubProjectList].Form.Requery

### BEGIN CODE ###

Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strProjectType As String
Dim strProjectStatus As String
Dim strProjectOwner As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryProjectList") Then
Set qdf = db.CreateQueryDef("qryProjectList")
Else
Set qdf = db.QueryDefs("qryProjectList")
End If
' Get the values from the combo boxes
If IsNull(Me.cboProjectType.Value) Then
strProjectType = " Like '*' "
Else
strProjectType = "='" & Me.cboProjectType.Value & "' "
End If
If IsNull(Me.cboProjectStatus.Value) Then
strProjectStatus = " Like '*' "
Else
strProjectStatus = "='" & Me.cboProjectStatus.Value & "' "
End If
If IsNull(Me.cboProjectOwner.Value) Then
strProjectOwner = " Like '*' "
Else
strProjectOwner = "='" & Me.cboProjectOwner.Value & "' "
End If
' Build the SQL string
strSQL = "SELECT tblProjects.* " & _
"FROM tblProjects " & _
"WHERE tblProjects.ProjectTypeID" & strProjectType & _
"AND tblProjects.ProjectStatus" & strProjectStatus & _
"AND tblProjects.ProjectOwner" & strProjectOwner & _
"ORDER BY tblProjects.ProjectTypeID;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery,
"qryProjectList") = acObjStateOpen Then
DoCmd.Close acQuery, "qryProjectList"
End If

' Requery the subform
Forms![frmProjects]![fsubProjectList].Form.Requery

cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle Errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub
### END CODE ###

Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@v61g2000cwv.googlegr oups.com...
This is an embedded subform right?
Yes.

Not one opened separately?

Correct.
If embedded are you using the MasterLink and ChildLink properties
to display the related records or does the subform's RecordSource
reference the ProjectType on the main form in its query criteria?
I'm not using the MasterLink and ChildLink properties. Instead, when
the user selects the search criteria (up to three criteria can be
selected using 3 combo boxes, one of which is ProjectType), the code
under the command button:

1) creates a query (called qryProjectList) (only does this if the query
doesn't exist),
2) gets the values from the combo boxes,
3) builds a SQL string,
4) passes the string to the query, and
5) requries the subform, which is based on this query.

Every step by the last one is working.

If the form is bound to the query how can you be "creating it"? Do you mean you
are just changing the SQL?

Have you tried using the MasterLink and ChildLink properties? They should allow
all of this without any code at all.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 5 '06 #14

P: n/a
Got it!

Instead of passing the SQL to the query, and then requerying the
subform, I simply updated the subforms' recordsource directly with
this:

Forms!frmProjects!fsubProjectList.Form.RecordSourc e = strSQL

When you clarified whether I was creating a query or simply changing
the SQL, it occurred to me that the RecordSource would be a more
efficient way to handle this.

But, I still think the previous solution should have worked. Oh well.

Thanks again.

Kurt

Jul 5 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.