Adding a new record to a subform within a form

[Chuckp]

I'm still confused - my programing background is using cards back in 1984 doing Fortran, Assembler and Logic classes - I've never done VBA, C+ all this new fancy stuff and most of all, even back then, I never took a Cobal or any other database class. I think somewhat of what I was trying to do was simplify things because I'm not sure exactly how things programed - I get the relationships and better get some other things.



Micron's list of links, read thru those - understand the naming reasons of forms, combo boxes etc.. The normalization stuff, I sorta grasp it, maybe more than sorta. I wondered about the list of courses, but again went the easy route because of not knowing better - mike60smart provided that link that was almost an exact example of the problem and how to correct it - I get it, understand fully why to do it that way and of course davegri did it that way.

Reasons why things are where they are:

Contact Form really is just plain Jane who you are, how to get a hold of you. We can get your contact info from our website inquiry, maybe you stopped in or maybe someone referred you to us. Just because you are in our contacts, you might not have a diver profile and you might not have a student profile. Yes we have a spot for a picture and once you've been into see us, we'd add that picture more as an aide for the office person to recognize you and call you by name but it's also a requirement for students because I have to submit a picture when I certify you and every dang instructor forgets to take the thing - I want it right at the top so everyone can get blamed for not taking the picture.

Diver Profile is filled out while you are here or maybe some info comes from initial contact. We like to know everything about you - even to the point of where you like to stay at, where you like to eat and what you drink. Business here for us, we treat everyone as family, the less I have to ask you every time you are here, the better. There are customers that are here every month to ones that come once a year. There are forms - liability, medical, general waiver etc that I need you to fill out yearly, some of those forms are needed if you are over 50 so I need your birthdate. Again, when Joe walks in the door every two months - I wanna ask how his wife is doing verses all these little technical questions - I think there's maybe 75% of the stuff we'd want to know about you on that existing form.

Student Profile - not every contact will have a student profile nor will every dive profile have a student profile. There's some legal forms required for students, some are good for a year, some only for that class - I think it's somewhat too complicated to encompass everything maybe but the catch all is the student record file - we must keep a paper one on hand for five years for every student - the agencies change requirements all the time and different agencies have different requirements - I'm OK with that. My biggest want is seeing who you took previous classes with (I need to add a note section), were you happy and if you were, I certainly want to put you with that instructor for any other classes.

Aside from the duplicate redundant course stuff in the student profile - my original problem was this: Jim Jones emailed us, we added his contact info into the database but now he's standing in the office and I need to enter some diver profile and student profile information for him. I thought I could have one master form (Contact Info) and two subforms (Dive and Student Profiles - the tabs are great ideas) so that the office person could just enter away BUT on this master form, if a contact has no dive or student profile, those forms will not load or do not appear which makes sense because there is no blank record available. I have the primary and foreign keys set up the way shown above - I just tried everything and cannot get a new record to open in either of the sub forms. It seems stupid simple to me that I'm just missing an obvious thing.

Dive and Student Profile are dependant Contact - you must have a contact to have a dive or student profile - I just need to be able to create those profiles and tie them to the contact.... It'd be great to have all three forms within one.

The zip files that davegri and mike60smart do not work, I couldn't get to any other records....

[davegri]

The zip files that davegri and mike60smart do not work, I couldn't get to any other records....

That's pretty cryptic as to what exactly the barrier is, but perhaps this could help:
Trusted Locations:

https://support.microsoft.com/en-us/...3-4e7c67147fb4

And thanks for the star!

[Chuckp]

That's pretty cryptic as to what exactly the barrier is, but perhaps this could help:
Trusted Locations:

https://support.microsoft.com/en-us/...3-4e7c67147fb4

And thanks for the star!

Well that was dang cryptic as to what the problem was LOL - I didn't see that at all - does that only happen if files are brought from the outside?

I see the code for the arrow forward and backward and think I understand that but could you explain this a little bit?

Private Sub cboFullName_AfterUpdate()
Dim strCriteria As String
strCriteria = "[Contact_PK] =" & Me.cboFullName - is this in essence saying hey whatever name we are on, add the primary key to it?
Me.RecordsetClone.FindFirst (strCriteria) - I'm not sure of this, going to the beginning, but then going to whatever name was in the combo box?
If Me.RecordsetClone.NoMatch Then
MsgBox "No entry found" - would only happen with no data?
Else
Me.Bookmark = Me.RecordsetClone.Bookmark - setting the actual record to the manipulated record?
End If
End Sub

I was confused as to how access actually opens the form and subform - I think I read it opened the subforms first, then the main form - but couldn't see where it assigns or looks for the foreign key within the subform to populate it or not....

I see it doesn't save blank records (like in dive profile if nothing was added) which I was concerned about - I guess why is that?

A question about data - I understand why we don't want more than one value in a single location - I'm talking about Favorite Dive Site in the dive profile form. If, those values were merely going to be text, info that is never searched, queried or whatever - the two values are there just for the user to read - is it ok to have or allow multi values in a lookup box? I read that multiple data is bad, when is it good?

And Thank You - if you ever want to dive in Cozumel - you're all welcome here!

[davegri]

Well that was dang cryptic as to what the problem was LOL - I didn't see that at all - does that only happen if files are brought from the outside?

No, applies to DBs you create also.

I see the code for the arrow forward and backward and think I understand that but could you explain this a little bit?

Moves the main form from record to record.

Private Sub cboFullName_AfterUpdate()

Basically (pun intended) this says move the main form to the PK specified in the combobox.

I was confused as to how access actually opens the form and subform - I think I read it opened the subforms first, then the main form - but couldn't see where it assigns or looks for the foreign key within the subform to populate it or not....

That hookup is defined here:

I see it doesn't save blank records (like in dive profile if nothing was added) which I was concerned about - I guess why is that?

It won't add a child record unless something is typed in first.