473,287 Members | 3,253 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Requery subform

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
14 11414
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
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
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

"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
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
"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
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
"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
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
"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
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: F. Michael Miller | last post by:
I need to requery a subform from a third form and can't seem to get it to work. frmForm1 has frmAddress as a subform. The button cmdReviseAddress opens the form frmUpdateAddress where all of my...
3
by: ken | last post by:
Hi, I have a main form with a text box and a filter button. I also have a subform in the main form. When I make an entry into the filter text box and click the filter button, I change the query...
1
by: BartonConstruction | last post by:
Greetings all, I have a main form (frmClients) with two subforms (subVisits) (subAccount). I got the subforms to reflect what the main form is showing by linking master and child fields (I...
1
by: Parasyke | last post by:
I have a subform within a tabpage that will requery/refresh (?) if I press F9 and it will update the data in the subform, but I have tried various combinations of Me!SubformName.requery...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
6
by: MarkoBBC | last post by:
Hi everyone, First a brief description of my form: I have a subform within a main form. In my subform, I have a listbox displaying address information by firm name. A user first has to select a...
11
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
2
by: mslagle1 | last post by:
Hi all, I am trying to requery a subform "frmOpenWorkOrders" when a value list "status" on my main form "frmWorkOrderMain" is changed. If I change "status" then click on "frmOpenWorkOrders" and...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.