Updating a recordset

[benkingery]

I have a recordset I want to update with some values derived from control on a form.

here is what I have so far:

Private Sub CountGroup_AfterUpdate()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySql As String
mySql = "SELECT TOP " & Me.Number & " SafetyCount.Warehouse_Location, SafetyCount.Master_Child, SafetyCount.CountGroup FROM SafetyCount ORDER BY SafetyCount.Warehouse_Location, SafetyCount.Master_Child"

I think I am generating the RS correctly, but I don't know how to update it. I want to update it with the values found on the form this will be coded on. The control is called "CountGroup".

You'll also notice that within the SQL statement that my RS is based on there is a SELECT TOP..... statement. Can I insert the variable as I've done here where the variable comes from the control "Number" on my form?

Any help would be much appreciated.

 

[benkingery]

Here's what I have now. I think I'm on the right track, but I think my update part where I need to update field 3 is not working right.

I get an error that says "Compile error: Invalid use of property"

The part of code that is highlighted is ".Fields(3) ="

Private Sub CountGroup_AfterUpdate()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySql As String
mySql = "SELECT TOP " & Me.Number & " SafetyCount.Warehouse_Location, SafetyCount.Master_Child, SafetyCount.CountGroup FROM SafetyCount ORDER BY SafetyCount.Warehouse_Location, SafetyCount.Master_Child"

DoCmd.SetWarnings False

myRecordSet.Open (mySql)
While Not myRecordSet.EOF
    Set myRecordSet.Fields(3) = Me.Number
    myRecordSet.MoveNext
Wend
DoCmd.SetWarnings True
Set myRecordSet = Nothing
Set cnn1 = Nothing

End Sub

Ideas????

 

[Scooterbug]

Well, a couple of them:

1. Set myRecordSet.Fields(3) = should be: myrecordset.fields(3) =
2. you have Wend, but not a With

 

[boblarson]

Well, for one you should not be having a field named NUMBER as that is an Access Reserved Word. I would change it to something else. If you must use it, then use square brackets:

Me.[Number]

 

[boblarson]

Well, a couple of them:

1. Set myRecordSet.Fields(3) = should be: myrecordset.fields(3) =

Nope, the capitalization actually is there and should be there but it really doesn't matter.

2. you have Wend, but not a With

He has a WHILE which then ends with the WEND.

 

[Scooterbug]

Nope, the capitalization actually is there and should be there but it really doesn't matter.


He has a WHILE which then ends with the WEND.

*Headdesk* my bad! (Stupid Fridays!)

 

[boblarson]

*Headdesk* my bad! (Stupid Fridays!)

You're funny. I wish we had the emoticon that Utter Access has which shows a smiley beating his head against the wall. (I've had days where I needed that one EXTENSIVELY)

 

[boblarson]

One thing you would need to do is to add this in:

While Not myRecordSet.EOF
    Set myRecordSet.Fields(3) = Me.[COLOR="red"][[/COLOR]Number[COLOR="Red"]][/COLOR]
    [COLOR="red"].Update[/COLOR]
    myRecordSet.MoveNext
Wend

and you might need the

.Edit

before the line which sets the value.

But looking at it, why aren't you just using an Update query? Seems to be a lot of work where an Update query would do it in one shot.

 

[benkingery]

I got it to work on my own. Yeah, finally I figured one out. Can I get credit for answering my own question?

Private Sub CountGroup_AfterUpdate()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySql As String
mySql = "SELECT TOP " & Me.Number & " SafetyCount.Warehouse_Location, SafetyCount.Master_Child, SafetyCount.CountGroup FROM SafetyCount WHERE (((SafetyCount.CountGroup) Is Null)) ORDER BY SafetyCount.Warehouse_Location, SafetyCount.Master_Child"


DoCmd.SetWarnings False

myRecordSet.Open (mySql), , , adLockOptimistic
While Not myRecordSet.EOF
    myRecordSet("CountGroup") = Me.CountGroup
    myRecordSet.Update
    myRecordSet.MoveNext
Wend
DoCmd.SetWarnings True
Set myRecordSet = Nothing
Set cnn1 = Nothing

MsgBox ("You have added " & Me.Number & " records to " & Me.CountGroup & " !  Please return count for entering when done.")

DoCmd.Close acForm, "frm_CountGroup_MassAssignSafety", acSavePrompt

 

[boblarson]

I got it to work on my own. Yeah, finally I figured one out. Can I get credit for answering my own question?

Maybe, if you can also answer why you aren't using an Update query which is more efficient than a recordset.

 

[pbaldy]

The compile error is due to the "Set", which I suspect is what Scooter meant. I would agree with the update query instead though.

 

[boblarson]

The compile error is due to the "Set",

Yeah, I missed that one. So "Set" and match to you

 

[benkingery]

Not using an UPDATE query because I couldn't figure out how to pass through a limiting statement like in a select statement (SELECT TOP XXX)

This is an important user component to updating the recordsets.

pbaldy and boblarson, you guys are always answering my stuff. You're great.