How to define an Array in VBA behind the Access Forms

Hi. I would like to create an array and use the data to populate some
new records when the user clicks on a button (that's the short
description).

If I were to code the array in C (I know ... not this forum, but it's
what I know) I would do it like this ...

/* Declare the structure template */
struct recdata {
long classID;
char lvl;
};

I would then define the array of structs as follows:

struct recdata classinfo[8] =
{{ 0x01, "A" },
{ 0x01, "B" },
{ 0x01, "C" },
{ 0x02, "A" },
{ 0x02, "B" },
{ 0x02, "C" },
{ 0x03, "A" },
{ 0x03, "B" }};

I'd probably define a recdata pointer and bump the pointer through the
array.

Is this possible in VBA?

I have two books on Access and VBA and neither covers arrays too well.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

On 18 Jan 2008 01:56:39 GMT, Susan Bricker
wrote:

the struct becomes a type.
Then you declare an array of that type.
Filling the array is not as elegant as in C: you have to fill each
element separately. Off the top of my head:
classinfo(0,classID) = &h01
classinfo(0,lvl) = "A"
etc.
To iterate over the array use an iterator:
for i = lbound(classinfo) to ubound(classinfo)
'do something
next i

Long Live Kernighan & Richie

-Tom.


>Hi. I would like to create an array and use the data to populate some
>new records when the user clicks on a button (that's the short
>description).
>
>If I were to code the array in C (I know ... not this forum, but it's
>what I know) I would do it like this ...
>
>/* Declare the structure template */
>struct recdata {
> long classID;
> char lvl;
>};
>
>I would then define the array of structs as follows:
>
>struct recdata classinfo[8] =
> {{ 0x01, "A" },
> { 0x01, "B" },
> { 0x01, "C" },
> { 0x02, "A" },
> { 0x02, "B" },
> { 0x02, "C" },
> { 0x03, "A" },
> { 0x03, "B" }};
>
>I'd probably define a recdata pointer and bump the pointer through the
>array.
>
>Is this possible in VBA?
>
>I have two books on Access and VBA and neither covers arrays too well.
>
>Regards,
>SueB
>
>*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

My first comment is that your structure looks like it'd probably be better
off with a table, however we'll stick to arrays.
Yeah, I remember converting from C to VB in the old days, oh fun. Trying
to convert from VBA to C++, now that's funner.

VBA doesn't have 'char', we always use 'string', however the byte (as you'd
know) is the same size as a 'char'.
VBA naming convention is somewhat different, so I'll use my typical VBA way.
I don't know know any simple way to add data to an Array of User Defined
Types

The equivalent to your C code (to the best of my ability) is:
-----------------------------------------------------------
Type REC_DATA
lngClassID As Long
bteLvl As Byte
End Type

Sub testarray()
Dim arecData(1 To 8) As REC_DATA
Dim intCount as Integer

arecData(1).lngClass = 1
arecData(1).bteLvl = Asc("A")

arecData(2).lngClass = 1
arecData(2).bteLvl = Asc("B")

arecData(3).lngClass = 1
arecData(3).bteLvl = Asc("C")

arecData(4).lngClass = 2
arecData(4).bteLvl = Asc("A")

arecData(5).lngClass = 2
arecData(5).bteLvl = Asc("B")

arecData(6).lngClass = 2
arecData(6).bteLvl = Asc("C")

arecData(7).lngClass = 3
arecData(7).bteLvl = Asc("A")

arecData(8).lngClass = 3
arecData(8).bteLvl = Asc("B")

' Ok lets test the result
For intCount = 1 to 8
MsgBox arecData( intCount ).lngClass & ":" & Chr(arecData(
intCount ).bteLvl)
Next intCount
End Sub

Hmmmm, C language looks easy!! Don't be fooled, I'd rather Basic anyday,
once you get the hang of it.

I hope this helps a bit


Dominic
MS-Access Wizard of Oz-tralia


"Susan Bricker" wrote in message
news:47900757$0$514$815e3792@news.qwest.net...
> Hi. I would like to create an array and use the data to populate some
> new records when the user clicks on a button (that's the short
> description).
>
> If I were to code the array in C (I know ... not this forum, but it's
> what I know) I would do it like this ...
>
> /* Declare the structure template */
> struct recdata {
> long classID;
> char lvl;
> };
>
> I would then define the array of structs as follows:
>
> struct recdata classinfo[8] =
> {{ 0x01, "A" },
> { 0x01, "B" },
> { 0x01, "C" },
> { 0x02, "A" },
> { 0x02, "B" },
> { 0x02, "C" },
> { 0x03, "A" },
> { 0x03, "B" }};
>
> I'd probably define a recdata pointer and bump the pointer through the
> array.
>
> Is this possible in VBA?
>
> I have two books on Access and VBA and neither covers arrays too well.
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

Susan Bricker wrote in news:47900757$0$514$815e3792
@news.qwest.net:

> Hi. I would like to create an array and use the data to populate some
> new records when the user clicks on a button (that's the short
> description).

What's the long description?

Except for speed, I cannot think of any advantage an array would have over
a recordset.
And except for detailed calculations, SQL INSERTS are almost always better
than populating a recordset.

If you tell us what you are trying to do from a practical point of view we
may be able to suggest a good strategy in VBA/Accees/JET, or whatever.
Perhaps it will be to use arrays as you plan, but I doubt that. VBA arrays
are the least powerful array construct I have ever seen, by a factor of ten
or so.

When I moved from Clipper to Access I had to learn to do things in
different ways using different tools, not to do things in the same way
using different tools.

Re: How to define an Array in VBA behind the Access Forms

Long? As opposed to Short, of course.

lol

Ahh Clipper, I remember. Ahhh, C programming, I remember. Ahhh VBA,
Love it!!!!

"lyle fairfield" wrote in message
news:Xns9A293A7866E0E6666646261@216.221.81.119...
> Susan Bricker wrote in news:47900757$0$514$815e3792
> @news.qwest.net:
>
>> Hi. I would like to create an array and use the data to populate some
>> new records when the user clicks on a button (that's the short
>> description).
>
> What's the long description?
>
> Except for speed, I cannot think of any advantage an array would have over
> a recordset.
> And except for detailed calculations, SQL INSERTS are almost always better
> than populating a recordset.
>
> If you tell us what you are trying to do from a practical point of view we
> may be able to suggest a good strategy in VBA/Accees/JET, or whatever.
> Perhaps it will be to use arrays as you plan, but I doubt that. VBA arrays
> are the least powerful array construct I have ever seen, by a factor of
> ten
> or so.
>
> When I moved from Clipper to Access I had to learn to do things in
> different ways using different tools, not to do things in the same way
> using different tools.

Re: How to define an Array in VBA behind the Access Forms

Tom,

Thank you for your suggestion. I do appreciate it.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

Dominic,

Thank you for your suggestion. Defining the TYPE ... that's it!!! I
sort of had an idea about the DIM statement 'cause I've used that, but
the structure thing I couldn't find. THANK YOU.

As for why I want to populate the records within the program ...

The db is for a dog-competition management system. Dogs compete
(Obedience competition) in 1 of 3 classes (Novice, Open, Utility) that
are held at a "trial". There may be more than trial at an event.
Novice and Open have 3 possible classes (A,B,C) and Utility has 1 of 2
possible classes. I have a table for Events called tblEvents (contains
start and end date, host club, host city and state). I have a table for
Trial information called tblTrials (trial secretary actually a FC back
to People table, trial number) with a FK back to the Event record. I
have a Class information table called tblTrialClass with the judge's
name (actually a FK IDnbr back to the People table), Class type
(novice,open,utility) actually FK back to a table of Classes, Class
Level. and FK back to the Trial record.

So ... tblEvents, tblTrials, tblTrialClass, tblPeople, tblScores,
tblDogs, tblDogTitles (and other tables)

Now the reason for populating those TrialClass Records from within the
module ...

At the time of the db design, it was my impression that there may or may
NOT be all those 8 classes at a trial.
At data entry time the db admin has to manually enter all the "stuff".
It turns out that most trials are going to have all the classes and the
db admin asked if I could prepopulate the class record with the
ClassType and Class Level and just leave the Judge field blank. It will
save her some time. So the plan is when the Trial Record is saved
(btn_Saved_Click( ) ) I will create all 8 TrialClass records with the
judgeID field being ZERO.

As far as allowing scored to be entered (another table) for a particular
Class ... I would "gate" that function by the existance of a Judge's ID
or not. If no judgeID then no scores may be entered into the db.

That's the long story.

THANKS, AGAIN.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

Lyle,

Thanks for responding. Please take a look at my reply to Dominic in the
tread. I explained my reason for wanting to populate the records from
within the form's module. Since it was a long (not short) explanation
it's best that I just write it once. So, please take a look and let me
know what your suggestion is. I'd love to hear your response. Thanks,
much.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

So no one caught my error in the C Language explanation of what I am
looking to do? Hmmmm

struct recdata classinfo[8] =
{{ 0x01, "A" },
{ 0x01, "B" },
{ 0x01, "C" },
{ 0x02, "A" },
{ 0x02, "B" },
{ 0x02, "C" },
{ 0x03, "A" },
{ 0x03, "B" }};

is INCORRECT and it SHOULD BE ...

struct recdata classinfo[8] =
{{ 0x01, 'A' },
{ 0x01, 'B' },
{ 0x01, 'C' },
{ 0x02, 'A' },
{ 0x02, 'B' },
{ 0x02, 'C' },
{ 0x03, 'A' },
{ 0x03, 'B' }};

notice the single quotes (') instead of the double quotes (") because
the second field in the structure is defined as a single CHAR (1 byte)
and not an array of CHARs (aka STRING).

That'll teach me to type these posts way after midnight here on the
EastCoast of USA.

Thanks, again to all those who responded.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

Most of us make signs to ward off evil when we see C language posted here,
and aren't going to nitpick your coding.

It is quite enough of a burden on happy VBA programmers to have to stay
cognizant enough of C to even extract the general idea from posted code.

And, of course, as long as we are consistent in which we are using, the
double-quote and single-quote are generally interchangeable in what we do,
so that's not a thing we'd be on the lookout to nitpick, even if we were
into nitpicking C.

Larry


"SueB" wrote in message
news:4790bdeb$0$505$815e3792@news.qwest.net...
> So no one caught my error in the C Language explanation of what I am
> looking to do? Hmmmm
>
> struct recdata classinfo[8] =
> {{ 0x01, "A" },
> { 0x01, "B" },
> { 0x01, "C" },
> { 0x02, "A" },
> { 0x02, "B" },
> { 0x02, "C" },
> { 0x03, "A" },
> { 0x03, "B" }};
>
> is INCORRECT and it SHOULD BE ...
>
> struct recdata classinfo[8] =
> {{ 0x01, 'A' },
> { 0x01, 'B' },
> { 0x01, 'C' },
> { 0x02, 'A' },
> { 0x02, 'B' },
> { 0x02, 'C' },
> { 0x03, 'A' },
> { 0x03, 'B' }};
>
> notice the single quotes (') instead of the double quotes (") because
> the second field in the structure is defined as a single CHAR (1 byte)
> and not an array of CHARs (aka STRING).
>
> That'll teach me to type these posts way after midnight here on the
> EastCoast of USA.
>
> Thanks, again to all those who responded.
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: How to define an Array in VBA behind the Access Forms

Ahhhh, notice I mentioned the 'Char' and suggested using a string in VBA.
Yes, I did notice, didn't think it needed any further comment.

Cheers

Dominic

"SueB" wrote in message
news:4790bdeb$0$505$815e3792@news.qwest.net...
> So no one caught my error in the C Language explanation of what I am
> looking to do? Hmmmm
>
> struct recdata classinfo[8] =
> {{ 0x01, "A" },
> { 0x01, "B" },
> { 0x01, "C" },
> { 0x02, "A" },
> { 0x02, "B" },
> { 0x02, "C" },
> { 0x03, "A" },
> { 0x03, "B" }};
>
> is INCORRECT and it SHOULD BE ...
>
> struct recdata classinfo[8] =
> {{ 0x01, 'A' },
> { 0x01, 'B' },
> { 0x01, 'C' },
> { 0x02, 'A' },
> { 0x02, 'B' },
> { 0x02, 'C' },
> { 0x03, 'A' },
> { 0x03, 'B' }};
>
> notice the single quotes (') instead of the double quotes (") because
> the second field in the structure is defined as a single CHAR (1 byte)
> and not an array of CHARs (aka STRING).
>
> That'll teach me to type these posts way after midnight here on the
> EastCoast of USA.
>
> Thanks, again to all those who responded.
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***