How do I refresh the data on a subform automatically?

[bevpro]

I've tried doing a requery on the OnClick event and it doesn't work. If I go to Records/Refresh then it works. But I want to refresh automatically.

 

[tunsarod]

You don't say where this OnClick Event is, on the subform or on the mainform. I have a Subform on a mainform which I refresh using:


Private Sub Form_GotFocus()
Me.TENANTS_subform.Requery
Me.TENANTS_subform.Form.Repaint
End Sub


This code is in the GotFocus event of my mainform. Each time an Update query is run to either add or delete a record from the subform's Record Source the above codes updates the subform as soon as control passes back to the mainform.

Rod

 

[tekila]

I've tried implementing this code in the OnClick event of Add command on my Add New Product form but the first record that I entered was updated in the subform only after the second record was entered. Thus the subform refreshes by one record slower. Why can't it be updated once the user hits the Add command; the most recent record is shown in the subform before the entry of the next record?

 

[tunsarod]

You don't give enough info so I'm not sure what you mean here - but I'll assume that you are using the subform in the usual way to display related records from another table. Or are you using it to display a range of fields from the mainform's current record???

How are the mainform and subform linked???

e.g. In the example I quoted the main form shows a record per page based on tblProperties and the subform which is based on tblTenants lists the tenants living in the currently displayed property. Thus the Link Child Fields and Link Master Fields property on the subform's property sheet are both set to PropertyID. PropertyID is the primary key field for the tblProperties and the foreign key field in the tblTenants.

If your Link Master Field (that's the field on the Mainform) for the relationship between the two forms is an Autonumber field then when you put the form into Add New record mode that particular field will initially be empty or null which will temporarily disable the relationship between the Mainform and the Subform.

Requerying the Subform at this point will be unsuccessful so either do it before setting the main form into Add New mode or after the new record has been entered and the Link Master field is filled again.

Also it might improve matters if the Master Link field refers to the form field textbox name rather than the underlying table's field name. You will need to ensure that the two are named differently. When Access creates a form using the wizards it uses the same name for the screen textbox control as you used for the underlying table field. This, in my experience, can cause problems when referencing fields in lookup routines, which is what subforms do.

If you want to display records in a subform before the new record is actually saved to the table you will need to refer to the forms textbox by a distinctive name. I usually add "txt" to the screen textbox name. e.g. The textbox that holds/displays the data for the field MyField I would name txtMyField.

I'm not sure this is central to your problem but it certainly comes to mind as a potential issue.

I would try requerying the subform from some other mainform event such as the On Current or After Update events.

Post back with more details if this doesn't help and I'll try to be more useful.

Regards
Rod

 

[OverDrive2]

bevpro . . .

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Try this, put this code in the "after update" event of the control you are updating or wherever needs updating (example: a listbox, combobox, etc...

If (for instance) you need a listbox to show up with the new information that you entered into a new record, take this code put it in the "after update" event of the conrol that you type the information for the new record in.

Good Luck!!

 

[tekila]

Rod, there isn't any link between the main form and subform, meaning no link child and link master fields. As I said, the main form serves as a Add New Product form, the purpose of the subform is just to let the user view the list of products in the underlying table. In fact, both the forms are based on the underlying table (Products). So I'm not using the subform for its usual purpose.

In the table (Products), there are three primary keys; Product Family, Product Code and Product Description.
There are three combo boxes on the main form; Family, Code and Description, whose contents are limited based on the value selected in the previous combo box list. The reason for using combo boxes and not textboxes is when the user wants to enter a product that is of a different product description from an existing record in the table, he can select the product family and code from the lists and enter the new description in the Description combo.

I used to include Me.Refresh (before your code) in the OnClick event of the Add command button and the subform refreshes right after I hit the Add comand button, provided the fields in the table are not primary keys. Since the user cannot enter an existing record, I have to set the fields as primary keys and ommit Me.Refresh (Setting both at the same time will cause a violation in saving an existing record if I enter an existing record).

This is what I have in the OnClick event of Add command:

Private Sub Add_Product_Click()

If IsNull(Me!Family) Then
MsgBox "Product Family is Null. Please select/enter a value. "
Me!Family.SetFocus
Exit Sub
End If

If IsNull(Me!Code) Then
MsgBox "Product Code is Null. Please select/enter a value. "
Me!Code.SetFocus
Exit Sub
End If

If IsNull([Description]) Then
If MsgBox("Is Product Description supposed to be Null?", vbYesNo) = vbYes Then
Me!Description = "-"
Else
Me!Description.SetFocus
Exit Sub
End If
End If

Me.Productssub.Requery
Me.Productssub.Form.Repaint

On Error GoTo Err_Add_Product_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Product_Click:
Exit Sub

Err_Add_Product_Click:
MsgBox Err.Description
Resume Exit_Add_Product_Click

End Sub

 

[tunsarod]

Open the Products_subform and view its own Property sheet.
In Record Source you should see a statement similar in format to this:

SELECT DISTINCTROW PRODUCTS.Family ,PRODUCTS.Code ,PRODUCTS.Description ,PRODUCTS.Field4 ,PRODUCTS.Field5 FROM PRODUCTS;

This need only list the fields you actually want to see displayed in the subform.

Also make sure that the subform's Filter property is blank.


In the mainform's subform control the properties; Link Child Fields and Link Master Fields should be left empty, since you are not restricting the record list for the subform.

Now insert the line:

Me.[Products_subform].Requery

in the AfterInsert event for the mainform. This should be all you need to ensure the subform displays an uptodate set of records.

Don't use the Refresh method because:

The Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried.

Taken from the Access help page on Refresh


======================================================

Since the subform displays a full list of products one trick you might like to use, if you haven't already, is to provide a button on the mainform that causes the mainform to jump to the same record that the subform is currently pointing to.

Create a textbox on the subform, call it, say, txtRecNo.
Set its Countrol Source to:

=[CurrentRecord]

You can hide this field as it need not be visible.

On the mainform add a textbox and call it txtTarget.

Set its ConrolSource to:

=[Products_subform]![txtRecNo]

Now add a command button to the mainform and call it cmdGoToRec. In place this in its Click event add:

Private Sub cmdGotoRec_Click()
On Error GoTo Err_cmdGotoRec_Click

DoCmd.GoToRecord , , acGoTo, txtTarget

Exit_cmdGotoRec_Click:
Exit Sub

Err_cmdGotoRec_Click:
MsgBox Err.Description
Resume Exit_cmdGotoRec_Click

End Sub


Hopefully you now have a subform that displays all the fields you need to see; is updated whenever a new record is added and you can use it as a means of quickly locating a particular record for editing on the mainform.

You might also like to place the Me.[Products_subform].Requery line in the mainform's After Del Confirm event.


Regards
Rod

 

[OverDrive2]

Hey Tunsarod, what is the difference between using
Me.[Products_subform].Requery
Rather than using
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Is there any difference in the two, or do they do the same thing in refreshing the form)?

Thanks bud
Chance~

 

[tunsarod]

Not much, except that Access help says DoMenuItem is being replaced by RunCommand. I personally prefer the simpler directness of Me.Object.Requery.

In Tekila's case the problem was more to do with his using Refresh as opposed to Requery.

Rod

 

[tekila]

Thanks Rod, now my subform is updated right after I move to the next new record and I'm able to keep my fields as primary keys.

 

[gator9]

Here this might help! I thank I know what you are trying to do.

Private Sub Your_Button_Name_Enter()
Forms![Your_Sub_Form].Refresh
End Sub

When you click the button on your for this code will auto refresh the subform.

Charles