Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL parameter Qry in Module

    The following was designed in VB 6.0 but 'translated' back into Access 97.
    How do you call a parameter query in a module so that you don't need to re-enter each value by hand in order to re-query?
    The process should hold the value, increment by one and concatenate to the other value.

    I tried to include all applicable code but the keyline I seek is highlited in red.

    Set rs1 = db.OpenRecordset("Company")
    Set rs2 = db.OpenRecordset("tbl_Final")
    sql = "select * from tbl_all where conum = "
    'tbl_company

    rs1.MoveLast
    rs1.MoveFirst
    ldata1count = rs1.RecordCount
    rs2.MoveLast
    rs2.MoveFirst
    lrecCount = rs2.RecordCount

    Do While Not rs1.EOF()

    z = rs1.Fields("CoNUM")

    <red> 'rs2.RecordSource = sql & Str(z)</red>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    Replace
    rs2.RecordSource = sql & Str(z)
    by
    Set rs2 = db.openrecordset(sql & str(z))

    Francois
    Francois

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    Sorry, that didn't work for me.

    'No current record' error was detected at the rs2.MoveLast line

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    In your table tbl_all, what is the type of conum ?
    If it is a text field then you have to add quotes

    Set rs2 = db.openrecordset(sql & chr(34)& str(z) & chr(34))

    If conum is a numeric field then the error means you don't have a record where conum equals the value of z. You have to trap this error or maybe there is something wrong with the logic of the program. I don't know the program but in the first rs2 you use table "tbl_Final" and in the sql you use "tbl_all" for the same rs2. Is that correct ?

    Francois
    Francois

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    CoNum is a numeric value.
    CompanyID is a string.
    RowID is the number I wish to increment when adding new records.
    I had a feeling that somehow those set statements were contradicting each other.

    The 'program' was designed in VB6 using data controls in a UserForm for the different tables. I had to modify the code by omitting the data controls to use it in Access97.
    Data 1 was replaced by rs1 etc..

    The statement itself calls table1 and counts the number of Companies. It then opens the larger table, compares the value of CoNum. If it's there it does a record count and determines the highest value. Then it adds records as descibed in the For x statement below. It loops through every record of Table1 and runs the For x statement.

    The data controls were used to concatenate the values of both 'datacontrol results' and create the string. Here is the entire code from the form.

    Private Sub Command1_Click()
    Dim i As Integer, x As Integer, z As Integer, itargetnum As Integer
    Dim rs1 As Recordset, rs2 As Recordset
    Dim db As Database
    Dim lrecCount As Long, ldata1count As Long
    Dim sql As String
    Dim cCoName As String
    sql = "select * from tbl_all where conum = "
    'tbl_company
    Data1.Recordset.MoveFirst
    ldata1count = Data1.Recordset.RecordCount
    Data1.Refresh
    Data1.Recordset.MoveFirst
    Data2.Recordset.MoveFirst

    lrecCount = Data2.Recordset.RecordCount

    Do While Not Data1.Recordset.EOF()

    z = Data1.Recordset.Fields("CoNUM")

    Data2.RecordSource = sql & Str(z)
    'This is where it concatenates the values, places it into 'the table I want to write to, and increments the field 'number by 1. This is called later in the x statement 'below.

    Data2.Refresh

    lrecCount = Data2.Recordset.RecordCount + 1

    cCoName = Data1.Recordset.Fields("CompanyID")

    For x = lrecCount To 38
    Data2.Recordset.AddNew
    Data2.Recordset.Fields("Conum") = z
    Data2.Recordset.Fields("SurveyNo") = "5002001"
    Data2.Recordset.Fields("CompanyID") = cCoName
    Data2.Recordset.Fields("rowid") = x
    Data2.Recordset.Update
    Next x

    Data1.Recordset.MoveNext

    Loop

    End

    End Sub

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    What are the recordsources of data1 and data2 when you call the sub ?

    Are there any records in tbl_final ? You don't write in tbl_final but in tbl_all.

    Francois
    Francois

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    Sorry, tbl_Final was a last minute name change.
    data1 was referring to tbl_Company data2 was sourcing tbl_Final. I posted a piece of old code. tbl_Final is the table I wish to write to.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameter Qry in Module

    Insert a condition before movig rs2 for the case recordcount is 0

    rs1.MoveLast
    rs1.MoveFirst
    ldata1count = rs1.RecordCount
    If rs2.recordcount <> 0 Then
    rs2.MoveLast
    rs2.MoveFirst
    End If
    lrecCount = rs2.RecordCount

    Francois
    Francois

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL parameter Qry in Module

    Since your original code uses a datacontrol, I assume this code is in a form, right? So, if I understand you, you're stepping through each record in the first recordset, getting the CONUM field value for each record, and trying to set the recordsource of the second recordset to the SQL string = "select * from tbl_all where conum = " concatenated to the CONUM value from the first recordset. Then you're using a For...Next loop that starts with the recordcount + 1 of the second recordset (which you've just changed to a whole new recordset in mid-stream) to 38 and adding a new record to the second recordset for each loop. I'm assuming that changing the recordsource of the second recordset is supposed to return an empty recordset, right?

    Welcome to the differences between Access and VB. I'm not surprised you're having problems. The DataControls in VB don't really translate to Access, since Access forms are databound by default. In effect, the datacontrols are already built in, and you don't really need them even in VB unless you want to bind data to a form. Otherwise, a recordset works just as well there as in Access. However, Access doesn't expose the recordsource property of a DAO recordset, which is why <font color=red>rs2.RecordSource = sql & Str(z)</font color=red> doesn't work. You can do something similar to this with ADO, but you'd have to completely rewrite your code to do that.

    In the original code, you have this <font color=red>lrecCount = Data2.Recordset.RecordCount</font color=red> before you enter the Do While...Loop, then you reset the recordsource of Data2 to somthing else, and then you have <font color=red>lrecCount = Data2.Recordset.RecordCount + 1</font color=red>. You've reset your variable without ever using it the first time that I can see, so it doesn't appear that you actually need the second recordset until you enter the Do...Loop. In that case, just close and reopen the second recordset using the SQL string as the source like this (you don't have to wrap the z variable in a Str() function since you're concatenating it to a string):

    <pre> rs2=db.OpenRecordset(sql & z, dbOpenDynaset)</pre>


    and put an rs2.close after the rs1.MoveNext and before the Loop and go on from there.
    Charlotte

Posting Permissions

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