Solved

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

Posted on 2008-10-08
11
1,064 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:gdunn59
  • 4
  • 4
  • 2
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22671971
how are you importing the file?
post the codes here
0
 

Author Comment

by:gdunn59
ID: 22674067
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22729926
it will help if you can attach sample db and the excel file.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 22730483
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
 

Author Comment

by:gdunn59
ID: 22791723
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 38

Expert Comment

by:Jim P.
ID: 22795593
Good luck. Hope it all goes well.
0
 

Author Comment

by:gdunn59
ID: 23035922
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 23039199
Glad to hear it went fairly well. Let us know.
0
 

Author Closing Comment

by:gdunn59
ID: 31504340
jimpen:

This worked.  Thanks very much!
gdunn59
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23126516
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now