I'm new to the forum so please forgive me if I'm not doing this right.
I have made a database to put in calibrations for gages w/out typing any SQL. There's a form to make a new gage, to add a new calibration for gages, and to search for gage histories. Within a form that connects to a query, shows a certain gage's records individually (Next/Previous buttons included). There has been a need to change specific records to update information (mostly about the stage of repairs or if there's a change in location). As of now, the form lets you change hard data on the master table (reminder: I'm a bit new at this). Now I am working on a way to add new record (disguised as an update) instead of changing the hard data, but I have hit a wall.
How do I save the changed data (unbound textboxes that default the record values) from the form onto the master table as a new entry? I have tried changing the settings to allow data entry, additions, and edits. I thought maybe an append query but there's no table to pull the data from. Even if I could make a table from the form, I'm having a hard time directing the values from the unbound boxes to a different location. Previously, I had the form sourced to the table of choice. Since this one has a query as it's source, I can't figure out how to send it to the table I need. I'm at the point of making 14 TempVars just to move everything over. If there's a better way, please let me know.
Attached is the code I made to get as far as I did.
Any help would be greatly appreciated.
Thank you
In a sense, yeah. We don't have logins as of yet. Only three people log in (myself, my boss who always opens backend anyway, and a coworker). My boss is big on keeping data, erroneous or otherwise. I do have entry dates and times set up so we can determine when data was entered. I'm just having a hard time figuring out how to move the "updated" info over to where I need it.
I'll apologize in advance if my response doesn't help you. Although I read your question many times, I still feel like I don't fully grasp what the situation and need are. Maybe just cuz it's Friday and my brain is mush!
But, just to directly answer the one specific question I've quoted directly:
To save the values of controls on an unbound form, in a way that inserts a new record to a table, here is one path to get you there. You have to code a dynamic SQL statement and execute it.
Create a button on the form
Code something like this behind the button:
Code:
If MsgBox("Proceed to create new record in table?", vbYesNo, " ") = vbNo Then Exit Sub
Dim strCol1 As String, strCol2 As String, strCol3 As String, strSQL As String
'prepare your variables for the INSERT INTO sql statement.
'if any are blank, they become the literal word Null
'if they are not blank, they become single quote delimited, like 'value'
'Note - if the destination column is Number, change single quote to nothing
' if the destination column is Date, change single quote to #
' and use Format(value,"mm/dd/yyyy")
strCol1 = IIf(Len("" & strCol1) = 0, "Null", "'" & strCol1.Value & "'")
strCol2 = IIf(Len("" & strCol2) = 0, "Null", "'" & strCol2.Value & "'")
strCol3 = IIf(Len("" & strCol3) = 0, "Null", "'" & strCol3.Value & "'")
strSQL = "insert into tablename (colName1,colName2,colName3) values (" & strCol1 & ", " & strCol2 & ", " & strCol3 & ")"
Debug.Print strSQL 'use this so in case of error & troubleshooting, you know WHAT the sql the db tried to execute, was
CurrentDb.Execute strSQL, dbFailonError
MsgBox "Complete"
Thank you for all of your help. I had to solve this issue within an unexpected time limit. Nevertheless, I thank you for your aid.
This is what I went with in the end. The form itself was bound to a query that could alter data, but not add data. As theDBguy had asked, I was making an audit trail. I didn't want to alter records, just make it seem to the end user that the records were being altered. I tried sifting through all of the properties for the query, the forms, and the associated tables, but to no avail. I could not add records through the query.
So, the whole thing started with a form to run a query to open a different form holding said query with unbound text boxes displaying the records values as default (so as not to alter hard data on the master list). I learned to use unbound text boxes for record display and alteration from YT: Design and Deploy.
From there, if you want to save altered data, it would save all of the unbound text boxes with individual tempvars to a newly created table, add that data to the master list, find all of that gage's records and mark the "latest entry" field as false, search for the latest entry by date calibrated and make a separate table with one record, toggle the "latest entry" field to true on the master list using the key ID, and marking the older entry (the one being "changed") to invalid so as not to pop up unnecessarily on searches.
This all seems like a ton of work, but I have it working to where I need it.
Code:
Option Compare Database
Private Sub DateCali_Entr8_LostFocus()
If Me.DateCali_Entr8 <> "" And Me.Interval_Entr8 <> "" Then
Me.CaliDue_Entr8 = DateAdd("yyyy", Me.Interval_Entr8, Me.DateCali_Entr8)
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Undo
End Sub
Private Sub Form_Load()
DoCmd.SetWarnings False
End Sub
Private Sub Interval_Entr8_LostFocus()
If Me.DateCali_Entr8 <> "" And Me.Interval_Entr8 <> "" Then
Me.CaliDue_Entr8 = DateAdd("yyyy", Me.Interval_Entr8, Me.DateCali_Entr8)
End If
End Sub
Private Sub Next_Btn9_Click()
'undoes any unsaved changes
Me.Undo
'moves to next record and updates text fields
Me.Recordset.MoveNext
Me.GageTyp_Entr8 = Me.Gage_Type
Me.Desc_Entr8 = Me.Description
Me.Loca_Entr8 = Me.Location
Me.Cert_Entr8 = Me.Cert_No
Me.CaliBy_Entr8 = Me.Who_Calibrated
Me.Interval_Entr8 = Me.Interval
Me.DateCali_Entr8 = Me.Date_Calibrated
Me.CaliDue_Entr8 = Me.Calibration_Due
Me.Activity_Entr8 = Me.Status_Activity
Me.Accept_Entr8 = Me.Status_Acceptability
Me.LocaStat_Entr8 = Me.Status_Location
Me.Cmt_Entr8 = Me.COMMENT
Me.Note_Entr8 = Me.Cleanup_Note
'condition: returns to last record if no more are found
If Recordset.EOF Then
MsgBox "No earlier records have been stored."
Recordset.MoveLast
End If
End Sub
Private Sub Prev_Btn9_Click()
'undoes any unsaved changes
Me.Undo
'moves to next record and updates text fields
Me.Recordset.MovePrevious
Me.GageTyp_Entr8 = Me.Gage_Type
Me.Desc_Entr8 = Me.Description
Me.Loca_Entr8 = Me.Location
Me.Cert_Entr8 = Me.Cert_No
Me.CaliBy_Entr8 = Me.Who_Calibrated
Me.Interval_Entr8 = Me.Interval
Me.DateCali_Entr8 = Me.Date_Calibrated
Me.CaliDue_Entr8 = Me.Calibration_Due
Me.Activity_Entr8 = Me.Status_Activity
Me.Accept_Entr8 = Me.Status_Acceptability
Me.LocaStat_Entr8 = Me.Status_Location
Me.Cmt_Entr8 = Me.COMMENT
Me.Note_Entr8 = Me.Cleanup_Note
'condition: returns to last record if no more are found
If Recordset.BOF Then
MsgBox "No later records have been stored."
Recordset.MoveFirst
End If
End Sub
Private Sub SaveChanges_Btn9_Click()
Dim myDateCali As String
Dim myCaliDue As String
Dim myLatest As String
If MsgBox("Are you certain these are the permanent changes you need to make?", vbOKCancel, "Double Check") = vbCancel Then
Me.Undo
Else
'If location, activity, acceptability, and location status are not null
If IsNull(Me.Location) Or IsNull(Me.Status_Activity) Or IsNull(Me.Status_Acceptability) Or IsNull(Me.Status_Location) Then
Me.GageTyp_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Desc_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Loca_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Activity_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Accept_Lbl8.ForeColor = RGB(255, 0, 0)
Me.LocaStat_Lbl8.ForeColor = RGB(255, 0, 0)
MsgBox "Required fields are missing", vbOKOnly, "No Save Made"
Else
'If Gage ID needs to be changed
DoCmd.OpenQuery "ChangeGageID_upqry2"
'Save New Data
myLatest = Me.Latest_Entr9
TempVars!Tlatest.Value = myLatest
TempVars!InvalidateRecVar.Value = Me.Entry_No.Value
TempVars!Tgagetype.Value = Me.GageTyp_Entr8.Value
TempVars!Tdesc.Value = Me.Desc_Entr8.Value
TempVars!Tloca.Value = Me.Loca_Entr8.Value
TempVars!Tcert.Value = Me.Cert_Entr8.Value
TempVars!Tcaliby.Value = Me.CaliBy_Entr8.Value
TempVars!Tinterval.Value = Me.Interval_Entr8.Value
'convert dates to strings and save them as var
myDateCali = Me.DateCali_Entr8
TempVars!Tdatecali.Value = myDateCali
myCaliDue = Me.CaliDue_Entr8
TempVars!Tcalidue.Value = myCaliDue
TempVars!Tactivity.Value = Me.Activity_Entr8.Value
TempVars!Taccept.Value = Me.Accept_Entr8.Value
TempVars!Tlocastat.Value = Me.LocaStat_Entr8.Value
TempVars!Tcmt.Value = Me.Cmt_Entr8.Value
TempVars!Tnote.Value = Me.Note_Entr8.Value
'run Queries
'Save Data to TempAddDataChange_tbl7
DoCmd.OpenQuery "AddDataChange_Tqry2"
'Add data to GageData_tbl
DoCmd.OpenQuery "AddDataChangeToGageData_apqry1"
'Mark all gage's Latest from true to false
DoCmd.OpenQuery "EditLatestEntry_upqry6"
'Search for gage's Latest by DateCali, stores the Top Result (Totals) on RetickLatest table
DoCmd.OpenQuery "SearchLatest_Tqry1"
'Mark gage's Latest from false to true based on Retick table
DoCmd.OpenQuery "RetickLatest_upqry4"
'marks the "edited" record invalid
DoCmd.OpenQuery "ValidToInvalid_upqry3"
'closing actions
MsgBox "The changes have been saved.", vbOKOnly, "Thank you"
DoCmd.Close
DoCmd.Close acForm, "WelcomePage_frm4", acSaveNo
DoCmd.OpenForm "WelcomePage_frm4"
DoCmd.OpenForm "EditCommentsAndNotes_frm9"
'End If
End If
End If
End Sub
Private Sub Trashcan_Btn8_Click()
If MsgBox("Would you like to remove the record?", vbYesNo, "Remove") = vbYes Then
'Marks valid selection to invalid
TempVars!InvalidateRecVar.Value = Me.Entry_No.Value
DoCmd.OpenQuery "ValidToInvalid_upqry3"
'if the latest entry is ticked
If Me.Latest_Entry = -1 Then
DoCmd.OpenQuery "EditLatestEntry_upqry6"
DoCmd.OpenQuery "SearchLatest_Tqry1"
DoCmd.OpenQuery "RetickLatest_upqry4"
End If
'closing actions
MsgBox "Record removed", vbOKOnly
DoCmd.Close
DoCmd.OpenForm "EditCommentsAndNotes_frm9"
End If
End Sub
Thank you for all of your help. I had to solve this issue within an unexpected time limit. Nevertheless, I thank you for your aid.
This is what I went with in the end. The form itself was bound to a query that could alter data, but not add data. As theDBguy had asked, I was making an audit trail. I didn't want to alter records, just make it seem to the end user that the records were being altered. I tried sifting through all of the properties for the query, the forms, and the associated tables, but to no avail. I could not add records through the query.
So, the whole thing started with a form to run a query to open a different form holding said query with unbound text boxes displaying the records values as default (so as not to alter hard data on the master list). I learned to use unbound text boxes for record display and alteration from YT: Design and Deploy.
From there, if you want to save altered data, it would save all of the unbound text boxes with individual tempvars to a newly created table, add that data to the master list, find all of that gage's records and mark the "latest entry" field as false, search for the latest entry by date calibrated and make a separate table with one record, toggle the "latest entry" field to true on the master list using the key ID, and marking the older entry (the one being "changed") to invalid so as not to pop up unnecessarily on searches.
This all seems like a ton of work, but I have it working to where I need it.
Code:
Option Compare Database
Private Sub DateCali_Entr8_LostFocus()
If Me.DateCali_Entr8 <> "" And Me.Interval_Entr8 <> "" Then
Me.CaliDue_Entr8 = DateAdd("yyyy", Me.Interval_Entr8, Me.DateCali_Entr8)
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Undo
End Sub
Private Sub Form_Load()
DoCmd.SetWarnings False
End Sub
Private Sub Interval_Entr8_LostFocus()
If Me.DateCali_Entr8 <> "" And Me.Interval_Entr8 <> "" Then
Me.CaliDue_Entr8 = DateAdd("yyyy", Me.Interval_Entr8, Me.DateCali_Entr8)
End If
End Sub
Private Sub Next_Btn9_Click()
'undoes any unsaved changes
Me.Undo
'moves to next record and updates text fields
Me.Recordset.MoveNext
Me.GageTyp_Entr8 = Me.Gage_Type
Me.Desc_Entr8 = Me.Description
Me.Loca_Entr8 = Me.Location
Me.Cert_Entr8 = Me.Cert_No
Me.CaliBy_Entr8 = Me.Who_Calibrated
Me.Interval_Entr8 = Me.Interval
Me.DateCali_Entr8 = Me.Date_Calibrated
Me.CaliDue_Entr8 = Me.Calibration_Due
Me.Activity_Entr8 = Me.Status_Activity
Me.Accept_Entr8 = Me.Status_Acceptability
Me.LocaStat_Entr8 = Me.Status_Location
Me.Cmt_Entr8 = Me.COMMENT
Me.Note_Entr8 = Me.Cleanup_Note
'condition: returns to last record if no more are found
If Recordset.EOF Then
MsgBox "No earlier records have been stored."
Recordset.MoveLast
End If
End Sub
Private Sub Prev_Btn9_Click()
'undoes any unsaved changes
Me.Undo
'moves to next record and updates text fields
Me.Recordset.MovePrevious
Me.GageTyp_Entr8 = Me.Gage_Type
Me.Desc_Entr8 = Me.Description
Me.Loca_Entr8 = Me.Location
Me.Cert_Entr8 = Me.Cert_No
Me.CaliBy_Entr8 = Me.Who_Calibrated
Me.Interval_Entr8 = Me.Interval
Me.DateCali_Entr8 = Me.Date_Calibrated
Me.CaliDue_Entr8 = Me.Calibration_Due
Me.Activity_Entr8 = Me.Status_Activity
Me.Accept_Entr8 = Me.Status_Acceptability
Me.LocaStat_Entr8 = Me.Status_Location
Me.Cmt_Entr8 = Me.COMMENT
Me.Note_Entr8 = Me.Cleanup_Note
'condition: returns to last record if no more are found
If Recordset.BOF Then
MsgBox "No later records have been stored."
Recordset.MoveFirst
End If
End Sub
Private Sub SaveChanges_Btn9_Click()
Dim myDateCali As String
Dim myCaliDue As String
Dim myLatest As String
If MsgBox("Are you certain these are the permanent changes you need to make?", vbOKCancel, "Double Check") = vbCancel Then
Me.Undo
Else
'If location, activity, acceptability, and location status are not null
If IsNull(Me.Location) Or IsNull(Me.Status_Activity) Or IsNull(Me.Status_Acceptability) Or IsNull(Me.Status_Location) Then
Me.GageTyp_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Desc_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Loca_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Activity_Lbl8.ForeColor = RGB(255, 0, 0)
Me.Accept_Lbl8.ForeColor = RGB(255, 0, 0)
Me.LocaStat_Lbl8.ForeColor = RGB(255, 0, 0)
MsgBox "Required fields are missing", vbOKOnly, "No Save Made"
Else
'If Gage ID needs to be changed
DoCmd.OpenQuery "ChangeGageID_upqry2"
'Save New Data
myLatest = Me.Latest_Entr9
TempVars!Tlatest.Value = myLatest
TempVars!InvalidateRecVar.Value = Me.Entry_No.Value
TempVars!Tgagetype.Value = Me.GageTyp_Entr8.Value
TempVars!Tdesc.Value = Me.Desc_Entr8.Value
TempVars!Tloca.Value = Me.Loca_Entr8.Value
TempVars!Tcert.Value = Me.Cert_Entr8.Value
TempVars!Tcaliby.Value = Me.CaliBy_Entr8.Value
TempVars!Tinterval.Value = Me.Interval_Entr8.Value
'convert dates to strings and save them as var
myDateCali = Me.DateCali_Entr8
TempVars!Tdatecali.Value = myDateCali
myCaliDue = Me.CaliDue_Entr8
TempVars!Tcalidue.Value = myCaliDue
TempVars!Tactivity.Value = Me.Activity_Entr8.Value
TempVars!Taccept.Value = Me.Accept_Entr8.Value
TempVars!Tlocastat.Value = Me.LocaStat_Entr8.Value
TempVars!Tcmt.Value = Me.Cmt_Entr8.Value
TempVars!Tnote.Value = Me.Note_Entr8.Value
'run Queries
'Save Data to TempAddDataChange_tbl7
DoCmd.OpenQuery "AddDataChange_Tqry2"
'Add data to GageData_tbl
DoCmd.OpenQuery "AddDataChangeToGageData_apqry1"
'Mark all gage's Latest from true to false
DoCmd.OpenQuery "EditLatestEntry_upqry6"
'Search for gage's Latest by DateCali, stores the Top Result (Totals) on RetickLatest table
DoCmd.OpenQuery "SearchLatest_Tqry1"
'Mark gage's Latest from false to true based on Retick table
DoCmd.OpenQuery "RetickLatest_upqry4"
'marks the "edited" record invalid
DoCmd.OpenQuery "ValidToInvalid_upqry3"
'closing actions
MsgBox "The changes have been saved.", vbOKOnly, "Thank you"
DoCmd.Close
DoCmd.Close acForm, "WelcomePage_frm4", acSaveNo
DoCmd.OpenForm "WelcomePage_frm4"
DoCmd.OpenForm "EditCommentsAndNotes_frm9"
'End If
End If
End If
End Sub
Private Sub Trashcan_Btn8_Click()
If MsgBox("Would you like to remove the record?", vbYesNo, "Remove") = vbYes Then
'Marks valid selection to invalid
TempVars!InvalidateRecVar.Value = Me.Entry_No.Value
DoCmd.OpenQuery "ValidToInvalid_upqry3"
'if the latest entry is ticked
If Me.Latest_Entry = -1 Then
DoCmd.OpenQuery "EditLatestEntry_upqry6"
DoCmd.OpenQuery "SearchLatest_Tqry1"
DoCmd.OpenQuery "RetickLatest_upqry4"
End If
'closing actions
MsgBox "Record removed", vbOKOnly
DoCmd.Close
DoCmd.OpenForm "EditCommentsAndNotes_frm9"
End If
End Sub