Hi All,
I have some SQL code running an update query based on a combo box selection that is working in a couple cases, but not in other cases. Below is the complete SQL Code:
The code works fine for (Me!cboReservationStatus = 2) and (Me!cboReservationStatus = 1 or 3), but for (Me!cboReservationStatus = 4), the result is tblBOM_Master.[BOMStatus] = 1, not 3.
I am definetly not an expert at SQL or VBA and would appreciate any and all suggestions.
I have some SQL code running an update query based on a combo box selection that is working in a couple cases, but not in other cases. Below is the complete SQL Code:
Code:
Private Sub cboReservationStatus_AfterUpdate()
Dim strSQL As String
If Me.Dirty Then Me.Dirty = False
If (Me!cboReservationStatus = 2) Then
strSQL = "UPDATE tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.[BOMID] = tblReservation_details.[BOMNumber])" & _
" ON tblReservations.[ReservationID] = tblReservation_details.ReservationID SET tblBOM_Master.[BOMStatus] = 2 " & _
" WHERE (((tblReservation_details.[ReservationID])=[forms]![frmReservations].[txtReservationID])) "
DoCmd.RunSQL (strSQL)
Debug.Print strSQL
ElseIf (Me!cboReservationStatus = 1 Or 3) Then
strSQL = "UPDATE tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.[BOMID] = tblReservation_details.[BOMNumber])" & _
" ON tblReservations.[ReservationID] = tblReservation_details.ReservationID SET tblBOM_Master.[BOMStatus] = 1 " & _
" WHERE (((tblReservation_details.[ReservationID])=[forms]![frmReservations].[txtReservationID])) "
DoCmd.RunSQL (strSQL)
Debug.Print strSQL
ElseIf (Me!cboReservationStatus = 4) Then
strSQL = "UPDATE tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.[BOMID] = tblReservation_details.[BOMNumber])" & _
" ON tblReservations.[ReservationID] = tblReservation_details.ReservationID SET tblBOM_Master.[BOMStatus] =3 " & _
" WHERE (((tblReservation_details.[ReservationID])=[forms]![frmReservations].[txtReservationID])) "
DoCmd.RunSQL (strSQL)
Debug.Print strSQL
ElseIf (Me!cboReservationStatus = 5) Then
strSQL = "UPDATE tblReservations INNER JOIN (tblBOM_Master INNER JOIN tblReservation_details ON tblBOM_Master.[BOMID] = tblReservation_details.[BOMNumber])" & _
" ON tblReservations.[ReservationID] = tblReservation_details.ReservationID SET tblBOM_Master.[BOMStatus] = 5 " & _
" WHERE (((tblReservation_details.[ReservationID])=[forms]![frmReservations].[txtReservationID])) "
DoCmd.RunSQL (strSQL)
Debug.Print strSQL
Else
End
End If
End Sub
The code works fine for (Me!cboReservationStatus = 2) and (Me!cboReservationStatus = 1 or 3), but for (Me!cboReservationStatus = 4), the result is tblBOM_Master.[BOMStatus] = 1, not 3.
I am definetly not an expert at SQL or VBA and would appreciate any and all suggestions.