Want to get the selected item in an Access Form combo box to display original table field in another text box

Hi everyone.
I am creating a "Translation Table" where a client's Chart of Accounts is assigned to the Account's Chart of Accounts (No, they don't use the same numbering systems)

I have two tables, One is tblCOA for the Client's COA and the other is tblAcctntCOA for the Accountant's Chart of Accounts

In the Client's tblCOA I have a field where you select the Accountant's COA.  I have a relationship setup between the two tables.

I then created a query of tblCOA to get a list of all the Client's Accounts that hadn't been assigned to the Account's Chart of Accounts.

I am using that query of unassigned accounts to create a form for the user to select the Accountant's correct GL Account Number.

I have an Access Form created from the query of Unassigned accounts where I have an unbound combo box and an unbound text box.  The user selects the Accountant's GL Acct in the combo box and I want to get the info from that combo box to populate a text box with GL Account's Description from the original table.

The form is created from a query of a Client's Acct List.  That query is qryCOA_Accts_Unassigned

The combo box is cboAcctntGLAcctSelected
It's Row Source is:
SELECT [qryAcctntCOA].[Acctnt GL Nbr], [qryAcctntCOA].[Acctnt GL Descriptions], [qryAcctntCOA].[Acct Type] FROM qryAcctntCOA;

The text box is txtAcctntGLAcctSelected

On the unbound combobox on the Event Tab in On Change I tried:
=[txtAcctntGLAcctSelected]=[tblAcctntCOA].[Acctnt GL Descriptions]

It doesn't like that.  I get The expression On Change ....error.  The object doesn't contain the Automation object 'tblAcctntCOA'.

Help!!
wlwebbAsked:
Who is Participating?
 
wlwebbAuthor Commented:
Ok, why the difference?  I notice I can even select the Acct# in the box that wasn't allowing me to select it before, which is why I used the unbounds.  (May have implications of other stuff in my full db is why I ask)
Also, the bottom Right field just displays the same acct# selected in the field just to it's left.  What I was trying to get there was the words Description of what that Account is.
0
 
peter57rCommented:
Looks like you want the second column from the combo......

me.txtAcctntGLAcctSelected= me.cboAcctntGLAcctSelected.Column(1)


I'm not quite clear why these controls are unbound if you want to create or store the results.

If they are unbound, and you display more than one record at a time (in a continuous form, say) then all records will show the value of the current record.
0
 
wlwebbAuthor Commented:
I agree that I too don't understand why I have to use unbound fields to be able to select the answer I want to update to the table. However when I just use the field Acctnt GL Accnt# from the original query I am not permitted to SELECT and save anything.  Doesn't make sense given the query and tables are linked. Is there a setting or something that defaults to prevent this?   Remember the info for the Form comes from a query and the info for that is from two different tables
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
you may have an nonupdateable query case here.
see this link for possible cause and solution

http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx

in most cases, setting the form's recordset type property  to Dynaset (Inconsistent Updates) solves the problem.

hth

0
 
wlwebbAuthor Commented:
Thanks for attempting.  Still get the same problem even though form is set to Dynaset.  I also tried using the formula Peter suggested.  I get the ?Name?

The last 3 fields of the form is where the problem is.

So i dumped needed stuff to a db that I've attached.  It is an Access 2007 db.  There are No macros in this db.
 GLtestdb.accdb

Any help or suggestion is appreciated.
0
 
Rey Obrero (Capricorn1)Commented:
ok, try this one. post back your comments
GLtestdb.accdb
0
 
Rey Obrero (Capricorn1)Commented:
check this one
GLtestdb.accdb
0
 
wlwebbAuthor Commented:
That is perfect.  But still don't understand the difference.  I saw the changes you made to my query.  Did the same in mine worked just like yours but the form still wouldn't let me select the Acctnt GL Acct# field in the form.  So, I looked at every line of the field's property sheet.  I don't see anything changed.  ??????
0
 
wlwebbAuthor Commented:
Cap1
Your Db works.  However when I import that form and related query to my DB I still get the same result.  It won't let me select a value in the dropdown of form field Acctnt GL Acct#?  Don't understand the difference.
0
 
wlwebbAuthor Commented:
Had to delete my Acctnt COA and reimport.  Don't know why but once I did that it now works?????
0
 
wlwebbAuthor Commented:
Cap1 is truly and expert savant
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.