Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Question Unanswered: Recordsets and database connections

    I am trying to populate a recordset from a query in my database. but i cant get the connection part right.

    here is the code i am tying to use. i want the recordset to populate when i click a button, then i want to pass the object to word and use the data from there.

    thank you all.

    you can email me if you are interested in helping me and would like more details.

    Private Sub cmdGenerateQuote_Click()
    On Error GoTo Err_cmdGenerateQuote_Click

    Dim oApp As Object
    'starts here

    Dim db As DAO.Database ' declaration for database
    Dim rst As Object ' declaration for the records to be pulled
    Dim strSQL As String ' SQL to get the filtered record - easy to make the query in access and copy and paste if you don't know SQL
    Dim i As Integer ' Field counter - we will just assume that we need the 1st two fields
    Dim x As Integer ' declaration to hold how many items are in the third combobox


    'set pointer to database
    'These are the lines i am having trouble with.
    Set db = CurrentDb.Connection
    strSQL = "SELECT * FROM QuotationNew"
    Set rst = db.OpenRecordset(strSQL) 'does this populate the recordset.


    Do While Not rst.EOF
    For Each fld In rst.Fields
    Debug.Print fld.Value & ";";
    Next
    Debug.Print
    rst.MoveNext
    Loop

    'Close the Recordset object.
    rst.Close

    'Close connection and destroy object variables.
    Set rst = Nothing

    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True

    Exit_cmdGenerateQuote_Click:
    Exit Sub

    Err_cmdGenerateQuote_Click:
    MsgBox Err.Description
    Resume Exit_cmdGenerateQuote_Click

    End Sub
    Nerdy Girl

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Look at the frmCustomer in the DB I attached

    You want to look at the Code in teh onClick event of the DAO update button

    S-

  3. #3
    Join Date
    Nov 2003
    Location
    Virginia Beach, VA
    Posts
    4

    Lightbulb Re: Recordsets and database connections

    Originally posted by nerdy_girl
    I am trying to populate a recordset from a query in my database. but i cant get the connection part right.

    here is the code i am tying to use. i want the recordset to populate when i click a button, then i want to pass the object to word and use the data from there.

    thank you all.

    you can email me if you are interested in helping me and would like more details.

    Private Sub cmdGenerateQuote_Click()
    On Error GoTo Err_cmdGenerateQuote_Click

    Dim oApp As Object
    'starts here

    Dim db As DAO.Database ' declaration for database
    Dim rst As Object ' declaration for the records to be pulled
    Dim strSQL As String ' SQL to get the filtered record - easy to make the query in access and copy and paste if you don't know SQL
    Dim i As Integer ' Field counter - we will just assume that we need the 1st two fields
    Dim x As Integer ' declaration to hold how many items are in the third combobox


    'set pointer to database
    'These are the lines i am having trouble with.
    Set db = CurrentDb.Connection
    strSQL = "SELECT * FROM QuotationNew"
    Set rst = db.OpenRecordset(strSQL) 'does this populate the recordset.


    Do While Not rst.EOF
    For Each fld In rst.Fields
    Debug.Print fld.Value & ";";
    Next
    Debug.Print
    rst.MoveNext
    Loop

    'Close the Recordset object.
    rst.Close

    'Close connection and destroy object variables.
    Set rst = Nothing

    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True

    Exit_cmdGenerateQuote_Click:
    Exit Sub

    Err_cmdGenerateQuote_Click:
    MsgBox Err.Description
    Resume Exit_cmdGenerateQuote_Click

    End Sub
    Part of the problem is that you have closed your dataset before you start your Word application. If you are trying to use WORD with information in Access, a simplier way to do that is to create a query that extracts the information you need from your database and then use the Word "mailmerge" feature of Word to connect to that query in your database. If you want to use the same Word template repeatedly with different datasets in the same database, you could use VBA to populate a "target" table and then attach to that. You could then start an instance of word using a specific document you have already created which would have been designed to work with the target table(s). Does this help any???

  4. #4
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Question Re: Recordsets and database connections

    Originally posted by fynder
    Part of the problem is that you have closed your dataset before you start your Word application. If you are trying to use WORD with information in Access, a simplier way to do that is to create a query that extracts the information you need from your database and then use the Word "mailmerge" feature of Word to connect to that query in your database. If you want to use the same Word template repeatedly with different datasets in the same database, you could use VBA to populate a "target" table and then attach to that. You could then start an instance of word using a specific document you have already created which would have been designed to work with the target table(s). Does this help any???
    Yes, this sounds like what i would need to do, I don't know what a target table is......can you send me an example and guide me to a site where i can learn more about this type of stuff. I am using ADODB as recommened by my tutor....is this going to be a problem. I am still learning about the differences between DAO and ADO so please bear with me.
    thank you.....
    Nerdy Girl

Posting Permissions

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