Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10

    Adding a new record to a subform within a form

    This is such a simple question, it has to be - I have very little experience with Access and VBA but have read and watched so many videos my mind is mush. I cannot get a new record to appear in a sub form.



    I have a form, the main form called [ContactMaster], on that form are two sub forms, one called [DiveProfile] and the other called [StudentProfile]

    I have three tables - [Contact] with primary key called [ID], [DiveProfile] and [StudentProfile] each have a foreign key called [CustomerID] - there is a relationship between the foreign keys and the primary key.

    The purpose of this database is to keep track of potential customers, add some data to their dive profile if they've dove with us and/or then add some data to their student profile if they've taken classes with us. Not every contact will have a dive or student profile - some contacts can have just a dive or student profile - some can have both. We want to eventually be able to search and sort so we can send those annoying emails to different types of customers. The main form with the subforms needs to be simplistic so that someone can easily navigate around to view/edit/add the known information about the contact.

    The form opens just fine, the first contact has contact info, dive profile and student profile info in the tables already - you can see all three forms and the info.

    There is an unbound combo box [cboFullName] on the main/parent form that I use to search for an existing contact - it works perfect in a way. If a contact is pulled up that doesn't have a dive or student profile, those form areas are blank, nothing can be seen in them and I think I'm ok with that, it wouldn't matter either way really. Here's the code for the combo box events that calls up the new contact:

    Private Sub cboFullName_AfterUpdate()
    On Error Resume Next
    Dim rst As Object
    Set rst = Me.RecordsetClone
    rst.FindFirst "[ID]=" & Me.cboFullName.Value
    Me.Bookmark = rst.Bookmark
    End Sub

    And

    Private Sub Form_Current()
    On Error Resume Next
    Me.cboFullName.Value = Me.ID.Value
    Me.AllowEdits = False
    End Sub

    The problem I'm struggling with is when I need to add say dive profile data to an existing contact, I can't get the sub form [diveprofile] to go to a new record. Even on the main form, when trying to add a new contact - the main contact info can be entered but there is just a blank area in the [diveprofile] section. It'd be nice to be able to add this info using the edit button on the main form. I've tried going to a new record, I've tried changing the value of [CustomerID] (which always seems to be null even after setting it to have a default value). It almost seems as though because there's no record in [DiveProfile] associated with the record in [ContactMaster] - the form [DiveProfile] doesn't even open or populate.

    This has got to be stupid simple, please help!!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might be simple, but when reading your post I come up with all sorts of questions around relationships, navigation controls, link master/child properties between subs and main, data entry property - to start with. If you could post a zipped copy of your db it would make it much easier for us. If you have sensitive info, you could update names/contact details with queries, or this might help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10
    Quote Originally Posted by Micron View Post
    Might be simple, but when reading your post I come up with all sorts of questions around relationships, navigation controls, link master/child properties between subs and main, data entry property - to start with. If you could post a zipped copy of your db it would make it much easier for us. If you have sensitive info, you could update names/contact details with queries, or this might help.
    No problems - all data is bogus test stuff. ContactMaster is the form in question - ignore the command buttons that are on the subform DiveProfile and some of the VBA code in that one also - I had it there from trying things and trying to track what happens when.

    Thank you

    I can't get any file to attach using that attachment manager, it'll show loaded 100% but no file.... Using Chrome will try and use something else

  4. #4
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10
    Quote Originally Posted by Micron View Post
    Might be simple, but when reading your post I come up with all sorts of questions around relationships, navigation controls, link master/child properties between subs and main, data entry property - to start with. If you could post a zipped copy of your db it would make it much easier for us. If you have sensitive info, you could update names/contact details with queries, or this might help.

    I see what was going on, had pictures in there just for testing that made it too big - here's the file.
    Attached Files Attached Files

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you're going to find that there are some issues with your schema but I'm going to save this for tomorrow as it's getting late. There's the usual special characters, etc. in names, ambiguous names and such. But you have DOB in a profile table, plus you have multiple fields for the same basic info (course#, instructor for a start). If you have never read up on database normalization, now would be a good time. Some bedtime reading...
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10
    I'm in Cozumel Mexico and we are supposed to get hit with a hurricane tomorrow night, I have some prep and running around to do right away in the morning but I'll get on the reading.

    Where you are seeing the multiple fields for courses and instructors - a student can take many courses, we'd like to keep track of all of them, a different instructor can teach those courses also. It would be like your course history with us - each specific course having a unique date with possibly a different instructor.........

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    See the attached in regard to your Blank Form

    If you open frmContact you will now be able to add a new Record and see the subform for the Dive Data Entry. It needs to be edited to display the data as you want it.

    I also agree with micron you have what is known as Repeating Groups in your StudentProfile table.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10
    Doing hurricane prep - will read this tonight - much appreciated, they are shutting the power off probably at midnight and who knows how long it'll be off - welcome to paradise!

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @mike60smart gave it the right name, but I'll add something to it.
    If you have Course1, Course2, .... as field names, course is the group and you are repeating these as fields. The litmus test for incorrect design like this is, if you added a course at some point you'd have to add a field to at least one table, likely need to add to every related table, add field to queries, add a control to forms ... It doesn't matter if you never would, it's good enough that we ask the question even if it is hypothetical. Having to do all this is a big red flag even if it doesn't go as deep as I've illustrated.

    You might have a solution that you can live with - for now. I would strongly advise you to read through all that stuff before you start over, and start over you should, because if you don't fix what you have you are going to continue to struggle. Sorry if that's not what you needed to discover, but it's what you need.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Chuck,
    I set up a many-to-many arrangement and added junction table to adddress the repeating courses as mentioned by Micron.
    Favorite dives should also be MTM, but I left that for you.
    Added tab control with 2 tabs to accommodate the Dive Profile and Student Profile forms. The MTM junction table form is a subform of the Student Profile form.
    The tab control eliminates the unwieldy vertical scrolling to see both forms.
    Added custom navigation buttons to main form to avoid having to go to the bottom for the default navigation buttons.
    Note that both the DiveProfile and StudentProfile tables are 1 to 1 with the Contacts table.
    There are other issues to be looked at that I did not address in this mod.

    AmbientDiver-davegri-v01.zip

    Click image for larger version. 

Name:	ReMaster.png 
Views:	44 
Size:	66.4 KB 
ID:	43121

    and the relationships

    Click image for larger version. 

Name:	MTM.png 
Views:	46 
Size:	51.9 KB 
ID:	43122
    Last edited by davegri; 10-07-2020 at 06:58 AM. Reason: clarif

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    While you're weathering the storm, you deserve a bonus. Here's a revision to above that adds the MTM for the favorite dives.

    AmbientDiver-davegri-v03.zip

    Click image for larger version. 

Name:	MTMDive.png 
Views:	40 
Size:	58.4 KB 
ID:	43148

    Click image for larger version. 

Name:	junc.png 
Views:	39 
Size:	39.2 KB 
ID:	43149

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I still wonder about the diver profile table. Methinks there are a few fields in it that probably shouldn't be there.
    - there would only be 1 never ending note - never dated?
    - no skill or training level history
    - only 1 cert, 1 policy, 1 question, 1 release, 1 emgContact/number, etc etc.

    You have to know the particulars of a business or process to know for sure, but it sure looks like an incorrect schema to me without that in depth understanding.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the comments/advice provided so far. Before getting too deep into forms and/or reports, it is good strategy to ensure your tables and relationships accurately support your business processes. As others have commented, there may be issues with some of your table designs. It would be helpful to you and readers if you would give us a few examples of the processes in your business along with some sample data. For instance, why is DOB in the DiveProfile? Is this the DOB of the Student? Do you have any representative documents for your current business--application, student info, dive sites, skills and levels. See this link for a few ideas re vetting your tables and relationships.
    Good luck with your project and the storm.

  14. #14
    Chuckp is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Posts
    10
    First hurricane for me, thankfully a small one but like being in a small tornado for 3 hours straight. Damage wasn't too bad other than lots of trees down and a dog that'll probably never be the same. I literally just got internet back today - some things move really slow here.

    I see the comments and was down to a post that addressed the multiple fields for classes - at first I couldn't understand why not but now I see and think I understand that and was just thinking how I'd deal with that - I scrolled down and see that you dealt with it the way I was thinking....

    I'm going to do some more digesting here tonight and tomorrow - I can't thank you all enough @micron and @davegri - not sure if that's the right way to tag on this board or not

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    not sure if that's the right way to tag on this board or not
    Always nice to receive such heartfelt thanks, but davegri deserves a rep boost for all that work. There's a little star icon at the bottom of each of your posts. He's probably too polite to ask for himself!

    It's not that I'm lazy, it's that I think you learn more by doing (but OK I'm lazy too).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-09-2017, 09:13 AM
  2. Replies: 4
    Last Post: 05-04-2017, 01:16 AM
  3. Replies: 21
    Last Post: 10-19-2015, 05:38 PM
  4. Replies: 6
    Last Post: 08-22-2012, 03:24 AM
  5. Replies: 4
    Last Post: 03-14-2012, 10:08 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