SQL Update query almost working (1 Viewer)

Sketchin · Sep 10, 2012

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:

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.

AccessMSSQL · Sep 11, 2012

Your problem is here:

Code:

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])) "

You can't use OR and then the value. You must explicitly state the fieldname again. Basically you are saying if (reservationstatus = 1) is a true statement OR three is a true statement then... "Three is a true statement - there's no evaluation of it.

modify it to this:

Code:

ElseIf (Me!cboReservationStatus = 1 Or me!cboReservationStatus = 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

You may also want to explore Select Case statements:

Code:

Select case me!cboReservationStatus
   Case 1, 3
     'do some code
   Case 2
      'do some code
   Case 4
      'do some code
   Case 5
      '...
 
End Select

Sketchin · Sep 11, 2012

Thats awesome, thanks for the help. I kind of figured that I was doing something stupid.

I will look into switch case for sure.

Thanks again.

SQL Update query almost working (1 Viewer)

Sketchin

Registered User.

AccessMSSQL

Seasoned Programmer-ette

Sketchin

Registered User.

Similar threads

Users who are viewing this thread