Error when trying to create recordset 1

[jallen919]

I'm new to Access VBA so bare with me

I bought a book on Access Development and there is some code (working with Recordsets) that I want to put into my Access database.

To add this code I click on Modules from the main window, start typing:

Public sub myfunctionname()
Dim rsLeads as

Then right after typing "rsLeads as" I get an error message that says "the form name "form1" is misspelled or refers to a form that doesn't exist"

No matter how I try to start typing this function I get that error message. It's my first time typing code like this in Access -- what am I doing wrong?

 

[ghubbell]

Hi J Allen!
Gremlins! more Gremlins! X-) That's really something! I think it might be time to make a fresh empty database and import all your good stuff in to it and take another shot.

There is absolutely nothing wrong with what you were doing. When you type that "as" you should be getting a little dropdown box of choices (recordset,string...) if you're not seeing this then they're definitely in the gears somewhere. Hopefully you won't have to reload Access but even if, not so bad. Oh, you could also try a compact~repair as that sometimes scares them off.... Gord
ghubbell@total.net

 

[jallen919]

OK creating a new DB and importing seemed to solve my problem but now when I create my code I'm getting an error that says "Method or data member not found"

here is the code I have -- can you tell me if there is something obvious that I am missing (keep in mind this is my first shot at doing anything in Access VBA):

Public Sub AssignBrokers()

Dim rstLeads As ADODB.Recordset
Dim rstBrokers As ADODB.Recordset

Set rstLeads = New ADODB.Recordset
rst.Open "tblImportedRecords", CurrentProject.Connection

Set rstBrokers = New ADODB.Recordset
rst.Open "qryActiveBrokers", CurrentProject.Connection

While Not rstLeads.EOF
If (rstBrokers.EOF) Then
rstBrokers.MoveFirst
End If
rstLeads.BrokerCode = rstBrokers.BrokerCode
rstBrokers.MoveNext
rstLeads.MoveNext
Wend

rstLeads.Close
rstBrokers.Close

Set rstLeads = Nothing
Set rstBrokers = Nothing

End Sub

 

[ghubbell]

Hi JAllen,
Excellent!

rstLeads.BrokerCode = rstBrokers.BrokerCode needs to be:

rstLeads!BrokerCode = rstBrokers!BrokerCode

.BrokerCode means its a method or property like .movefirst
or .close, but when you use the "bang" (!) it will refer to the field called "BrokerCode" in that recordset.

I make the same slip frequently and find I really have to concentrate with these. Maybe someday they will make it one "dot" easier for us! Gord
ghubbell@total.net

 

[jallen919]

That got me past the first message -- now I get "the object or provider is not capable of performing the requested operation"

any thoughts?

PS. You rock thanks for all of your help on this.

 

[ghubbell]

Thanks J,
I actually don't use ADO stuff much (learning though) and tend to stick to DAO but they seem pretty similar so I'll give this a shot:

Please try:

While Not rstLeads.EOF
If (rstBrokers.EOF) Then
rstBrokers.MoveFirst
End If
rstLeads.Edit '<--set it ready to be modified
rstLeads!BrokerCode = rstBrokers!BrokerCode
rstLeads.Update '<--update the mod!
rstBrokers.MoveNext
rstLeads.MoveNext
Wend

See if that'll go. Gord
ghubbell@total.net

 

[MichaelRed]

Gord, JAllen,

Gord - I don't think so. First, the code posted does not request any db action. There is NO .Edit or .Update in the code, so setting the field should only be a temporary thing. Second, ADO specifically eliminates the requirement for the .Edit

Unless I'm missing something, the error must be something else. The .Move instructions should be direction to 'Abandon' any changes made to the current record.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jallen919]

Not sure exactly how I fixed it but changed my code so my first recordset opened all records in the table for update and then my second record set was opened as a select statement and as a static set of data -- then it seemed to work. I posted my new code below. Thanks to both of you for your help. This service has been invaluable.

Public Sub AssignBrokers()

Dim rstLeads As ADODB.Recordset
Dim rstBrokers As ADODB.Recordset

Set rstLeads = New ADODB.Recordset
Set rstLeads.ActiveConnection = CurrentProject.Connection
rstLeads.CursorType = adOpenKeyset
rstLeads.LockType = adLockOptimistic
rstLeads.Source = &quot;tblImportedRecords&quot;
rstLeads.Open Options:=adCmdTableDirect

Set rstBrokers = New ADODB.Recordset
rstBrokers.Open &quot;SELECT BrokerCode FROM tblBrokers WHERE ActiveBroker=true&quot;, CurrentProject.Connection

While Not rstLeads.EOF
If (rstBrokers.EOF) Then
rstBrokers.MoveFirst
End If
rstLeads!BrokerCode = rstBrokers!BrokerCode
rstBrokers.MoveNext
rstLeads.MoveNext
Wend

rstLeads.Close
rstBrokers.Close

Set rstLeads = Nothing
Set rstBrokers = Nothing

End Sub

 

[ghubbell]

Learn something new every day! Thanks J, Mike! Gord
ghubbell@total.net