Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: VBA Code to get data from 2 relational tables

    Hello,

    I have 2 tables with one-to-many relationship

    T_RPO_Header (Eno (number) Primarykey, Ename (Text))
    T_RPO_Footer(Eno (number), RPO_Number (Text), some other fields)

    Field Eno is tied with one-to-many relationship.

    I am fetching data from Access a single table to Excel. It works fine with following code line.


    'Other Code lines here to open Excel Application
    --
    --
    --
    Set rst = CurrentDb.OpenRecordset("SELECT ENO, RPO_No, FROM T_RPO_Footer;")
    --
    --
    --
    But I am confused what could be the code line incase I need to pull the data from 2 tables which are tied with one-to-many relationship.

    For example I need pull Ename also from Header table in the above code line. How can I do that?

    One more thing I need to have clarification that I put all the above lengthy VBA code on Click Event of a button. The above code produces ONE report only. I need to have 14 reports so if I do write similar code lines for each reports, would the form be very heavy while loading?

    And even for one single report it takes long time since record set is of 25 columns..

    I then decided to put all the above code in a module / modules and just call it from the same event of button. This will help to bring my form to light weight.

    I was under impression that module works faster than VBA codes written behind the form. But it takes similar time.

    With kind regards,

    Ashfaque

  2. #2
    Join Date
    Sep 2006
    Posts
    3

    Use the query design view to develop your select statement

    Use the query design view to develop your select statement...be sure and add and join the two tables appropiately. When u get it working use the View sql to get to the sql..

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I tried following but it produces syntax error # 3131 in FROM clause.

    Set rst = CurrentDb.OpenRecordset("SELECT ENO, Ename, MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK FROM T_RPO_Footer " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO;")

    I added only Ename field from T_RPO-Header table but no use...

    Please advice.

    Regards,
    Ashfaque

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Set rst = CurrentDb.OpenRecordset("SELECT ENO, Ename, MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK FROM T_RPO_Footer " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO;")

    ENO exists in both tables - specify which one you want
    SELECT T_RPO_Header.ENO

    the first FROM shouldn't be there.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    OK but how about the data field Ename which is in header table only. I need to get the data of this field.

    "Eno" is in both table but Ename is in header table only.

    I tried you code but still same ERROR.

    Any other ideas ?

    Regards,
    Ashfaque

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? the same error?
    that is "syntax error # 3131 in FROM clause"
    after you have removed the first FROM clause?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Thumbs down

    Set rst = CurrentDb.OpenRecordset("SELECT ENO, Ename, ENO,MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO;")

    Only Ename is from Header table and rest from footer.

    Error 3079. Specified field Eno could refer in more than 2 tables.

    Regards,
    Ashfaque
    Last edited by Ashfaque; 09-16-06 at 09:57. Reason: Spellingd

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Set rst = CurrentDb.OpenRecordset("SELECT T_RPO_Header.ENO, Ename, MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO;")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks for the help r937.

    How about the where clause? If I need the footer table data based on ENO or Ename that I selects on a XYZ form?

    The last 2 lines I put in following way but error.

    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO " & _
    "WHERE T_RPO_header.ENO = me!txteno ORDER BY MQE_NO;")

    I tried this way also:
    "WHERE T_RPO_header.ENO = me!txteno ORDER BY MQE_NO;")

    But produces Error 3079. Specified field Eno could refer in more than 2 tables.

    I need data to get from footer table based on the criteria (ENO or Ename) I select thru a combo on form XYZ.

    Please extend your help.

    Regards,
    Ashfaque

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show your latest query that got the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Here is my VBA that produces error:

    Set rst = CurrentDb.OpenRecordset("SELECT T_RPO_Header.ENO, Ename, eno,MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO " & _
    "WHERE T_RPO_header.ENO = me!txteno ORDER BY MQE_NO;")

    Regards,
    Ashfaque

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Set rst = CurrentDb.OpenRecordset("SELECT T_RPO_Header.ENO, Ename, eno,MQE_NO, RPO_No, WORKSHEET_NO, WORKORDER_NO," & _
    "WORK_DESC, PL, PipeLineKM, DiaMeter, PipeLength, Status, PipeLineArea, Start_Date," & _
    "Completion_Date, RPO_AMOUNT,REMARK " & _
    "FROM T_RPO_Header LEFT JOIN T_RPO_Footer ON T_RPO_Header.ENO = T_RPO_Footer.ENO " & _
    "WHERE T_RPO_header.ENO = me!txteno ORDER BY MQE_NO;")

    !!!

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I Removed second Eno. Now the run-time erorr is 3061. Too few parametes. Expected. 1

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    test your query directly in access before trying to use it in vba
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this
    "WHERE T_RPO_header.ENO = me!txteno ORDER BY MQE_NO;")
    is doomed!

    is txteno a number?
    then try
    "WHERE T_RPO_header.ENO = " & me!txteno & " ORDER BY MQE_NO;")

    is txteno a string?
    then try
    "WHERE T_RPO_header.ENO = '" & me!txteno & "' ORDER BY MQE_NO;")

    izy
    currently using SS 2008R2

Posting Permissions

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