About that "Requery"...

[jwindon]

Thank you Fornation for clearing up the terminology for me. I think maybe my instructor skipped that part of the book way back when. Anyway, since I have started dabbling in the VBA ..could you help me learn the syntax?

The AddNewRecord forms come up when a command button from ViewAllForm (from a query), the user enters new data and hits command to "save new record", the form closes and sets focus back on a control. Now..how do I add the Requery?

Thanks. These little things make more sense to me when I see them on screen.

 

[R. Hicks]

It your are trying to Requery the currently active form, then use:

Me.Requery

If you want to Requery another form that is open but does not have focus from a form that has focus, then use:

Forms![FormNameToRequery].Requery

HTH
RDH

[This message has been edited by R. Hicks (edited 09-01-2001).]

 

[jwindon]

OK. I got that to work, but I had to put the "Me.Requery" on the form on its OnActivate event. How should the Code read on the PopUpform so that I requery the underlying form BEFORE I close it? I tried the Forms![FormName].Requery, but I can't figure what to put before that?!? I know its stupid, but I'm only 2 weeks into VBA and teaching myself (along with a little help from the "experts" on Access World!)

 

[R. Hicks]

Place the following in the On Close event of the PopUp form you have. This will trigger the form you have chosen to requery during the close of the PopUp form.

Forms![FormName].Requery
(change "FormName" to the actual name of form to be requeried)

HTH
RDH

 

[R. Hicks]

BTW ... if this is a subform that you want to Requery then you need to actually requery the subform control:

Forms![YourFormName]![SubFormControlName].Requery

RDH

 

[jwindon]

Thanks Rick(? is it?)! I got that to work, now it would be nice to GOTO that record I just added? Any suggestions. The records are sorted by a control Ascending.

 

[R. Hicks]

Rick ... Ricky .... whatever

I assume the form you are requerying is open in the background.
If this is true then try this. Add the following to the On Close event of the PopUp form after the requery line:

Forms![YourFormName].SetFocus
DoCmd.GoToRecord , , acLast

(again change "YourFormName" to your form's name)

HTH
RDH

[This message has been edited by R. Hicks (edited 09-01-2001).]

 

[jwindon]

Thanks Rick. I can get that to work as well, but my query is sorted as "ascending". The new record falls in somewhere in between. I guess I'm asking too much? It probably isn't possible to "pick out" the record that was added last. If not, that's OK. I can live with it.

 

[Fornatian]

Which field is your form sorted 'ascending' by, if you do it by an autonumber field then the last record created should be the last one presented by the query.

If by a text field you will need to look into using a DAO Recordset to run a FindFirst method then set the forms bookmark equal to the value you're looking for:

Here's an example from a Soil Stratification DB, in this example I use a listbox to change the current record to that of the record selected in the list box
'set variables
Dim strCrit As String
Dim rst As Recordset

'build the criteria for the FindFirst method
'where I have list0 you would probably use a public variable to reference the primary' key of the record you're searching for. Alternatively you could use a Forms("FormName") reference as well


strCrit = "[Soil ID] = '" & List0 & "'"

'set the pointer
Set rst = Me.RecordsetClone
'search for the record
rst.FindFirst strCrit
'if no match found, actually it's not possible because my DB reference the same table for both the form and the listbox, yours may not so handle that error here.

If rst.NoMatch Then
MsgBox "Error, please re-open this form and try again"
Exit Sub
Else
'set the forms bookmark equal to the recordset bookmark, this changes the record
Me.Bookmark = rst.Bookmark
End If
'release variable
Set rst = Nothing

I hope you can manipulate that lot to your own needs.

 

[jwindon]

Thanks for the help Rick. The DIM stuff is something I still haven't been able to grasp. It's difficult to teach yourself this with no programming background. I'm waiting for a class to be offered at work. Should be in October. Until then, I'll just have to muddle through the best I know how. I will try to decipher your Code. I was playing around with the Find function while you were replying so I know I was on the right track.

Anyway, You mentioned your list box being drawn from the same source as your form. I think you might be able to help WayneF on the thread below me. He is having a problem pointing to his query in the combo based off of the same form.

 

[R. Hicks]

Actually "Fornatian" gave that reply .... and I agree with his response.

RDH

[This message has been edited by R. Hicks (edited 09-01-2001).]

 

[jwindon]

Sorry Fornation. Haven't had enough coffee this morning. Thanks to both of you. You've been so much help, so often in the Forum!

 

[princess15s]

www.wopr.com has the answer for all question guys.