Requery difficulty when record added on popup form

[Catherine M]

I have two forms " frmClientList" and "frmClientEntry"
based on one table "tblClientList".

"frmClientEntry" is a popup nonmodal form opened from
form "frmClientList". It is not a subform.

The form "frmClientList" displays all records but does not
allow edits, deletions or additions. All changes/additions
are done on form "frmClientEntry". No deletions are
allowed.

I want to requery form "frmClientList" whenever a record
is saved on frmClientEntry, and move to the record just
saved. This works fine when editing an existing record
but not when a record is added. I believe this is because
the recordset for form "frmClientList" does not include
the new record. How do I get the requery to include a
record added on form "frmClientEntry"?

On form "frmClientEntry" I use the following code to save
the record and then requery the underlying
form "frmClientList":

DoCmd.Save
gstrHoldRecNum = Me.txtRecNum 'global variable
used to move to this record on form "frmClient List" after
the requery
Forms!frmClientList.SetFocus 'Sets focus to
form "frmClientList" so requery can be done
Call Forms.frmClientList.subRequery_ClientList '
calls the requery subroutine
Forms!frmClientEntry.SetFocus ' Sets focus back
to form "frmClientEntry"

In subRequery_ClientList on from "frmClinetList" I have:

' Any filter is removed to be sure added/edited data
will be displayed.
Me.FilterOn = False
Me.Filter = ""

DoCmd.Requery "" ' Requery the form

Me.Recordset.MoveLast ' Make sure all record are
available

Me.RecNum.SetFocus ' set focus to field you are
looking for value of
If Nz(gstrHoldRecNum) <> "" Then ' Make sure
variable has a value
If gstrCurrentRecNum <> gstrHoldRecNum Then
DoCmd.FindRecord gstrHoldRecNum, , ,
acSearchAll, , acCurrent ' goes to record just added or
edited
End If
End If


Does anyone have a solution?

Catherine

 

[Dirk Goldgar]

I have two forms " frmClientList" and "frmClientEntry"
based on one table "tblClientList".

"frmClientEntry" is a popup nonmodal form opened from
form "frmClientList". It is not a subform.

The form "frmClientList" displays all records but does not
allow edits, deletions or additions. All changes/additions
are done on form "frmClientEntry". No deletions are
allowed.

I want to requery form "frmClientList" whenever a record
is saved on frmClientEntry, and move to the record just
saved. This works fine when editing an existing record
but not when a record is added. I believe this is because
the recordset for form "frmClientList" does not include
the new record. How do I get the requery to include a
record added on form "frmClientEntry"?

On form "frmClientEntry" I use the following code to save
the record and then requery the underlying
form "frmClientList":

DoCmd.Save
gstrHoldRecNum = Me.txtRecNum 'global variable
used to move to this record on form "frmClient List" after
the requery
Forms!frmClientList.SetFocus 'Sets focus to
form "frmClientList" so requery can be done
Call Forms.frmClientList.subRequery_ClientList '
calls the requery subroutine
Forms!frmClientEntry.SetFocus ' Sets focus back
to form "frmClientEntry"

In subRequery_ClientList on from "frmClinetList" I have:

' Any filter is removed to be sure added/edited data
will be displayed.
Me.FilterOn = False
Me.Filter = ""

DoCmd.Requery "" ' Requery the form

Me.Recordset.MoveLast ' Make sure all record are
available

Me.RecNum.SetFocus ' set focus to field you are
looking for value of
If Nz(gstrHoldRecNum) <> "" Then ' Make sure
variable has a value
If gstrCurrentRecNum <> gstrHoldRecNum Then
DoCmd.FindRecord gstrHoldRecNum, , ,
acSearchAll, , acCurrent ' goes to record just added or
edited
End If
End If


Does anyone have a solution?

Catherine

Wow, Catherine, this is *way* more than you need, and some of it is
wrong. For instance,

DoCmd.Save

doesn't save the current record. It saves the *design* of the form. To
save the current record, you could write

RunCommand acCmdSaveRecord

On the other hand, if your code on frmClientEntry is executed in the
form's AfterUpdate event, as seems reasonable, you don't need to
explicitly save the record, because it has already been saved.

Also, you don't have to go setting the focus all over the place.

Try this simplified version:

'------ start of code for frmClientEntry -----
'** Note: the following 3 lines may not be necessary **
If Me.Dirty Then
RunCommand acCmdSaverecord
End If

With Forms!frmClientList
.FilterOn = False
.Filter = ""
.Requery
.Recordset.FindFirst "RecNum=" & Me.txtRecNum
' ** Note: the above assumes RecNum is a numeric field.
' ** If it's text, use the following line instead:
' .Recordset.FindFirst "RecNum='" & Me.txtRecNum & "'"
End With
'------ end of code for frmClientEntry -----

That should be all you need. The subRequery_ClientList procedure on
frmClientList isn't needed, because we're doing it here.

 

[Guest]

-----Original Message-----


Wow, Catherine, this is *way* more than you need, and some of it is
wrong. For instance,


doesn't save the current record. It saves the *design* of the form. To
save the current record, you could write

RunCommand acCmdSaveRecord

On the other hand, if your code on frmClientEntry is executed in the
form's AfterUpdate event, as seems reasonable, you don't need to
explicitly save the record, because it has already been saved.

Also, you don't have to go setting the focus all over the place.

Try this simplified version:

'------ start of code for frmClientEntry -----
'** Note: the following 3 lines may not be necessary **
If Me.Dirty Then
RunCommand acCmdSaverecord
End If

With Forms!frmClientList
.FilterOn = False
.Filter = ""
.Requery
.Recordset.FindFirst "RecNum=" & Me.txtRecNum
' ** Note: the above assumes RecNum is a numeric field.
' ** If it's text, use the following line instead:
' .Recordset.FindFirst "RecNum='" & Me.txtRecNum & "'"
End With
'------ end of code for frmClientEntry -----

That should be all you need. The subRequery_ClientList procedure on
frmClientList isn't needed, because we're doing it here.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Dirk,

Thanks for your help. The RunCommand did the trick. I
kept the requery subroutine in frmClientList, changing
focus and calling it from frmClientEntry. One reason is
that I want more than the last record to show in the list,
which usually happenes. If the record that was saved or
updated was the last record I move to it, then do the
following:
DoCmd.GoToRecord acDataForm, "frmClientList", acPrevious,
12 ' Goes back 12 records
Me.Recordset.MoveLast ' Returns to last record
This usually works - if the from size and font size are
the same as when I did my testing.

In addition I had problems with some other things I was
doing in frmClientEntry. It is working great with the one
change.

Thanks again,

Catherine

 

[Dirk Goldgar]

Dirk,

Thanks for your help. The RunCommand did the trick. I
kept the requery subroutine in frmClientList, changing
focus and calling it from frmClientEntry. One reason is
that I want more than the last record to show in the list,
which usually happenes. If the record that was saved or
updated was the last record I move to it, then do the
following:
DoCmd.GoToRecord acDataForm, "frmClientList", acPrevious,
12 ' Goes back 12 records
Me.Recordset.MoveLast ' Returns to last record
This usually works - if the from size and font size are
the same as when I did my testing.

In addition I had problems with some other things I was
doing in frmClientEntry. It is working great with the one
change.

Thanks again,

Catherine

You're welcome, Catherine. I still think you would do much better not
to switch the focus around the way you're doing. You don't need to, to
accomplish any of the things you've mentioned. But if you're satisfied
now, that's fine with me.