[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
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
Medium Priority
?
1,078 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
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22671971
how are you importing the file?
post the codes here
0
 
LVL 1

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22729926
it will help if you can attach sample db and the excel file.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 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
 
LVL 1

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
 
LVL 38

Expert Comment

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

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
 
LVL 1

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

830 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