refresh not working

[rlowry]

Here's my problem:

I have a main form (frmEnter) which shows records of expenses. I have added a command button to this form which successfully opens a second form (frmAddrec) to the current record (based on a Autonumber field "recnum" that appears in both forms). The idea of frmAddrec is to allow me to add records when I need to without losing my place in frmEnter. I also want the fields in frmEnter to be mostly locked but the fields in frmAddrec to be mostly editable. The problem is that I can't seem to get frmEnter to update with the new record added in frmAddrec. I've added a refresh button to frmEnter which doesn't seem to do anything. frmEnter does reflect any changes I make in frmAddrec to existing records but doesn't reflect new records. I've noticed that if I close frmEnter and reopen it, the new record is there. I'm not too familiar with Access so I'm feeling stuck.

Thanks.

 

[RoyVidar]

That's a good description of the help topic on the .Refresh method. To see additions and deletions, you will probably need to requery the form, which will also position the form to the first record. Then how? Perhaps something along these lines as the contents of the "refresh button" (also - in VBE - Tools | References, ensure the reference to Microsoft DAO 3.N Object Library is checked):

[tt]dim lngPK as long
dim rs as dao.recordset
lngPK = me!txtControlHoldingPK.Value
me.requery
set rs=me.recordsetclone
rs.findfirst "PKField = " & lngPK
if not rs.nomatch then me.bookmark = rs.bookmark
set rs=nothing[/tt]

- typed not tested...

Roy-Vidar

 

[rlowry]

Thank you much Roy-Vidar. That seems to work to at least update the form (without having to open and close the form). It still seems to start me back at the begining though. Too bad for me. Here's what I have in the On_click command of my Command button:

Private Sub Command17_Click()

Dim lngrecnum As Long
Dim rs As DAO.Recordset
lngPK = Me!recnum.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "recnum = " & lngrecnum
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

End Sub

note that "recnum" is the name of both the unique identifier and the field in both forms.

Many thanks for the speed and utility of your reply.

 

[NewTexican]

I'm no royvidar but I believe all the highlighted parts should be the same

Dim [highlight]lngrecnum[/highlight] As Long
Dim rs As DAO.Recordset
[highlight]lngPK[/highlight] = Me!recnum.Value
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "recnum = " & [highlight]lngrecnum[/highlight]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

 

[RoyVidar]

lngPK is my variable name, you use lngrecnum

[tt]lngrecnum = Me!recnum.Value[/tt]

- 'nother recommandation, place the line

[tt]option explicit[/tt]

as the second line in every module - just after Option Compare Database (and/or even better, in VBE - Tools | Options, check "Require variable declaration", which will automaticly insert the line, and enforce variable declaration in every new module.

Roy-Vidar

 

[rlowry]

you're both brilliant. stupid mistake on my part. Many thanks.