From Query to Form to Table (1 Viewer)

NavyBlueBolt

New member
Local time
Today, 06:10
Joined
Sep 9, 2020
Messages
9
Hi All!

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 :)
 

Attachments

  • SearchGageCmtsNotes.txt
    8.6 KB · Views: 124

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Are you talking about creating an audit trail?
 

NavyBlueBolt

New member
Local time
Today, 06:10
Joined
Sep 9, 2020
Messages
9
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.
 

Isaac

Lifelong Learner
Local time
Today, 03:10
Joined
Mar 14, 2017
Messages
8,738
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'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.
  1. Create a button on the form
  2. 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"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,175
you have Lots of code if your form is Unbound.
Lots of code to get data, save edited record, save new record.

you need to use Bound form, instead of unbound.
 

NavyBlueBolt

New member
Local time
Today, 06:10
Joined
Sep 9, 2020
Messages
9
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

Thanks again, everyone.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Isaac

Lifelong Learner
Local time
Today, 03:10
Joined
Mar 14, 2017
Messages
8,738
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

Thanks again, everyone.
Wow, ok,well very glad to hear you got something working.
 

Users who are viewing this thread

Top Bottom