Stumped on looping (1 Viewer)

csh2013 · Mar 13, 2014

I've been searching for an answer to my specific problem, but I've finally conceded defeat and need some assistance (I'm still relatively new to VBA). I have a database that tracks exceptions for different loans. The loan record is in one table and the exceptions are in a different table because there can be multiple exceptions per loan. These tables are tied together by the loan number.

The end users, however, have a form to key the loan record and any accompanying exceptions. That form ("Loan Tracking Form") is using a subform (the Exceptions table) and, like the tables, are tied together by the loan number. In a perfect world, we would enter the correct loan number every time. However, if a user has to change the number of the loan, I need to find the appropriate code to loop through my Exceptions table, identify all exceptions that were tied to that loan record, and change the loan number to match the new loan record so they don't become detached from the record. Is that enough information for those of you with vastly more knowledge than my own to help me?

jdraw · Mar 13, 2014

This is a little scary on first read

However, if a user has to change the number of the loan,

What exactly are the reasons/conditions that require a LoanNumber to be changed? Is LoanNumber the PK of the LoanTable?
Could the user select and record the OriginalNumber and the revisedNumber and then someone else confirm that a change is needed? Seems it's too easy to modify a loan --but that's a comment after only a quick look at the post.

csh2013 · Mar 13, 2014

Primarily because of fat-fingering errors. It's not often this happens, but I need something better than what I have happening now. Also, to answer your other question, the loan number is not the primary key in the table. Maybe I was too vague in my description, so let me add a bit more detail. Each loan record is auto-assigned a uniquely identifying number string, which is a combination of the date and the loan number. This unique number is the primary key and will not change after it has been established. The tables and forms are jointly tied together by this unique number and the loan number. So, what I have users doing right now, until I found a solution, is they change the loan number on the form, which detaches all of the exception records since they're tied by both. They click on a "Change" Command button, which does a DLookup function to find all exceptions that have the same unique number string as the current loan record and then they have to manually change the loan number on all the exceptions as well. It's open to error, it's open to be forgotten, it's more time consuming, etc. So, I'm just looking for a loop that will filter through in the same way (identifying the exceptions that have the same unique number string as the original loan record) and modify the loan number without human intervention.

willknapp · Mar 13, 2014

Exactly how are your tables tied together? That is, what is the relationship between them? If your tables are tied together by the loan number - meaning the loan number is the primary key in the loan table, and a foreign key in the Exceptions table - then changing the loan number in the Loan Number table should change the loan number in the exceptions table. Regardless of what the primary key is on the Loan Number Table, as long as Loan Number is a unique value in that table, your Exceptions table should link to that primary key.

To answer your loop question, though, you could just do something like this, assuming there is a table called "Exceptions" with a field called "LoanNumber":

Code:

Public Sub ChangeNumber(lngOldNumber as Long, lngNewNumber as Long)
 
Dim db as DAO.Database
Dim rs as DAO.Recordset
 
Set db = Access.CurrentDB
Set rs = db.openrecordset("SELECT * FROM Exceptions WHERE LoanNumber = " & lngOldNumber)
 
With rs
    .MoveFirst
    Do
        .Edit
        !LoanNumber = lngNewNumber
        .Update
        .MoveNext
    Loop until rs.EOF
    .Close
End With
Set rs = Nothing
Set db = Nothing

You would then use the following statement to change, for example, Loan 12345678 to Loan 87654321:

Code:

ChangeNumber 12345678, 87654321

jdraw · Mar 13, 2014

In plain English what do you do now to associate the exceptions with the proper (whatever that is Orig/revised/mistyped) Loan Number?

How is your database structured? Can you post a jpg of your tables and relationships? You'll have to zip the file because of your post count.

csh2013 · Mar 13, 2014

Apologies. I edited my second post to include additional information while you were replying. I'm not able to create a zip file at work. If you need to see that, maybe I'll be able to do it from home. Also, I can see I did not include enough information from the start, so hopefully I can clear it up here.

One of the pieces of information I added in my edited post is that I have each loan record and exception being auto-assigned a unique number string when the information is entered in the form. The unique number string is a combination of today's date and the loan number. The unique number string serves the purpose of differentiating a re-finance of loan number 123456 that occurs today, versus the instance of it when loan 123456 was first booked 3 years ago. That unique string the primary key in both the loan table and the exception table and, my mistake, this is what the TABLES are joined by.

Once you get to the form, the loan form is the main form, but is joined to its subform (the exception table) by both the unique number string and the loan number. So, when a user changes a loan number, the exceptions no longer contain the same loan number, but they DO contain the same unique number string. That's how users are editing the exceptions now. Once they change the loan number, they click on a command button that does a DLookup of all exceptions that contain the same unique number string as the loan record and then they manually update the loan number on each exception. So relationships look like this:

Loan Table------(joined by unique number)------Exceptions Table
|
|
Loan Form------(joined to Exceptions Table by uniq no. and loan no.)

jdraw · Mar 13, 2014

???

The unique number string is a combination of today's date and the loan number.

when a user changes a loan number, the exceptions no longer contain the same loan number, but they DO contain the same unique number string.

I'm confused.

csh2013 · Mar 13, 2014

The unique number string doesn't change once it's assigned. So, for instance, loan number 123456 is established today. It will be auto-assigned number string:

031314123456

All of the exceptions entered on this loan record will also contain that same string. User realizes they made a mistake and needs to key the loan number to 123457. Loan number has changed on the loan record, but not the exceptions. However, the unique number string for BOTH remains 031314123456. So, in the the After Update option of the loan record, I need the VBA code will loop through all Exceptions, locate all with unique string 031314123456 and change the loan number from 123456 to 123457.

jdraw · Mar 13, 2014

You could try this code in your query designer SQL View.
It only does a READ so not updates are effected. This will show exceptions related to loans. You may have to change the field/table names to match your values.You can add more fields to display if necessary.

Code:

Select Loans.UniqueNumber
,Exceptions.Uniquenumber
,Exceptions.LoanNumber
,Loans.LoanNumber
From Loans Inner Join Exceptions ON
  Loans.UniqueNumber = Exceptions.UniqueNumber

Try it and post back.

To get only those records that "probably need changing", you could add

WHERE Loans.LoanNumber <> Exceptions.LoanNumber

csh2013 · Mar 17, 2014

Apologies on the delinquent reply. I think I have this resolved without needing the loop function at all, thanks to some of the information provided on this page that made me re-think the structure of this database. Most of what I know about Access is self-taught from reviewing existing databases. So, my normalization wasn't the best in this scenario. I'm actually removing the loan number entirely from the exceptions table because that data was directly filling from the loan table anyway. The tables are still linked together via the unique number I have Access auto-assigning to each table, so having the loan number in both table was redundant.

Thank you, jdraw and willknapp for your assistance!

Stumped on looping (1 Viewer)

csh2013

Registered User.

jdraw

Super Moderator

csh2013

Registered User.

willknapp

Registered User.

jdraw

Super Moderator

csh2013

Registered User.

jdraw

Super Moderator

csh2013

Registered User.

jdraw

Super Moderator

csh2013

Registered User.

Similar threads

Users who are viewing this thread