Need Coding Advice

[Guest]

it possible to work with two recordsets? I keep getting the error message
"Object variable or With block variable not set" for the variables strCtl2
and strCtl3 but not strCtl1..... they appear to me to be set. One of you
Access lords will probably spot the problem in the code below, which I've
been staring at far too long - thanks for your help!

Public Sub CreateFieldValues(strRecSource1, strRecSource2, strCtl1, strCtl2,
strCtl3 As String)
On Error Resume Next
Dim db As DAO.Database
Dim rst1, rst2 As DAO.Recordset
Dim strFieldValue As String

Set db = DBEngine(0)(0)
Set rst1 = db.OpenRecordset(strRecSource1)
If rst1.EOF And rst1.BOF = True Then
Exit Sub
End If

' loop trough the records and capture the data value, insert into new
record

Set rst2 = db.OpenRecordset(strRecSource2)

While Not rst1.EOF
strFieldValue = strCtl1
With rst2
!strCtl2 = strFieldID
!strCtl3 = strFieldValue
End With
rst1.MoveNext
rst2.MoveNext
Wend

Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing
End Sub

this is then called when a button on a form is clicked:
Call CreateFieldValues("Current Agency Contact", "New Agency Contact",
"AC_Code", "CYCode", "AC_Code")

 

[Douglas J Steele]

What are you trying to do? Since I have to guess, I'd say that you're hoping
that the following code

While Not rst1.EOF
strFieldValue = strCtl1
With rst2
!strCtl2 = strFieldID
!strCtl3 = strFieldValue
End With
rst1.MoveNext
rst2.MoveNext
Wend

will use whatever values you're passing for strCtl2 and strCtl3 as field
names in the recordset.

First, to change values in a DAO recordset, you need to issue the Edit
method before, and the Update method after.

To use variables as field names, you need to use the Fields collection.

Try:

While Not rst1.EOF
strFieldValue = strCtl1
With rst2
.Edit
.Fields(strCtl2) = strFieldID
.Fields(strCtl3) = strFieldValue
.Update
End With
rst1.MoveNext
rst2.MoveNext
Wend

BTW, your parameter declaration probably isn't doing what you think it is.

(strRecSource1, strRecSource2, strCtl1, strCtl2, strCtl3 As String)

declares 5 parameters: 4 as Variants, and 1 (strCtl3) as a string. You can't
"short circuit" declarations in VBA. You probably want:

(strRecSource1 As String, strRecSource2 As String, strCtl1 As String,
strCtl2 As String, strCtl3 As String)

 

[Michel Walsh]

Hi,


It is possible, sure.

Note that in VBA,


Dim rst1, rst2 As DAO.Recordset


declare rst1 as a Variant, not a DAO.Recordset. Preferable to do it in two
lines:

Dim rst1 AS DAO.Recordset
Dim rst2 As DAO.Recordset


Next, DAO requires that you use the Edit method of the recordset before
starting writing in it, or AddNew, and it seems it is what you try to do,
if you want to append a new record to its recordset.


Finally, it may be preferable that you use an SQL statement to

INSERT INTO table2(listOfFIelds) SELECT listOfFields FROM table1 WHERE
someCriteria


which turns to be a single line of code,

CurrentDb.Execute "INSER INTO ... " , dbFailOnERror


and probably perceptibly faster, than looping through recordset to make "n"
append operations.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


And as Doug mentioned it, with recordset, indeed, even if I didn't mention
it, you need to call the Update method when you have finished the Edit, or
Append, for each record you edit/append... if you ever desire to go the
"recordset" way rather than with the SQL way.

Vanderghast, Access MVP