2nd row in continuous form doesn't update

[SBBmaster09]

Hi, I have a continuous form. I am able to update the data if the continuous form has only one entry. I successfully use the code below:

  Set dbforms = CurrentDb
    
    strSQL = "SELECT * FROM MainData WHERE [Enterprise ID] = '" & Me.txtAttUser.Value & "'"
    Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)

        
            rstforms.Edit
            rstforms("[Employment Status]").Value = Me.txtAttStat.Value
            rstforms.Update

        
    rstforms.Close
    dbforms.Close

My problem is, when my query has multiple data, it only updates the 1st row of the continuous form. How can I update the other next entries?

Existing code below doesn't works.

    Set dbforms = CurrentDb
    
    strSQL = "SELECT * FROM MainData WHERE [Enterprise ID] = '" & Me.txtAttUser.Value & "'"
    Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)
    cnt = 0

        rstforms.MoveFirst
        Do Until rstforms.EOF
        
            rstforms.Edit
            rstforms("[Employment Status]").Value = Me.txtAttStat.Value
            rstforms.Update
            
        cnt = cnt + 1
        rstforms.MoveNext
        Loop
        
    rstforms.Close
    dbforms.Close

 

[pbaldy]

Try

Do While Not rstforms.EOF

 

[SBBmaster09]

still not works for the 2nd one

 

[pbaldy]

Would the value of txtAttUser be different for the second record?

 

[SBBmaster09]

Yes, it is the unique identifier I used.

 

[pbaldy]

Well then, obviously the second record wouldn't be in the recordset, since it was limited to the first. You mentioned a query; could the recordset be based on the query?

 

[SBBmaster09]

The recordset is based on a query - yes.
The txtAttUser is the name of the textfield with unique identifier.

 

[pbaldy]

Does changing this work?

strSQL = "SELECT * FROM QueryName"

 

[SBBmaster09]

No, because it uses the where clause to find the txtAttUser on updating which record.

 

[SBBmaster09]

I have been reading about recordsetclone and bookmark, is this something which can help me, i dont know how to use that thing,

 

[SBBmaster09]

I am getting an error with the code below:
Error: "You cant go to the specified record."

What can be an error?

    Set dbforms = CurrentDb
    strSQL = "SELECT * FROM MainData WHERE [Enterprise ID] = '" & Me.txtAttUser.Value & "'"
        
        Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)
        Do Until rstforms.EOF
            
            rstforms.Edit
            rstforms("[Employment Status]").Value = Me.txtAttStat.Value
            rstforms.Update
            
            DoCmd.GoToRecord , , acNext
        Loop

 

[moke123]

after this line
Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)
add..

rstforms.movelast
msgbox rstforms.recordcount

and see how many records you are returning in your recordset.

also how many records have the same value in - txtAttUser ?

 

[pbaldy]

Is there a way to identify which records from the query are displayed on the form (something that can be used as a criteria)? If not, you're probably going to have to step through the form's records directly.

 

[Cronk]

The continuous form is presumably based on a query using fields from table MainData. If the OP has Me.txtAttUser bound to a unique ID field, the recordset will only have one record which will be updated.

I think we need to know the fields in the continuous form recordsource and the criteria/filter being applied.
[FONT=&quot][FONT=&quot][/FONT][/FONT]

 

[SBBmaster09]

after this line
Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)
add..

rstforms.movelast
msgbox rstforms.recordcount

and see how many records you are returning in your recordset.

also how many records have the same value in - txtAttUser ?

Hi Moke123,

still nothing change,

 

[moke123]

Quote:
Originally Posted by moke123 View Post
after this line
Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)
add..

Code:
rstforms.movelast
msgbox rstforms.recordcount
and see how many records you are returning in your recordset.

also how many records have the same value in - txtAttUser ?
Hi Moke123,

still nothing change,

did you get a msgbox telling you how many records were in your recordset?

 

[SBBmaster09]

I dont need to display the count, I need to record each lines.

 

[moke123]

we realize that you dont want to display the count. What we are trying to determine is how many records are being returned in your recordset.