very basic, basic ;)

[kicken18]

in VBA, i want to write something along the lines of

if Customer Name = Orders > 5
PromptBox "sorry only 5 orders max"

do i just use the varible names like in SQL like TBL_Customer.CustomerID
or is it differnt for VBA

Thanks
Chris

 

[meboz]

Are you wanting to display a msgbox if a customers total orders are >5?

 

[kicken18]

yes, but thats not the question here, my question is i am trying to write something which looks at a customer ID and if they have > 5 order ID's (they have over 5 orders) then a message box comes up. Now in SQL its TBL_Customers.CustomerID do i write the same in VBA?

 

[modest]

if you already have a query, you can just call they query by it's name, otherwise:

Option (1)

Dim strSQL As String
Dim rs as Recordset

strSQL = "SELECT [ORDERS] FROM [TABLENAME] WHERE [CUSTOMER NAME] = 'John';"

Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast

If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    If (rs.RecordCount > 5) Then
        Msgbox "Sorry 'customer name' you may have a maximum of 5 orders at a given time"
    End If
End If

Option (2):

Dim intCount As Integer

intCount = DCount("[ORDERS]","Table Name","[CUSTOMER NAME] = 'John' ")

If intCount > 5 Then
    Msgbox "Sorry 'customer name' you may have a maximum of 5 orders at a given time"
End If

Note: In both cases you can replace [ORDERS] with * because I'm assuming a records is entered for each order a customer places.

Hope this helps

 

[Rich]

Are you entering the orders via a form, if so the RecordsetClone.RecordCount method will work