Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145

    Parent and Child Tables with Auto-Generate Keys

    Hello Forum,

    I need a solution where the Child Table (tblInspectionReports; see picture) where the foreign key has to be manually inputted from a sourced data Form. I want it to be generated automatically based on the "Name" of the inspector in which the names are manually entered into the database. The Parent Table (tblQEInames) has the primary key under (QEIID) and is tied to the "Names" of inspectors.



    Click image for larger version. 

Name:	Access 1.JPG 
Views:	20 
Size:	50.9 KB 
ID:	41083Click image for larger version. 

Name:	Access 2.JPG 
Views:	20 
Size:	37.1 KB 
ID:	41084
    Do we use a query for this or should I just some kind of formula within the table?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You can use a form/subform arrangement. Or select name from combobox which saves QEIID to field. Otherwise, will need code.
    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
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    it the master table has an autonum, then it creates it when you make a record.
    the subform on this record should have the same name field, BUT it should be LONG INTEGER bound to the master key auto field.
    then the masterID (auto) and child (long) will both get assigned with no extra work, no programming.

    the subform can also have an autonum too.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as already said - use a form/subform arrangement - but also create the relationship between the two fields so the master/child properties are automatically created if you drag the subform onto the main form

  5. #5
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Can I use an expression builder for this that if the Form selects InspectorName = "Raymond Chow" ; then QEIID will automatically be "1"?


    Thanks,

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There is no need for expression builder.

    Have you considered suggestions aready provided?

    Do you understand how to design a combobox so it displays decriptive text but saves ID?
    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
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by June7 View Post
    There is no need for expression builder.

    Have you considered suggestions aready provided?

    Do you understand how to design a combobox so it displays decriptive text but saves ID?
    No I actually don't. I know how to make My form's "QEIID" into a combobox. How do I get it to follow automatically enter the ID number based on the Inspector's Name?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Combobox properties:

    RowSource: SELECT QEIID, QEINames FROM tblQEINames ORDER BY QEInames;
    BoundColumn: 1
    ColumnCount: 2
    ColumnWidths: 0";1.5"
    ControlSource: QEIID (the field from tblInspectionReports)

    Users will see names but QEIID value will be saved.

    InspectorName should not even be saved into tblInspectionReports.
    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.

  9. #9
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    I don't believe I did it right... help?
    Attached Thumbnails Attached Thumbnails Access 3.jpg  

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post your dB? It would be easier to have your dB than for me to spend the time making something up.
    Change any sensitive data...... Do a Compact and Repair, then compress (Zip).

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    ControlSource is not as I showed. It should simply be the field name, not an expression.
    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.

  12. #12
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Access Send.zip

    Please See Attached. I think the problem is that in my Inspection Form .. I have combo box selected for QEIInitials (initials of the inspector's name). I needed this for the FileName box.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you would follow the instructions I provided the combobox will work. I will repeat (modified to include initials field):

    Combobox properties:

    RowSource: SELECT QEIID, QEINames, QEInitials FROM tblQEINames ORDER BY QEInames;
    BoundColumn: 1
    ColumnCount: 3
    ColumnWidths: 0";1.5";0.25"
    ControlSource: [QEIID] (the field from tblInspectionReports)

    Users will see names but QEIID value will be saved.

    Advise not to build lookup fields in tables. The filename expression can be in textbox instead of query and reference column of combobox.
    =Format([Date of Inspection],"yyyy-mm-dd") & "-" & txtName.Column(2)

    Just bind form to table.

    InspectionName field which is saving initials should not even be in tblInspectionReports.
    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.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are going to have lots of problems because of all of the Look up FIELDS and the Multi-Value FIELDS in the tables.

    In table "tblInspectionReports", you have a default value of 0 (zero); so now there 36 records with a value of 0 in the "QEIID" field.

    In the form, you deleted the control for "QEIID". You have the control (txtName) for the Inspector Name bound to the "InspectorName" field in the table. The field "InspectorName" is a Look up field AND you have a combo box control trying to save initials to the same field. So what is being saved to the field "InspectorName"? Very confusing!


    I would do as June7 said: Change all of the look up fields back to a text box for the DISPLAY CONTROL (in the table design).
    I would also remove the MVFs - they are a pain in the neck (only 3 feet lower for me). I NEVER use MVFs.

  15. #15
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by June7 View Post
    If you would follow the instructions I provided the combobox will work. I will repeat (modified to include initials field):

    Combobox properties:

    RowSource: SELECT QEIID, QEINames, QEInitials FROM tblQEINames ORDER BY QEInames;
    BoundColumn: 1
    ColumnCount: 3
    ColumnWidths: 0";1.5";0.25"
    ControlSource: [QEIID] (the field from tblInspectionReports)

    Users will see names but QEIID value will be saved.

    Advise not to build lookup fields in tables. The filename expression can be in textbox instead of query and reference column of combobox.
    =Format([Date of Inspection],"yyyy-mm-dd") & "-" & txtName.Column(2)

    Just bind form to table.

    InspectionName field which is saving initials should not even be in tblInspectionReports.
    Thanks June. Really helpful.

    I can't get the filename expression to work though. Any luck with maybe zipping it back to me to see what you did?

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

Similar Threads

  1. Replies: 3
    Last Post: 04-06-2016, 12:49 AM
  2. Replies: 7
    Last Post: 07-28-2015, 11:50 AM
  3. Replies: 7
    Last Post: 01-21-2014, 11:32 AM
  4. No (+) for parent-child tables in linked database
    By Seamus59 in forum Import/Export Data
    Replies: 3
    Last Post: 11-18-2013, 11:39 AM
  5. Replies: 3
    Last Post: 07-03-2013, 01:20 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