Requery a record in a form

[tgavin]

I have an tabular form with check boxes that need to be changed but the form
is not updatable. I wrote some code that seems to work but the form needs to
be requeried to show that it has been updated. The only code I can get to
work with this requeries the entire list which causes the user to lose his
place. The code I am using is:

Dim SQL As String


DoCmd.SetWarnings False

If Me!booHolidayCard.Value = -1 Then
SQL = "Update [tblClients-Peach]" & _
"Set [tblClients-Peach].booHolidayCard = 0 " & _
"WHERE [tblClients-Peach].CustID =
[Forms]![frmGiftSelection]![CustID]"
DoCmd.RunSQL SQL
DoCmd.Requery

ElseIf Me!booHolidayCard.Value = 0 Then
SQL = "Update [tblClients-Peach]" & _
"Set [tblClients-Peach].booHolidayCard = -1 " & _
"WHERE [tblClients-Peach].CustID =
[Forms]![frmGiftSelection]![CustID]"
DoCmd.RunSQL SQL
DoCmd.Requery

End If

How can I write it so that it only requeries the specific record in the form?

Thanks!
Terri

 

[tgavin]

Never mind...I finally figured out it was Me!Requery instead.

 

[Dale Fye]

You could also use the Refresh method.
Personally, I avoid the SetWarnings method by using the Execute method to
execute action queries. The advantage of this is that it allows the
dbFailOnError argument, which causes an error if the action query fails,
allowing me to trap for the error. Furthermore, since booHolidayCard is
boolean (I would assume this is a yes/no checkbox), you could simplify your
code as:

Dim SQL As String

SQL = "Update [tblClients-Peach]" _
& " Set [booHolidayCard] = " & (NOT me.booHolidayCard) _
& " WHERE [tblClients-Peach].CustID =
[Forms]![frmGiftSelection]![CustID]
Currentdb.Execute SQL, dbfailonerror
me.Refresh

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Never mind...I finally figured out it was Me!Requery instead.

I have an tabular form with check boxes that need to be changed but the form
is not updatable. I wrote some code that seems to work but the form needs to
be requeried to show that it has been updated. The only code I can get to
work with this requeries the entire list which causes the user to lose his
place. The code I am using is:

Dim SQL As String


DoCmd.SetWarnings False

If Me!booHolidayCard.Value = -1 Then
SQL = "Update [tblClients-Peach]" & _
"Set [tblClients-Peach].booHolidayCard = 0 " & _
"WHERE [tblClients-Peach].CustID =
[Forms]![frmGiftSelection]![CustID]"
DoCmd.RunSQL SQL
DoCmd.Requery

ElseIf Me!booHolidayCard.Value = 0 Then
SQL = "Update [tblClients-Peach]" & _
"Set [tblClients-Peach].booHolidayCard = -1 " & _
"WHERE [tblClients-Peach].CustID =
[Forms]![frmGiftSelection]![CustID]"
DoCmd.RunSQL SQL
DoCmd.Requery

End If

How can I write it so that it only requeries the specific record in the form?

Thanks!
Terri