Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Dunfermline, Fife, Scotland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordsets (Access 97 SR2)

    A quick request for advice.
    Running a piece of code which calls a recordset on the current database. Only trouble is that the recordset only pulls up one record. I am probably missing something painfully obvious.

    Can anyone give me any pointers as to where to start looking for a solution?

    Sorry to be a pain.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsets (Access 97 SR2)

    You can get plenty of help in Woody's Lounge (welcome!), but you will have to provide enough information to make that possible. It is impossible to pinpoint the problem from your description.

    Please tell us what piece of code you are running, and what table(s) is/are involved.

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Dunfermline, Fife, Scotland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordsets (Access 97 SR2)

    My Appologies Hans. Thought I had missed something very obivous.

    There are three tables. The fields used are as follows;
    Publications: [Publication Ref] <autonumber>
    Author_Publication_Link: [Publication Ref], [Name Reference]
    Name: [Name Reference], [Surname]

    For a given record in the publications table there can be a number of names.

    I have created SQL code used the querybuilder in Access as follows

    strSQL = "SELECT Publications.[Publication Ref], Name.[Name Reference], Name.Surname " _
    & "FROM Publications INNER JOIN (Name INNER JOIN Author_Publication_Link ON Name.[Name Reference] " _
    & "= Author_Publication_Link.[Name Reference]) ON Publications.[Publication Ref] = " _
    & "Author_Publication_Link.[Publication Reference] " _
    & "WHERE Publications.[Publication Ref] = " & lnpubref
    lnpubref = Long variable for the publication reference I wish to find the names for.

    I have used the following code to extract recordset;

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    MsgBox "Record Count = " & rst.RecordCount & Chr$(13) & "Publication Ref " & lnpubref

    What I find is that the RecordCount =1 regardless of the number of names associated with the [Publication Ref]. I have run the SQL as a query and it works correctly.

    I find myself at a loss as to why this should be

    Any suggestions would be most welcome.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsets (Access 97 SR2)

    Immediately after OpenRecordset, the RecordCount property does not return an accurate count of records; you can only use it to determine if the recordset is empty or not:
    <UL><LI>If the recordset is empty, RecordCount will be 0.
    <LI>If the recordset is not empty, RecordCount will be greater than 0, usually 1.[/list]To get an accurate record count, use the MoveLast method before retrieving RecordCount; this forces Access to read all records in the recordset. For very large recordsets, this may have a noticable impact on performance. If you want to loop through the records, don't forget to move back to the first record before doing so.

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    MsgBox "Record Count = " & rst.RecordCount & vbCrLf & "Publication Ref " & lnpubref

    ' optional:
    rst.MoveFirst
    Do While Not rst.EOF
    ...
    rst.MoveNext
    Loop

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Location
    Dunfermline, Fife, Scotland
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordsets (Access 97 SR2)

    Thanks Hans. That did the trick. I knew it was somthing silly. I had come across this in combo boxes but had not realised that the same principle applied when opening a recordset - silly really.

    Thank you very much for your help.

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •