How do I create unknown number of variables?

[Guest]

I have a table, MtchTbl, with two text fields: DonID and EndwID, which can
contain any number of matches. In VBA, I need to find all the instances of a
particular DonID, say "D100", and place each of the matching EndwID codes
into variables.

How do I get VBA to click through all instances of "D100"? How do I create
variables on the fly, without knowing how many?

I am totally in the dark on this.

Thanks in advance.

 

[Guest]

It would help to know why you need to do this. This is not something that one
usually needs to do. If you do need to do this, it might be better to declare
an array and fill it with EndwId codes.

Barry

 

[Guest]

Hi - this solution would not work on millions of records but it works on
most of
the stuff I have to deal with. Try this out:

Make up a table called Table1 with the fields as shown:
id DonID EdwID Another

Where id is an autonumber and the other fields are text.

1 D100 C398 Steve
2 D100 D100 Mike
3 D101 D893 Carol
4 D100 D100 Phil
5 D100 A293 Charles
6 D100 D100 Mary

Then try this code:
You should see all the names where the DonID and EdwId match
Code follows:

Public Sub UnknVbl()
'Test Program to collect unknown number
'of matches into an array where they can be manipulated

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strMatch As String
Dim astrMatch() As String
Dim vElmt As Variant

Set cn = New ADODB.Connection
Set cn = CurrentProject.AccessConnection

Set rs = New ADODB.Recordset
With rs
.SOURCE = "SELECT * from Table1 WHERE (DonID = EdwID);"
.ActiveConnection = cn
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open Options:=adCmdText

'the trick - make a big long string with a delimiter ie ";"
'between each entry

strMatch = ""
Do While Not .EOF
strMatch = strMatch & !Another & ";"
.MoveNext
Loop

'then lop off the trailing delimiter
strMatch = mID$(strMatch, 1, Len(strMatch) - 1)

'then "split" the string into an array
astrMatch = Split(strMatch, ";")

'now you have an array that has an entry for each match
'here are the values

For Each vElmt In astrMatch
Debug.Print vElmt
Next vElmt
.Close

End With

Set rs = Nothing
Set cn = Nothing

End Sub

Good Luck
Ken Higgins