I have a form for which the Record Source is a certain table. Then on that form there is a button to display another form, which adds a new record to the same table. Then the new form is closed.
The trouble is, the original form cannot search for the new record, until you close the form and reopen it.
I have tried using Me.Dirty is false, and also a requery of the original form, but nothing works. Only closing the form and reopening it will display the newly added record.
What can I do to make the original form recognize the newly added record?
Private Sub Form_Close()
'
Dim strFrmName As String
strFrmName = "frmTest1" 'First forms Name
DoCmd.OpenForm strFrmName 'You probably don't need this line
With Forms(strFrmName)
.RecordSource = "tblTest" 'First forms Record Source
'.fSetUp
'.Caption = "I CAN CHANGE THE CAPTION"
End With
End Sub
I'm not even sure why you would need to open another form if both Record Sources are bound to the same table. What's the reasoning behind this design? And how did you requery the form?
1. Create a query based on the table
2. Make the query the record source of both forms
3. On closing the form requery the other form like so:
Private Sub Form_Close()
Dim strFrmName As String
strFrmName = "frmTest1" 'First forms Name
DoCmd.OpenForm strFrmName 'You probably don't need this line
With Forms(strFrmName)
.Requery
'.fSetUp
'.Caption = "I CAN CHANGE THE CAPTION"
End With
End Sub
I believe my way works because basically it forces the requery anyway. ( this is a guess) but go with vbaInet's code.
If you asked me why I would show you this way of doing something, it's to do with bad experiences in trying to make a form do what I wanted it to do in my early learning process. Particularly getting the refresh/requery to work when I expected/wanted it to.
A bit of history might not be amiss, not MS Access history but my journey in learning MS Access.
It all started when I discovered custom properties. You can write code in your form that will store information for you a bit like a variable.
You can also pass values to these custom properties when you open your form with codes like I have shown above.
The traditional method of doing this is to place a text box on your form and make it invisible. I call them "yellow text boxes" because an unofficial convention is to make the background colour yellow so that you know they are an invisible textbox for the purpose of passing information from one form to another.
I just hated them. To my mind they looked untidy they represented a fudge, I couldn't see any reason why any self respecting programmer would use such a device.
I added my newly discovered custom properties to my forms and was well pissed off when it didn't work.
The variables were passed along ok but when the forms Load event went to use the variable the variable wasn't present.
I devoted hours of my spare time, late nights fussing around trying to find out what was wrong. For some strange reason the forms load event, open event, any f***ing event you can think of... (I'm having "a vent") is triggered "before" the values are past to the custom properties! How stupid is that!
After more hours, days weeks of using up my spare time I discovered a simple solution!
Place your own function ("fSetUp") in the form you are going to open, pass through your variables into the custom properties ... And then call your function ... have this function do what you want with the values in your custom properties and it works! (this function has to be public).
Re GKirkup - Comment:- Gizmo:
Are .fsetup and .caption commented out?
That's what the line "fSetUp" (function set up) is for in the above code.
In other words I have grown to distrust MS Access in its basic functionality; it does not always work as you would expect! It pays to take control, and do it in a way that you know works, a way you are in control of.
I devoted hours of my spare time, late nights fussing around trying to find out what was wrong. For some strange reason the forms load event, open event, any f***ing event you can think of... (I'm having "a vent") is triggered "before" the values are past to the custom properties! How stupid is that!
requery does what it says. requeries the underlying query and puts you back to record 1
refresh does something slightly different. it doesn't find new rows, but refreshes the data for changes in rows that it had already found, and doesn't reposition to record 1. (This won't help your current situation - you do need requery for that)
