multiple recordets in a sub

B

bella

Registered User.
Local time
Today, 13:20
Joined
Jul 31, 2003
Messages
38
Hi

is it possible to declare and use multiple rescordsets inside one sub?

eg.

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim sql1 As String
Dim sql2 As String

sql1 = blah
sql2 = blah

blah blah (some code)

rst1.Update
rst2.Update


?

i tried this, but its not working...have i done something wrong?

Bella
 
Wayne

Hi Wayne

a really weird thing is happening to my code.

I declared multiple recordsets in my sub, and used them, and somehow it suddenly all worked properly.

Then, all of a sudden, it decided not to load the recordsets anymore.

eg. the following code gives rstLC!LO1_ID an integer value which it gets from rst1!LO_ID - but when i run this block of code it gives me a run time error because rstLC!LO1_ID is not loading the integer.

' Get LO1_ID value from recordset rst1
rstLC!LO1_ID = rst1!LO_ID

rst1!LO_ID loads its integer inmthe following manner:
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 1

so why is it not storing a value??


If I do this code below: rst1!LO_ID stores the correct integer...but i dont understand why the above code, which worked yesterday morning, has suddenly decided not to work now?? :(

Dim y As Integer

y = DMax("LO_ID", "[Learning Outcomes]") + 1
rst1!LO_ID = y




This is a copy of the entire code in this sub. I think it just wants to eat me

Bella
----------------------------------------------------------------------------------

Private Sub Step1cmd_Click()

Dim dbs As DAO.Database

Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim rst5 As DAO.Recordset
Dim rst6 As DAO.Recordset
Dim rstLC As DAO.Recordset

Dim sql As String
Dim sql2 As String
Dim strSQL As String

Set dbs = CurrentDb


'Build an SQL string that selects fields from the Subject List table to update to.

sql = "SELECT [Subject List].[SL_ID], [Subject List].[Subject Name], " & _
"[Subject List].[Year_ID], [Subject List].[KLA_ID] " & _
"FROM [Subject List];"


Set rst = dbs.OpenRecordset(sql)
rst.AddNew

' Get next number
rst!SL_ID = DMax("SL_ID", "[Subject List]") + 1

' Get Subject Name value from text box & put this value into the recordset
rst![Subject Name] = Me.subject

' Get Kla value from kla combo box & put this value into the recordset
rst!KLA_ID = Me.kla

' Get Year_ID value from year combo box & put this value into the recordset
rst!Year_ID = Me.cboyear


' ------------------------------------------------------------------------------

'Build an SQL string that selects fields from the Learning Outcomes Table

sql2 = "SELECT [Learning Outcomes].[LO_ID], [Learning Outcomes].[Learning Outcomes] " & _
"FROM [Learning Outcomes];"

Set rst1 = dbs.OpenRecordset(sql2)
Set rst2 = dbs.OpenRecordset(sql2)
Set rst3 = dbs.OpenRecordset(sql2)
Set rst4 = dbs.OpenRecordset(sql2)
Set rst5 = dbs.OpenRecordset(sql2)
Set rst6 = dbs.OpenRecordset(sql2)


rst1.AddNew
rst2.AddNew
rst3.AddNew
rst4.AddNew
rst5.AddNew
rst6.AddNew


' Get Next LO_ID in Learning Outcomes Table
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 1
rst2!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 2
rst3!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 3
rst4!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 4
rst5!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 5
rst6!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 6


' Get Learning Outcomes value from text box
rst1![Learning Outcomes] = Me.txtLO1
rst2![Learning Outcomes] = Me.txtLO2
rst3![Learning Outcomes] = Me.txtLO3
rst4![Learning Outcomes] = Me.txtLO4
rst5![Learning Outcomes] = Me.txtLO5
rst6![Learning Outcomes] = Me.txtLO6

' Update the loaded recordset to the Learning Outcomes table
rst1.Update
rst2.Update
rst3.Update
rst4.Update
rst5.Update
rst6.Update

' --------------------------------------------------------------------------------------
' Build string to fill in the [Learning Outcomes Connector]

strSQL = "SELECT [Learning Outcomes Connector].SL_ID, [Learning Outcomes Connector].LO1_ID, " & _
"[Learning Outcomes Connector].LO2_ID, [Learning Outcomes Connector].LO3_ID, " & _
"[Learning Outcomes Connector].LO4_ID, [Learning Outcomes Connector].LO5_ID, " & _
"[Learning Outcomes Connector].LO6_ID, [Learning Outcomes Connector].LO_Counter " & _
"FROM [Learning Outcomes Connector];"

Set rstLC = dbs.OpenRecordset(strSQL)

rstLC.AddNew

' Get next SL_ID for the Learning Outcomes Connector Table
rstLC!SL_ID = rst!SL_ID

' Get LO1_ID value from recordset rst1
rstLC!LO1_ID = rst1!LO_ID

' Get LO2_ID value from recordset rst2
rstLC!LO2_ID = rst2!LO_ID

' Get LO3_ID value from recordset rst3
rstLC!LO3_ID = rst3!LO_ID

' Get LO4_ID value from recordset rst4
rstLC!LO4_ID = rst4!LO_ID

' Get LO5_ID value from recordset rst5
rstLC!LO5_ID = rst5!LO_ID

' Get LO6_ID value from recordset rst6
rstLC!LO6_ID = rst6!LO_ID

' Assign random LO_Count value into the recordset

rstLC!LO_Counter = 55

' Update the loaded recordset to the learning Outcomes Connector table
rstLC.Update


' Update the loaded recordset to the Subject List table
rst.Update

End Sub


:confused: :confused:
 
bella,

I haven't seen your app, but I'll bet you that you need another table
in there. Anytime you have something like txtLO1, txtLO2 ... it
indicates that your app is not flexible. Some day when they add one
or more "LOs", whatever they are, you will have a problem.

I reduced the number of recordsets and shortened your code a bit.

Run it with the debugger and let me know.

Post your db if you have a problem.

Code: 
Private Sub Step1cmd_Click() 

Dim dbs As DAO.Database 

Dim rst As DAO.Recordset 
Dim rst1 As DAO.Recordset 
Dim rstLC As DAO.Recordset 

Dim sql As String 

Set dbs = CurrentDb 

'Build an SQL string that selects fields from the Subject List table to update to. 

sql = "SELECT [Subject List].[SL_ID],   [Subject List].[Subject Name], " & _ 
             "[Subject List].[Year_ID], [Subject List].[KLA_ID] " & _ 
      "FROM [Subject List];" 

Set rst = dbs.OpenRecordset(sql) 

rst.AddNew 
rst!SL_ID = DMax("SL_ID", "[Subject List]") + 1  
rst![Subject Name] = Me.subject 
rst!KLA_ID = Me.kla 
rst!Year_ID = Me.cboyear 
rst.Update

'Build an SQL string that selects fields from the Learning Outcomes Table 

sql = "SELECT [Learning Outcomes].[LO_ID], [Learning Outcomes].[Learning Outcomes] " & _ 
      "FROM [Learning Outcomes];" 

Set rst1 = dbs.OpenRecordset(sql2) 
rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 1 
rst1![Learning Outcomes] = Me.txtLO1 
rst1.Update

rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 2 
rst1![Learning Outcomes] = Me.txtLO2
rst1.Update

rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 3
rst1![Learning Outcomes] = Me.txtLO3
rst1.Update

rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 4
rst1![Learning Outcomes] = Me.txtLO4
rst1.Update

rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 5
rst1![Learning Outcomes] = Me.txtLO5
rst1.Update

rst1.AddNew 
rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + 6 
rst1![Learning Outcomes] = Me.txtLO6
rst1.Update

' -------------------------------------------------------------------------------------- 
' Build string to fill in the [Learning Outcomes Connector] 

sql = "SELECT [Learning Outcomes Connector].SL_ID,  [Learning Outcomes Connector].LO1_ID, " & _ 
             "[Learning Outcomes Connector].LO2_ID, [Learning Outcomes Connector].LO3_ID, " & _ 
             "[Learning Outcomes Connector].LO4_ID, [Learning Outcomes Connector].LO5_ID, " & _ 
             "[Learning Outcomes Connector].LO6_ID, [Learning Outcomes Connector].LO_Counter " & _ 
      "FROM [Learning Outcomes Connector];" 

Set rstLC = dbs.OpenRecordset(strSQL) 

rstLC.AddNew 
rstLC!SL_ID = rst!SL_ID 
rstLC!LO6_ID = DMax("LO_ID", "[Learning Outcomes]")
rstLC!LO5_ID = DMax("LO_ID", "[Learning Outcomes]") - 1
rstLC!LO4_ID = DMax("LO_ID", "[Learning Outcomes]") - 2
rstLC!LO3_ID = DMax("LO_ID", "[Learning Outcomes]") - 3
rstLC!LO2_ID = DMax("LO_ID", "[Learning Outcomes]") - 4
rstLC!LO1_ID = DMax("LO_ID", "[Learning Outcomes]") - 5
rstLC!LO_Counter = 55 
rstLC.Update  

End Sub

Wayne
 
Same code as Wayne's, just shortened by using a loop
no real reaso; just nursing a hangover :(

Code: 
Private Sub Step1cmd_Click() 

    Dim dbs As DAO.Database 

    Dim rst As DAO.Recordset 
    Dim rst1 As DAO.Recordset 
    Dim rstLC As DAO.Recordset 

    Dim sql As String, intCounter As Integer

    Set dbs = CurrentDb 

    'Build an SQL string that selects fields from the Subject List table to update to. 

    sql = "SELECT [Subject List].[SL_ID],   [Subject List].[Subject Name], " & _ 
             "[Subject List].[Year_ID], [Subject List].[KLA_ID] " & _ 
      "FROM [Subject List];" 

    Set rst = dbs.OpenRecordset(sql) 

    rst.AddNew 
    rst!SL_ID = DMax("SL_ID", "[Subject List]") + 1  
    rst![Subject Name] = Me.Subject 
    rst!KLA_ID = Me.Kla 
    rst!Year_ID = Me.cboYear 
    rst.Update

    'Build an SQL string that selects fields from the Learning Outcomes Table 

    sql = "SELECT [Learning Outcomes].[LO_ID], [Learning Outcomes].[Learning Outcomes] " & _ 
        "FROM [Learning Outcomes];" 


    Set rst1 = dbs.OpenRecordset(sql2) 
    For intCounter = 1 To 6
        rst1.AddNew 
        rst1!LO_ID = DMax("LO_ID", "[Learning Outcomes]") + intCounter 
        rst1![Learning Outcomes] = Me.Controls("txtLO" & intCounter)
        rst1.Update
    Next intCounter

' -------------------------------------------------------------------------------------- 
' Build string to fill in the [Learning Outcomes Connector] 

    sql = "SELECT [Learning Outcomes Connector].SL_ID, "

    For intCounter = 1 To 6
        sql = sql & "[Learning Outcomes Connector].LO" & intCounter & "_ID, "
    Next intCounter
    sql = sql & "[Learning Outcomes Connector].LO_Counter " & _
        "FROM [Learning Outcomes Connector];" 

    Set rstLC = dbs.OpenRecordset(strSQL) 

    rstLC.AddNew 
    rstLC!SL_ID = rst!SL_ID 
    For intCounter = 0 To 5
        rstLC.Fields("LO" & (6-intCounter) & "_ID") = DMax("LO_ID", "[Learning Outcomes]") - intCounter
    Next intCounter
    rstLC!LO_Counter = 55 
    rstLC.Update  

End Sub
 
:(

Hi guys,

thanks for your code, but its still not updating LO1_ID, LO2_ID, etc to the Learning Outcomes Connector Table...cos its not reading those int values into the recordset that stores the IDs...

This is the line that isnt storing a value (its telling me that rstLC!LO1_ID = 0 which isnt true.

Its also telling me that rst1!LO_ID has the value its supposed to hold. so why isnt it allocating this value to rstLC!LO1_ID??

' Get LO1_ID value from recordset rst1
rstLC!LO1_ID = rst1!LO_ID

I ran both your code's and im having the same prob with both of them...although i do appreciate your more elegent solutions ;)


This is the bit that has the problem: (Bella's Code)
=============================================
' Build string to fill in the [Learning Outcomes Connector]

strSQL = "SELECT [Learning Outcomes Connector].SL_ID, [Learning Outcomes Connector].LO1_ID, " & _
"[Learning Outcomes Connector].LO2_ID, [Learning Outcomes Connector].LO3_ID, " & _
"[Learning Outcomes Connector].LO4_ID, [Learning Outcomes Connector].LO5_ID, " & _
"[Learning Outcomes Connector].LO6_ID, [Learning Outcomes Connector].LO_Counter " & _
"FROM [Learning Outcomes Connector];"

Set rstLC = dbs.OpenRecordset(strSQL)

rstLC.AddNew

' Get next SL_ID for the Learning Outcomes Connector Table
rstLC!SL_ID = rst!SL_ID

' Get LO1_ID value from recordset rst1
rstLC!LO1_ID = rst1!LO_ID

' Get LO2_ID value from recordset rst2
rstLC!LO2_ID = rst2!LO_ID

' Get LO3_ID value from recordset rst3
rstLC!LO3_ID = rst3!LO_ID

' Get LO4_ID value from recordset rst4
rstLC!LO4_ID = rst4!LO_ID

' Get LO5_ID value from recordset rst5
rstLC!LO5_ID = rst5!LO_ID

' Get LO6_ID value from recordset rst6
rstLC!LO6_ID = rst6!LO_ID

' Assign random LO_Count value into the recordset

rstLC!LO_Counter = 55

' Update the loaded recordset to the learning Outcomes Connector table
rstLC.Update


' Update the loaded recordset to the Subject List table
rst.Update

End Sub

??

totally in need of a miracle/sleep...

Bell

:confused: ' Get LO1_ID value from recordset rst1
 
You must log in or register to reply here.

Similar threads

L
Problem typing searh text field
Replies
15
Views
500
LjushaMisha
L
Y
New with a problem
Replies
4
Views
439
Pat Hartman
P
C
Problem with 'Like' SQL filter
Replies
6
Views
311
Josef P.
J
Z
Finding and opening a hyperlink
Replies
3
Views
306
zebrafoot
Z
N
Solved Failing To Save Imported Data from the internet in Ms Access
Replies
6
Views
639
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom