Autopopulate field based on other fields

[jclu]

Hi, I have a form bound to a table, to allow easier updating of records and addition of records. The table is used in a financial context, and basically connects particular bank accounts with ledger accounts in the client's ledger program. The table (and thus the form) has three fields that are really concatenations of some of the other fields in the form. For example, there's a field called CustodianAcct that holds the bank account name, and another field called CurrCode that holds the currency that the account is in. One of the three fields I'm talking about is called CIBC_Acct is a concatenation of the two fields I just mentioned, so the data looks like "Bank_Account_Name Currency". This information is used as a filter in various queries and reports, which is why this field was created as part of the table. Currently, when creating a new entry in the table, I manually populate CustodianAcct, CurrCode, and CIBC_Acct. Is it possible to set up the form so that the CIBC_Acct field on the form gets automatically populated once data is entered in the CustodianAcct and CurrCode fields? Thanks.

 

[missinglinq]

If you don't need to actually see it as you're entering a new record, you could use:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.CIBC_Acct = Me.CustodianAcct & Me.CurrCode
End Sub

which will populate the third field when the record is saved. If you want to "see" it as you enter the new record, you put the same thing in the BeforeUpdate for each of the feeder fields:

Private Sub CurrCode_BeforeUpdate(Cancel As Integer)
  Me.CIBC_Acct = Me.CustodianAcct & Me.CurrCode
End Sub

Private Sub CustodianAcct_BeforeUpdate(Cancel As Integer)
  Me.CIBC_Acct = Me.CustodianAcct & Me.CurrCode
End Sub

If you want a space between the two segments, change the code to

Me.CIBC_Acct = Me.CustodianAcct & " " & Me.CurrCode

Good Luck!

Linq

 

[jclu]

Thanks missinglinq; for the feeder fields, why am I putting it in the BeforeUpdate section?

 

[neileg]

You don't need to store this information, though. You can calculate it in a query and any place you want to use a table you can use a query instead. Your form can still show the concatenated data in a control by basing the form on the query, you just don't need to save it.

 

[jclu]

Thanks neileg, but in this case I do want to store the info. I was trying to save myself (or another user) time when creating new entries in the table, by not having to fill in the CIBC_Acct field, since it's really the CustodianAcct & CurrCode appended together.

 

[neileg]

Thanks neileg, but in this case I do want to store the info. I was trying to save myself (or another user) time when creating new entries in the table, by not having to fill in the CIBC_Acct field, since it's really the CustodianAcct & CurrCode appended together.

Two things
1) You don't need to store it. I have no idea why you want to hold redundant data, you're simply wasting space. You would also have an issue if you discovered a data entry error in one of the original fields. Correction would require amending both the original data and the concatenated field.
2) I wasn't suggesting that the user enetered the concantenated data. The query would do that automatically.

 

[jclu]

Two things
1) You don't need to store it. I have no idea why you want to hold redundant data, you're simply wasting space. You would also have an issue if you discovered a data entry error in one of the original fields. Correction would require amending both the original data and the concatenated field.

I see your point; I think I'm hesitant to delete the field because I didn't originally design and set up the database. The db is actually part of a process to take a csv file of financial txs on certain bank accounts (the txs are posted by the bank itself), and then upload them into the ledger software. All these txs used to be done by hand, and this db along with several Excel macros/spreadsheets were created to automate this. Fortunately for me, and unfortunately for the client, the person before me never fully finished automating for all the types of txs, but did however set up the initial macros and database well. Well enough that all I have to do is really modify and update the queries/scripts/reports and either update the macros or create new ones based off the existing ones to automate for the other txs. I have noticed certain fields in some of the tables that seem redundant in that they are not being used in the queries, etc. As I said I'm hesitant to delete them because I don't see my not having figured out the reason the person before me created those fields as justification to decide those fields are useless.

 

[neileg]

I regard what you have said as a genuine pragamatic reason for breaking the rules of database design!

In that case, missinglinq's advice is almost there except I would put it in the AfterUpdate event of the other controls.

 

[jclu]

What's the difference logically in using the BeforeUpdate or AfterUpdate event? And for better or worse, if you see my last post in "help in filling in field", I decided to delete the ISO Currency Code field and the CIBC field, since they did seem for all intents and purposes to be duplicates of CIBC Currency Code and CIBC_Account, respectively. For the CIBC_Account field, it actually isn't redundant because the csv file that we import is first run through a macro that among other things, creates a column (in the excel spreadsheet) that concats the bank account name and the currency code the account operates in. Because of this, the table that contains the daily imported csv file (or rather the cleaned-up excel spreadsheet based on the csv file), has such a field. Thus, it's easier to use the two identical fields in the two different tables to relate the two tables for queries and the like.