Basic If Function

[iazcac]

I am trying to get this code to work but cannot get it to work properly

Basically, I want BOX1 to be displayed if the Quantity is larger than Eng_Qty_Amount. If not then I want BOX2 to be displayed

Code i have so far is:

Private Sub Code_AfterUpdate()

If Quantity > Eng_Qty_Amount Then

MsgBox "BOX 1"

Else

MsgBox "BOX 2"

End If

End Sub

Any suggestions?

Chris

 

[trucktime]

Box

Try something like this:

Private Sub Code_AfterUpdate()

If Quantity > Eng_Qty_Amount Then

BOX 1.Visible = True
BOX 2.Visible = False
Else
BOX 2.Visible = True
BOX 1.Visible = False
End If
End Sub

 

[iazcac]

Where would i define what Box 1 and 2 are

(Box 1 and 2 arent the actual words i want, just used to keep posting simpler)

 

[trucktime]

Box

I'm not sure what you mean?
Can you post a sample of your mdb or a screen picture?
What did you mean by Box 1 and 2.

 

[iazcac]

OK, have stripped down database, so only the subform is there and attatched

Problem is in the AfterUpdate on the Code field

C

 

[unclejoe]

Have a look at this.

 

[mresann]

Hello, let me deduce your problem. You are trying to compare the field "quantity" in the table "order items", with the field "Eng_Qty_Amount" in the table "stock", using a one-to-one correspondence between the two tables with the field "code", similarly named in both tables. Gotcha so far.

I do have an immediate fix, and if you don't do any more tweaking with the database this bandaid will work, hopefully enough to get you going. But the trouble people have in general is a lack of understanding of how to associate tables with one another. I see you have no queries, which is essential to the efficient use and operation of a database. In addition, the property boxes allow for formatting data and outputs in the text boxes. When throwing VBA into the mix, one can really get baffled over even the simplest operations at times.

There are actually several ways to perform the operation of comparing different fields to a common field. In this case, i'm just going to use the "dlookup" function to do the comparing. Copy and paste the following code into the "Code_AfterUpdate()" subroutine.

''''''''start of code

PROC_DECLARATIONS:
Dim lngQuantity As Long
Dim lngEng_Qty_Amount As Long
Dim strCode As String
Dim strMessage As String
Dim varLookup As Variant

PROC_START:
On Error GoTo PROC_ERROR
'assign field values to variables
lngQuantity = [Quantity]
strCode =

PROC_MAIN:
   
   'look up Eng_Qty_Amount on stock table based on "Code", link to both tables
   varLookup = DLookup("Eng_Qty_Amount", "stock", "Code = '" & strCode & "'")
   
   'check for null value, if it exists warn operator that there is no matching code
   'this is probably due to an error in entry, or an omission of the Code in the stock table
   If IsNull(varLookup) Then
      strMessage = MsgBox("Code does not match")
      GoTo PROC_EXIT
   End If
   
   'assign record to variable
   lngEng_Qty_Amount = CLng(varLookup)

   'now do the comparison
   If lngQuantity > lngEng_Qty_Amount Then
      strMessage = MsgBox("Above Quantity Amount", vbOKOnly)
   Else
      strMessage = MsgBox("Below Quantity Amount", vbOKOnly)
   End If
   
PROC_EXIT:
   Exit Sub

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: Code_AfterUpdate" & vbCrLf & _
           "Module: Form_order items Subform"
   GoTo PROC_EXIT

'''''''end of code

Good luck!!

 

[iazcac]

Thank you very much for that, works brilliantly!

DB does have queries, but i remade it for the sample to simplify things