me.requery not working (1 Viewer)

Hello, I am hoping someone can help me...

I am wanting a sub form (SubSent) to requery so that the txtDocDates text boxes alter depending on which company a user is looking at (the sub form is based on a query)

I have tried to put a re query on several events, including events on the sub form, the main form, the tab control which the sub-form sits on and the search form from which the main form is loaded (which the sub-form sits on). None of them are working - yet; I have put a command button on the sub form with a simple me.requery on the 'on click' event; which when clicked works perfectly?

I would like some advice on where to put this event for it to work without the user having to click a command button to display the correct info...??


Thanks in anticipation

Where is the code that is going to want to have the SubForm perform a Requery?

If on the main form, perhaps you can send a message from the main Form to the SubForm with similar syntax to what I use to populate SubForm fields:

So syntax is:

Me (referring to the form, or in my case I have a reference to the form in MePointer)
Name of subform object on the form
Form is static to be able to refer to fields on the subform
fieldname

Air Code that just might work...


Let us know how it goes.

as I said, I have tried the code in several places, none of which appear to be working - only if the command button on the subform is pressed does it work. I need the sub form to be 'stand alone' in a sense, therefore it is not directly linked (parent and child) with the main form... could this be what is causing the issues with the requery?

access7 said:

I need the sub form to be 'stand alone' in a sense, therefore it is not directly linked (parent and child) with the main form.

Click to expand...

What exactly does that mean?

If data has been updated in the table that the subform displays, then I would have thought you could send the subform a requery event to have it update itself.

But with what you said, I have no idea what you are up to. Please explain further.

Please see below the code I have on the subform... this was written with the help of my techy friend who helps to teach me from time to time.

Without you looking at the database then I will try and explain as best as I can what I am trying to achieve....
I would like three lists of documents (sent / received and other) that once a user has either sent or received to / from a company they can put a date in next to that item.
I have two tables - one is a list which names all the documents and the other is a list which links those documents to a particular company and date. I then have a query to link the two tables together *which is the Record Source for the subforms. I have three sub forms; one for sent docs, one for received docs and one for any 'other' docs. These are all driven from the same query but are filtered according to DocType (i.e. "S" for Sent; "R" for received, "O" for Other).
I have a continuous sub form with the following fields DocName; DocDate and then hidden fields for the DocRef (primarykey) and CompanyRef (or as you will see in the code below ICompanyRef (which is a global variable set when someone chooses a particular company from a listview).
Hope I've not lost you so far... here is the code on the subform...


Option Compare Database

Private Sub Command9_Click()
Me.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lsSQL As String

If IsNull(Me.txtCompanyRef) Then
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_CompanyDocs ( CompanyRef, DocRef, DocDate ) "
lsSQL = lsSQL & " VALUES ( " & ICompanyRef & ", '" & Me.txtDocRef & "', #" & Me.txtDocDate & "#)"
Else
lsSQL = ""
lsSQL = lsSQL & " UPDATE Tbl_CompanyDocs Set DocDate = #" & Me.txtDocDate & "# "
lsSQL = lsSQL & " Where CompanyRef = " & ICompanyRef
lsSQL = lsSQL & " And DocRef = '" & Me.txtDocRef & "'"

End If

CurrentDb.Execute lsSQL
'If CurrentDb.RecordsAffected = 0 Then
'MsgBox "Didnt save!"
'Stop
'End If

Cancel = -1
Me.Undo
Me.Requery

End Sub

Private Sub Form_Load()
Me.Requery
End Sub

Private Sub txtDocDate_Change()
Me.txtCompanyRef = ICompanyRef
'Me.txtCompanyDocRef = Me.txtDocListRef
End Sub

How about putting on the Form which is used as the subform:

And on the main form invoke that event with:

Substitude the name of the subform control on the main form where "subformcontrolname" appears.

I notice you have a Me.Requery in the form's On Load event. You don't requery on form load as it is just loading the recordset anyway.

For more about the subform control (control on the parent form which houses the subform) see my quick tutorial here:
http://www.btabdevelopment.com/ts/ewtrhtrts

Thanks for everyone's response on this one... I have now fixed the problem. It was in the part of the code where I was calling a procedure to open the main form... I was not setting the company ref until after I had opened the form, meaning that the only way the subforms got the information for the correct company ref was on a requery ... I have now changed it so that the variable (ICompanyRef) is set BEFORE the form is open... now on load of the form it all works perfectly. Novice error.
Many thanks though to all of you who took time to look at this post, it is, as always, much appreciated!