help on filling in field

[jclu]

Thanks for all the help to those who have answered my question! Please excuse the fact that I've been asking questions and not really answering any, but currently I'm more a beginner when it comes to the advanced stuff in access, such as creating vb modules and sql statements to do more than the basic functions. I'm actually looking at getting a vb (or vba) book and an sql book to get a grasp of the fundamentals - anyone have any good suggestions? I'm also not planning to just post questions and never contribute; it just might be a little bit before I have enough knowledge to contribute

Anyway, I did have another question: I have three fields in a table that specify the currency code for an account. The three fields are 1)Client code (called MFC_Code), 2)ISO code (called ISO_Code), and 3)CIBC code (called CIBC_Code). The table in question is used to link up CIBC bank accounts with the accounting program's ledger numbers, which are unique to not only each bank account, but each currency that the account operates in.

I've created a form to facilitate easier entering of new records in the table. I have also created a second table for the currencies used, which specifies the currency name, and the three types of codes used (thus, there are 4 fields in the second table). The form for the first table is set so that the three fields in question (MFC, ISO, and CIBC) are combo boxes and pull their values from the second table. This way, there is only one choice for each currency.

I want to set up the three fields so that when the user enters data on the form in one of those three fields, the other two fields auto-populate with the corresponding data based on the second table. For example, the second table has an entry: Canadian Dollar, C, CAD, CAD (Currency_Name, MFC_Code, ISO_Code, CIBC_Code). I want to be able to, when entering a new record on the form, only type in C in the MFC_Code field, and have the ISO and CIBC fields auto-populate with the CAD value, which they can pull from the second table.

This probably will require vb code, but again, I'm very much a noob with vb code in ms access. Any help is greatly appreciated. Thanks.

 

[ajetrumpet]

want to be able to, when entering a new record on the form, only type in C in the MFC_Code field, and have the ISO and CIBC fields auto-populate with the CAD value

jclu,

You need to populate your combo box records with all the field values that you're going to use in your form (MFC, ISO and CIBC). In the combo wizard, specify the fields, then according to how they are displayed in the dropdown, only make the field you want for that specific combo visible. You do this by modifying the column widths (in properties) in such a format.... 0";1";0" (left to right column order - This shows only the second field in your box.)

Example - if the "MFC_Code field" combo box has width settings like the one above, and MFC_code is the second column (width 1"), type this into the code builder (VB module) in the "After update" line in properties....

Me.[ISO combo name] = Me![MFC combo name].column(1)
Me.[CIBC combo name] = Me![MFC combo name].column(3)

This will populate the other combos on your form (and any other box you need as selection criteria)...you may notice too that when your typing this code into a module, the VB editor automatically gives you a list of objects after the DOT(.) notation in your code typing. There is a lot to learn about the way code is written by this. See what you can learn from it. It's a good tool. (It's just another variation of Visual Basic, backwards when you compare it to Excel!)

Here's the Microsoft article that gives the technique...
http://support.microsoft.com/kb/319482/en-us

 

[jclu]

Thanks ajetrumpet! What does the Me identifier refer to - the local form/report that the code is attached to, or even more specifically the local control that the code is attached to? What about the difference between using the dot/period (.) and exclamation mark (!) - what do each of them signify?

 

[jclu]

I tried the code and I'm encountering some problems. To make things simpler, since the ISO field was redundant, I got rid of it in the underlying two tables (the cross-ref table and the currency table). But the CIBC combo box isn't being update automatically. In fact, in the underlying table, the field is blank for that record. Let me try and detail what I have so far. (I may have mixed up the names in my first post, so these are the correct table and field names).

The cross-ref table is called MFC_CIBC_XREF, and the two fields in question are called MFC_CurrencyCode and CIBC_CurrencyCode. The currency table is called CIBC_CurrName&Code, and the two fields in question are called MFC_Code and CIBC_Code. Anyway, I have MFC_CurrencyCode and CIBC_CurrencyCode on the form to be combo boxes, with the following properties:

MFC_CurrencyCode
Row Source: SELECT [CIBC_CurrName&Code].[MFC_Code], [CIBC_CurrName&Code].[CIBC_Code] FROM [CIBC_CurrName&Code];
Column Count: 2
Column Widths: 1";0"
Bound Column: 1

CIBC_CurrencyCode
Row Source: SELECT [CIBC_CurrName&Code].[CIBC_Code] FROM [CIBC_CurrName&Code]; 
Column Count: 1
Column Widths: 1"
Bound Column: 1

And the vb code I have for MFC_CurrencyCode and CIBC_CurrencyCode is the following:

Private Sub MFC_CurrencyCode_AfterUpdate()
    Me.CIBC_CurrencyCode = Me.MFC_CurrencyCode.Column(2)
End Sub

Private Sub CIBC_CurrencyCode_BeforeUpdate(Cancel As Integer)
    Me.CIBC_Account = Me.CustodianAcct & " " & Me.CIBC_CurrencyCode
End Sub

Private Sub CustodianAcct_BeforeUpdate(Cancel As Integer)
    Me.CIBC_Account = Me.CustodianAcct & " " & Me.CIBC_CurrencyCode
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.CIBC_Account = Me.CustodianAcct & " " & Me.CIBC_CurrencyCode
End Sub

In included the code related to CustodianAcct since it's concatenated with CIBC_CurrencyCode and stored in the field CIBC_Account. So basically, CustodianAcct is the bank account name, CIBC_CurrencyCode is the bank code for the currency the account operates in, MFC_CurrencyCode is the client's code for the currency, and CIBC_Account is the bank account name followed by the bank code for the currency. This is done because a particular bank account can operate in different currencies, and each currency is recorded as a separate entry.

But my question is: based on all the above, why isn't CIBC_CurrencyCode automatically being filled in when I select a value for MFC_CurrencyCode? Thanks for the help.

 

[Rabbie]

What about the difference between using the dot/period (.) and exclamation mark (!) - what do each of them signify?

I was always taught that you use ! with names you made up yourself and . with names that access has generated. seems to work OK for me. often it doesn't seem to matter if you use . instead of !

 

[ajetrumpet]

based on all the above, why isn't CIBC_CurrencyCode automatically being filled in when I select a value for MFC_CurrencyCode?

I noticed the "." character instead of the "!" character in the reference (right side of equal sign) portion of the VB subs.

Me[COLOR="Green"].[/COLOR]MFC_CurrencyCode.Column(2)

Do the "!" characters on this side of the equal sign change to a "." after you close the editor?? Just wondering...if they don't, change them to "!" characters.
Also, put a "1" in the column reference instead of a "2" in your code for the control....

Private Sub MFC_CurrencyCode_AfterUpdate()
    Me.CIBC_CurrencyCode = Me.MFC_CurrencyCode.Column(2)[color=red]<---put a "1" here[/color]
End Sub

Also, what about this comment from you...

But the CIBC combo box isn't being update automatically. In fact, in the underlying table, the field is blank for that record

Did you try to select from MFC and not get anything in the CIBC?? If you selected a choice in MFC that is located in a record that holds a null value in the CIBC field, you shouldn't see anything(it populated, but populated the NULL field, which is blank)...so your controls would really be working if that's what you did.

 

[ajetrumpet]

What about the difference between using the dot/period (.) and exclamation mark (!) - what do each of them signify

I believe the "." character refers to "action" type tasks or commands. The "!" character usually refers to groups of items (locations). In general, the "!" can be looked at as a sort of "reference" type character.

*Example = if you "refer" to a control in a query or a report, or any object for that matter, you type in this...

[Object Group]![Object]![source object]

You don't see a "!" after a control because Access has already found the location of the source by []! and []! before that.

You can't tell Access to "look here" for a source of any kind, it has to search through the file and the groupings of items you've created, and you have to tell it to do that too.

If you're still having problems now...I could take a look at the DB if it's not too sensitive. If you've got account numbers in it though, wouldn't be a good idea....just a thought.