Refresh form after table updated 2

[Lhuffst]

I have a continuous form that displays all records. One of the fields is a check box so the user can select which records. The marked records are moved to a new table where a report will be generated. When the process is done, on the form you see #deleted# for each field and record. What I need to do is refresh the screen with only the remaining records in the original table (those that were not selected). I have tried repaint, refresh, me.field.requery and still no luck.

I have one command button that saves the records once they have been checked. Once the records have been saved, then another command button becomes visible that actually does the moves. This is the code for the button that moves the data.


Dim oldRs As Recordset
Dim NewRs As Recordset
Dim reccnt As Integer


Set db = CurrentDb()
ssql = "Select * from tblSurvey where chksurvey = true"
Set oldRs = db.OpenRecordset(ssql)

Set NewRs = db.OpenRecordset("Select * from tblsurveyFinal")

While Not oldRs.EOF 'send data to tblsurveyfinal --only the exported records are here
NewRs.AddNew
NewRs("fldFirstname") = oldRs("fldFirstname")
NewRs("fldLastName") = oldRs("fldLastName")
NewRs("fldAddress") = oldRs("fldAddress")
NewRs("fldCity") = oldRs("fldCity")
NewRs("fldState") = oldRs("fldState")
NewRs("FldZip") = oldRs("FldZip")
NewRs("fldWorkDesc") = oldRs("fldWorkDesc")
NewRs("fldWoNumber") = oldRs("fldWoNumber")
NewRs("fldCompletedate") = oldRs("fldCompleteDate")


reccnt = reccnt + 1
NewRs.Update 'save the record
oldRs.MoveNext 'get next record from query

Wend

'delete the records from tblsurvey after they have been moved to tblsurveyfinal

If reccnt > 0 Then
ssql = "delete * from tblsurvey where chksurvey"
db.Execute (ssql)
End If
reccnt = 0
oldRs.Close
NewRs.Close


'now update the current date for surveydate to show it has had a label printed

Set NewRs = db.OpenRecordset("Select * from tblsurveyFinal where isNull(fldsurveydate)")

While Not NewRs.EOF
NewRs.Edit
NewRs("fldsurveydate") = Format(Date, "mm/dd/yyyy")

NewRs.Update
NewRs.MoveNext
Wend

Me.FldFirstName.Requery
Me.fldLastName.Requery
Me.FldAddress.Requery
Me.Fldcity.Requery
Me.Fldstate.Requery
Me.fldWorkDesc.Requery
Me.FldWoNumber.Requery
Me.FldCompleteDate.Requery
Me.ChkSurvey.Requery

NewRs.Close

Me.Repaint
'Me.Requery

Any help would be appreciated.
Lhuffst

 

[TheAceMan1]

how are ya Lhuffst . . .

You need to requery the form, not the field.

[blue]Me.requery[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

Another way:
Me.RecordSource = Me.RecordSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886