Update field in a table using VBA ? (1 Viewer)

[Dogz]

I need to update a field in a table based on what another field is

Now_month Count
1 1
1 2
1 3
1 4
1 5
2 1
2 2

What I need to do is insert incremental values in the count field based on what the now_month field value is. When the now_month field changes the counter needs to reset and then start counting again + inserting that value into the table.

I assume that the best way to do this is using VBA ?

Any help much appreciacted !!!!!

 

[Ally]

Hi - here's a rough way of doing it. There may be easier or tidier ways but Col worked this out and just did a lesson with me and he's just gone home so left me to post it!

Function UpdTblField()
    Dim db As Database
    Dim r As Recordset
    Dim x As Integer, y As Integer, z As Integer

    Set db = CurrentDb
    Set r = db.OpenRecordset("NameOfYourTable")
    r.MoveFirst
    
    x = 0
    y = r.Fields("NameOfFirstFieldInTable")
    z = r.Fields("NameOfFirstFieldInTable")
        
Do While Not r.EOF
    r.Edit
    z = r.Fields("NameOfFirstFieldInTable")
10: If y = z Then
        x = x + 1
        r.Fields("NameOfSecondFieldInTable") = x
        r.Update
        
    Else
        y = z
        x = 0

        GoTo 10

    End If

    r.MoveNext
    
Loop

End Function

 

[Dogz]

Thanks for that.

Just one or two problems.

I have created a query to run the function :

Update sample
Set count_em = UpdTblField ()

When I run the query I get the following error.

Compile Error : User defined type not defined.

Dim db As Database is hightlighted as the line causing the error.

Any ideas or am I doing something stupid ?

Cheers

Dave

 

[Pat Hartman]

When using DAO in an A2K database, you need to qualify DAO object names and make sure that you have a reference to the DAO 3.6 library.

Dim db As DAO.Database
Dim r As DAO.Recordset

 

[Dogz]

Cheers Pat. Thats solved that problem. I didn't realise that you had to qualify the object name with DAO. I was scratching my head over that this afternoon.

The query now runs and updates all rows but with blanks.

I've attached a small database so that you can see what I have done and check that I haven't made any stupid errors.

Cheers

Dave

 

[ColinEssex]

The code is fine. You needn't run it from an Update query because its already updating within the code.

I ran it direct from the Function and its ok, I also ran it from a Command button and its ok. Just depends where you need it to run from really.

(sorry about the DAO business, I did it in Access97)

Col

 

[Dogz]

Got it working now - Many thanks for all your efforts guys - Appreciate it !!!!