Access DAO

[Tark221]

Hi there,

I have the following code below which will update any of the fields in a recordset.

Dim dbsHelpdesk as DAO.Database
Dim rstAbsence as DAO.Recordset
Set dbsHelpdesk = currentdb
Set rstAbsence = dbsHelpdesk.openrecordset("Absence")
rstAbsence.MoveFirst
Do until rstAbsence.EOF

[COLOR="Orange"]If rstAbsence!AbsenceID = 2 Then[/COLOR]
rstAbsence.Edit

I havent wrote the rest just because I know it's fine. The issue I'm having is with the line in orange which is used to identify which record needs updating. If I simply write 2 in the code it will find that Absence ID. If I try to use a control instead say a textbox1.value it doesn't work. The strange thing is when I run line by line the textbox1.value will show the number 2 but it still won't accept it. Is there a difference between simply writing the number 2 and using a control which is storing the value 2 in.

Thanks for any help

 

[Galaxiom]

Are you sure you need a recordset to do this job?

If so use a query with the condition when you open the recordset . It will save retrieving records you don't need.

 

[Tark221]

So create an update query and set the criteria to a control and update them that way?

 

[Tark221]

That worked thank you !

 

[gemma-the-husky]

if you want to reference a column of a recordset via a variable then the alternative syntax is as follows, given your example

rst!absenceid OR
rst.fields("absenceid")

given the latter construct, we can instead use a variable

rst.fields(myar)

hope this helps for the future

 

[Galaxiom]

BTW You can make code easier to type, edit and read by using the With construct.

Set rstAbsence = dbsHelpdesk.openrecordset("Absence")
With rstAbsence
    .MoveFirst
    Do While Not .EOF

        If !AbsenceID = 2 Then
            .Edit
            etc
            .Update
        End If
    Loop
End With