Results 1 to 11 of 11
  1. #1
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95

    Populate 3rd field based on fields 1 and 2 (selected by combos)

    Hello there.



    On click of a button [btn_Confirm_Son] I need VBA code to transfer my choice from combos: [cbo_Select_Name_Son]
    and
    [cbo_Select_Surname_Son]
    to a field
    [tbl_Members].[Son]
    (Here I need to join Son’s Name and Surname)

    Also, on click of a button [btn_Confirm_Dauhter] I need VBA code to transfer my choice from combos: [cbo_Select_Name_Dauhter]
    and
    [cbo_Select_Surname_Dauhter]
    to a field
    [tbl_Members].[Dauhter]
    (Here I need to join Dauhter’s Name and Surname)

    Please find my database in ZIP attached below.
    Thanks.

    Click image for larger version. 

Name:	Forum 30092017.jpg 
Views:	20 
Size:	248.4 KB 
ID:	30549
    Attached Files Attached Files

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can help you achieve this, but it's not only not necessary, it's not good practice generally speaking. This is because you're repeating information in your tables when it isn't necessary. The great part about forms is that they permit the display of information in just about any fashion while allowing you to maintain good design practice in your tables. What you should be doing is having child field that contains every child name for a parent. Thus you would have Bravo as the surname for Dave and Emily, who are children of John (not John Bravo). Your form can assemble values as you wish by using expressions that concatenate values inside of text boxes. The calculated control is visible while the controls that contain the individual parts are not.

    That being said, if you still want to do the original thing, have a control on your form that contains =[Name] & " " & [Surname]
    I didn't download your file for various reasons.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Hi there.. Thanks for the answer.. I need this as only some records have son and a daughter... This database attached is just to show what I need.

    Actually, in my real database I have appx 100 000 records and when there is a need, I must connect my record to another two records.. For example, in my database I have welds (pipe joints created by process of welding) which we test using radiography-RT (father here). When that weld appears to be bad on RT film, as an addition, we must RT two more welds (penalty shot) of same person whose weld is bad..This is to make sure that this his mistake was an exception, not a rule. This is why every now and then I must assign another two records to the one which is currenty shown on my form..

    Then, I can define a query to show me each weld with bad RT film and for each of those, two penalty shots. My pursuit here is to clearly see from the database, eg, for 50 bad RT films, which 100 welds were shot (penalty shots) and for each of those, which two are assigned.

    Hope this clarifies situation a bit..

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This appears to be a 'recursive' type database - family tree and manufacturing are two common examples.

    Nice to know the true nature of database. So names are not an issue. You just need to save record ID into the two 'child' fields.

    VBA should not be needed. Bind each combobox to respective field. However, this creates situation where selection in each combobox cannot be allowed in other combobox. Review https://www.accessforums.net/showthread.php?t=68235

    Alternatively, instead of 2 fields/comboboxes for 'children', have 1 for 'parent'.

    In query do a self-join on the 1 or 2 fields to show the associated data for each 'parent' or 'child'.
    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
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Thanks for your unswer.. Now I know where to start.. I appreciate your directions

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Methinks I would keep the penalty shots in a different table. For any RT id, there may or may not be child records. If there are, the RT id would be the foreign key in tblPenaltyShots. Easier to deal with related records rather than have to worry about Nulls in the child field(s) for RT records without associated secondary testing, IMHO.

  7. #7
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    This would satisfy me the most.. How do I connect my ID to other two IDs?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I understand the relationships, consider a main form with the one/parent side controls (fields) in its header. A subform datasheet (or perhaps continuous subform) on that form is where you enter the 2 penalty shot records for the parent (RT) record. Link the subform to the main via the RT id field (autonumber?) so that for each unique RT, there is a corresponding value in the penalty records where the RT id is a foreign key in penalty records. The key thing is that the RT value has to be unique, so if it's remotely possible that film numbers could repeat/duplicate, I'd use an RTid autonumber field to tie them together. This approach means that RT parent data must exist before ps records can be created. Consider the user, who may or may not attempt to enter subform data before there is a valid parent showing in the main form. The table data might look like
    Rtid
    RTNo
    1 1989
    2 4568
    3 7852

    Psid
    Rtid_FK Position
    1 2 1
    2 2 2
    This shows that for RTNo 2, there are 2 penalty shots. I threw in something to denote that they have a position, which may or may not be relevant. If it is, they are in a normalized form rather than putting each one it it's own field. While the latter would work, it isn't an example of normalization at best. At worst, should you decide to add a 3rd shot in the future, you'd need to add a field - a sure indicator of inefficient design. Indexing to prevent duplicates of the FK and position might not be advisable here. Should any ps have to be re-shot (say, due to imaging equipment or operator failure) you might want to reflect the fact that a position had to be redone. Thus a field to denote the difference between two FK's with the same position (Date/Time?) would be needed. This would allow you to report on how many re-takes there were in a period, which might indicate some sort of corrective action is required.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Connect what to what? Are you going with Micron's suggestion for a separate table? Use form/subform arrangement for data entry.
    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.

  10. #10
    vazdajic is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    95
    Before I start explaining what I want, sooooory for (maybe) complicating things here..

    On the picture below I have shown what I would like to achieve here..

    As only some welds will be bad on RT, I want IDs of those welds in a separate table, in a same row with IDs of his penalty shots welds (additional RT shots, up to 10).

    On the picture below, I have shown how I would like to see that overview on my separate Form. I know that as there is only one source table for all welds [tbl_Welds], I need just to have IDs saved, although, once again, for each of those IDs I need to see weld number and its KKS on the form (see KKS as a family name).

    Click image for larger version. 

Name:	Forum 04102017.jpg 
Views:	10 
Size:	283.1 KB 
ID:	30593

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Both June7 and I gave you pretty much the same answer - a main form with a subform datasheet. The penalty shots would be in rows not columns. Forget your current setup; my second paragraph in my last post says as much. I also get the impression that you need to research what subform datasheets (or continuous subforms) are, how they work and how to create them.

    The only difference now is that it seems you ought to have a table for primary films (tblPrimary), a secondary table for films shot as a result of a first level failure (tblSecondary) and from your latest post, tblTertiary for films shot as a result of a second level failure. Each of these would contain the FK from the primary table in order to associate any level that's beyond the primary shot - as shown in second table of post #8. Add quaternary, etc. if you have to go deeper. I might call the autonumber (PK field) as SecID, TertID, QuatID, etc).
    Last edited by Micron; 10-04-2017 at 05:51 PM. Reason: clarification

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

Similar Threads

  1. Auto-populate 1 field based on 2 fields
    By Ray Peterson in forum Queries
    Replies: 3
    Last Post: 11-10-2015, 12:42 PM
  2. Replies: 7
    Last Post: 11-28-2012, 01:41 PM
  3. Fields populate based on another field
    By jlclark4 in forum Forms
    Replies: 3
    Last Post: 12-27-2011, 05:21 PM
  4. Populate one field based upon another fields data
    By BigBrownBear in forum Queries
    Replies: 1
    Last Post: 03-23-2010, 04:27 PM
  5. Replies: 3
    Last Post: 10-05-2009, 07:22 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