Updating a field

[JohnBoy]

Hi All,

Having a little brain ache with a small update procedure, I have a field in each line of data that shows the visit number,

I casn set the Number 1 visit but thereafter nothing I can think of seems to work for the 2's and 3's etc, my latest attempt is

Private Sub UpdateVistRecord()
On Error GoTo Err_TagRecord

Dim Myrs As DAO.Recordset
Dim MyDb As DAO.Database
Dim VisitTag As Integer
Dim CurID As Integer

Set MyDb = CurrentDb()
Set Myrs = MyDb.OpenRecordset("tblMyTable", dbOpenDynaset)

With Myrs
CurID = !VistTag

Do While Not .EOF

If !CurID = 1 Then
.MoveNext



Else

If !CurID = "" Then
.Edit
!CurID = 2
.Update
.MoveNext

End If
End If

.MoveNext

Loop
End With

Exit_TagRecord:
On Error GoTo 0
Myrs.Close
Set Myrs = Nothing
Set MyDb = Nothing
Exit Sub
Err_TagRecord:
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume Exit_TagRecord
End Sub

Also is there a way that you can check a field value say CustID gainst the one before it like you can in excel

Cheers

JB

[RuralGuy]

Is this table the RecordSource of a form you are using?

[JohnBoy]

HI RG, we speak again, no its not a form based bit of code but does apply to a recordset I use for some reports, if you recal you helped me with the same recordset a while back looking at allocating a flag where there was no R code, all I want to do is to look at the record set and where it has a 1 move to the next field, if it is blank I would like it to update to 2, and then 3 etc etc, now the main kick in all of this is that I need it to check to see if the customer ID is the same as the previous one so I know its allocating another visit to the same customer but I was just trying to get it to do the first bit first so to speak then I would work on the the check the custID bit after that.. any suggestions (polite ones) would again be gratefully received..

[RuralGuy]

So the records have already been created and you want to create some code to assign the proper visit number to the field, correct? Is it important to know the visit number?

[JohnBoy]

Unfortunately yes it is needed as the vist number will be used in queries to pull various reports etc, again I can do it in Excel by using the offset function etc to test for various criteria but the users dont want to outload to excel, run the updates and import back which is understandable so I just struggle to get my head around how to do it in Access, I have looked at CASE Statements as a possible means to an end but not got far with it as yet, if you could tell me of a way to test one CustID against say the one before or the one after then I may be able to figure it out as thats the bit that is stumping me,

I also thought about an SQL statement to update but again it's how do you test one Field against the one before or after it..

Cheers

JB

[RuralGuy]

Will you be doing this often or is it a one shot catchup procedure?

[JohnBoy]

Its something that will be done every week or so, the DB imports certain data from the main SQL source tables and then does a series of updates before finally appending the new records to the report table (via excel at the mo)

[RuralGuy]

Your table Has both a VistTag field and a CurID field?

[JohnBoy]

Thats correct, as I said I want to look at the CustID to test it to see if it's the same as one before or after etc and allocate a visit number as appropriate, the table is sorted on CustID by the way,

[RuralGuy]

Can you explain what the two fields are used for?

[JohnBoy]

Sorry my mistake, no the curid bit was my bad attempt at trying to store a value to match against another value so disregard that, there is a field that is called CustID and a field that is called VisitTag, they are the ones I want to use for this update... sorry again for the confusion..

JB

[RuralGuy]

Do you also have a DateTime field so you can get the sort correct?

[JohnBoy]

Thats afirmative, it sorts by CustID and then DTTM

[RuralGuy]

Sorry but I must leave for a few hours. I'll check in when I get back. Show me 5 sample records. 3 from one CustID and 2 from the next. Just the fields we are concerned with.

[JohnBoy]

No probs and thanx for your help so far,

CustID....................DTTM............VisitTag
ABC123456.............24/01/2010........1
ABC123456.............31/01/2010........2 (this is what I want it to be)
ABC123456.............28/02/2010........3 (Again what im looking to do)
BCA321654.............12/12/2009........1
BCA321654.............27/01/2010........2

and so on and so forth...

Thanx again

JB