requery but record ON not getting updated

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

I have A FORM THAT SHOW ALL JOBS - WE check off(yes/no) which jobs to be at.
On the form I have a command button tied to an Update query that clears all
the jobs to be at check box.

I have a line in the event procedure of the command button to

me.formname.requery

The list get all cleared(meaning not check boxes are checked) - except for
the record that I am current ON - see pencil in left side. when I close form
and come back in check box clear on current record but what code do I need to
have to have it cleared without have to come out and back into the form???

thanks,
barb
 
You need to SAVE the changes to the record before you requery.

One method would be as follows

If Me.Dirty = True Then Me.Dirty = False

Since you did not post all your code, you will have to figure out where that
line belongs in your routine.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Here is my code below - for some reason - it is not Refreshing the form and
even when I close and come back in - one record always Still has a check
box???

thanks,
barb

Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

stDocName = "JeffCurrentJobsClearTobeAt&CrossOut"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click
If Me.Dirty = True Then Me.Dirty = False
Me.[jeffcurrentjobs pick from list].Requery

End Sub
 
I would think something like the following.


Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

'Save the current record so it will be included in whatever action
'you are trying to do with the open query.

If Me.Dirty = True Then Me.Dirty = False

'? the query name has an & in it. That requires you to
'surround it with brackets. If you don't your DoCmd.OpenQuery should
'be generating an error - which means that you are going to
'jump to the error handler. You have nothing in the
'error handler to tell you that you have an error.
stDocName = "[JeffCurrentJobsClearTobeAt&CrossOut]"

'? What are you attempting to do with this command?
DoCmd.OpenQuery stDocName, acNormal, acEdit
'If your query is an action query (updates,appends,or deletes records)
', you might try the following line in place of the above.
CurrentDb.QueryDefs(stDocName).Execute


Me.[jeffcurrentjobs pick from list].Requery

Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click
'? why is the requery in the error code? It
Me.[jeffcurrentjobs pick from list].Requery

End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Here is my code below - for some reason - it is not Refreshing the form and
even when I close and come back in - one record always Still has a check
box???

thanks,
barb

Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

stDocName = "JeffCurrentJobsClearTobeAt&CrossOut"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click
If Me.Dirty = True Then Me.Dirty = False
Me.[jeffcurrentjobs pick from list].Requery

End Sub


John Spencer said:
You need to SAVE the changes to the record before you requery.

One method would be as follows

If Me.Dirty = True Then Me.Dirty = False

Since you did not post all your code, you will have to figure out where that
line belongs in your routine.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.
Click to expand...
Click to expand...
 
Thanks soo much for helping -
The RESET Job List button works and Clears the check marks off all jobs(yes
it is an update query to change all yes in check box field to NO)

but the when I click on the command button to now go see what JOBS TO Be at
It should be NONE since All check marks removed but - it navigates to the
Jobs to be at form - based on a query to show only jobs where check marks are
YES)
But it shows all of the last set of jobs - lists them with No check marks -
it should have NO jobs showing

I think I need to rerun the query when navigate to Jobs to be at form but
not sure of the correct code - to I requery the query or the form based on
the query???

Below is the code for the two command buttons the Reset job list seem
good(since getting rid of the check marks)

thanks soo much,
Barb
Here is the code:

Private Sub cmdjustjobstobeAT_Click()
On Error GoTo Err_cmdjustjobstobeAT_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.Dirty = True Then Me.Dirty = False
stDocName = "JeffJobsToBEATqry Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.Refresh.Query


Exit_cmdjustjobstobeAT_Click:
Exit Sub

Err_cmdjustjobstobeAT_Click:
MsgBox Err.Description
Resume Exit_cmdjustjobstobeAT_Click

End Sub
Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

'Save the current record so it will be included in whatever action
'you are trying to do with the query

If Me.Dirty = True Then Me.Dirty = False

stDocName = "[JeffCurrentJobsClearTobeAt&CrossOut]"
CurrentDb.QueryDefs(stDocName).Execute
Me.Refresh



Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click

End Sub

John Spencer said:
I would think something like the following.


Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

'Save the current record so it will be included in whatever action
'you are trying to do with the open query.

If Me.Dirty = True Then Me.Dirty = False

'? the query name has an & in it. That requires you to
'surround it with brackets. If you don't your DoCmd.OpenQuery should
'be generating an error - which means that you are going to
'jump to the error handler. You have nothing in the
'error handler to tell you that you have an error.
stDocName = "[JeffCurrentJobsClearTobeAt&CrossOut]"

'? What are you attempting to do with this command?
DoCmd.OpenQuery stDocName, acNormal, acEdit
'If your query is an action query (updates,appends,or deletes records)
', you might try the following line in place of the above.
CurrentDb.QueryDefs(stDocName).Execute


Me.[jeffcurrentjobs pick from list].Requery

Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click
'? why is the requery in the error code? It
Me.[jeffcurrentjobs pick from list].Requery

End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Here is my code below - for some reason - it is not Refreshing the form and
even when I close and come back in - one record always Still has a check
box???

thanks,
barb

Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

stDocName = "JeffCurrentJobsClearTobeAt&CrossOut"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click
If Me.Dirty = True Then Me.Dirty = False
Me.[jeffcurrentjobs pick from list].Requery

End Sub


John Spencer said:
You need to SAVE the changes to the record before you requery.

One method would be as follows

If Me.Dirty = True Then Me.Dirty = False

Since you did not post all your code, you will have to figure out where that
line belongs in your routine.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

babs wrote:
I have A FORM THAT SHOW ALL JOBS - WE check off(yes/no) which jobs to be at.
On the form I have a command button tied to an Update query that clears all
the jobs to be at check box.

I have a line in the event procedure of the command button to

me.formname.requery

The list get all cleared(meaning not check boxes are checked) - except for
the record that I am current ON - see pencil in left side. when I close form
and come back in check box clear on current record but what code do I need to
have to have it cleared without have to come out and back into the form???

thanks,
barb
.
Click to expand...
Click to expand...
.
Click to expand...
 
Private Sub cmdjustjobstobeAT_Click()
On Error GoTo Err_cmdjustjobstobeAT_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.Dirty = True Then Me.Dirty = False
' FIX the NAME. You need brackets around the name
'because it has a space in the name.
'Good naming practice is to use ONLY Letters, Numbers
'and the underscore character. Any other characters
'in the names of objects in the database means you will
'need to surround them with square brackets to avoid
'problems.
stDocName = "[JeffJobsToBEATqry Form]"
DoCmd.OpenForm stDocName, , , stLinkCriteria

'This line makes no sense
' Me.Refresh.Query
'Perhaps you want one of the following lines
Me.Requery 'requery the current form
Forms(stDocName).Requery 'Requery the [JeffJobsToBEATqry Form]


Exit_cmdjustjobstobeAT_Click:
Exit Sub

Err_cmdjustjobstobeAT_Click:
MsgBox Err.Description
Resume Exit_cmdjustjobstobeAT_Click

End Sub
Private Sub cmdResetJobList_Click()
On Error GoTo Err_cmdResetJobList_Click

Dim stDocName As String

'Save the current record so it will be included in whatever action
'you are trying to do with the query

If Me.Dirty = True Then Me.Dirty = False

stDocName = "[JeffCurrentJobsClearTobeAt&CrossOut]"
CurrentDb.QueryDefs(stDocName).Execute
Me.Refresh



Exit_cmdResetJobList_Click:
Exit Sub

Err_cmdResetJobList_Click:
MsgBox Err.Description
Resume Exit_cmdResetJobList_Click

End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks soo much for helping -
The RESET Job List button works and Clears the check marks off all jobs(yes
it is an update query to change all yes in check box field to NO)

but the when I click on the command button to now go see what JOBS TO Be at
It should be NONE since All check marks removed but - it navigates to the
Jobs to be at form - based on a query to show only jobs where check marks are
YES)
But it shows all of the last set of jobs - lists them with No check marks -
it should have NO jobs showing

I think I need to rerun the query when navigate to Jobs to be at form but
not sure of the correct code - to I requery the query or the form based on
the query???

Below is the code for the two command buttons the Reset job list seem
good(since getting rid of the check marks)

thanks soo much,
Barb
Here is the code:
Click to expand...
SNIP
 
Back
Top