Updating a field

[RuralGuy]

JB, did you ever get this thread resolved?

[JohnBoy]

Hi, no as yet, I have been trying to find a way of doing the check one custid value against the previous but failed to date, i'm probably looking at it the wrong way knowing me, sorted some other issues though so not all bad ha ha ha..

[RuralGuy]

This is <<<AIR CODE>>> so it may have some errors but a function similar to the following in a standard module should give you what you want.

Public Sub UpdateVistRecord()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim CurID As String
   Dim Visit As Integer
   Dim Phase As Integer
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("qryYourQuery", dbOpenDynaset)
   '-- This assumes the query is sorted by CustID (a string/text field) and then the DTTM field (a DateTime field)
   '-- and returns at least the CustID field and the VisitTag field
   With rs
      While Not EOF(1)
         If !CustID <> CurID Then
            '-- New Customer
            CurID = !CustID
            Visit = 1
         Else
            '-- Still the same Customer
            Visit = Visit + 1
         End If
         .Edit
         !VisitTag = Visit
         .Update
         .MoveNext
      Wend
      .Close
   End With
   Set rs = Nothing
   Set db = Nothing
   MsgBox "Process complete!"
End Sub

[JohnBoy]

HI RG many thanx for this, I will give it a go and let you know the result, I may also have a cpl of questions for you once I read through it etc so I can get my head round how this stuff works a bit better and stop bothering you good people to much, on the thread of questions I have a general question about access you may be able to enlighten me on, I am working on another small project that revolves around reporting, do you know if there is a limit at which access reporting capabilities become shall we say a bit sketchy, I have built a report that contains a lot of data from a query on each page, however it seems to "Forget" to bring in odd records and shows certain flags for all records when it should not, when I went to use the wizard it told me there were to many fields and to basically loose some but if I build the report a bit at a time it lets me but has these little nervous breakdowns.. any ideas..??
Cheers JB

[JohnBoy]

HI,
I have run the code and it sort of works however a couple of wee fobiles,
If I run as is I get Runtime error 52 bad file name or number and the line While Not EOF(1) highlighted,
If I remove the (1) it highlights again and states Compile error Argument not optional,
If I put a . before EOF it runs but sets all flags to 1..

Also am I correct in saying that you use the CurID as a temp in memory to hold the current CustID value and it checks it against the one it is currently on..??

Cheers in advance JB

[RuralGuy]

Change EOF(1) to .EOF

[RuralGuy]

Do you know how to single step the code so you can watch and see what is happening?

[RuralGuy]

HI RG many thanx for this, I will give it a go and let you know the result, I may also have a cpl of questions for you once I read through it etc so I can get my head round how this stuff works a bit better and stop bothering you good people to much, on the thread of questions I have a general question about access you may be able to enlighten me on, I am working on another small project that revolves around reporting, do you know if there is a limit at which access reporting capabilities become shall we say a bit sketchy, I have built a report that contains a lot of data from a query on each page, however it seems to "Forget" to bring in odd records and shows certain flags for all records when it should not, when I went to use the wizard it told me there were to many fields and to basically loose some but if I build the report a bit at a time it lets me but has these little nervous breakdowns.. any ideas..??
Cheers JB

I'm not familiar with what you are describing but there are certainly limits to what the wizard can accomplish.

[JohnBoy]

Hi RG.. well all working thank you very much, not sure why it diod not work the first time as I did what you said before I replied last time...??? anyway many thanks again for all of your help... JB

[RuralGuy]

Excellent! I think this thread is ready for you to follow the link in my sig and mark as Solved.