Help With Module - sorry, one more question

G

Guest

Thanks, Doug and Mike for your suggestions the other day, you did put me on
the right track. It's still not behaving, so I have a new question..... my
apologies for not being clear previously. I want to loop through a
recordset, copy one of the field values, then create a new record and paste
this value into one of the fields. I need the new record to have a different
key field value, so the SQL way to append it doesn't seem to me like it would
work. Here's my revised code:

Public Sub NewContractYear(strRecSource1 As String, strRecSource2 As String,
strCtl1 As String, strCtl2 As String, strCtl3 As String, strCtl4 As String)
On Error Resume Next
Dim db As DAO.Database
Dim rst1 As Variable
Dim rst2 As Variable
Dim strValue As String

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

While Not rst1.EOF
strValue = rst1(strCtl1).Value

With rst2
.AddNew
rst2(strCtl2).Value = ContractNumber
rst2(strCtl3).Value = strCYCode
rst2(strCtl4).Value = strValue

Stepping through, moving my cursor over these lines just above (rst) gives
me the error message: Object Variable of With Block Variable not set. It
appears to me to be set. The string control names are recognized. Again,
it's probably obvious to you what's wrong. Here's the rest:

.Update
End With
Wend
rst1.MoveNext

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

Then called from a button click:

Call NewContractYear("Current Agency Contact",
"Crossreference:ContractAgencyContact", "AC_Code", "Contract_Number",
"CYCode", "AC_Code")

Thanks in advance for all your help. - Anne
 
G

Guest

First problem:
If rst1.EOF And rst1.BOF = True Then Change to:
If rst1.EOF And rst1.BOF True Then Or:
If rst1.EOF = True And rst1.BOF = True Then

The logic in your code will work, but it is not well written. If you used
the same construct for non-boolean varialbes, it would either not work or
cause an error. Each variable has to be compared separately:

If A And B = "Something" ' Incorrect
If A = "Something" And B = "Something" ' Correct

These are incorrectly cast and are probably responsible for your error:
 
G

George Nicholson

1) Try changing these 2 lines and see if it works better
("As Variable" should cause compile errors unless you have a custom class
obect by that name):

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

2) you need to relocate your rst1.MoveNext line so that it is within your
While...Wend or you be in an endless loop, never moving off the 1st record
but adding records to Rst2 into infinity:
While Not rst1.EOF
......
rst1.MoveNext
Wend

HTH,
 
G

Guest

In my original posting, I did have:

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

and it was suggested that I change it to variable. Either way, I still get
the error. Thanks Klatuu for pointing our the logic error, and thank you
George for the suggestion to move the "rst.MoveNext" line. But even with
these changes I'm still getting:

"rst2(strCtl2).value = Object variable or With Block variable not set", for
each of these lines with the corresponding control.

Perhaps one of you could hold the window open while I push the computer
out....! Thanks anyway for your help.
 
G

Guest

What is ContractNumber?
rst2(strCtl2).Value = ContractNumber
I don't see it Dimmed anywhere.

Can we tie my computer to yours so they will fall faster?
 
G

Guest

ContractNumber is a global variable declared earlier in the process - didn't
include this 'cause that part is working beautifully.

Yes!! If we film it we could make Late Night with Letterman, though we
could be starting a trend. I fear office building windows might open around
the world.

I moved the line "Set rst2 = db.OpenRecordset(strRecSource2)" to just above
the With statement and now it works. Make any sense to you?
 
G

George Nicholson

I moved the line "Set rst2 = db.OpenRecordset(strRecSource2)" to just
above
the With statement and now it works. Make any sense to you?

Well, not sure why that works, but it's a bad place for it. That puts it
within your loop, so that table/query will be re-opened every iteration of
the loop, rather than once. The severity of the performance hit this creates
will depend on your data (and network, if appropriate).

Looking at it again, it may be that the apparent redundancy of specifying
rst2 within what is already a "With rst2" structure might be confusing
things. Something like this would probably be cleaner:

With rst2
.AddNew
.Fields(strCtl2).Value = ContractNumber
..... (etc)
.Update
End With

HTH,
 
G

Guest

I think I see what caused it:
With rst2
.AddNew
rst2(strCtl2).Value = ContractNumber
rst2(strCtl3).Value = strCYCode
rst2(strCtl4).Value = strValue

would be the same as:
rst2. rst2(strCtl2).Value = ContractNumber
Which would confuse the dickens out of Access. To use the With, it would
need to be:

With rst2
.AddNew
.(strCtl2).Value = ContractNumber
.(strCtl3).Value = strCYCode
.(strCtl4).Value = strValue

Geroge is correct about putting it inside the loop. Not good.

Well, I always have said I hate computers, but without them, I'd have to get
a real job.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top