how to write an array? (Access 2000)

Topic

New Reply

How to write an array?
I want to perfrom an action only when a certain customer number is chosen in the atabase.Unfortunately the customer numbers do not follow any logical order, rather they are numbered after the postal code of the city.So my customer numbers are:
123,9000,2700,6300,5000 and 7700.

I would like to write a code something like that:
Dim CustomerId as Control
Set customerID = Forms![FOrderInformation]![Customerid]
If CustomerId = 123 Or 900 Or 6300 or 5000 Or 770 Then
Dim message, title, default, myvalue
message = “enter Auftrag Number, please ” ‘set prompt
end if

However somehow Acces cannot recoginze my numbers.Is there any more bulletproof method as Array,
or perhaps i am mistaken with my code?

Reply | Quote

Replies

Why not store the relevent customer numbers in a table, then count the number of records returned when that table is queried with the active customer number as a criterion? if no records are returned, then the active customer is not one of the specified customers where special action must be taken. If the query returns a result, then the active customer is in the set of ‘special customers’ and you can display the massage and take appropriate actions.

This should also make it easier to maintain the special customer numbers, I would think.

If you want to hard-code the numbers into VBA as you are showing, I think the syntax for the “OR” operator is to make disjunctions between two expressions – you would have to use something like:

If(((((custID=123 OR custID=9000) OR custID=2700) OR custID=6300) OR _
custID=5000)  OR custID=770) then...

(I may have the brackets wrong – this is on-the-fly) The intent is to chain the “OR” operators so that “(test1 OR test2) is presented as a single truth value (call it R1) to the next “OR” as “(R1 OR test3)” – call that R2 – and present it to the next “OR” as “(R2 OR test4)” and so on…

It would probably be easier to set this up as a CASE statement:

Select Case CustID
Case 123
   Msg routine
Case 9000
   Msg routine
...
Case Else
  continue
End Select

Reply | Quote

You don’t need all the brackets. = takes precedence over OR. So,

if custid=123 or custid=9000 or custid=27000 then…

is valid. You do need to specify each comparison though (which was not done in the original message).

Reply | Quote

The easiest way is to use a Select…Case statement.

Select Case CustID

Case 123, 900,6300, 5000, 770
… put your message here
Case Else
… just in case
End Select

Reply | Quote