Tables not updating correctly

[worthmi]

I am trying to write a series of queries that are activated by a form button; however, when the first query results two entries (the form is in not in data entry) in the "Downtimes" table, and unless all of the fields are filled in on the form most of the data will not be entered into the either the "Downtimes" or "Active_Steps" tables.

To remedy the problem w/duplicate entries into the "downtimes" table, I tried creating a clean up query, but that does not alway work.

My apologies for rambling; I am an novice SQL/VBA/Access user.

Option Compare Database

'====================================================================================================
'
' Begin Variable declaration
'
'====================================================================================================

Dim strSystemsDown As String
Dim strTextOfSystemsDown As String

'====================================================================================================
'
' End Variable declaration
'
'====================================================================================================

Option Explicit


Private Sub btnAddSystem_Click()

Me.cmbSystemsDown.SetFocus


If Not Me.cmbSystemsDown.Text = "" Then
    If Me.lstNewSystemsDown.RowSource = "" Then
        Me.lstNewSystemsDown.RowSource = Me.cmbSystemsDown.Column(0) & "," & Me.cmbSystemsDown.Column(1)
        strSystemsDown = strSystemsDown & Me.cmbSystemsDown.Column(0) & ","
        strTextOfSystemsDown = strTextOfSystemsDown & Me.cmbSystemsDown.Column(1) & ","
    Else
        Me.lstNewSystemsDown.RowSource = Me.lstNewSystemsDown.RowSource & "," & Me.cmbSystemsDown.Column(0) & "," & Me.cmbSystemsDown.Column(1)
        strSystemsDown = strSystemsDown & Me.cmbSystemsDown.Column(0) & ","
        strTextOfSystemsDown = strTextOfSystemsDown & Me.cmbSystemsDown.Column(1) & ","
    End If
Else
    MsgBox ("Please select an actual system.")
    DoCmd.CancelEvent
End If


Me.cmbSystemsDown.SelText = ""

End Sub

Private Sub btnClearEntries_Click()

Me.lstNewSystemsDown.RowSource = ""

End Sub
Private Sub Form_Load()

Me.cmbSystemsDown.SetFocus
Me.cmbSystemsDown.SelText = ""
strSystemsDown = ""
strTextOfSystemsDown = ""

End Sub

Private Sub Form_Open(Cancel As Integer)


DoCmd.GoToRecord , , acNewRec
Me.cmbSystemsDown.SetFocus
Me.cmbSystemsDown.SelText = ""
strSystemsDown = ""
strTextOfSystemsDown = ""


End Sub

Private Sub btnCreateDowntime_Click()

'====================================================================================================
'
' Begin Variable declaration
'
'====================================================================================================

Dim intCommCoordinator As Integer
Dim intTechnicalLead As Integer
Dim strDowntimeName As String
Dim strInitNotes As String
Dim strSQLcleanupActiveSteps As String
Dim strSQLcleanUpDowntimes As String
Dim strSQLcreateDowntime As String
Dim strSQLdowntimeSteps As String

'====================================================================================================
'
' End Variable declaration
'
'====================================================================================================


If strSystemsDown = "" Then
    MsgBox ("You need to select a system to create a downtime.")
    DoCmd.CancelEvent
Else
    strSystemsDown = Left(strSystemsDown, Len(strSystemsDown) - 1)
End If

If strTextOfSystemsDown = "" Then
    DoCmd.CancelEvent
Else
    strTextOfSystemsDown = Left(strTextOfSystemsDown, Len(strTextOfSystemsDown) - 1)
End If

Me.cmbCommCoordinator.SetFocus
intCommCoordinator = Nz(Me.cmbCommCoordinator.Value, 10)

Me.cmbTechLead.SetFocus
intTechnicalLead = Nz(Me.cmbTechLead.Value, 10)

Me.txtInitialNotes.SetFocus
strInitNotes = Nz(Me.txtInitialNotes.Text, "Nothing was entered here.")
strInitNotes = Replace(strInitNotes, "'", "`")

Me.txtDowntimeName.SetFocus
strDowntimeName = Me.txtDowntimeName.Text
strDowntimeName = strDowntimeName & Format(Now(), "yyyy_mm_dd_hh_mm")

'====================================================================================================
'
' Begin SQL statements
'
'====================================================================================================

' There will be two queries. The first will add an entry to the downtimes table, and the second
' will update the active_steps table.



strSQLcreateDowntime = "INSERT INTO downtimes ([Downtime_Name], [Comm_Coordinator], [Tech_Lead], [Initial_Notes], [Systems_Down], [Text_Systems_Down])"
strSQLcreateDowntime = strSQLcreateDowntime & " VALUES (" & "'" & strDowntimeName & "'" & "," & intCommCoordinator & "," & intTechnicalLead & "," & "'" & strInitNotes & "'," & "'" & strSystemsDown & "'" & "," & "'" & strTextOfSystemsDown & "'" & ");"

DoCmd.SetWarnings (False)

DoCmd.RunSQL strSQLcreateDowntime, dbFailOnError


strSQLdowntimeSteps = "INSERT INTO active_steps ([Downtime_ID], [Sys_Step_Row_ID], [System_ID])"
strSQLdowntimeSteps = strSQLdowntimeSteps & " SELECT Downtimes.AutoID, [System_Steps].AutoID, [System_Steps].[System_ID]"
strSQLdowntimeSteps = strSQLdowntimeSteps & " FROM downtimes, [system_steps]"
strSQLdowntimeSteps = strSQLdowntimeSteps & " WHERE ([Downtime_Name] = " & "'" & strDowntimeName & "') "
strSQLdowntimeSteps = strSQLdowntimeSteps & " OR ([System_ID] IN " & "(" & strSystemsDown & "))"

DoCmd.RunSQL strSQLdowntimeSteps, dbFailOnError

strSQLcleanUpDowntimes = "DELETE * FROM downtimes WHERE [Systems_Down] IS NULL;"
strSQLcleanupActiveSteps = "DELETE * FROM [active_steps] WHERE [Sys_Step_Row_ID] = 0"

DoCmd.RunSQL strSQLcleanUpDowntimes, dbFailOnError
DoCmd.RunSQL strSQLcleanupActiveSteps, dbFailOnError

DoCmd.Close

End Sub

 

[JamesMcS]

I think there's a problem in strSQLdowntimesteps, you're not SELECTing downtime_name in the query. Is that causing a problem?

 

[worthmi]

Thanks for the help. I was able to resolve my issues with the form with your help.

 

[JamesMcS]

Good stuff. Hope the BH weekend was fun!