Requery a record in a form

  • Thread starter Thread starter tgavin
  • Start date Start date
T

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
 
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.



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

tgavin said:
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
Click to expand...
Click to expand...
 
Back
Top