Results 1 to 4 of 4
  1. #1
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    SQL Statement with Dependent Combo

    Hi all,
    I started this new form today and I created CboBoxes with wizard. They work fine however, when I tried to make one dependent on the other, I added a WHERE to the SQL which worked but wont let me click on the one I want to add to the record.
    So, on Frm_Main


    I am trying to get the Cbo_AccountSource to be dependent on the Cbo_AccountType. It comes up as I wanted, just wont allow to click on anything or saves it as the first one in list.????The form is not done, just trying to get it working....
    The other issue I am having is how in heck do I get the actual AccountName in the TxtAccountName Field. I can get the ID # but not the name.
    DB attached
    Thank you,
    Dave

    DataAccountTest.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Suggest code in form Current event as well as cbo_AccountType AfterUpdate to requery the RowSource.

    Why not combine AccountName and AccountSetUp tables? Otherwise, CboAccountSetUpID RowSource has to be a query that joins them. Actually, I am not sure why these are not combined with AccountMain.

    Band of America ?

    All code modules should have Option Explicit in header. Can set up application to automatically include this line.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    d9pierce1 is offline Expert
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June,
    If I put the SQL statement in CboAccountSourceID, it works fine, stores the selection. if I put a WHERE condition in it to get only the AccountSource that is dependent on selection in CboAccountType, then it appears in the dropdown, but wont let me make a selection. Could it be possible that my WHERE clause is wrong? Just wondering...

    Code:
    SELECT [Tbl_AccountSource].[AccountSourceID], [Tbl_AccountSource].[AccountSource] FROM Tbl_AccountSource ORDER BY [AccountSource];
    If I add WHERE
    Code:
    SELECT [Tbl_AccountSource].[AccountSourceID], [Tbl_AccountSource].[AccountTypeID], [Tbl_AccountSource].[AccountSource] FROM Tbl_AccountSource WHERE (((Tbl_AccountSource.AccountTypeID)=[Forms]![Frm_AccountMain]![CboAccountTypeID]));

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why set Caption property in table if you don't use those captions anywhere else?

    Change BoundColumn to 1 instead of 2. Don't need AccountTypeID in the SELECT clause:

    SELECT [AccountSourceID], [AccountSource] FROM Tbl_AccountSource WHERE AccountTypeID=[CboAccountTypeID];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-23-2017, 01:16 PM
  2. Dependent combo box
    By kiranair in forum Access
    Replies: 2
    Last Post: 06-14-2016, 02:27 AM
  3. Replies: 1
    Last Post: 04-05-2016, 09:25 AM
  4. Dependent Combo Box
    By tigers in forum Forms
    Replies: 1
    Last Post: 06-16-2009, 12:46 PM
  5. Replies: 3
    Last Post: 02-26-2009, 10:17 AM

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