Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243

    Thank you. Issue resolved and I appreciate your help. On to another question, if you're up for it:
    I linked my tables as per your suggestion in a prior post, with the publishers and Languages information each having their own tables. Problem is, I can't figure out for the life of me how to implement this. I considered just adding those two categories to the tblBooks table. Can you point be in the right direction on this one?
    Click image for larger version. 

Name:	Rltnshps2.JPG 
Views:	9 
Size:	36.8 KB 
ID:	24495
    Attached Thumbnails Attached Thumbnails ManyToMany.JPG  
    Last edited by snowboarder234; 04-28-2016 at 01:48 PM. Reason: Ignore attached table. See one posted in message.

  2. #17
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Look at the dB I posted in Post #8.
    Click the button "Add/Edit/Delete Books" (third from the bottom).

    When the form opens, look at the right two columns - they are combo boxes.

    You can also look at the relationship window.
    I always try to have the "one" table on the left and the "many" table on the right. The flow seems easier to follow.

    In Post #16, in the bottom image, the "Books" table should not have the field "author_FK". The top image is correct.

  3. #18
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Thank you for this tutorial. However, I was not successful in setting up the combo box. The foreign key field entitled Languages in the tblBooks turned into a combo box, but does not reveal the data from the tblLanguages.
    Click image for larger version. 

Name:	ComboBox.JPG 
Views:	7 
Size:	118.4 KB 
ID:	24506Click image for larger version. 

Name:	combobox2.JPG 
Views:	7 
Size:	17.8 KB 
ID:	24508

  4. #19
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From Post #7:
    Quote Originally Posted by ssanfu View Post
    Also found another error. In "tblLanguages", "Language" is a reserved word.
    Change it to something like "LanguageName" or "txtLanguage".
    In tblBooks, the field "txtLanguage" MUST be a type Number-Long because it should be a foreign key to the PK field in "tblLanguages".


    For the Languages combo box, look at my demo dB. Look at the combo box row source.
    It should be
    Code:
    SELECT tblLanguages.Languages_ID, tblLanguages.LanguageName FROM tblLanguages ORDER BY tblLanguages.LanguageName;
    Why do you have tblBooks.Languages"????????

  5. #20
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Okay, I finally got it to work. However, the data displayed in the tblBooks tables/Languages_FK field are numbers, and as such those numbers feed into the form as numbers, rather than text. Even though there are only 3 numbers to choose from in the form's Language field, this will not be helpful to the end user.
    Click image for larger version. 

Name:	LanguagesTable.JPG 
Views:	5 
Size:	31.1 KB 
ID:	24530Click image for larger version. 

Name:	FormLanguages.JPG 
Views:	5 
Size:	21.0 KB 
ID:	24531
    Thank you.

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the Recordsource of your form??

    You should include LanguageName from table
    tblLanguages

  7. #22
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Click image for larger version. 

Name:	rowsource2.JPG 
Views:	6 
Size:	26.4 KB 
ID:	24532Click image for larger version. 

Name:	rowsource.JPG 
Views:	6 
Size:	24.4 KB 
ID:	24533 I'm using tblBooks and tblLanguages

  8. #23
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Again, look at the demo I posted in Post #8.

    From the image, I looks like you are trying to use a look up FIELD. See http://access.mvps.org/access/lookupfields.htm
    I NEVER use look up fields.

    The row source of the language combo box on the form should be
    Code:
    SELECT tblLanguages.Languages_ID, tblLanguages.LanguageName FROM tblLanguages ORDER BY tblLanguages.LanguageName;
    On the properties format tab, the Column Count property should be 2 and the Column Widths property should be 0.

  9. #24
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Quote Originally Posted by ssanfu View Post
    Again, look at the demo I posted in Post #8.

    From the image, I looks like you are trying to use a look up FIELD. See http://access.mvps.org/access/lookupfields.htm
    I NEVER use look up fields.

    The row source of the language combo box on the form should be
    Code:
    SELECT tblLanguages.Languages_ID, tblLanguages.LanguageName FROM tblLanguages ORDER BY tblLanguages.LanguageName;
    On the properties format tab, the Column Count property should be 2 and the Column Widths property should be 0.
    Dear ssanfu;
    I have looked at your demo many times and am deeply appreciative of the time you invested in providing that resource.
    In the tblBooks, I get the same row source code that you included above -- however, I don't see it on the form.
    In the tblBooks, in the Languages_FK combo box, the ID numbers are displayed [just as they are in your demo]. The issue before me now is how to get those numbers in the table's combo box to display as text [not numbers] in the frmAuthors?

  10. #25
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the tblBooks, I get the same row source code that you included above -- however, I don't see it on the form.
    tblBOOKS WILL display a number, since it is a foreign key field of type number - long integer.
    Users should never directly access a table!

    In the "MainMENU" form, Click on the "Add/Edit/Delete Books" button
    Click image for larger version. 

Name:	Slide1.JPG 
Views:	6 
Size:	15.9 KB 
ID:	24537




    This opens the form named "BooksAdd".
    This is where you can add the language the book is written in and select the publisher.
    Click image for larger version. 

Name:	BooksAdd1.jpg 
Views:	6 
Size:	72.1 KB 
ID:	24539


    Or click on the button for "Add Books - Authors". This form allows you to add multiple authors to one book. You can also add the language the book is written in.
    Click image for larger version. 

Name:	BooksAuthors1.jpg 
Views:	6 
Size:	44.5 KB 
ID:	24540



    You should never have to look at a table to select the language..


    In the tblBooks, in the Languages_FK combo box, the ID numbers are displayed [just as they are in your demo].
    In my demo, tblBooks.Languages_FK is not a combo box. I do not use look up FIELDS in tables.



    The issue before me now is how to get those numbers in the table's combo box to display as text [not numbers] in the frmAuthors?
    I am confused. What does the language have to do with Authors? The Language field is in tblBooks.



    Maybe I need to see your latest dB to understand.

  11. #26
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    I apologize for the confusion. When looking at your DB I didn't realize the combo boxes were confined to the form. That's why I kept trying to insert them into the table -- my oversight.

    It's an extraordinary piece of work you put together. I'll continue to tinker with it. However, in the long run, I'm tasked with putting together a product that combines all the data into one user-friendly portal. Will that be possible based on your DB design? Years ago I put together a DB that had a portal as depicted below. This one was easier to design because it was not a many to many relationship. I'm really struggling with the many-to-many, that and the fact that the project depicted below was abandoned when it was determined the data would go online. Now, I'm having to relearn ACCESS all over again.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	4 
Size:	213.3 KB 
ID:	24545
    Thank you.

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    However, in the long run, I'm tasked with putting together a product that combines all the data into one user-friendly portal. Will that be possible based on your DB design?
    Not sure. Depends on what you mean by "Portal". The "UI"?

    You could put all fields in one table (like a spreadsheet), but that kinda defeats the purpose of a database. In a spreadsheet, you have a lot of redundancy.
    You have a many to many relationship, so a database is probably what you need. Just a matter of how much you want/need to de-normalize.

    Maybe the attached dB interface is closer to what you are thinking of?
    I enabled cascading deletes for the junction table links. This will enable you to delete an author or a book.
    Attached Files Attached Files

  13. #28
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    Wow -- that is exactly what I was trying to achieve! Except, how do I access the tables to do an alpha sort? Not all the authors are in alpha order? Well, no -- maybe not. I had envisioned something like this where author and details were displayed in a subform below so the viewer could see all the author's publications on the same page. This is a down and dirty layout of what would work best for the people who will be viewing the database -- I know most of them will not be able to navigate all those tabs. I am trying to do something like this with all the author/data in one view. Only problem is, I can already see the flaw because this author should have 2 publications showing, not only one.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	4 
Size:	61.2 KB 
ID:	24548

  14. #29
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Except, how do I access the tables to do an alpha sort?
    You don't sort tables! Tables just store/hold data. Sorting is done in queries.

    I usually have a query as the record source for forms. The sort order can be set or changed in a query.
    (I see I used tables as the record source for subforms "sfAuthorsBooks" and "sfBooksAuthors" ... must of had a brain fa#t when I create them )


    This is a down and dirty layout of what would work best for the people who will be viewing the database
    You might, stress on the might, be able to create something like that.
    But it might take a lot of VBA code. And be difficult!


    Remember, you can pick an author and see all of the books the author wrote OR pick a book and see all of the authors.



    Only problem is, I can already see the flaw because this author should have 2 publications showing, not only one.
    It looks like the form view is "Single Form" view, not "Continuous Forms" view. You would have to scroll to see the other record(s) or change the default view.

    And the form is designed to take up a lot of space. Notice how I designed the forms that have subforms.

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

Similar Threads

  1. Replies: 3
    Last Post: 09-21-2015, 08:10 PM
  2. avoiding long requeries
    By dennisem in forum Queries
    Replies: 2
    Last Post: 11-04-2012, 05:35 PM
  3. Avoiding Ambigous Joins
    By dandoescode in forum Programming
    Replies: 6
    Last Post: 12-19-2011, 11:35 AM
  4. Avoiding a cartesian product
    By johnmerlino in forum Queries
    Replies: 0
    Last Post: 10-25-2010, 07:52 AM
  5. Avoiding duplicates in query
    By theracer06 in forum Queries
    Replies: 1
    Last Post: 08-24-2010, 12:49 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