Populating a field automatically OR allowing manual entering of data

[duckster]

I have a simple table with two fields in that table called:

1. BankName
2. BankNumber

Each bank has a bank number. For example Bank XYZ and all of its branches have the same Bank Number 123. There are 5 banks I have listed in a combo list under the BankName field. I can also type in a different bank in that same field if it is not listed in the combobox list.

Now, I want the BankNumber to automatically populate based on what I choose under BankName. If the BankName is manually entered (for banks that are not in the combobox), or if the BankName field is blank, I want the BankNumber field to be able to enter a number manually.

For example, if I go to the BankName field and under the combobox I select Bank XYZ, i want the BankNumber field to automatically populate as 123. If the BankName has a bank name that was manually entered, i want BankNumber field to allow me to manually enter a number.

Thanks for ur help. I couldn't figure this simple request out.

 

[llkhoutx]

Put the Bank Number as an additional column in the rowsource of the combo box.

If you have three columns, ID, BankName, BankNumber, on the AfterUpdate event of the combo box

me.BankNumber = Me.ComboBoxName.column(2)

 

[duckster]

^^ I'm not sure what you mean. Right now I have the BankName AND BankNumber as separate fields in one table.

For the BankName field I have the display control set to "Combo Box", the Row Source Type as "Value List", and in the Row Source, I've manually entered different bank names for the value list (i.e. "ABC Bank"; "XYZ Bank".

The BankNumber name is basically a text box. I want the BankNumber field to automatically populate IF one of the values in the BankName field is chosen. If one of the values in the BankName field is entered manually, the BankNumber field should not automatically populate; rather, it allows for manual entering instead.

Thanks for your help!

 

[duckster]

^^ Thanks Pat, I will add that option. I'm still stuck though with my original problem. Can't seem to get the SQL right, and not sure where to put the it.

 

[ScottGem]

I'm not clear what the problem is. Are you entering data in this same table or in another table? If you are entering into the same table then you really should have 2 tables:

tblBanks
BankID (PK autonumber)
BankNoumber (if using the BankID isn't sufficient)
BankName

tblBankBranches
BranchID (PK autonumber)
BankID (FK)
BranchName (or Address or whatever)

You would then have a form bound to tblBankBranches with a Combobox where you select the BankName but store the BankID.

If you are entering data in another table, then you should have a similar combo bound to the BankID field buy listing BankName (the combobox wizard will create such a combo). You don't need the BankName in that table since you can get it by a join to the Banks table.