VBA Code to loop through a recordset and change a field based off of what another field contains

I have a procedure that imports a file, and during the import, I need VBA Code that will loop through a recordset and check all records with the same Acct # to see if the records that have a "ContactTypeID" as a "4", and the "isPreferredContactInfo" field is a minus 1 (-1) and if so, change the existing record's "isPreferredContactInfo" to a zero (0), and change the latest record's "isPreferredContactInfo" field that is being imported, to a -1.

Can this be done?  I have attached a document with sample data, and explained in the attached document what I'm trying to accomplish.

I have attached a word document with a sample of the data that I'm referring to.

Thanks,
gdunn59
Sample.doc
LVL 1
gdunn59Asked:
Who is Participating?
 
Jim P.Commented:
Below is all aircode. Run on a test copy of your data.

You'll have to change the table name in the query.

I'm not even going to give the full comment on using special characters in field names. Just saying bad juju.

Public Function Update_Preferred_Conatact_Info()
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
Dim CurrAcctNum As String
Dim FirstPass As Boolean
 
'Change the table name in the query!!
SQL = "SELECT [Acct #] As Acct_Num, IsPreferredContactInfo,  dtRecordAdded " & _
    "FROM MyTable " & _
    "WHERE ContactTypeID = 4 " & _
    "ORDER BY [Acct #], dtRecordAdded DESC"
 
'Opening the query
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
 
'Checking for data
If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Fuction"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If
 
CurrAcctNum = "Blank"
 
'Updating the records
Do Until RS.EOF = True
    If CurrAcctNum <> RS!Acct_Num Then
        CurrAcctNum = RS!Acct_Num
        With RS
            .Edit
            !IsPreferredContactInfo = True
            .Update
        End With
    Else
        With RS
            .Edit
            !IsPreferredContactInfo = False
            .Update
        End With
    End If
    RS.MoveNext
Loop
 
Set RS = Nothing
Set DB = Nothing
 
End Function

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
how are you importing the file?
post the codes here
0
 
gdunn59Author Commented:
capricorn1,

I have attached a word document with the code.  There are two functions in it that process for the import.

Thanks,
gdunn59
SentCode.doc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
it will help if you can attach sample db and the excel file.
0
 
gdunn59Author Commented:
All,

Sorry I haven't had a chance to get back to this in a bit.  Have been extremely busy because I am going to be out for surgery tomorrow, and probably not returning until Tuesday of next week.

I will check back in then.

Thanks,
gdunn59
0
 
Jim P.Commented:
Good luck. Hope it all goes well.
0
 
gdunn59Author Commented:
Hey everyone,

Sorry I haven't been in touch lately, just been extremely busy since getting back from surgery.  Surgery went well.

Anyway, I will try to get back on board with this sometime in the next couple of weeks.

Thanks again and have a Happy Thanksgiving!

gdunn59
0
 
Jim P.Commented:
Glad to hear it went fairly well. Let us know.
0
 
gdunn59Author Commented:
jimpen:

This worked.  Thanks very much!
gdunn59
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.