Subform Refresh HELP!!

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

Guest

I have read thru previous posts on this subject, and still need more specific
help. I have a Form: Merchants, with several tabs at the top. Users will
verify data on the Merchant Form, and then add data to the subforms under
each Tab. On the Equipment Subform, the user may add several records. This
is all working correctly. The next Tab -- Features Subform will not
automatically display the equipment that was just entered. The records are
in the table, and will display in the Subfrom only after exiting from the
Merchant Form, either by closing or scrolling to another record. I think I
have to Requery or Refresh, but I am not a programmer, so can someone please
help me with specific instructions of where to type what???

Thanks!
 
Sylvia said:
I have read thru previous posts on this subject, and still need more
specific help. I have a Form: Merchants, with several tabs at the
top. Users will verify data on the Merchant Form, and then add data
to the subforms under each Tab. On the Equipment Subform, the user
may add several records. This is all working correctly. The next
Tab -- Features Subform will not automatically display the equipment
that was just entered. The records are in the table, and will
display in the Subfrom only after exiting from the Merchant Form,
either by closing or scrolling to another record. I think I have to
Requery or Refresh, but I am not a programmer, so can someone please
help me with specific instructions of where to type what???
Click to expand...

Requery is what you want. I'd suggest using the tab control's Change
event to requery that subform whenever the Features tab page is brought
to the front. I can give you sample code to do this, but I don't know
the actual names of your tab control or subform, so you'll have to
determine what these are and replace the names in my sample code with
the real names. I suggest you take the code I'm posting below, copy it
to the clipboard, paste it into a Notepad window, make whatever name
changes are required, then copy the modified code and paste it into the
form's code module. You can open the form's code module for pasting by
clicking the "Code" button on the toolbar, or by click te View -> Code
menu items, while the form is open in design view.

'----- start of sample code -----
Private Sub YourTabControl_Change()

If Me!YourTabControl = 1 Then
Me![Features Subform].Requery
End If

End Sub
'----- end of sample code -----

Things to change in the above code:

"YourTabControl" - replace this with the actual name of the tab
control on your form.

"1" - replace this with the Page Index property of the tab page that
holds the subform you want to requery. The first page has Page Index =
0, the second has Page Index = 1, and so on. To verify this, you can
click on the page, bring up its property sheet, and look on the Format
tab for the Page Index property.

"Features Subform" - replace this with the name of the subform you
want to requery. Note that this must be the name of the subform
*control* on the main form, the "window" control that displays another
form as a subform. It may or may not be the same as the name of the
form being displayed in that window.
 
Ok, I understand what I am supposed to do...but I can't get it to work. My
Form Name is frmMerchants. It pulls data from tblMerchants. My first Tab is
named EquipmentLog--it opens frmEquipmentLog, which pulls data from
tbl_Equipment. So far, so good. Now starts the Problem: My next Tab is
named EntitlementsLog--it opens frmEntitlementsLog, which pulls data from
tbl_Equipment.

When I enter data into the EquipmentLog, it does not save to the table until
I exit that subform. I exit that form by opening the next Tab --
EntitlementsLog. So, I need to save the EquipmentLog and requerry the
Equipment Log when I open the EntitlementsLog.

I open my frmMerchants in Design View. I open the Code Module, but where do
I put the code? The properties at the left say Tab Control, and the window
that opens says General Declarations. I can change the Properties or the
General . But I'm not sure what to do. I have tried a few things but
nothing is working....please HELP!!!

Sylvia

Dirk Goldgar said:
Sylvia said:
I have read thru previous posts on this subject, and still need more
specific help. I have a Form: Merchants, with several tabs at the
top. Users will verify data on the Merchant Form, and then add data
to the subforms under each Tab. On the Equipment Subform, the user
may add several records. This is all working correctly. The next
Tab -- Features Subform will not automatically display the equipment
that was just entered. The records are in the table, and will
display in the Subfrom only after exiting from the Merchant Form,
either by closing or scrolling to another record. I think I have to
Requery or Refresh, but I am not a programmer, so can someone please
help me with specific instructions of where to type what???
Click to expand...

Requery is what you want. I'd suggest using the tab control's Change
event to requery that subform whenever the Features tab page is brought
to the front. I can give you sample code to do this, but I don't know
the actual names of your tab control or subform, so you'll have to
determine what these are and replace the names in my sample code with
the real names. I suggest you take the code I'm posting below, copy it
to the clipboard, paste it into a Notepad window, make whatever name
changes are required, then copy the modified code and paste it into the
form's code module. You can open the form's code module for pasting by
clicking the "Code" button on the toolbar, or by click te View -> Code
menu items, while the form is open in design view.

'----- start of sample code -----
Private Sub YourTabControl_Change()

If Me!YourTabControl = 1 Then
Me![Features Subform].Requery
End If

End Sub
'----- end of sample code -----

Things to change in the above code:

"YourTabControl" - replace this with the actual name of the tab
control on your form.

"1" - replace this with the Page Index property of the tab page that
holds the subform you want to requery. The first page has Page Index =
0, the second has Page Index = 1, and so on. To verify this, you can
click on the page, bring up its property sheet, and look on the Format
tab for the Page Index property.

"Features Subform" - replace this with the name of the subform you
want to requery. Note that this must be the name of the subform
*control* on the main form, the "window" control that displays another
form as a subform. It may or may not be the same as the name of the
form being displayed in that window.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Sylvia said:
Ok, I understand what I am supposed to do...but I can't get it to
work. My Form Name is frmMerchants. It pulls data from
tblMerchants. My first Tab is named EquipmentLog--it opens
frmEquipmentLog, which pulls data from tbl_Equipment. So far, so
good. Now starts the Problem: My next Tab is named
EntitlementsLog--it opens frmEntitlementsLog, which pulls data from
tbl_Equipment.

When I enter data into the EquipmentLog, it does not save to the
table until I exit that subform. I exit that form by opening the
next Tab -- EntitlementsLog. So, I need to save the EquipmentLog and
requerry the Equipment Log when I open the EntitlementsLog.

I open my frmMerchants in Design View. I open the Code Module, but
where do I put the code? The properties at the left say Tab Control,
and the window that opens says General Declarations. I can change
the Properties or the General . But I'm not sure what to do. I have
tried a few things but nothing is working....please HELP!!!
Click to expand...

No need to shout, I'm already helping. For best results, you do need to
say exactly what you have tried and exactly what happened when you tried
it. "Nothing is working" isn't much to go on.

You haven't told me the name of the tab control -- is it actually
*named* "Tab Control"? -- or the Page Index property of the
EntitlementsLog tab page, so I can't tell you exactly what changes you
need to make to the code I posted. Assuming you've changed that code as
instructed, then all you need to do is paste the modified code into the
module window. When the window opens and says "(General)" in the Object
box at top left, and "(Declarations)" in the Procedure box at top right,
there should be one or two lines of code at the top of the main part of
the window. The first line will read

Option Compare Database

and the second, if present, will read

Option Explicit

Leave a blank line after the last of those line, and paste the code
right there. Then go back to the form, save it, and try it to see if it
works. If it doesn't, there are a variety of things that might be
wrong, so you have to report back what happens.
 
The Tab Control Name is TabCtl303. The Page Index property of the
EntitlementsLog tab is 3. This is how I changed the code:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub

This is what the code module window looks like after I made the change:
Option Compare Database

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub


Private Sub FindRecordButton_Click()
On Error GoTo Err_FindRecordButton_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecordButton_Click:
Exit Sub

Err_FindRecordButton_Click:
MsgBox Err.Description
Resume Exit_FindRecordButton_Click

End Sub

Nothing happens--meaning, the EntitlementsLog does not refresh with the new
equipment. I have tried changing the "Features Subform" to frmEquipmentLog
but I get a Run-time error 2465 PreCall Database can't find the field
frmEquipmentLog referred to in your expression. I think I need to requerry
the Merchant Form, which is the main form. Now what???
 
Sylvia said:
The Tab Control Name is TabCtl303. The Page Index property of the
EntitlementsLog tab is 3. This is how I changed the code:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub

This is what the code module window looks like after I made the
change: Option Compare Database

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub


Private Sub FindRecordButton_Click()
On Error GoTo Err_FindRecordButton_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecordButton_Click:
Exit Sub

Err_FindRecordButton_Click:
MsgBox Err.Description
Resume Exit_FindRecordButton_Click

End Sub

Nothing happens--meaning, the EntitlementsLog does not refresh with
the new equipment. I have tried changing the "Features Subform" to
frmEquipmentLog but I get a Run-time error 2465 PreCall Database
can't find the field frmEquipmentLog referred to in your expression.
I think I need to requerry the Merchant Form, which is the main form.
Now what???
Click to expand...

If I understand what youi've told me up to this point, you don't need to
requery the main form. It's the subform on the EntitlementsLog page
that you need to requery. Find out the actual name of the subform
control on that page -- not the name of the form that is displayed in
the subform control, but the name of the subform control itself -- and
substitute that for "MerchantForm" in the above code. The rest of it
looks okay to me.
 
Dirk,
It worked!!!! Hooray!!! Thank your so much for all your help!!! But, now
I have one more question. The next Tab is named Receipts. I need it to
refresh it after the Entitlements are entered. I tried to put in another
statement just following the first:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 4 Then
Me![ frmReceiptsLog].Requery
End If

End Sub

But I get an error message: Ambiguous Name Detected: TabCtl303_Change. I
must be putting it in the wrong place...how can I refresh the Receipts Tab??

Sylvia

Dirk Goldgar said:
Sylvia said:
The Tab Control Name is TabCtl303. The Page Index property of the
EntitlementsLog tab is 3. This is how I changed the code:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub

This is what the code module window looks like after I made the
change: Option Compare Database

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 3 Then
Me![MerchantForm].Requery
End If

End Sub


Private Sub FindRecordButton_Click()
On Error GoTo Err_FindRecordButton_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecordButton_Click:
Exit Sub

Err_FindRecordButton_Click:
MsgBox Err.Description
Resume Exit_FindRecordButton_Click

End Sub

Nothing happens--meaning, the EntitlementsLog does not refresh with
the new equipment. I have tried changing the "Features Subform" to
frmEquipmentLog but I get a Run-time error 2465 PreCall Database
can't find the field frmEquipmentLog referred to in your expression.
I think I need to requerry the Merchant Form, which is the main form.
Now what???
Click to expand...

If I understand what youi've told me up to this point, you don't need to
requery the main form. It's the subform on the EntitlementsLog page
that you need to requery. Find out the actual name of the subform
control on that page -- not the name of the form that is displayed in
the subform control, but the name of the subform control itself -- and
substitute that for "MerchantForm" in the above code. The rest of it
looks okay to me.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Sylvia said:
Dirk,
It worked!!!! Hooray!!! Thank your so much for all your help!!!
Click to expand...

Excellent, Sylvia!
But, now I have one more question. The next Tab is named Receipts.
I need it to refresh it after the Entitlements are entered. I tried
to put in another statement just following the first:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 4 Then
Me![ frmReceiptsLog].Requery
End If

End Sub

But I get an error message: Ambiguous Name Detected:
TabCtl303_Change. I must be putting it in the wrong place...how can
I refresh the Receipts Tab??
Click to expand...

You mustn't create two procedures with the same name. Instead, you need
to first get rid of the second TabCtl303_Change() procedure you just
added, and second, modify the existing TabCtl303_Change() procedure to
handle both cases. The modified code would look something like this:

'----- start of modified code -----
Private Sub TabCtl303_Change()

Select Case Me!TabCtl303
Case 3
Me![frmEntitlementsLog].Requery
Case 4
Me![frmReceiptsLog].Requery
End Select

End Sub
'----- end of modified code -----

You'll notice that I changed the simple "If ... Then ... End If"
statement to a "Select Case" structure. I did that because we now have
two distinct cases we need to check for and handle separately, and may
have more in the future, for all I know. You can find information about
the Select Case statement in the online help when you're in the VB
Editor environment.

One note: In the code you posted and I quoted above, you have a leading
space in the name of the subform, " frmReceiptsLog". I'm assuming for
now that that is a typo, and the subform doesn't actually have a leading
space in its name. If it does, you should get rid of that leading
space. Embedded spaces in object names are troublesome enough; leading
spaces are a Bad Idea.
 
It's all working perfectly!!! You are awsome!!! Thanks so much for your
help. Maybe I should take a VB Class....

Sylvia

Dirk Goldgar said:
Sylvia said:
Dirk,
It worked!!!! Hooray!!! Thank your so much for all your help!!!
Click to expand...

Excellent, Sylvia!
But, now I have one more question. The next Tab is named Receipts.
I need it to refresh it after the Entitlements are entered. I tried
to put in another statement just following the first:

Private Sub TabCtl303_Change()

If Me!TabCtl303 = 4 Then
Me![ frmReceiptsLog].Requery
End If

End Sub

But I get an error message: Ambiguous Name Detected:
TabCtl303_Change. I must be putting it in the wrong place...how can
I refresh the Receipts Tab??
Click to expand...

You mustn't create two procedures with the same name. Instead, you need
to first get rid of the second TabCtl303_Change() procedure you just
added, and second, modify the existing TabCtl303_Change() procedure to
handle both cases. The modified code would look something like this:

'----- start of modified code -----
Private Sub TabCtl303_Change()

Select Case Me!TabCtl303
Case 3
Me![frmEntitlementsLog].Requery
Case 4
Me![frmReceiptsLog].Requery
End Select

End Sub
'----- end of modified code -----

You'll notice that I changed the simple "If ... Then ... End If"
statement to a "Select Case" structure. I did that because we now have
two distinct cases we need to check for and handle separately, and may
have more in the future, for all I know. You can find information about
the Select Case statement in the online help when you're in the VB
Editor environment.

One note: In the code you posted and I quoted above, you have a leading
space in the name of the subform, " frmReceiptsLog". I'm assuming for
now that that is a typo, and the subform doesn't actually have a leading
space in its name. If it does, you should get rid of that leading
space. Embedded spaces in object names are troublesome enough; leading
spaces are a Bad Idea.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Sylvia said:
It's all working perfectly!!! You are awsome!!!
Click to expand...

Shucks, Ma'am, 'tweren't nuthin'.
Thanks so much for
your help. Maybe I should take a VB Class....
Click to expand...

I've heard good things about a book, _Beginning Access VBA_, by Smith
and Sussman, published by Wrox Press. (I think I have all those details
right.)
 
Back
Top