Results 1 to 10 of 10
  1. #1
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243

    query fields won't accept text

    Creating a database of several different journals. After populating Titles table, the goal is to build a form that has all the issues emanating from that specific journal appear in a sub form. IE:
    Title: Southwest Journal
    Issue 1
    Issue 2
    Issue 3

    Title: Revista Chapingo Journal
    Issue 1
    Issue 2
    Issue 3 --- and so on. The main form is working. But not the subform.
    Click image for larger version. 

Name:	queries.JPG 
Views:	16 
Size:	48.3 KB 
ID:	24725

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    main form should have tblJournalTitle as recordsource
    sub form should have tblJournalIssues as recordsouce
    subform control manages the relationship - set the linkchild property to JournalTitle_FK and the linkmaster property to JournalTitle_ID

    rule is one form, one table

  3. #3
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    I'm sorry to be dense, but your instructions were a little (okay, a lot) above my skill set with Access. But I appreciate your response none the less.

    I was able to set up the query, but unfortunately, the subform generated from the query is not being specific to the title. Another words, the issues are not linking to the correct title. The data (journal issues) in the subform do not change as you click through to the next title. Or to put it another way, the journal title is not matching to the correct issue.

    I would like to do one form, one table -- that would be the easiest thing to do, but then I would have the Title replicated multiple times over in the database if I have the Title AND the issues in the same table. This is a one to many relationship, if I understand this correctly. One journal title has many issues since this a continuing periodical, not a book.
    Click image for larger version. 

Name:	Title_IssuesFormView2.JPG 
Views:	15 
Size:	48.5 KB 
ID:	24736Click image for larger version. 

Name:	JrnlRltnshps.JPG 
Views:	12 
Size:	30.1 KB 
ID:	24737
    I feel like I'm almost there..... if I can just get the titles and issues to sync in the form/subform.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    read my post again - the rule is one form one table - you have 4

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You don't need a query here, and any defined relationships won't be reflected in how the form works.

    You have a main form and a sub-form. The record source for the main form is tblJournalTitle, and the record source for the subform is tblJournalIssues.

    To link the form and subform, go to the main form control that contains the subform, and in the properties for that control, click the "data" tab. Set Link Master Fields to JournalTitleID, and set Link Child Fields to JournalTitle_FK.

    That should be all you need.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02 worth....


    Based on what you said and your images,
    for the main form, I would use a query on these tables:
    Click image for larger version. 

Name:	JournalQuery.jpg 
Views:	13 
Size:	13.7 KB 
ID:	24742
    Code:
    SELECT tblJournalTitle.JournalTitleID_PK, tblJournalTitle.JournalTitle, tblJournalEditor.EditorFirstName, tblJournalEditor.EditorLastName
    FROM tblJournalTitle INNER JOIN (tblJournalEditor INNER JOIN tblJunctionTitleEditor ON tblJournalEditor.JournalEditorID_PK = tblJunctionTitleEditor.JournalEditorID_FK) ON tblJournalTitle.JournalTitleID_PK = tblJunctionTitleEditor.JournalTitleID_FK
    ORDER BY tblJournalTitle.JournalTitle;

    For the subform, I would use use a query on this table:
    Click image for larger version. 

Name:	IssuesQuery.jpg 
Views:	13 
Size:	5.9 KB 
ID:	24743
    Code:
    SELECT tblJournalIssues.JournalTitleID_PK, tblJournalIssues.JournalTitle_FK, tblJournalIssues.Volume, tblJournalIssues.JournalNumber, tblJournalIssues.PublicationYear
    FROM tblJournalIssues;
    NOTE: "Number" is a reserved word in Access. I changed it to "JournalNumber".



    The form looks like:
    Click image for larger version. 

Name:	FormTitles.jpg 
Views:	14 
Size:	30.1 KB 
ID:	24746

    (do not/ should not display the PK fields.....)

  7. #7
    snowboarder234's Avatar
    snowboarder234 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    243
    John G:
    Well, I tried -- but now I have no data in the subform.
    Click image for larger version. 

Name:	Nodata.JPG 
Views:	11 
Size:	35.2 KB 
ID:	24749Click image for larger version. 

Name:	NoData2.jpg 
Views:	12 
Size:	124.8 KB 
ID:	24750

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe I did something wrong....

    Example dB
    Attached Files Attached Files

  9. #9
    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
    Maybe I did something wrong....

    Example dB
    No, ssanfu -- my response was to John G's suggestion. I'll investigate the zip that you provided, probably tomorrow, and see if I can get that to work. Thanks to everyone for their time and effort.

  10. #10
    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
    Maybe I did something wrong....

    Example dB
    The zipped file was Invaluable. The query did the trick. After I was further able to compare your file to mine, I realized I was failing to populate the foreign Key field in the Issues table, which is why the volumes and titles weren't syncing. The learning curve has been steep and this forum is a Godsend! Thank you again.
    Click image for larger version. 

Name:	thanks.JPG 
Views:	8 
Size:	59.3 KB 
ID:	24766

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

Similar Threads

  1. Replies: 2
    Last Post: 02-07-2015, 11:20 PM
  2. Replies: 2
    Last Post: 10-17-2012, 01:35 PM
  3. Counting Text Fields in a Query
    By TimMoffy in forum Queries
    Replies: 2
    Last Post: 05-14-2012, 08:00 AM
  4. Query Won't Accept Criteria from Form
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 11-30-2010, 01:51 PM
  5. Setting a field to only accept text characters, not numbers
    By USAFA2012 in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 12:37 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