Results 1 to 8 of 8
  1. #1
    funkykizzy is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2013
    Posts
    15

    Two Tables, One Form

    Greetings!



    I designed a form for a colleague using a listbox where the user can enter their contact information and then use the listbox to select what they are interested in out of 30 options. The new record then populates a master table, with one column indicating the selections as a string with VBA code.

    They really like what I've done, but instead of a listbox, they want check boxes next to each of the 30 options for the user to indicate interest. I created a second table with 31 columns to join with the master table of user information, each user having a unique ID and using that number as the join key.

    Access really doesn't like this idea, so I tried to "unbound" each check box, but now I am wondering how to link them all back up again and insert into the proper table with the proper user ID number. Any thoughts???

    Thanks a million!!

    (Also, I'm using Access 2016)

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The problem with check boxes to indicate interest is that you have to redesign the dB to add/change/delete interests.

    I would have a table to hold the interests and a table to hold the interests selected.


    "master table of user information" (the one side) linked to the "selected interests table" (the many).
    The selected interests table would have 3 fields: an autonumber PK field, a FK field for the user PK field (long integer) and a text field to hold the interest.
    I would have a sub form with a combo box to select the interests.


    You could use unbound check box controls, but you would need code on the form load and form current events to set/reset the check boxes and code to save changes to the selections (add or delete).


    My $0.02 worth........

  3. #3
    funkykizzy is offline Novice
    Windows XP Access 2016
    Join Date
    Dec 2013
    Posts
    15
    Hi Steve!

    Thank you SO MUCH for your $0.02!

    I seem to be having some difficulty with my SQL code (of all things), so maybe a one-to-many is needed. I do have 3 tables at the moment: the master table (with "customer" PK), the interests table (with "interest" PK), and the selected interest table (with customer FK).

    I am trying to avoid the subform to keep the layout clean as requested by the colleague, but I'm starting to get desperate. I'm running out of time!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure why you think a subform is "unclean"! It is all in the presentation. However. a listbox of selections and each time they double-click a selection it adds a record to your selected table (with a second list box showing the selected table entries, with a double-click to delete).

    Post your SQL so that we can see what you are talking about. Your relationships will definitely be one-to-many: one customer to many selections, one selection to many customers.

  5. #5
    funkykizzy is offline Novice
    Windows XP Access 2016
    Join Date
    Dec 2013
    Posts
    15
    Thanks! I am going to redo my db from fresh and try using the subform. My colleague specifically wanted checkboxes and absolutely no scrolling so that is why I was avoiding the subform (it has to fit on the screen of a small laptop).

    I am trying again using your suggestions...

  6. #6
    funkykizzy is offline Novice
    Windows XP Access 2016
    Join Date
    Dec 2013
    Posts
    15
    So, I have three tables:
    Table1 = customer information; the name, address, affiliation, phone, email and PK ID for each customer that interacts with the form
    AOI = the list of the areas of interest that the customer can choose from, this is the list I want in my subform with checkboxes (there are 30 options/interests to choose from)
    AOIselections = the table where I want the selections made by each customer (FK "CustomerID") and their selections

    It seems like the best way would be to do a combobox or listbox and then VBA the selections into the table AOIselections? I don't seem to be able to do checkboxes in a subform... I admit, I have almost zero subform experience.

    I am not sure this is going to work because the form needs to allow multiple selections and needs to be designed for the most computer illiterate (no scrolling, no holding ctrl or double-clicking, etc.).

    My next post will be the SQL code I was having trouble with from my first attempt.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Having two listboxes as I described previously would be one way of doing it (you could use single click instead of double-click if you prefer altho I always feel it might be clicked inadvertently). The listbox would be able to show many selections whereas a combobox is one at a time - whichever works for you.

    A subform is the same as a form but for your purposes it would need to be a temporary table with all the selections available, so might be a bit cumbersome for your purposes here.

  8. #8
    funkykizzy is offline Novice
    Windows XP Access 2016
    Join Date
    Dec 2013
    Posts
    15
    So, back to my original db that I was having trouble with, changing the table to be one-to-many solved the problem. Yayyy!!!
    I get all my information in the tables, the user does not have to scroll or do anything "fancy" for the multiple selections, and after they have clicked the button the form goes to the next new record with all information and check reset to False.

    I guess I will just have to recombine the information depending on how my colleague wants it to look later.

    Thank you so much for the help!!

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

Similar Threads

  1. Can't Update Tables from Form - Form Locked?
    By faythe1215 in forum Forms
    Replies: 1
    Last Post: 02-12-2015, 12:22 AM
  2. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  3. 1 form two tables
    By keiath in forum Forms
    Replies: 27
    Last Post: 01-28-2014, 01:17 PM
  4. Replies: 14
    Last Post: 01-26-2012, 02:20 AM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

Tags for this Thread

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