Refreshing subdata form

R

Robert Robinson

I have created a form which has a main sheet (with lease header information)
and a subform (which has lease detail information). I have established the
necessary one-to-many relationship between the header and the detail. The
detail will often consist of 50+ lines, so I created a macro which updates
the detail table directly. The macro works perfectly; the problem is with the
subform. The subform does not display the lines that have been added to the
base table. From what I can see, the subform perform a query to set up the
fields allowing user entry; however, it does not refresh when data is entered
into the table by means other than entry through the form (subform). Is my
understanding correct? If so (or not), what is the most efficient way to
refresh the subform so that it reflects the imported data?
 
K

kingston via AccessMonster.com

Use the VBA command:
Me.subform_control_name.Requery
Hopefully, the data import is done via your form so there is an event trigger
that you can use.
 
R

Robert Robinson

Kingston -

Here is my VBA code for the combination upload/refresh:

Private Sub Import_Lease_Detail_Click()
On Error GoTo Err_Import_Lease_Detail_Click
Dim stDocName As String
stDocName = "Lease Detail upload"
DoCmd.RunMacro stDocName
Me.Agreement_Detail_subform.Requery
Exit_Import_Lease_Detail_Click:
Exit Sub
Err_Import_Lease_Detail_Click:
MsgBox Err.Description
Resume Exit_Import_Lease_Detail_Click
End Sub

The lease detail uploads, but the subform does not requery. Not sure what I
am missing...
 
K

kingston via AccessMonster.com

I don't see anything wrong with your code. Does it give you any error
messages? If it doesn't then the requery command is executing and just not
working like you think it should because of the way your forms and data are
tied together. Make sure the subform control name is
"Agreement_Detail_subform"; this is not necessarily the same as the subform
name. In design view of the main form, look for the subform control's name.
When you type "Me." while coding, does the name of the subform control
automatically appear? Is the subform bound to the data table or query that
you want to refresh (check the subform's record source)? Are there any
filters turned on? They conflict with subforms. Also, double-check your
macro by running it and then opening the subform independently of the main
form. Do the correct records appear then?

Robert said:
Kingston -

Here is my VBA code for the combination upload/refresh:

Private Sub Import_Lease_Detail_Click()
On Error GoTo Err_Import_Lease_Detail_Click
Dim stDocName As String
stDocName = "Lease Detail upload"
DoCmd.RunMacro stDocName
Me.Agreement_Detail_subform.Requery
Exit_Import_Lease_Detail_Click:
Exit Sub
Err_Import_Lease_Detail_Click:
MsgBox Err.Description
Resume Exit_Import_Lease_Detail_Click
End Sub

The lease detail uploads, but the subform does not requery. Not sure what I
am missing...
Use the VBA command:
Me.subform_control_name.Requery
[quoted text clipped - 12 lines]
 
R

Robert Robinson

Not an error message.

When I start the VBA code "Me.", yes, "Agreement_Detail_Subform" does appear
(a handy little tool).

There are no filters.

This is the darnedest thing; by all rights, this bloody bit of code should
work. I even created a general "Requery" Macro, and ran it in the form with
no errors, but no results...
--
Robert Robinson


kingston via AccessMonster.com said:
I don't see anything wrong with your code. Does it give you any error
messages? If it doesn't then the requery command is executing and just not
working like you think it should because of the way your forms and data are
tied together. Make sure the subform control name is
"Agreement_Detail_subform"; this is not necessarily the same as the subform
name. In design view of the main form, look for the subform control's name.
When you type "Me." while coding, does the name of the subform control
automatically appear? Is the subform bound to the data table or query that
you want to refresh (check the subform's record source)? Are there any
filters turned on? They conflict with subforms. Also, double-check your
macro by running it and then opening the subform independently of the main
form. Do the correct records appear then?

Robert said:
Kingston -

Here is my VBA code for the combination upload/refresh:

Private Sub Import_Lease_Detail_Click()
On Error GoTo Err_Import_Lease_Detail_Click
Dim stDocName As String
stDocName = "Lease Detail upload"
DoCmd.RunMacro stDocName
Me.Agreement_Detail_subform.Requery
Exit_Import_Lease_Detail_Click:
Exit Sub
Err_Import_Lease_Detail_Click:
MsgBox Err.Description
Resume Exit_Import_Lease_Detail_Click
End Sub

The lease detail uploads, but the subform does not requery. Not sure what I
am missing...
Use the VBA command:
Me.subform_control_name.Requery
[quoted text clipped - 12 lines]
understanding correct? If so (or not), what is the most efficient way to
refresh the subform so that it reflects the imported data?
 
K

kingston via AccessMonster.com

Try this:

Me.Agreement_Detail_subform.Form.Requery

Robert said:
Not an error message.

When I start the VBA code "Me.", yes, "Agreement_Detail_Subform" does appear
(a handy little tool).

There are no filters.

This is the darnedest thing; by all rights, this bloody bit of code should
work. I even created a general "Requery" Macro, and ran it in the form with
no errors, but no results...
I don't see anything wrong with your code. Does it give you any error
messages? If it doesn't then the requery command is executing and just not
[quoted text clipped - 34 lines]
 
R

Robert Robinson

Tried it. Populates the table fine, does not update the subform, no errors
found.

Am checking the subform to see if anything would prevent updates...
--
Robert Robinson


kingston via AccessMonster.com said:
Try this:

Me.Agreement_Detail_subform.Form.Requery

Robert said:
Not an error message.

When I start the VBA code "Me.", yes, "Agreement_Detail_Subform" does appear
(a handy little tool).

There are no filters.

This is the darnedest thing; by all rights, this bloody bit of code should
work. I even created a general "Requery" Macro, and ran it in the form with
no errors, but no results...
I don't see anything wrong with your code. Does it give you any error
messages? If it doesn't then the requery command is executing and just not
[quoted text clipped - 34 lines]
understanding correct? If so (or not), what is the most efficient way to
refresh the subform so that it reflects the imported data?
 
K

kingston via AccessMonster.com

Perhaps the subform and main form already have a parent-child link
established? If so, clear the Link Child Fields and Link Master Fields
properties in the subform control. What data is the subform bound to? Is it
a table or a query? Either way, make sure the table or query displays the
correct data after your import macro. Open the subform by itself to check
this.

Robert said:
Tried it. Populates the table fine, does not update the subform, no errors
found.

Am checking the subform to see if anything would prevent updates...
Try this:
[quoted text clipped - 15 lines]
 
R

Robert Robinson

You are correct, sir.

I have 3 fields linked parent to child. I think you have identified a design
flaw. I duplicated the 3 fields to mimic an ERP program (for user
comfortability), but it looks like this design prevents the refresh of data
after the upload. As soon as I removed the parent/child link, the refresh
took place automatically. Thanks for your help.
--
Robert Robinson


kingston via AccessMonster.com said:
Perhaps the subform and main form already have a parent-child link
established? If so, clear the Link Child Fields and Link Master Fields
properties in the subform control. What data is the subform bound to? Is it
a table or a query? Either way, make sure the table or query displays the
correct data after your import macro. Open the subform by itself to check
this.

Robert said:
Tried it. Populates the table fine, does not update the subform, no errors
found.

Am checking the subform to see if anything would prevent updates...
Try this:
[quoted text clipped - 15 lines]
understanding correct? If so (or not), what is the most efficient way to
refresh the subform so that it reflects the imported data?
 
R

Robert Robinson

As an addendum (so others do not make my mistake), the primary link between
the parent and child forms (necessary for the subform to function properly)
did NOT cause the problem with refreshing the subform. The 2nd and 3rd links
were what prevented the refreshing of the subform using the "Requery" command
or macro.
Oh well, you learn best by getting caught in these programming "cracks"...
--
Robert Robinson


kingston via AccessMonster.com said:
Perhaps the subform and main form already have a parent-child link
established? If so, clear the Link Child Fields and Link Master Fields
properties in the subform control. What data is the subform bound to? Is it
a table or a query? Either way, make sure the table or query displays the
correct data after your import macro. Open the subform by itself to check
this.

Robert said:
Tried it. Populates the table fine, does not update the subform, no errors
found.

Am checking the subform to see if anything would prevent updates...
Try this:
[quoted text clipped - 15 lines]
understanding correct? If so (or not), what is the most efficient way to
refresh the subform so that it reflects the imported data?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top