Re: Output data from multiple records in a table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.)





On Wed, 6 Jul 2005 23:56:02 -0700, "Dave"
<Dave@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: Output data from multiple records in a table
    ... > Dim rsR As DAO.Recordset ... > Dim strSQL As String ... > such as display it in a textbox on your form. ...
    (microsoft.public.access.modulesdaovba)
  • SendObject Action Message
    ... Dim strMsg As String, strTitle As String ... 'There is no need to check if the form is blank (as with report and preview) ... 'will display the name of the report. ...
    (microsoft.public.access.formscoding)
  • Re: Allen Browne Audit Log
    ... >> also need to ensure that the PK added to the audit table begins with aud ... >> Function AudText(sAudTable As String, sKeyField As String, AudID As ... >> Dim rs As Recordset ... If you wanted to display just the fields that have been ...
    (microsoft.public.access.formscoding)
  • RE: Email
    ... If i use Display instead of send i get the New email' window like expected. ... I did notice that you're not explicitly destroying the MyOutlook object as ... Public Sub SendEMail(strMailTo As String, strFile As String, _ ... Dim MailList As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)