If Else then Update Currentdb.Execute NOT WORKING CORRECTLY

[ecastro]

I am creating a database for a Warehouse Yard Management where a user can detect which dock doors are in use, inactive, or available. But its not working like it should. Basically if a truck driver arrives we assign it a dock door. After we assign a dock door then it checks that dock door is "In use" it is then it displays message then it undo. Then it checks to see if its "inactive" if it is then it displays message then it undo. If "dock doors" is empty then it changes the "dock doors" status to "In use" and the carrier to the carrier that the user inputed in the form. It somehow working but only in the order it arrives, for example if driver arrives and assign dock door #1 then the code works but lets say drive arrives and we try to assign dock door #8, then for some reason the code is not working, it has to be in order in order for it to work for some reason.... if you need further explanation please reply.... the "dockdoor_id" and "carrier_id" are number fields. here's the code:

Private Sub DockDoor_AfterUpdate()

Dim qdf As QueryDef

If Me.[DockDoor].Value = DLookup("DockDoor_ID", "tbl_YardDockDoors", "Status = 'IN USE'") Then
MsgBox "This Dock is unavailable. Please use another dock door", vbOKOnly, "Dock in use"

Me.[DockDoor] = Null

ElseIf Me.[DockDoor].Value = DLookup("DockDoor_ID", "tbl_YardDockDoors", "Status = 'INACTIVE'") Then
MsgBox "This Dock is INACTIVE. Please use another dock door", vbOKOnly, "Dock in use"

Me.[DockDoor] = Null

If Me.[DockDoor].Value = DLookup("DockDoor_ID", "tbl_YardDockDoors", "Status = 'EMPTY'") Then
CurrentDb.Execute "UPDATE tbl_YardDockDoors SET Status = '" & "IN USE" & "' " _
& "WHERE DockDoor_ID = " & Me.DockDoor, dbFailOnError
CurrentDb.Execute "UPDATE tbl_YardDockDoors SET Carrier_ID = '" & Me.Carrier_ID & "' " _
& "WHERE DockDoor_ID = " & Me.DockDoor, dbFailOnError

End If
End If

End Sub


Thank you very much!

 

[Scooterbug]

From what I can see, the Updating is only occuring if the door status is inactive. You might be better served by using a Select Case statement.

also, you Dim a QueryDef but dont use it.

Dim stDoorStatus as string
 
stDoorStatus = DLookup("DockDoor_ID", "tbl_YardDockDoors","DockDoor_ID=" & me.[DockDoor])
 
 
Case select stDoorStatus
     Case "In Use"
        'What to do if In Use
     Case "Inactive"
        'What to do if Inactive
     Case "Empty"
        'What to do if Empty
end select

 

[pbaldy]

Your DLookup will return the ID of the first record it finds that matches the criteria, which may not be the record you're concerned with. I might flip it around like:

If DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID=" & Me.[DockDoor]) = "In Use" Then

 

[Scooterbug]

Oops! forgot to add the criteria...my bad

 

[pbaldy]

stDoorStatus = DLookup("DockDoor_ID", "tbl_YardDockDoors","DockDoor_ID=" & me.[DockDoor])

You also might want to lookup the status.

 

[ecastro]

For some reason, they are not working. I am not well with code. Is it better if add this code in an after event or before event?

 

[ecastro]

This is what I tried....

Dim stDoorStatus As String
stDoorStatus = DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.DockDoor)

If stDoorStatus = "IN USE" Then
'What to do if In Use
MsgBox "In use", vbOKOnly, "In use"
If stDoorStatus = "INACTIVE" Then
'What to do if Inactive
MsgBox "InActive", vbOKOnly, "InActive"
If stDoorStatus = "EMPTY" Then
'What to do if Empty
MsgBox "Empty", vbOKOnly, "Empty"
End If
End If
End If

 

[pbaldy]

Probably the before update event, adding

Cancel = True

to stop the update if they've chosen a bad door.

 

[Scooterbug]

This is what I tried....

Dim stDoorStatus As String
stDoorStatus = DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.DockDoor)

If stDoorStatus = "IN USE" Then
'What to do if In Use
MsgBox "In use", vbOKOnly, "In use"
If stDoorStatus = "INACTIVE" Then
'What to do if Inactive
MsgBox "InActive", vbOKOnly, "InActive"
If stDoorStatus = "EMPTY" Then
'What to do if Empty
MsgBox "Empty", vbOKOnly, "Empty"
End If
End If
End If

If you are going to use If...Then statements, I would move the End Ifs around. It just doesn't look right otherwise.

If stDoorStatus = "IN USE" Then
  'What to do if In Use
   MsgBox "In use", vbOKOnly, "In use"
[COLOR=red] End if[/COLOR]
[COLOR=#ff0000][/COLOR] 

[COLOR=#ff0000][COLOR=black]If stDoorStatus = "INACTIVE" Then[/COLOR]
[COLOR=black]   'What to do if Inactive[/COLOR]
[COLOR=black]   MsgBox "InActive", vbOKOnly, "InActive"[/COLOR]
[COLOR=red]End if[/COLOR]
 
[COLOR=black]If stDoorStatus = "EMPTY" Then[/COLOR]
[COLOR=black]   'What to do if Empty[/COLOR]
[COLOR=black]   MsgBox "Empty", vbOKOnly, "Empty"[/COLOR]
[COLOR=black][COLOR=red]End If[/COLOR][/COLOR]
[COLOR=black][COLOR=#ff0000]

[/COLOR]

And yea, pbaldy, total brain fart. Should of been "Status" instead of "DockDoor_ID". Can't believe I did that
[/COLOR][/COLOR]

 

[ecastro]

If messages are displaying but for some reason if its null it is displaying a runtime error 3075 " syntax error (missing operator) in query expression 'DockDoor_ID='. Here's the code I have:

Private Sub DockDoor_BeforeUpdate(Cancel As Integer)
Cancel = True
Dim stDoorStatus As String
stDoorStatus = DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.DockDoor)
If stDoorStatus = "IN USE" Then
MsgBox "In use", vbOKOnly, "In use"
End If

If stDoorStatus = "INACTIVE" Then
MsgBox "InActive", vbOKOnly, "InActive"

End If

If stDoorStatus = "EMPTY" Then
MsgBox "Empty", vbOKOnly, "Empty"

End If
End Sub

 

[Scooterbug]

Is the DockDoor number and DockDoor_ID the same?

Any chance of you posting the db? Might make things easier.

Also, I think you can tighten things up by using a combo box to display any door that is currently Empty. You can then use it to pick the Door, and with a button change the status to "In Use".

 

[ecastro]

I know DockDoor_ID is a number but how can I tell if DockDoor (form text box) is?

 

[Scooterbug]

What you are telling the code to do is to find the status of a door based on the ID number of the Door. If you are putting in the Door number, then you need to change the DLookup to:

stDoorStatus = DLookup("Status", "tbl_YardDockDoors", "DockDoor= " & Me.DockDoor)

 

[pbaldy]

If status can be Null, then you need to account for it, as a string variable can't take a Null.

stDoorStatus = Nz(DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.DockDoor), "")

 

[pbaldy]

I just realized you meant the control on the form was null, so you might wrap the whole thing in:

If Len(Me.DockDoor & vbNullString) > 0 Then

 

[ecastro]

THANK YOU GUYS!!!!! I DEFINITELY BOOKMARK THIS WEBSITE! IN CASE ANYBODY IS WONDERING HERE'S THE CODE SETUP:

Private Sub DockDoor_AfterUpdate()
If Len(Me.DockDoor & vbNullString) > 0 Then
If Nz(DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.[DockDoor]), "") = "INUSE" Then
MsgBox "This Dock is unavailable. Please use another dock door", vbOKOnly, "Dock in use"
Me.[DockDoor] = Null
End If
End If
If Len(Me.DockDoor & vbNullString) > 0 Then
If Me.[DockDoor].Value = DLookup("DockDoor_ID", "tbl_YardDockDoors", "Status = 'INACTIVE'") Then
MsgBox "This Dock is INACTIVE. Please use another dock door", vbOKOnly, "Dock in use"
Me.[DockDoor] = Null
End If
End If
If Len(Me.DockDoor & vbNullString) > 0 Then
If Nz(DLookup("Status", "tbl_YardDockDoors", "DockDoor_ID = " & Me.[DockDoor]), "") = "EMPTY" Then
MsgBox "This dock is available.", vbOKOnly, "Dock available"
CurrentDb.Execute "UPDATE tbl_YardDockDoors SET Status = '" & "INUSE" & "' " _
& "WHERE DockDoor_ID = " & Me.DockDoor, dbFailOnError
CurrentDb.Execute "UPDATE tbl_YardDockDoors SET Carrier_ID = '" & Me.Carrier_ID & "' " _
& "WHERE DockDoor_ID = " & Me.DockDoor, dbFailOnError
End If
End If
End Sub

 

[pbaldy]

Glad you got it sorted. For efficiency, I would only use one DLookup and put the existing status into a variable, as you had before. In this code, you conceivably run it 3 times. While not a big problem, it's the kind of thing that combined with others can contribute to a noticeable performance problem.

 

[pbaldy]

Oh, and on a similar note, just use one of these around the whole thing:

If Len(Me.DockDoor & vbNullString) > 0 Then

You might also consider using ElseIf or Select/Case, so that once a condition is met, code exits. The way you have it, even if the first condition of "In Use" is met, it's still going to test the others.

Little things I agree, but they add up.