SQL parameter Qry in Module

Topic

New Reply

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”)

‘rs2.RecordSource = sql & Str(z)

Reply | Quote

Replies

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

Francois

Reply | Quote

Sorry, that didn’t work for me.

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 rs2.RecordSource = sql & Str(z) 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 lrecCount = Data2.Recordset.RecordCount before you enter the Do While…Loop, then you reset the recordsource of Data2 to somthing else, and then you have lrecCount = Data2.Recordset.RecordCount + 1. 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):

  rs2=db.OpenRecordset(sql & z, dbOpenDynaset)

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

Reply | Quote