VBA Tutorial & Question

[Alan Little]

Two items:

1) Where can I find a good online Access VBA tutorial (I've done a fair
amount of VBA programming in Excel)

2) I need to query a single column in my database and output the values,
200 per row. Is there an easy way to do this?

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

[Alan Little]

Carved in mystic runes upon the very living rock, the last words of Alan
Little <al**@n-o-s-p-a-m-phorm.comof comp.databases.ms-access make plain:

2) I need to query a single column in my database and output the values,
200 per row. Is there an easy way to do this?

A little more detail: these are ID numbers; the client wants to paste them,
200 at a time, separated by commas, into a field on a web site search form.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

[Kc-Mass]

Try something like this:

Sub Concat()
Dim MyDB As database
Dim MyRS As Recordset
Dim MyNewRS As Recordset
Dim strManyClientIds As String
Dim intRecordsProcessed As Integer
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblClientDB", dbOpenSnapshot)
Set MyNewRS = MyDB.OpenRecordset("tblOutPut", dbOpenDynaset)
Do While Not MyRS.EOF
strManyClientIds = ""
intRecordsProcessed = 0
'** This concatinates the IDs from the next 200 records into a string
that is comma delimted
Do Until intRecordsProcessed = 200 Or MyRS.EOF
strManyClientIds = strManyClientIds & ", " & MyRS!fldClientID
intRecordsProcessed = intRecordsProcessed + 1
MyRS.MoveNext
Loop
'** This adds a new record to the Output table and adds the comma
delimited string to the appropriate field
MyNewRS.AddNew
MyNewRS!FldManyClientIDs = strManyClientIds
MyNewRS.Update
Loop

Set MyDB = Nothing
Set MyRS = Nothing
Set MyNewRS = Nothing

End Sub

The string being built will require a memo field in Access for storage
"Alan Little" <al**@n-o-s-p-a-m-phorm.comwrote in message
news:Xn**************************@216.196.97.131.. .

Two items:

1) Where can I find a good online Access VBA tutorial (I've done a fair
amount of VBA programming in Excel)

2) I need to query a single column in my database and output the values,
200 per row. Is there an easy way to do this?

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

[Alan Little]

Carved in mystic runes upon the very living rock, the last words of
Kc-Mass of comp.databases.ms-access make plain:

"Alan Little" <al**@n-o-s-p-a-m-phorm.comwrote in message
news:Xn**************************@216.196.97.131.. .

>>
2) I need to query a single column in my database and output the
values, 200 per row. Is there an easy way to do this?

Try something like this:

Sub Concat()

[snip]

End Sub

The string being built will require a memo field in Access for storage

Thanks. One silly question: how do I invoke it? I tried just running it
from the editor, but it dies on this line:

Set MyDB = CurrentDb()

saying, "ActiveX component can't create object".

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

[Kc-Mass]

Check your References under Menu item -Tools:References from within the
editor.
I tried it here and it runs with the following references:

Visual basic for Applications
Microsoft Access 9.0
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Extensibility 5.3

"Alan Little" <al**@n-o-s-p-a-m-phorm.comwrote in message
news:Xn**************************@216.196.97.131.. .

Two items:

1) Where can I find a good online Access VBA tutorial (I've done a fair
amount of VBA programming in Excel)

2) I need to query a single column in my database and output the values,
200 per row. Is there an easy way to do this?

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/