If then help

[rgreene]

How would I go about doing this:
If ContainerType = Box then Container must equal a number between 1 and 200. If not in the range then message box.
My thoughts are to put it in the Before Update section sound right?

Along that lines how about this one:
If a container number is already used then a message box about choose another container number.

I have a product that gets moved from container to container. On my Event I have a Container field where I enter in the container number. If I enter in a container number that is already being used then either we didn't "empty" the container or we wrote down the wrong container number.

Thanks,
Rick

 

[CraigDolphin]

Assuming you have two text boxes on your form called txtContainerType and txtContainer respectively and that you want to prevent the user entering the container number without first entering the container type....

Private Sub txtContainer_BeforeUpdate()
Select Case Nz(Me.txtContainerType.Value,"Missing")
Case "Box"
   if Me.txtContainer.Value <= 200 then
      if Nz(Dlookup("BoxNumberFieldNameHere","BoxNumberTableOrQueryNameHere","[BoxNumberFieldNameHere]=" & Me.txtContainer.Value),"Missing")<>"Missing" then
      MsgBox "Sorry,Already allocated",vbinformation
      Cancel = true
      End if
   Else
      Msgbox "That number is not in the allowed range!", vbinformation
      Cancel = True
   End if
Case "Missing"
   Msgbox "You must specify the container type first!", vbinformation
   Cancel = True
   Me.txtContainerType.Setfocus
End Select
End Sub

You will also need to substitute the relevant field/table/query names in the Dlookup portion. There may be better methods available that one of the gurus might suggest. The table or query that you reference in the Dlookup should list all of the containers that are already 'filled'.

 

[rgreene]

CraigDolphin - Thanks Guy it's awesome. Exactly what I needed