Subform refresh and query view.

[Rotorian]

Hello again,

I have a subform that gets populated from a records query. I needed the subform to refresh once a record is added via another form. I accomplished this by re-running the query for the subform upon closing the record addition form. It works like a champ, except I also the get the query datasheet view in addtion to the subform.

I am sure there is an obvious answer, that I am missing. Please let me know what other information you may need to help illustrate the problem.

Regards,
Willie

[ChipR]

Save the query, set the subform source to the query, and after the data set is changed, call

subformControl.Form.Requery

Or, just wait the preset amount of time for Acess to update itself.

[Rotorian]

Thank you for the reply.

I did that, but I still get the query data sheet view on top of the subform being refreshed. I guess I didnt put it in the right spot. Here is the button code.

On Error GoTo Err_addrecord_Click


    DoCmd.GoToRecord , , acNewRec

Exit_addrecord_Click:
    Exit Sub

Err_addrecord_Click:
    MsgBox Err.Description
    Resume Exit_addrecord_Click
    
End Sub
Private Sub OpenAddRecords_Click()
On Error GoTo Err_OpenAddRecords_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "tologentry"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenAddRecords_Click:
    Exit Sub

Err_OpenAddRecords_Click:
    MsgBox Err.Description
    Resume Exit_OpenAddRecords_Click
    
    subformControl.Form.Requery
    
End Sub

[ajalwaysus]

The line will only execute OnError where it is right now, put it as follows..

subformControl.Form.Requery

Exit_OpenAddRecords_Click:
    Exit Sub

Err_OpenAddRecords_Click:
    MsgBox Err.Description
    Resume Exit_OpenAddRecords_Click
    
End Sub

[ChipR]

First, replace "subformControl " with the actual name of the subform control on your form. Second, what is this "tologentry " you are opening?

[Rotorian]

Hmmm okay, did this:




"code deleted to save space, sorry"

Still get datasheet ivew, and now I get error "Object Required" upon clicking the button to add record.

[Rotorian]

You know I think I am going about this the wrong way. I posted the vb code for the button that is open a records entry form. Where I think i should post the code for the records entry form for an "OnClose" event. Let me double ck.

[ajalwaysus]

Also I think you need to address what ChipR is asking, before we can help you further.

-AJ

[Rotorian]

Okay form "tologentry " is the medium for updating records in table "tolog" in the "OnClose" event for "tologentry " I put this code:

Private Sub Form_Close()

historyrequest SubForm.Form.Requery

End Sub

This should refresh "historyreq uest subform" within another form, right? However, it is not happening, and I am getting the following error:

"Run-time error: 424 Object required"

When you guys ask for control I assume you mean the query that populates the subform? Under record source? for the subform properties. I think I am missing the obvious somewhere.

Sorry.

[Rotorian]

Please note I replaced the error code. In case anyone read it before my edit, apologies.

[NeoPa]

It's not a great idea to have names with embedded spaces in. This causes the interpreter to get confused unless you make it explicit :

[historyrequest SubForm].Form.Requery

[NeoPa]

This should refresh "historyreq uest subform" within another form, right?

No, this code (even my fixed version) assumes this is a subform on the current form.

When you guys ask for control I assume you mean the query that populates the subform? Under record source? for the subform properties.

Again no.

I believe they're talking about the subform control on your form. A subform control contains a form, which acts as a subform. Referring to Items on a Sub-Form gives a bit of an explanation. Please read it so that you can reply here as required.

Good luck.

[Rotorian]

Thank you Neo,

I found that post while searching for "form control" earlier today. I will go back to study it, as the answer still escaped even after reading it. Which could only mean that I did not pay much attention. Hopefully, tomorrow, I'll see the light :).

Regards

[NeoPa]

One of the main keys to understanding it is that the form you're using as a subform is not, but is contained within, the SubForm control on your main form.

People often refer blithely to their subform without realising that they are not saying what they think they are. Often they continue to do this even after it's been explained, but we normally get there in the end.

[Rotorian]

Okay, I reread your insight again. The part that I am missing is where within the code does the information reside? If I open the form "tolog" go to design view and press {code} to open the VB editor, I get the following:

On Error GoTo Err_addrecord_Click

    DoCmd.GoToRecord , , acNewRec

Exit_addrecord_Click:
    Exit Sub

Err_addrecord_Click:
    MsgBox Err.Description
    Resume Exit_addrecord_Click
    
End Sub

Private Sub historyrequest_subform_Enter()

End Sub

Private Sub OpenAddRecords_Click()
On Error GoTo Err_OpenAddRecords_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "tologentry"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_OpenAddRecords_Click:
    Exit Sub

Err_OpenAddRecords_Click:
    MsgBox Err.Description
    Resume Exit_OpenAddRecords_Click
    
End Sub

Now, this is a very crude and simple form so it shouldnt be hard for me to find it. However, I guess I cant see the forest from the tree :|.