Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    65

    Question Unanswered: How to create a table from a query in VBA

    I want to create a table from a query using VBA and then compare the results with another table. The following code should be executed after the user makes a selection from a combo. When I run it, I get err 3075 (missing operator...) and stop at the line: set rs1 = ...

    --------CODE BEGINS--------------
    Private Sub SelectProgram_Click()

    Dim db As Database
    Dim rs1 As DAO.Recordset '--showing required courses--
    Dim rs2 As DAO.Recordset '--showing completed courses--
    Dim rs3 As DAO.Recordset '--tbl "StilltoComplete-----

    Dim sqlString As String

    Set db = CurrentDb

    sqlString = "SELECT ProgramDetails.ProgramId, ProgramDetails.CourseId" _
    & "FROM ProgramDetails WHERE ((ProgramDetails.ProgramId='& [Forms]![Progress].[SelectProgram]& ');"

    Set rs1 = db.OpenRecordset(sqlString) '--required courses--
    Set rs2 = db.OpenRecordset("Marks") '--completed courses--
    Set rs3 = db.OpenRecordset("StillToComplete")

    rs2.MoveFirst
    Do While Not rs2.EOF
    rs1.FindFirst "CourseId = '" & rs2!CourseId & "'"
    If Not rs1.NoMatch Then '--found a course that is required and has been completed---
    '...do nothing... go to the next record
    Else:
    rs3.Edit
    rs3!ProgramId = rs1!ProgramId
    rs3!CourseId = rs2!CourseId
    rs3.Update

    End If
    rs2.MoveNext

    Loop

    End Sub
    -------- CODE END---------------------

    Any help to get this working would be appreciated.

    Thanks,
    Jazz

  2. #2
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Hope This Helps

    1-Open Query in Design View.
    2-Click on Query Tool MEnu.
    3-Click on "Make Table Query"
    4-Enter the name u Want to Give to Ur table.
    5-Run that Query and it will generate that table with desired Data according to Query.


    Cheers.

  3. #3
    Join Date
    Feb 2003
    Posts
    65

    Re: Hope This Helps

    Originally posted by VBAPROGRAMMER
    1-Open Query in Design View.
    2-Click on Query Tool MEnu.
    3-Click on "Make Table Query"
    4-Enter the name u Want to Give to Ur table.
    5-Run that Query and it will generate that table with desired Data according to Query.


    Cheers.
    Thanks VBAPROGRAMMER for your reply. I new about the action query. The problem is that I want to pass a variable to the query, so each time I can change the value based on a combo box (SelectProgram). It did not work for some reason, so I decided to do it in VBA.

    The error I get is a sintax error in the query statement. Hope someone could catch the problem.

    Jazz

  4. #4
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50

    Re: How to create a table from a query in VBA

    HI,

    the bound column in your combo box should be ProgramID. Just change your sql statement to:

    sqlString = "SELECT ProgramDetails.ProgramId, ProgramDetails.CourseId FROM ProgramDetails WHERE ProgramDetails.ProgramId=" & [Forms]![Progress].[SelectProgram]

    hope this helps!

    tjacobs

    Originally posted by jazz novice
    I want to create a table from a query using VBA and then compare the results with another table. The following code should be executed after the user makes a selection from a combo. When I run it, I get err 3075 (missing operator...) and stop at the line: set rs1 = ...

    --------CODE BEGINS--------------
    Private Sub SelectProgram_Click()

    Dim db As Database
    Dim rs1 As DAO.Recordset '--showing required courses--
    Dim rs2 As DAO.Recordset '--showing completed courses--
    Dim rs3 As DAO.Recordset '--tbl "StilltoComplete-----

    Dim sqlString As String

    Set db = CurrentDb

    sqlString = "SELECT ProgramDetails.ProgramId, ProgramDetails.CourseId" _
    & "FROM ProgramDetails WHERE ((ProgramDetails.ProgramId='& [Forms]![Progress].[SelectProgram]& ');"

    Set rs1 = db.OpenRecordset(sqlString) '--required courses--
    Set rs2 = db.OpenRecordset("Marks") '--completed courses--
    Set rs3 = db.OpenRecordset("StillToComplete")

    rs2.MoveFirst
    Do While Not rs2.EOF
    rs1.FindFirst "CourseId = '" & rs2!CourseId & "'"
    If Not rs1.NoMatch Then '--found a course that is required and has been completed---
    '...do nothing... go to the next record
    Else:
    rs3.Edit
    rs3!ProgramId = rs1!ProgramId
    rs3!CourseId = rs2!CourseId
    rs3.Update

    End If
    rs2.MoveNext

    Loop

    End Sub
    -------- CODE END---------------------

    Any help to get this working would be appreciated.

    Thanks,
    Jazz

  5. #5
    Join Date
    Feb 2003
    Posts
    65

    Re: How to create a table from a query in VBA

    Originally posted by tjacobs
    HI,

    the bound column in your combo box should be ProgramID. Just change your sql statement to:

    sqlString = "SELECT ProgramDetails.ProgramId, ProgramDetails.CourseId FROM ProgramDetails WHERE ProgramDetails.ProgramId=" & [Forms]![Progress].[SelectProgram]

    hope this helps!

    tjacobs
    Thanks tjacobs, it did the trick. At least I moved a bit ahead in getting this sub working. I stumbled now with using .Bookmark, however I will post the question after I try a few more times to solve it myself.

    Thanks again.
    Jazz

  6. #6
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    You could still use an action query, to get it to work from code you have to run through the parameters ( in this case your combo box value).

    a simple way is:

    ------

    dim db as dao.database
    dim qdf as dao.querydef
    dim prm as dao.parameter


    set db = currentdb()
    set qdf = db.querydefs("Action query here")

    for each prm in qdf.parameters
    prm.value = eval(prm.name)
    next prm

    qdf.execute

    --------


    I use this quite a lot in my code to run update/append queries as it saves a lot of code writting and debugging.

    Remember to add the parameter and it's type to the parameters on the query menu in the action query.

    Hope this helps

Posting Permissions

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