Form to create multiple records and update others

[gleblanc]

I've got a database that I'm using to help keep track of tools and maintenance on our assembly line. My current project is to create a form that I can use when a swap out a tool on the line.

First let me try to describe how I want the form to look. The form will have a combobox to select the Tool ID. This should then look up the current model number, serial number, and last service date (from tbl_Tool_Task). Then the user will select the reason for the tool swap from another combobox. The reason can be either "Failed" or "PM". Finally, they will proceed to the "New Tool" section of the form, and select the Model Number and Serial Number of the replacement tool, and enter the torque calibration value of the tool.



I managed to create a form that does all of the lookups and auto-populates the forms just how I'd like it to. My problem now is figuring out how to create and update the records. I need to create 2 records in tbl_ChangeHistory. The first record is for the old tool, and should include the model number, serial number, date, change type (failed/PM), requestor, processor, and Tool ID. This tells me when this tool was taken out of service. The second record is for the new tool, and should include model number, serial number, date, torque, change tyoe (tool issued), requestor, processor, and Tool ID. Finally, I need to update tbl_Tool_Task with the new model number, serial number, and date for the appropriate Tool ID.

I can't quite wrap my head around how to set this up. I've got no qualms about doing this through VBA and SQL if that's going to be the easiest way. I've attached the two pertinent databases (front and back ends) in a zip file. Any feedback of suggestions greatly appreciated.

[aytee111]

You could put a button on the form that says "Swap" or something and there you can do all the updating.

Create three queries (or DoCmd.RunSQL) to update the tables as you require - adding the two new records and updating the tool task info.

[gleblanc]

OK, headed down the path to write the SQL statements and have them executed when I click a command button. Having a little trouble with my variables. I've written the following:

Private Sub SwapButton_Click()
    Dim OldToolSQL As String
    
    OldToolSQL = "INSERT INTO tbl_ChangeHistory " & _
        "[Model Number], [Serial Number] " & _
        "VALUES " & Me.Model_Number & " , " & Me.Serial_Number.ItemData(0)
        
    Debug.Print OldToolSQL
    Stop
End Sub

Which gives me the following in the Immediate window:

INSERT INTO tbl_ChangeHistory [Model Number], [Serial Number] VALUES I-001 , I-001

I don't understand why Me.Model_Number doesn't give the value from the combo box named Model Number. What have I got wrong?

[aytee111]

The syntax of the SQL is incorrect. Create an append query outside of the form and get it working, then copy the SQL into your code making changes as needed. Also, strings have to be within quotes ("....'" & Me!Model_Number & "'....").

[gleblanc]

I've got this working much better now. Here's the code I wound up using, complete with (crappy) documentation.

Private Sub SwapButton_Click()
    Dim OldToolSQL, NewToolSQL, UpdateToolTaskSQL As String
    
    'The following "If..ElseIf..End If" block is used to make sure that all of
    'the required fields are filled out before we try to construct the SQL
    'statements.  Could be made more generalized, but I don't write code for
    'a living.  Only the first instance is completely documented
    If (IsNull(Me.Requestor)) Then 'Check to see if the Requestor field is blank
        MsgBox "Please fill out the Requestor before swapping tools." 'Display a message informing the user that the field is blank
        Me.Requestor.SetFocus 'Set the focus to the field that needs to be filled in.
        Exit Sub 'Exit this function
    ElseIf (IsNull(Me.Processor)) Then
        MsgBox "Please fill out your name before swapping tools."
        Me.Processor.SetFocus
        Exit Sub
    ElseIf (IsNull(Me.Tool_ID)) Then
        MsgBox "Please fill out the Tool ID before swapping tools."
        Me.Tool_ID.SetFocus
        Exit Sub
    ElseIf (IsNull(Me.Reason)) Then
        MsgBox "Please fill out the reason for the swap before swapping tools."
        Me.Reason.SetFocus
        Exit Sub
    ElseIf (IsNull(Me.New_Model_Number)) Then
        MsgBox "Please fill out the new tool model number before swapping tools."
        Me.New_Model_Number.SetFocus
        Exit Sub
    ElseIf (IsNull(Me.New_Serial_Number)) Then
        MsgBox "Please fill out the new tool serial number before swapping tools."
        Me.New_Serial_Number.SetFocus
        Exit Sub
    ElseIf (IsNull(Me.Torque)) Then
        MsgBox "Please fill out the torque before swapping tools."
        Me.Torque.SetFocus
        Exit Sub
    End If
    
    'This creates the SQL to insert the old tool into the database.
    OldToolSQL = "INSERT INTO tbl_ChangeHistory ([Model Number], " & _
        "[Serial Number], [Date], [ChangeType], [Requestor], [Processor]) " & _
        "VALUES (""" & Me.[Model Number].Column(1) & """, """ & _
        Me.Serial_Number.Column(1) & """, #" & Me.Date & "#, " & _
        """" & Me.Reason.ItemData(0) & """, """ & Me.Requestor & """, """ & _
        Me.Processor & """);"
        
    'This creates the SQL to insert the new tool into the database.
    NewToolSQL = "INSERT INTO tbl_ChangeHistory ([Model Number], " & _
        "[Serial Number], [Date], [ChangeType], [Requestor], [Processor], " & _
        "[Final Torque]) " & _
        "VALUES (""" & Me.New_Model_Number & """, """ & _
        Me.New_Serial_Number.Column(1) & """, #" & Me.Date & "#, " & _
        """" & Me.Reason.ItemData(0) & """, """ & Me.Requestor & """, """ & _
        Me.Processor & """, " & Me.Torque & ");"
        
    'This creates the SQL to update the tool to task relationship table.
    UpdateToolTaskSQL = "UPDATE tbl_Tool_Task SET [Model Number] = """ & _
        Me.New_Model_Number & """, [Serial Number] = """ & _
        Me.New_Serial_Number.Column(1) & """, [Date Issued] = #" & _
        Me.Date & "# WHERE [Tool ID] = """ & Me.Tool_ID & """;"

        
'    Debug.Print OldToolSQL
'    Debug.Print NewToolSQL
'    Debug.Print UpdateToolTaskSQL
    DoCmd.SetWarnings False 'turns off warnings
    DoCmd.RunSQL OldToolSQL 'inserts the old tool into the DB
    DoCmd.RunSQL NewToolSQL 'inserts the new tool into the DB
    DoCmd.RunSQL UpdateToolTaskSQL 'updates the tool to task relationship
    DoCmd.SetWarnings True 'turns warnings back on
    
    'The following clears out all the form fields
    Dim ctl As Control
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If ctl.ControlSource = "" Then
                    ctl.Value = Null
                End If
            Case Else
        End Select
    Next ctl
    
    Me.Date = Now() 'Fills in the date field with the current date and time
    Me.Requestor.SetFocus 'Sets the focus to the Requestor field.
End Sub

The trickiest part was writing the SQL using access variables. Several places I had to use Me.[Field].Column(1) to get the right piece of data, since my selection criteria for those combo boxes is a bit complex. I hope somebody else finds this useful. Let me know if I should post the complete database again.