Output data from multiple records in a table

[Guest]

I have an application which displays data from a table on a form. I have
written code such that when you double click on a field on the form, it opens
another database, finds the record whose name matches the data in the field
(name being the PrimaryKey in the second table), then dumps the contents of
the other record into wordpad for display on the screen. (The code uses
MyTable.seek to match the unique record and then the TableDefs property to
get the data from all fields for that record). So far, so good...

What I need to do now is to be able to click on another field, find the
matching record in another remote table and do the same thing for a table
which contains duplicates, i.e., I want to dump the contents of all matching
records into Wordpad. (There are two indexes on the second table, one on name
and another one which is indexed on name and another field)

Can anyone see what I'm trying to do and help please ? Do I need to use an
SQL query ? If so, I'm struggling to get the syntax right

any help much appreciated
Dave

 

[John Nurick]

Hi Dave,

I think you're making more of a meal of this than strictly necessary.

Something like this air code will get you a recordset containing the
records you want and then stuff the contents of the fields into a
string. it works regardless of the number of records found

I'm assuming the primary key field is called RecName and that it's
displayed on the form in a textbox called txtRecName:

Dim rsR As DAO.Recordset
Dim strSQL As String
Dim j As Long
Dim strStuff As String

strSQL = "SELECT * FROM OtherTable " _
& " IN 'C:\Folder\Folder\OtherDB.mdb' WHERE RecName='" _
& Me.txtRecName.Value & "';"

Set rsR = CurrentDB.OpenRecordset(strSQL)

Do While Not rsR.EOF
With rsR
For j = 0 to .Fields.Count - 1
strStuff = Nz(.Fields(j).Value, "") & vbCrLf
Next
.MoveNext
End With
Loop

rsR.Close

Having got all the data into a string you can do what you want with it,
such as display it in a textbox on your form. (For most purposes, it
seems to me, it would be more useful just to open another form with the
data you've found.)

 

[Guest]

John,

you got it in 1 !

As you say, it turns out I was making a bigger deal of this than I needed
to. I think that I had got myself headed down the wrong track and lost sight
of the wood for the trees.

My solution is probably not elegant or efficient, but I realised that I
could just seek to the first match and then use Do While, MoveNext to step
through the table. I'll give your solution a try though to improve my (basic)
skills.

I would have used another form to display the data but the problem is that
the linked data can be very large (20K is not untypical). I only need to be
able to view the data, so just using Wordpad seemed a rough 'n' ready way of
doing it.

Thanks for the advice,
regards
Dave