Update Query for Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question

I have a form with a bunch of checkboxes on it. The user can check and
uncheck what they want. I would like to have an option where the user can
reset the values to the original (default) settings.

To do this, I have a table with the default settings that shares a name with
the table that the form is based on. I have an update query that updates the
values in the table equal to the default table values. It works. When I
look at the table the data changes.

I can't seem to get the change to reflect on the form.

Not sure what I am doing wrong. Here is my code.

Private Sub cmdReset_Click()
Dim TheRecNum As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportShowHideChecklist") 'Table where data
changes
rst.MoveFirst

TheRecNum = 1
'This is to find out what record number I'm on as the form is sorted by
ShowHideID
Do Until rst!ShowHideID = Me.ShowHideID 'Me.ShowHideID is the ID on the
form, rst!ShowHideID is the Recordset
Debug.Print Me.ShowHideID
Debug.Print TheRecNum
TheRecNum = TheRecNum + 1
rst.MoveNext
Loop

'The Update Query
DoCmd.RunSQL "Update tblReportShowHideChecklist_Defaults " & _
"INNER JOIN tblReportShowHideChecklist ON
tblReportShowHideChecklist_Defaults.INDMProgNameListID =
tblReportShowHideChecklist.INDMProgNameListID " & _
"SET tblReportShowHideChecklist.DryMatter =
[tblReportShowHideChecklist_Defaults]![DryMatter],
tblReportShowHideChecklist.CrudeProtein =
[tblReportShowHideChecklist_Defaults]![CrudeProtein],
tblReportShowHideChecklist.ADP = [tblReportShowHideChecklist_Defaults]![ADP],
tblReportShowHideChecklist.ADF = [tblReportShowHideChecklist_Defaults]![ADF],
tblReportShowHideChecklist.NDF = [tblReportShowHideChecklist_Defaults]![NDF],
tblReportShowHideChecklist.IFp = [tblReportShowHideChecklist_Defaults]![IFp],
tblReportShowHideChecklist.PD = [tblReportShowHideChecklist_Defaults]![PD],
tblReportShowHideChecklist.Fat = [tblReportShowHideChecklist_Defaults]![Fat],
tblReportShowHideChecklist.CrudeFiber =
[tblReportShowHideChecklist_Defaults]![CrudeFiber],
tblReportShowHideChecklist.Ash = [tblReportShowHideChecklist_Defaults]![Ash],
tblReportShowHideChecklist.Ca = [tblReportShowHideChecklist_Defaults]![Ca] "
& _
", tblReportShowHideChecklist.Mg =
[tblReportShowHideChecklist_Defaults]![Mg], tblReportShowHideChecklist.P =
[tblReportShowHideChecklist_Defaults]![P], tblReportShowHideChecklist.K =
[tblReportShowHideChecklist_Defaults]![K],
tblReportShowHideChecklist.Moisture =
[tblReportShowHideChecklist_Defaults]![Moisture],
tblReportShowHideChecklist.AvailProtein =
[tblReportShowHideChecklist_Defaults]![AvailProtein],
tblReportShowHideChecklist.SolProtein =
[tblReportShowHideChecklist_Defaults]![SolProtein],
tblReportShowHideChecklist.DegProtein =
[tblReportShowHideChecklist_Defaults]![DegProtein],
tblReportShowHideChecklist.UnDegProtein =
[tblReportShowHideChecklist_Defaults]![UnDegProtein],
tblReportShowHideChecklist.RFV = [tblReportShowHideChecklist_Defaults]![RFV],
tblReportShowHideChecklist.NSC = [tblReportShowHideChecklist_Defaults]![NSC],
tblReportShowHideChecklist.TDN = [tblReportShowHideChecklist_Defaults]![TDN]
" & _
", tblReportShowHideChecklist.NEL =
[tblReportShowHideChecklist_Defaults]![NEL] " & _
"WHERE
(((tblReportShowHideChecklist.ShowHideID)=[Forms]![frmtblReportShowHideChecklist]![ShowHideID]));"

'Requery the form to pick up the updates
'Upon requery, the form moves to the first record
Me.Requery
'Move to the record the user was on when they started this code
DoCmd.GoToRecord acDataForm, "frmtblReportShowHideChecklist", acGoTo,
TheRecNum
'Notify user that updates were made
MsgBox "Default settings for this Program Name have been applied."
End Sub
 
If it has not been saved yet them Me.UnDo will restore the defaults.
Thanks for taking the time to read my question

I have a form with a bunch of checkboxes on it. The user can check and
uncheck what they want. I would like to have an option where the user can
reset the values to the original (default) settings.

To do this, I have a table with the default settings that shares a name with
the table that the form is based on. I have an update query that updates the
values in the table equal to the default table values. It works. When I
look at the table the data changes.

I can't seem to get the change to reflect on the form.

Not sure what I am doing wrong. Here is my code.

Private Sub cmdReset_Click()
Dim TheRecNum As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportShowHideChecklist") 'Table where data
changes
rst.MoveFirst

TheRecNum = 1
'This is to find out what record number I'm on as the form is sorted by
ShowHideID
Do Until rst!ShowHideID = Me.ShowHideID 'Me.ShowHideID is the ID on the
form, rst!ShowHideID is the Recordset
Debug.Print Me.ShowHideID
Debug.Print TheRecNum
TheRecNum = TheRecNum + 1
rst.MoveNext
Loop

'The Update Query
DoCmd.RunSQL "Update tblReportShowHideChecklist_Defaults " & _
"INNER JOIN tblReportShowHideChecklist ON
tblReportShowHideChecklist_Defaults.INDMProgNameListID =
tblReportShowHideChecklist.INDMProgNameListID " & _
"SET tblReportShowHideChecklist.DryMatter =
[tblReportShowHideChecklist_Defaults]![DryMatter],
tblReportShowHideChecklist.CrudeProtein =
[tblReportShowHideChecklist_Defaults]![CrudeProtein],
tblReportShowHideChecklist.ADP = [tblReportShowHideChecklist_Defaults]![ADP],
tblReportShowHideChecklist.ADF = [tblReportShowHideChecklist_Defaults]![ADF],
tblReportShowHideChecklist.NDF = [tblReportShowHideChecklist_Defaults]![NDF],
tblReportShowHideChecklist.IFp = [tblReportShowHideChecklist_Defaults]![IFp],
tblReportShowHideChecklist.PD = [tblReportShowHideChecklist_Defaults]![PD],
tblReportShowHideChecklist.Fat = [tblReportShowHideChecklist_Defaults]![Fat],
tblReportShowHideChecklist.CrudeFiber =
[tblReportShowHideChecklist_Defaults]![CrudeFiber],
tblReportShowHideChecklist.Ash = [tblReportShowHideChecklist_Defaults]![Ash],
tblReportShowHideChecklist.Ca = [tblReportShowHideChecklist_Defaults]![Ca] "
& _
", tblReportShowHideChecklist.Mg =
[tblReportShowHideChecklist_Defaults]![Mg], tblReportShowHideChecklist.P =
[tblReportShowHideChecklist_Defaults]![P], tblReportShowHideChecklist.K =
[tblReportShowHideChecklist_Defaults]![K],
tblReportShowHideChecklist.Moisture =
[tblReportShowHideChecklist_Defaults]![Moisture],
tblReportShowHideChecklist.AvailProtein =
[tblReportShowHideChecklist_Defaults]![AvailProtein],
tblReportShowHideChecklist.SolProtein =
[tblReportShowHideChecklist_Defaults]![SolProtein],
tblReportShowHideChecklist.DegProtein =
[tblReportShowHideChecklist_Defaults]![DegProtein],
tblReportShowHideChecklist.UnDegProtein =
[tblReportShowHideChecklist_Defaults]![UnDegProtein],
tblReportShowHideChecklist.RFV = [tblReportShowHideChecklist_Defaults]![RFV],
tblReportShowHideChecklist.NSC = [tblReportShowHideChecklist_Defaults]![NSC],
tblReportShowHideChecklist.TDN = [tblReportShowHideChecklist_Defaults]![TDN]
" & _
", tblReportShowHideChecklist.NEL =
[tblReportShowHideChecklist_Defaults]![NEL] " & _
"WHERE
(((tblReportShowHideChecklist.ShowHideID)=[Forms]![frmtblReportShowHideChecklist]![ShowHideID]));"

'Requery the form to pick up the updates
'Upon requery, the form moves to the first record
Me.Requery
'Move to the record the user was on when they started this code
DoCmd.GoToRecord acDataForm, "frmtblReportShowHideChecklist", acGoTo,
TheRecNum
'Notify user that updates were made
MsgBox "Default settings for this Program Name have been applied."
End Sub
Click to expand...
 
Good point. I'm also looking at changing them after they've been saved. So
they set up the options one way which works for them for a while, then they
may need to go back to the default settings.

I'm working with my update query, and it seems that it is not changing the
table.

Do you see anything wrong with my query?

Brad

ruralguy via AccessMonster.com said:
If it has not been saved yet them Me.UnDo will restore the defaults.
Thanks for taking the time to read my question

I have a form with a bunch of checkboxes on it. The user can check and
uncheck what they want. I would like to have an option where the user can
reset the values to the original (default) settings.

To do this, I have a table with the default settings that shares a name with
the table that the form is based on. I have an update query that updates the
values in the table equal to the default table values. It works. When I
look at the table the data changes.

I can't seem to get the change to reflect on the form.

Not sure what I am doing wrong. Here is my code.

Private Sub cmdReset_Click()
Dim TheRecNum As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportShowHideChecklist") 'Table where data
changes
rst.MoveFirst

TheRecNum = 1
'This is to find out what record number I'm on as the form is sorted by
ShowHideID
Do Until rst!ShowHideID = Me.ShowHideID 'Me.ShowHideID is the ID on the
form, rst!ShowHideID is the Recordset
Debug.Print Me.ShowHideID
Debug.Print TheRecNum
TheRecNum = TheRecNum + 1
rst.MoveNext
Loop

'The Update Query
DoCmd.RunSQL "Update tblReportShowHideChecklist_Defaults " & _
"INNER JOIN tblReportShowHideChecklist ON
tblReportShowHideChecklist_Defaults.INDMProgNameListID =
tblReportShowHideChecklist.INDMProgNameListID " & _
"SET tblReportShowHideChecklist.DryMatter =
[tblReportShowHideChecklist_Defaults]![DryMatter],
tblReportShowHideChecklist.CrudeProtein =
[tblReportShowHideChecklist_Defaults]![CrudeProtein],
tblReportShowHideChecklist.ADP = [tblReportShowHideChecklist_Defaults]![ADP],
tblReportShowHideChecklist.ADF = [tblReportShowHideChecklist_Defaults]![ADF],
tblReportShowHideChecklist.NDF = [tblReportShowHideChecklist_Defaults]![NDF],
tblReportShowHideChecklist.IFp = [tblReportShowHideChecklist_Defaults]![IFp],
tblReportShowHideChecklist.PD = [tblReportShowHideChecklist_Defaults]![PD],
tblReportShowHideChecklist.Fat = [tblReportShowHideChecklist_Defaults]![Fat],
tblReportShowHideChecklist.CrudeFiber =
[tblReportShowHideChecklist_Defaults]![CrudeFiber],
tblReportShowHideChecklist.Ash = [tblReportShowHideChecklist_Defaults]![Ash],
tblReportShowHideChecklist.Ca = [tblReportShowHideChecklist_Defaults]![Ca] "
& _
", tblReportShowHideChecklist.Mg =
[tblReportShowHideChecklist_Defaults]![Mg], tblReportShowHideChecklist.P =
[tblReportShowHideChecklist_Defaults]![P], tblReportShowHideChecklist.K =
[tblReportShowHideChecklist_Defaults]![K],
tblReportShowHideChecklist.Moisture =
[tblReportShowHideChecklist_Defaults]![Moisture],
tblReportShowHideChecklist.AvailProtein =
[tblReportShowHideChecklist_Defaults]![AvailProtein],
tblReportShowHideChecklist.SolProtein =
[tblReportShowHideChecklist_Defaults]![SolProtein],
tblReportShowHideChecklist.DegProtein =
[tblReportShowHideChecklist_Defaults]![DegProtein],
tblReportShowHideChecklist.UnDegProtein =
[tblReportShowHideChecklist_Defaults]![UnDegProtein],
tblReportShowHideChecklist.RFV = [tblReportShowHideChecklist_Defaults]![RFV],
tblReportShowHideChecklist.NSC = [tblReportShowHideChecklist_Defaults]![NSC],
tblReportShowHideChecklist.TDN = [tblReportShowHideChecklist_Defaults]![TDN]
" & _
", tblReportShowHideChecklist.NEL =
[tblReportShowHideChecklist_Defaults]![NEL] " & _
"WHERE
(((tblReportShowHideChecklist.ShowHideID)=[Forms]![frmtblReportShowHideChecklist]![ShowHideID]));"

'Requery the form to pick up the updates
'Upon requery, the form moves to the first record
Me.Requery
'Move to the record the user was on when they started this code
DoCmd.GoToRecord acDataForm, "frmtblReportShowHideChecklist", acGoTo,
TheRecNum
'Notify user that updates were made
MsgBox "Default settings for this Program Name have been applied."
End Sub
Click to expand...
Click to expand...
 
Figured it out. I have to close the form for it to work when the form is
open. I guess the form locks the records so the update query doesn't work.

Brad

Brad said:
Thanks for taking the time to read my question

I have a form with a bunch of checkboxes on it. The user can check and
uncheck what they want. I would like to have an option where the user can
reset the values to the original (default) settings.

To do this, I have a table with the default settings that shares a name with
the table that the form is based on. I have an update query that updates the
values in the table equal to the default table values. It works. When I
look at the table the data changes.

I can't seem to get the change to reflect on the form.

Not sure what I am doing wrong. Here is my code.

Private Sub cmdReset_Click()
Dim TheRecNum As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReportShowHideChecklist") 'Table where data
changes
rst.MoveFirst

TheRecNum = 1
'This is to find out what record number I'm on as the form is sorted by
ShowHideID
Do Until rst!ShowHideID = Me.ShowHideID 'Me.ShowHideID is the ID on the
form, rst!ShowHideID is the Recordset
Debug.Print Me.ShowHideID
Debug.Print TheRecNum
TheRecNum = TheRecNum + 1
rst.MoveNext
Loop

'The Update Query
DoCmd.RunSQL "Update tblReportShowHideChecklist_Defaults " & _
"INNER JOIN tblReportShowHideChecklist ON
tblReportShowHideChecklist_Defaults.INDMProgNameListID =
tblReportShowHideChecklist.INDMProgNameListID " & _
"SET tblReportShowHideChecklist.DryMatter =
[tblReportShowHideChecklist_Defaults]![DryMatter],
tblReportShowHideChecklist.CrudeProtein =
[tblReportShowHideChecklist_Defaults]![CrudeProtein],
tblReportShowHideChecklist.ADP = [tblReportShowHideChecklist_Defaults]![ADP],
tblReportShowHideChecklist.ADF = [tblReportShowHideChecklist_Defaults]![ADF],
tblReportShowHideChecklist.NDF = [tblReportShowHideChecklist_Defaults]![NDF],
tblReportShowHideChecklist.IFp = [tblReportShowHideChecklist_Defaults]![IFp],
tblReportShowHideChecklist.PD = [tblReportShowHideChecklist_Defaults]![PD],
tblReportShowHideChecklist.Fat = [tblReportShowHideChecklist_Defaults]![Fat],
tblReportShowHideChecklist.CrudeFiber =
[tblReportShowHideChecklist_Defaults]![CrudeFiber],
tblReportShowHideChecklist.Ash = [tblReportShowHideChecklist_Defaults]![Ash],
tblReportShowHideChecklist.Ca = [tblReportShowHideChecklist_Defaults]![Ca] "
& _
", tblReportShowHideChecklist.Mg =
[tblReportShowHideChecklist_Defaults]![Mg], tblReportShowHideChecklist.P =
[tblReportShowHideChecklist_Defaults]![P], tblReportShowHideChecklist.K =
[tblReportShowHideChecklist_Defaults]![K],
tblReportShowHideChecklist.Moisture =
[tblReportShowHideChecklist_Defaults]![Moisture],
tblReportShowHideChecklist.AvailProtein =
[tblReportShowHideChecklist_Defaults]![AvailProtein],
tblReportShowHideChecklist.SolProtein =
[tblReportShowHideChecklist_Defaults]![SolProtein],
tblReportShowHideChecklist.DegProtein =
[tblReportShowHideChecklist_Defaults]![DegProtein],
tblReportShowHideChecklist.UnDegProtein =
[tblReportShowHideChecklist_Defaults]![UnDegProtein],
tblReportShowHideChecklist.RFV = [tblReportShowHideChecklist_Defaults]![RFV],
tblReportShowHideChecklist.NSC = [tblReportShowHideChecklist_Defaults]![NSC],
tblReportShowHideChecklist.TDN = [tblReportShowHideChecklist_Defaults]![TDN]
" & _
", tblReportShowHideChecklist.NEL =
[tblReportShowHideChecklist_Defaults]![NEL] " & _
"WHERE
(((tblReportShowHideChecklist.ShowHideID)=[Forms]![frmtblReportShowHideChecklist]![ShowHideID]));"

'Requery the form to pick up the updates
'Upon requery, the form moves to the first record
Me.Requery
'Move to the record the user was on when they started this code
DoCmd.GoToRecord acDataForm, "frmtblReportShowHideChecklist", acGoTo,
TheRecNum
'Notify user that updates were made
MsgBox "Default settings for this Program Name have been applied."
End Sub
Click to expand...
 
I thought you said that the table was in fact getting changed to the defaults
but you couldn't get the form to show that fact. If so then the query you
have put together seems to be working just fine. If you close the form after
the reset to default and then open it again, do the values for that record
show as defaulted?
Good point. I'm also looking at changing them after they've been saved. So
they set up the options one way which works for them for a while, then they
may need to go back to the default settings.

I'm working with my update query, and it seems that it is not changing the
table.

Do you see anything wrong with my query?

Brad
If it has not been saved yet them Me.UnDo will restore the defaults.
Click to expand...
[quoted text clipped - 84 lines]
Click to expand...
 
If your form is based on a query then there should be no problem. Do you
have your record locking set correctly? It is common to do what you are
doing and it should not be necessary to close the form to get it to update.
Figured it out. I have to close the form for it to work when the form is
open. I guess the form locks the records so the update query doesn't work.

Brad
Thanks for taking the time to read my question
Click to expand...
[quoted text clipped - 82 lines]
MsgBox "Default settings for this Program Name have been applied."
End Sub
Click to expand...
Click to expand...
 
Back
Top