Tab Control - How to add a new record

[teachme]

Hi, I have a form and in there I have a tab control with multiple pages.

The db has 3 tables (one-to-many relationships).

Question: How do I add a new record in the tab control? When I try to add a button in the form, it doesnt work and when I create a button within the page of a tab it creates a new record in that particular page only and the data on all other tabs still shows. I want it to create a new record (blank) so when a user clicks a button, all tabs clear and the user is able to all new data in all tabs.

Thank you

 

[Uncle Gizmo]

Are you using subforms?

Sent from my SM-G925F using Tapatalk

 

[teachme]

Are you using subforms?

Sent from my SM-G925F using Tapatalk

Yes.

I have a main form and then a big tab control with 5 tabs (pages) and each page has a sub form.

I tried putting the fieds in the tab controls without the subform but when i dod that it would not let me edit any of the fields which is why i had to put a sub form under each of the pages.

Thanks

 

[arnelgp]

have a look at this:
http://stackoverflow.com/questions/17347503/making-docmd-gotorecord-function-work-on-a-subform

 

[NauticalGent]

Are there fields in the main part of the form, or are all the fields contained on one of the tabs. Also, where is the command button, on one of the tabs?

 

[NauticalGent]

Yes.

I have a main form and then a big tab control with 5 tabs (pages) and each page has a sub form.

I tried putting the fieds in the tab controls without the subform but when i dod that it would not let me edit any of the fields which is why i had to put a sub form under each of the pages.

I assume the form's record source is a query? If so, will it allow you to edit the fields when you run it? If not, that means your RS is read only...you can still make it work but its going to take some hocus-pocus.

 

[moke123]

Is there any master/child relationship between the main form and the subs?

 

[teachme]

Are there fields in the main part of the form, or are all the fields contained on one of the tabs. Also, where is the command button, on one of the tabs?

All fields are inside the tab control and then inside the subform for each of the tabs.

1 - when I put the command button and used the wizard to create a new record on the main form (under page header) it gave me a message "you cant go to the specified record'

2 - when i put the command button to create a new record in the sub form (which is located inside the tab control) it did create a new record but just for that one tab (page) only. I saved it, exited out and then open that form again. The newly created record was there and all other tabs were blank which is how it should be but I didnt have to exit out of the main form.

 

[teachme]

I assume the form's record source is a query? If so, will it allow you to edit the fields when you run it? If not, that means your RS is read only...you can still make it work but its going to take some hocus-pocus.

Yes, the record source for both the main form and all subforms are queries and i am able to edit all records.

 

[NauticalGent]

Have you tried to set the main form's Data Entry property to "yes" to see if all the tabs will let you add records?

 

[teachme]

Is there any master/child relationship between the main form and the subs?

I have 'RecordID' as a primary key on one of the tables which is linked to other 2 tables - see attached

I have 'RecordID' as Master and Child fields in the subform.

 

[teachme]

Have you tried to set the main form's Data Entry property to "yes" to see if all the tabs will let you add records?

Its already set to 'yes'

 

[NauticalGent]

I had a similar problem with a project I was doing a few years back. Your main table with the Master PK is tblProjectInfo. If I didn't have a corresponding record in the to child forms, it would not let me enter any records unless I manually made them.

I had to right some code that inserted a record with the Master PK to avoid this problem. I am sure there is a more interactive way to do it but it worked so I didn't try to find it.

 

[moke123]

2 - when i put the command button to create a new record in the sub form (which is located inside the tab control) it did create a new record but just for that one tab (page) only. I saved it, exited out and then open that form again. The newly created record was there and all other tabs were blank which is how it should be but I didnt have to exit out of the main form.

have you tried a requery after adding the record? It is not really clear to me why your having a problem. irrespective of the tab control your subforms should function the same as if the were outside the tab control.

on another note, your milestones table has repeating fields (ie milestone1, milestone2) this is generally an indication of bad relational design.

 

[teachme]

have you tried a requery after adding the record? It is not really clear to me why your having a problem. irrespective of the tab control your subforms should function the same as if the were outside the tab control.

on another note, your milestones table has repeating fields (ie milestone1, milestone2) this is generally an indication of bad relational design.

Thanks for the tip. However, requery didnt solve the issue but I was able to find a work around as follows:

I created a tiny form (see attached) with just 2 fields and then i added a button with a label 'complete project details' and included the following steps in the macro for that button.

1 - save the project id and project name
2 - open the main form with multiple tabs
3 - go to the last record
4 - close the tiny form previously opened

So in summary when the user enteres a new project ID and a project name for a new project - which are the mandatory fields they click on 'complete project details' button which takes them to the main forms with multiple tabs (last record which is the newly created record)

I know its probably not the best design and one additional click but it works for now. I may just add a few more fields to make it more pretty lol

So your comment on repeating fields - can you clarify what you mean? I am tracking 5 critical milestones in the db and these (milestone 1, milestone 2)....are separate fields not repetitive. These are just like any other individual fields I may be missing something.

Thank you

 

[JEEDEE]

Hi,

The error is in your relationships.
You have a "one to many" relationship from one table to two other tables. The "one" side is a unique key, while the "many" sides are not. This is not redundant, and you have to reconfigure that.
Watch carefully how you create relationship.

A simple way to check if your relationships are redundant, is to open your "one" table, you should see a cross on the left side, indicating there is a "many" table related.

In your case this is not possible, you have to add the "recordid" to all tables and make a unique key of it (together with your other keys)

 

[moke123]

So your comment on repeating fields - can you clarify what you mean? I am tracking 5 critical milestones in the db and these (milestone 1, milestone 2)....are separate fields not repetitive. These are just like any other individual fields I may be missing something.

The snapshot of your tables you posted didnt show all your fields but i did note the milestone1,milestone2,etc. I was just pointing out this is usually an indicator of non-normalized design, not always, but usually. A for instance may be what happens when a 6th milestone gets thrown into the mix. do you have to re-write all your forms and queries, etc. Or do you have a table of milestones reached with a lookup table of those milestones types. Then adding a new milestone is just a matter of adding it to the lookup table and perhaps tweaking a few things. i tend to favor making things as flexible as possible as requirements often change over time.

 

[teachme]

The snapshot of your tables you posted didnt show all your fields but i did note the milestone1,milestone2,etc. I was just pointing out this is usually an indicator of non-normalized design, not always, but usually. A for instance may be what happens when a 6th milestone gets thrown into the mix. do you have to re-write all your forms and queries, etc. Or do you have a table of milestones reached with a lookup table of those milestones types. Then adding a new milestone is just a matter of adding it to the lookup table and perhaps tweaking a few things. i tend to favor making things as flexible as possible as requirements often change over time.

I see what you are saying.

So I will be tracking 10 milestones in the db and none of the projects in the portfolio of accounts will have more than 10 milestones.
For reporting, if there is less than 10 milestones, I will be playing with shrink, visible, grow etc. properties of the fields (for unused milestones) so there is no empty space in reports.

To that point, is there is way to dynamically add fields in the db (forms). I dont think its possible but I thought I should ask.

 

[AccessBlaster]

Sounds like a survey database, questions 1-10.


Duane Hookom's - At your survey

 

[moke123]

So I will be tracking 10 milestones in the db and none of the projects in the portfolio of accounts will have more than 10 milestones.
For reporting, if there is less than 10 milestones, I will be playing with shrink, visible, grow etc. properties of the fields (for unused milestones) so there is no empty space in reports.

To that point, is there is way to dynamically add fields in the db (forms). I dont think its possible but I thought I should ask.

if i'm following what youre saying, there shouldnt be a need to add fields in forms or reports. You would use subforms or subreports to only return the relevant records. if you were to use repeating fields ie. milestone 1,2,3,4 etc you would have to but if you had a tblMilestones (MilestoneID,ProjectID,MilestoneTypeID for instance) and you join on the projectID it would only return the relevant records in the subforms/reports.