New record in subform - need help to find a solution.

[Behedwin]

Hey

I need some help to solve a problem with creating a new record.
i have uploaded my project in both 2016 and 2003 version of access!
Please view the zip file where both files are.

to find the place i want help finding a solution to you go to:
1. open form "selectprofile_frm"


2. then doubleclick on a name in the listbox
3. go to tab "Kontrakt"
4. here you see a listbox and a button and some textboxes. when i click the button, i want to create a new record in table "contract_tbl".

I have setup things like this
Form selectprofile_frm is where i select what staff member i want to work on.
then i double click and open the proifle in form "profile_frm".
here i view data and update and add new. so far i have only come to create the "kontrakt" tab.
Under the contract tab there is a listbox and a subform.
Subform goes to "contract_frm" that display the contract information.
listbox is just to select what contract to display.

but now i want to add the feature of adding a new contract.
i get it to work just fine when not using the subform... but i want to do it in the subform.
how can this be done?

let me know if i should post screenshots or anything else.

hope someone can help.

upload proj.zip

[ssanfu]

Comments in-line:

Form selectprofile_frm is where i select what staff member i want to work on.
then i double click and open the proifle in form "profile_frm".
OK, did that. I selected Anna.
here i view data and update and add new. so far i have only come to create the "kontrakt" tab.
Under the contract tab there is a listbox and a subform.
Subform goes to "contract_frm" that display the contract information.
The sub form is a bound form with bound controls.
listbox is just to select what contract to display.
When a record is selected in the list box, the sub form moves to that record.
BTW, in the sub form "Contract_Frm", the control named Text20, bound to field "Contract_ID" should NOT be visible because "Contract_ID" is an Autonumber type field.


"when i click the button, i want to create a new record in table "contract_tbl"."
What data and from where do you want to add to table"contract_tbl"?
"Anna" is the record in the main form. When I click on the tab "Kontrakt", the list box displays 2 records.
What now?
Explain (step by step) what should happen/actions taken if you want to add a new contract record.

[Behedwin]

when selecting a row in the listbox in profile_frm under tab "Kontrakt" the subform contract_frm should be populated with data from contract_tbl
The listbox contains a summery of data from contract_tbl
So when selecting a row in the listbox, it brings up full data in several textboxes in the subform (contract_frm)
Now i want to create a new record in contract_tbl.
Click a button in either profile_frm or contract_frm and fill in the textboxes in contract_frm and create a new record.
a new record is then displayed in the listbox as a new contract to be clicked on and viewed and edited.

the ID textboxes are visible just for testing now
when i got things working i will hide them.

[ssanfu]

Well, you didn't explain (with examples) what should happen/how to "Create a New Employment" (Skapa ny anställning) or what you want to see when the button is pressed. (it really helps us if our questions are answered)

I changed quite a few things - I don't remember all of them.

I changed the FK fields in table "Contract_Tbl". They should not have a default value of zero.
I changed the relations in the relationship window.
I edited the queries.
I added/changed the form "sfContract_Frm" (sf prefix = sub form)
I renamed some controls ...( "txtNew" is a terrible name for a button).



See if this is close ....

[Behedwin]

Thank you. I have found a few solutions already. but i can see some improvements being done based on your changes aswell.

We might not agree on all things
for example the relationships i had set were there for a reason (i think) and the names, sure i can have them set to something better

[ssanfu]

We might not agree on all things
for example the relationships i had set were there for a reason (i think)

Agreed...

But here is my reasoning........

The fields in the RED boxes are foreign key (FK) fields. You have a default value of zero set for these fields.
The primary key (PK) fields in the 3 tables are autonumber type fields. Because the primary key fields are autonumbers, they can never have a value of zero.
You do not have referential integrity (RI)set for the relationships between tables "Role_Tbl", "Jobbarpa_Tbl" and "Contract_Tbl".
This allows you the possibility of creating orphan records in "Contract_Tbl".
So you had to change the join type to be able to see these "orphan" records.


Removing the default value of Zero from the 5 FK fields in "Contract_Tbl" and changing/fixing the relationships to include RI,

IMHO, results in a better design. No orphan records allowed.


But this is your dB.... I am just offering ideas that I have found to work for me. I hope this has helped.


Good luck with your project.