Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40

    Looking up, limiting and storing data from another table

    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

  2. #2
    BluffMeAllIn is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2013
    Location
    Canada
    Posts
    21
    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

  3. #3
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    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.

  4. #4
    BluffMeAllIn is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2013
    Location
    Canada
    Posts
    21
    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

  5. #5
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    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

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

  7. #7
    BluffMeAllIn is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2013
    Location
    Canada
    Posts
    21
    Quote Originally Posted by CT_AccessHelp View Post
    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.

  8. #8
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    Form Name is frm_Expend
    Tables are tbl_Accnt_Code and tbl_Expend
    The form is basically all the info from the tbl_Expend

    Click image for larger version. 

Name:	Frm.png 
Views:	17 
Size:	176.2 KB 
ID:	14631

  9. #9
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    Maybe these will help too?



    Click image for larger version. 

Name:	tbl_acct.png 
Views:	18 
Size:	210.4 KB 
ID:	14632Click image for larger version. 

Name:	tbl_Expend.png 
Views:	18 
Size:	161.3 KB 
ID:	14633

  10. #10
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Try this, assuming the combo box for county on the form is called CNTY:
    Code:
    "Select [AccountingCode] from [tbl_Accnt_Code] Where [County] = '" & Me.CNTY & "'"
    By the way, if it's unbound, it's not saving anything anywhere...

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The row source for the combo box (I called it cboCounty) bound to the field "CNTY" should be
    Code:
    SELECT DISTINCT County FROM tbl_Accnt_code ORDER BY County
    In the after update event of the combo box (cboCounty), the code would be
    Code:
    Private Sub cboCounty_AfterUpdate()
       Me.cboAccntCode.Requery
    End Sub


    And the row source for the "Accounting Code" combo box (cboAccntCode)
    Code:
    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

  12. #12
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    Sorry for being slow but I put this code where?

  13. #13
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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:
    Code:
    private sub MyComboBoxName_AfterUpdate()
    and before the line that says:
    Code:
    End sub

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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]

  15. #15
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    SWEET!!!! SUCCESS!!! Thank You!!!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Storing an object in a table
    By Paul H in forum Programming
    Replies: 2
    Last Post: 08-26-2013, 07:28 AM
  2. Replies: 11
    Last Post: 06-02-2013, 10:42 PM
  3. Limiting access to BE data
    By sotssax in forum Access
    Replies: 2
    Last Post: 08-05-2011, 11:12 AM
  4. Storing code in a table
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 07-27-2011, 07:22 PM
  5. Replies: 4
    Last Post: 01-04-2011, 06:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums