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]
It doesn't like that. I get The expression On Change ....error. The object doesn't contain the Automation object 'tblAcctntCOA'.
Help!!
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]
It doesn't like that. I get The expression On Change ....error. The object doesn't contain the Automation object 'tblAcctntCOA'.
Help!!
ASKER
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
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
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
ASKER
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.
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.
ok, try this one. post back your comments
GLtestdb.accdb
GLtestdb.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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. ??????
ASKER
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.
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.
ASKER
Had to delete my Acctnt COA and reimport. Don't know why but once I did that it now works?????
ASKER
Cap1 is truly and expert savant
me.txtAcctntGLAcctSelected
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.