?
Solved

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

Posted on 2011-05-01
11
Medium Priority
?
540 Views
Last Modified: 2012-05-11
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!!
0
Comment
Question by:wlwebb
  • 7
  • 3
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35500286
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
 

Author Comment

by:wlwebb
ID: 35500373
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35500691
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:wlwebb
ID: 35500818
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35500857
ok, try this one. post back your comments
GLtestdb.accdb
0
 

Accepted Solution

by:
wlwebb earned 0 total points
ID: 35500876
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35500907
check this one
GLtestdb.accdb
0
 

Author Comment

by:wlwebb
ID: 35500936
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
 

Author Comment

by:wlwebb
ID: 35501191
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
 

Author Comment

by:wlwebb
ID: 35501233
Had to delete my Acctnt COA and reimport.  Don't know why but once I did that it now works?????
0
 

Author Closing Comment

by:wlwebb
ID: 35704558
Cap1 is truly and expert savant
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question