Results 1 to 7 of 7
  1. #1
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24

    Report not showing correctly.

    Hello,



    I have a report that is not showing like needed. I have a query that the report is based on. The query shows everything correctly. The problem I am having is the report is not showing the city and zip code correctly.

    Here is the SQL of the query.
    Code:
    SELECT tblPersons.ChurchFK, tblPersons.FName, tblPersons.LName, tblPersons.Birthday, tblPersons.EMail, tblPersons.Anniversary, tblPersons.CellPhone, tblPersons.ActMember, tblPersons.ActVisitor, tblAddresses.AddressP, tblAddresses.AddressM, tblFamilies.FamilyName, tblAddresses.CityFK, tblAddresses.StateFK, tblAddresses.ZipFK, tblAddresses.HPhone, tblPersons.FamiliesFK, tblChurches.CName, tblChurches.AddressP, tblChurches.AddressM, tblChurches.CityFK, tblChurches.StateFK, tblChurches.ZipCodeFK, tblChurches.BPhoneFROM tblFamilies INNER JOIN (tblChurches INNER JOIN (tblAddresses INNER JOIN tblPersons ON tblAddresses.AddressID = tblPersons.AddressFK) ON tblChurches.ChurchID = tblPersons.ChurchFK) ON tblFamilies.FamiliesID = tblPersons.FamiliesFK
    WHERE (((tblPersons.ChurchFK)=[Forms]![frmDirectory]![cboSelectChurch]));
    The report shows the state correctly by showing the actual state. The City and zip code does not show correctly as it shows the ID number instead of the actual City / Zip Code. I am not sure why this is happening or how to fix it. Can someone please help? Thanks in advance.

    --
    Sincerely,
    Clifford86

  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
    It shows ID because that is what is actually saved in field. To retrieve associated descriptive info, include table that has the text values. I see only key fields CityFK and ZipFK.
    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
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Thanks June7. I did get it to work. However I had to remove one table completely out of the query. That's fine I guess.

    I do have another question.
    This is a church directory. I can have multiple churches in the database.
    This report will be filtered by a combo box on a form according to the church that is selected.
    I was wondering if there was a way to have the report show the church information in the header of the report based off of the church selected in the form?
    The table that I had to remove was the church table from the query. It still filters and everything good. Now I just don't have the churches information other than the name.
    I had to remove the table because it was causing issues with the query not showing all the records correctly.
    Can you please help point me in the right direction? Thanks in advance.

    --
    Sincerely
    Clifford86

  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
    What does "not correctly" mean? I don't see any reason can't include tblChurches. If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  5. #5
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    I am attaching a copy of the database below.

    I am now having some other major problems I am trying to get worked out. If you don't mind maybe I could please get some help with?

    When you open the database it will ask for a username & password. You can close the dialog box by clicking the button at the top. (This is temp and not completely setup yet.)

    I did something and then things went crazy for me. So I deleted all sample data and added new data and I am totally stuck at the moment. The form I am using to add the sample data is the frmFamiliesAddEdit. For some reason when adding a new family it does not add the ChurchFK to all persons in the subform. I thought it had something with linking Master and Child fields. When I went to do that then the main form started showing each person as a record for the main form instead of just grouping them together by the address. This also has had an effect on the rptDirectory as well. Can you please take an look and see what you can figure out the issue?

    For the directory report I am using the qryDirectory, frmDirectory & rptDirectory.

    Thanks for all the help and time your giving to help. It is much appreciated.

    --
    Sincerly,
    Clifford86
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The dialog box X close is not enabled nor is right click menu nor ribbon so cannot close that dialog. Have to open db with shift key bypass.

    A form should be used to enter/edit data to one table. Can include lookup tables just to display (not edit) related data. However, this can cause confusion.

    You are trying to enter data to multiple tables on main form. There are no fields in tblFamilies for address, phone, email, church - these go in tblPersons. Remove tblChurches and tblAddresses and tblPersons from main form RecordSource. Bind main form just to tblFamilies. Move controls for address, phone, church to subform.

    If you want to add a new address or church record 'on-the-fly' during data entry, use combobox NotInList event. This is a common topic.

    The subform Master/Child links are wrong. Should be FamiliesID and FamiliesFK.

    Alternatively, main form bound to tblAddresses, subform bound to tblPersons with comboboxes to select family and church. Then Master/Child Links would be address keys.

    If you want to enter multiple persons in one session and you want some inputs of first record to carry forward to subsequent records until different value entered by user, use VBA code to set control DefaultValue property. For example, to carry forward church ID:

    Private Sub Church_AfterUpdate()
    Me.Church.DefaultValue = Me.Church
    End Sub
    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.

  7. #7
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Hello June7,

    I had a button on the dialog box at the top next to the word Users that would have closed the user login dialog.
    Thanks for the information. I will redesign the form.

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

Similar Threads

  1. Replies: 9
    Last Post: 08-08-2016, 02:57 PM
  2. Name not showing correctly
    By jopi in forum Reports
    Replies: 1
    Last Post: 05-12-2016, 08:38 AM
  3. Replies: 15
    Last Post: 04-16-2014, 01:15 PM
  4. Replies: 3
    Last Post: 01-03-2012, 12:28 PM
  5. Subform not showing correctly
    By ricardo9211 in forum Forms
    Replies: 1
    Last Post: 08-27-2009, 07:49 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