Help with an Array Problem

[Connie Kamrowski via AccessMonster.com]

Hi,

I need some help with an array that is driving me batty. I have a database
which stores records about licenses. The user may ask for one or more
licences to be printed, if so I need to check if we have licence and
insurance information for this licence or licences. I cannot allow them to
print the report if we don't have this information To Check this I need to
run this SQL string -

StrSQL = "SELECT tblDAuthorityRenewal.AuthorityRenewalID FROM
tblDAuthorityRenewal WHERE tblDAuthorityRenewal.FeesPaid = -1 AND
tblDAuthorityrenewal.InsuranceSighted = -1"

And store the resultant AuthorityRenewalId's in an Array. I then need to
pass this array to a function, run one or more input authority numbers
against it one at a time to see if they are in this array and if so return
a true to a query to pass them to the report for printing. I also need to
capture the ones that cannot be printed and store them in a seperate array
to present to the user.

I have tried to do this using several examples but have been getting either
a type mismatch or subscript out of range error.. all help appreciated .

Connie

 

[Albert D. Kallal]

I am going to suggest you dump the idea of a arrays here. Arrays are
kind of out dated these days, and you don't need them. Arrays go back to
the good old days in software..but between objects, collections and other
tools...we don't need, nor should use arrays very much.

If you use an array, you will to fill the array with data that comes from
the database (that is waste of time, coding time, and even a waste of
resources). You then will have to write a whole whack of code to
test/search for those numbers that the user entered.
Why not let the database system do all the heavy lifting here? Just send the
list of numbers to the database..and tell it go find them. this is faster,
and MUCH MUCH less code on your part.

You don't mention how the "list" of numbers is to entered by the user.

Also, do you need to tell the user which number failed here?

If you just need a simply yes/no test result for a list of numbers, then
you can go:

Dim strNumberList As String
Dim intInList As Integer
Dim strWhere As String

strNumberList = "3456,12,55557,13"

intInList = UBound(Split(strNumberList, ",")) + 1

strWhere = "(FeesPaid = -1) AND (InsuranceSighted = -1)"

strWhere = strWhere & " and ( AuthorityRenewalID in (" & strNumberList &
") )"

If DCount("*", "tblDAuthorityRenewal", strWhere) < intInList Then

' hey...not all numbers are there!

End If

And, you could certainly use the "dcount" idea during data entry, to test
each number as the user enters it..and again you don't need much code
here...

And, while the above has a hard coded string..that string could certainly be
the result of some user input. So, you could pass the "whole" string, but
why not check if the ID is present during data entry. You could use:

strWhere = "(FeesPaid = -1) AND (InsuranceSighted = -1)"

strWhere = strWhere & " and (AuthorityRenewalID = " strIdEntered & ")"

If DCount("*", "tblDAuthorityRenewal", strWhere) = 0 then
' not a valid input id

So, you can easilty do this during data entry time, and simply the code even
furhter....

 

[Connie Kamrowski via AccessMonster.com]

Thanks Albert,

I will play with this today and see how I go. The list is entered as a
parameter field for a report ie the user enters a start authority number
and an end Authority Number and then I need to check if all of these have
fees paid before sending them to the query to produce the report.

And yes I do need to let the user know which Authorities didn't have the
info for QA purposes.

Thanks again for the pointer,

Connie

 

[Albert D. Kallal]

Thanks Albert,

You are most welcome

I will play with this today and see how I go. The list is entered as a
parameter field for a report ie the user enters a start authority number
and an end Authority Number and then I need to check if all of these have
fees paid before sending them to the query to produce the report.

Ah, hah!!..the plot thickens! Testing for a bunch a numbers entered is a
different problem then looking for numbers in a *range*.

So, really, we don't need a whole bunch of numbers to test here..but in fact
just a start/end number. This changes the problem, and makes it a good deal
easier here!

So, the process we are looking to now is:

Get start number
Gen End number

It is quite a simple matter to test if the FeesPaid,a nd the Insurance
sighed is set for a range. I am assuming that the start/end range is easily
sorted?

Not only does this make the problem much easier..but it also makes printing
of the "bad" ones very easy also....

You could build a nice prompt form where the start/end number is entered,
and then display how many are good, and how many are bad. You could then
have two buttons on the form...one to print the good ones..and one to print
the bad ones....