Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Unanswered: Query only returning one record

    I am using DAO to connect form word (using VBA) to an Access database. I have defined the following query in Access:

    SELECT Modules.ModuleID, Modules.Naam, Modules.Type, Modules.PrijsEenmalig, Modules.PrijsMaandelijks
    FROM Produkten INNER JOIN (Modules INNER JOIN ModulesPerProdukt ON (Modules.ModuleID = ModulesPerProdukt.ModuleID) AND (Modules.ModuleID = ModulesPerProdukt.ModuleID)) ON Produkten.ProduktID = ModulesPerProdukt.ProduktID
    WHERE Produkten.ProduktNaam=[PName];

    When I run the query in Access (with the right paramter) it returns 4 records. When I run the query from DAO (using the same paramter) it only returns one record! What's going on? I use the following method in my word document:

    Function haalModuleBijProdukt(produktNaam As String) As DAO.Recordset

    Dim definitie As DAO.QueryDef
    Set definitie = SQLLib.database.QueryDefs("getModulesByProduct")
    definitie("[PName]") = produktNaam
    Set haalModuleBijProdukt = definitie.OpenRecordset()

    End Function

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure it's only returning 1 record? Remember that when you link to an MSAccess database, Word has a separate toolbar for cycling through records (you don't scroll down but instead click the green (or blue) left and right record arrows - i.e. show the Word MailMerge or Database toolbar.)
    Last edited by pkstormy; 06-01-07 at 13:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2007
    Posts
    4
    Maybe my explanation wasn't quite good enough. I'm using code (VBA using DAO) to connect to the database and run the query. So the Word UI doesn't even come into play.

    When I debug my code and look at the recordcount property of the dataset it reads '1' after the query has been executed.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Check out the help file on DAO.Recordcount, it will give you the number of accessed records - so if you want the count, do a .movelast first, which "populates" the recordset and should give correct count.
    Roy-Vidar

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    .MoveLast to populate the recordset. Sometimes you don't get the actual record count without doing this (depends on the settings you use).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    Posts
    4
    your peeps are the shizzle, the movelast command solved my problem...thanx

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Being picky about your query
    Code:
    SELECT	 Modules.ModuleID
    	,Modules.Naam
    	,Modules.Type
    	,Modules.PrijsEenmalig
    	,Modules.PrijsMaandelijks
    FROM	Produkten
    INNER JOIN Modules
    INNER JOIN ModulesPerProdukt
    	ON  Modules.ModuleID = ModulesPerProdukt.ModuleID
    	AND Modules.ModuleID = ModulesPerProdukt.ModuleID
    	ON Produkten.ProduktID = ModulesPerProdukt.ProduktID
    WHERE	Produkten.ProduktNaam = [PName]
    Can't we remove the AND criteria on your join?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RoyVidar
    Check out the help file on DAO.Recordcount, it will give you the number of accessed records - so if you want the count, do a .movelast first, which "populates" the recordset and should give correct count.
    Sniped! I missed that!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2007
    Posts
    4
    hehe, I have to confess that I created that query the lazymans way...using the design view in Access . I didn't take a look at the syntax i'm afraid but you are absolutely right.

Posting Permissions

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