Looking up, limiting and storing data from another table

[CT_AccessHelp]

Looking for some help.



I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table. Any suggestions?

Table examples:

County	Code	Funds Spent	Accounting Code	Date Entered
Aitkin	123	$20.00	Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.	12/4/2013
Aitkin	112	$23.00	Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.	12/4/2013
Becker	145	$12.00	Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.	12/4/2013

County	Accounting Code
Aitkin	4872727001000000
Aitkin	4842727001000000
Becker	4872727005000000

[BluffMeAllIn]

Hi CT,

It's going to depend on how the system is designed obviously but I would imagine you have a form in which the user has a combo box to select their account code for that particular entry?

Right now you probably have the combo box source for the account code set directly to the accounting code field in the table, however you should limit it to only the accounting codes which have the county value of the county for the particular entry.

In VBA code you would do this by setting the rowsource of the combo box such as:
cboAccountingCode.RowSource = "Select AccountingCode from tblAccountingCodes Where County = '" & Forms!nameofentryform.nameoffieldcontainingcountyc ode.value & "'"

Similarly in the properties if not using vba in the database, you can use the same Select statement, obviously changing it to reflect your fieldname, tablename, formname, and control name.

Let me know if this is confusing or is clear enough to asist with your issue.

Thanks,
Dave

[CT_AccessHelp]

Hmmm it didn't work. I entered

= "Select AccountingCode from tbl_Accnt_Code Where County = '" & Forms!frm_Expend.CNTY.value & "'"

In the control source for Account Code on my form and I get Enter Parameter Value tbl_Accnt_Code.Accnt Code When I try to select on the form.

Sorry I have not used Access in a looong time, a little rusty.

[BluffMeAllIn]

Ok, I would assume obvoiusly from the above that the table name is tbl_Accnt_Code.

However you have .Accnt Code as something its looking for from the parameter? Is that the exact message, also is the field containing the Account Code in that table named AccountingCode or Accnt Code?

You have indicated the control source, so these are bound controls? Essentially for the combo box in an unbound situation this would be the RowSource, but for bound controls I am also a little rusty as most of my experience and preference in access I have steered away from bound controls to keep all of the control from the programming side.

Thinking however if you are using bound controls then the account code field on the form will have to stay bound to the account code field in the entry table, so that when you view a record that has already been entered it will display the correct accounting code that was entered on the record. Perhaps the form has a text box from a view perspective and the combo box is seperate just for the data entry side of the fence, in which case you can sepecify the select statement in the Row Source, no = before it and don't need the quotes either when putting it in the properties (although I don't think it will break if you have them, can't recall).

Basically the message is saying it can't find a reference to tbl_Accnt_Code.Accnt Code and so it expects it to be an input parameter.

Give this a try in the row Source for the combo:
"Select AccountingCode from tbl_Accnt_Code Where County = '" & Forms!frm_Expend.CNTY.value & "'";

Hope this helps.

Dave

[CT_AccessHelp]

There is nothing named .Accnt Code so not even sure where that is comeing from.

The table with all of the accounting codes is named tbl_Accnt_Code, the column with the codes is named AccountingCode.

Maybe it is just too far over my head - I can't make it work... Thanks for trying

[ssanfu]

To get a more precise answer, you need to provide more precise info.

What is the form name?
What are the table names?
What are the control names?

Is there a control on the form where you enter the county name? What is the control name? Is it a combo box or a text box?

Maybe post a pic of the form.... visuals help.

[BluffMeAllIn]

There is nothing named .Accnt Code so not even sure where that is comeing from.

The table with all of the accounting codes is named tbl_Accnt_Code, the column with the codes is named AccountingCode.

Maybe it is just too far over my head - I can't make it work... Thanks for trying

That is what I had thought from your select statement, so yes very odd that it would be making a reference to Accnt Code in the error message popup, would seem it has to be in the code somewhere "or you have gremlins lol".

Did it still give you the same error message when you tried the select statement I indicated in the RowSource property of the table? As ssanfu suggested perhaps snap a couple of pictures, maybe the error message being recieved and the properties for the combo box (such as rowsource etc).

Is the account code field in your entry table possibly accnt_code? That has to be somewhere for it to show up in the message.

Hopefully you can provide some pics that might be able to help us help you further.

[CT_AccessHelp]

Form Name is frm_Expend
Tables are tbl_Accnt_Code and tbl_Expend
The form is basically all the info from the tbl_Expend

[CT_AccessHelp]

Maybe these will help too?

[Dal Jeanis]

Try this, assuming the combo box for county on the form is called CNTY:

"Select [AccountingCode] from [tbl_Accnt_Code] Where [County] = '" & Me.CNTY & "'"

By the way, if it's unbound, it's not saving anything anywhere...

[ssanfu]

The row source for the combo box (I called it cboCounty) bound to the field "CNTY" should be

SELECT DISTINCT County FROM tbl_Accnt_code ORDER BY County

In the after update event of the combo box (cboCounty), the code would be

Private Sub cboCounty_AfterUpdate()
   Me.cboAccntCode.Requery
End Sub

And the row source for the "Accounting Code" combo box (cboAccntCode)

SELECT [Accounting Code] FROM tbl_Accnt_code WHERE [County] = Forms!frm_Expend.cboCounty ORDER BY [Accounting Code]

*** I think you were getting an error because it looks like there is a space in the field name "Accounting Code" in tbl_Expand.
Spaces and special characters in object names will cause you headaches.
Only use numbers, letters and the underscore.

If you feel you must use spaces, you must enclose the field name with brackets =>> [Accounting Code].
Examples of better names:
AccountingCode
Accounting_Code

[CT_AccessHelp]

Sorry for being slow but I put this code where?

[Dal Jeanis]

From Design view on the form, right-click the particular combo box, and open the properties pane.

Row Source is on the data tab. Paste the code there.

After update is on the events tab. Right-click in the box for the proper event, then click the button with three dots on it. select code builder. Insert the code after the line that says:

private sub MyComboBoxName_AfterUpdate()

and before the line that says:

End sub

[ssanfu]

You have a form named "frm_Expend"

The top combo box is what I named "cboCounty". Don't know what you have it named.
The label is "CNTY"

Row Source : SELECT DISTINCT County FROM tbl_Accnt_code ORDER BY County
After Update event code: Me.cboAccntCode.Requery




For the unbound combo box with the label "Accounting Code" I called "cboAccntCode":
Row Source: SELECT [Accounting Code] FROM tbl_Accnt_code WHERE [County] = Forms!frm_Expend.cboCounty ORDER BY [Accounting Code]

[CT_AccessHelp]

SWEET!!!! SUCCESS!!! Thank You!!!