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.

[NeoPa]

Hi Behedwin.

It seems you've gone to a lot of trouble preparing this in advance, which is good. Unfortunately, you've fallen a little foul of the correct way of doing things on a forum (Most are very similar but we do publicise our own rules and posting advice - How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!).

As a general rule we will not be inclined to look at someone's project for them unless we decide it's absolutely necessary. It involves massively more work for us than a properly worded question presented in text. No-one is generally comfortable going to that extra level to allow a poster to avoid the work and effort of writing the question up properly.

Now, we understand what it's like being new, and we appreciate people mis-step. That's fine. I'm just trying to explain why it is that, even though you clearly have a positive attitude and aren't afraid of doing the preparation, we aren't keen on dealing with this question as it stands.

Please feel free to read the linked article and try again and we'll be glad to try and help. We may even request a copy of your project at some point. If/when we do will be a great time to post it for us.

Good luck and Welcome to Bytes.com.

NB. By the time you read this I'll have removed your project attachment for you.

[behedwin]

Should i create a new post or keep this one?

[NeoPa]

Good question. I'd say keep this one for now and we'll see how we get on. As I say, your first attempt definitely showed promise.

[behedwin]

Okey here goes @NeoPa, I'll try to make a correct post :)


I am trying to create a project where I want to display staff members and view their profiles. A profile contains personal data like address etc, this I have not built yet tho. So far I am building the profiles section called "Kontrakt". Kontrakt means contract and a staff member can have one or many contracts.

I have the following tables:
1. Profile_tbl = contains names of staff members
2. Contract_tbl = contains contract information
3. Role_tbl = subtable to contract, contains names of what contract roles are availible.
4. Jobbarpa_tbl = subtable to contract, contains names of what places staff members can be hired on.

These are the forms I have:
1. SelectProfile_frm = User starts here, gets a listbox to double click the name of the staffmember he want to work on. When double clicking next form opens.
2. Profile_frm = this is the main profile form. Here the name of the staff members is displayed and then a tab control where I have two tabs so far. Tab "Kontrakt" should display the staff´s one or many contracts.
3. Contract_frm = display the contract in several textboxes, is a subform to profile_frm where contract_frm is placed under tab "Kontrakt".

Currently this works on my db:
User can open selectprofile_frm and double click a staff member and bring up the profile_frm. Here I see the names of the profile and I see the tab control. When selecting the tab Kontrakt I see a listbox where I see the contracts of the that specific staff member. The listbox is getting it´s data from contract_tbl and jobbarpa_tbl and role_tbl.
When I select a row in the listbox in profile_frm under Kontrakt tab, it populates textboxes in contract_frm (subform under the listbox).

The subform is with Masterfield as the listbox in profile_tbl and childfield as contract_ID. This is to get the function "when selecting a row in listbox, it display that ID´s data in textobxes in contract_frm).

Now I want to create a new record, a new contract in contract_tbl. To do this my idea is to have a button under the listbox either in profile_frm or in contract_frm. Click the button and all fields in contract_frm should become empty and the user can populate them with new data. When done a new contract record is created and a new row should be displayed in the listbox as a new record/contract.

When I am trying to do this I get errors that the profile_id is not being connected to the new contract record. This I belive is that because my subform is not having the normal child and master relationship, but changing that and I remove the function of having "select row and populate textboxes".

I have tried things like this.
in button on click event

DoCmd.GoToRecord , , acNewRec

in contract_frm startup (on load event)

If Not IsNull(Me.OpenArgs) Then
    Me.contract_id = Me.OpenArgs
End If

but so far I always get stuck on that the new record can not be created since it does not get the correct ID´s right..... profile_id and contract_id

I have attatched a few screenshots that I hope can help understanding what I have done. for those that want, I have also made a short video that you can view here:
https://streamable.com/gv279

I see one thing I could do (dont know how tho).
1. Change the master/child relationship to what access suggest, profile_ID and Profile_ID_SK (primary key and secondary key in contract_tbl). It feels like this would solve the situation of creating a new post.
But if I do this, then the function that populate the textboxes in contract_frm when selecting a row in the listbox will be destroyed and I do not know how to do that in another way.

2. I could create a "add new record" form that is standalone and opens up in a new window... but that is so ugly and nothing I want

Just a heads up. I do want my listbox where to select the contracts. I do not want to change the listbox to a combobox.

And just to clarify: The current amount of columns in my tables are just the beginning. The listbox will display a preview of a contract.... the textboxes in contract_frm will display everything in the future. And I do know that I should hide the ID fields later on.

I hope this post is better than my first and someone are able to come with tips on a solution. Hopefully this post did not become too large and too messy :)

[NeoPa]

It does seem very large and messy. There is too little focus on exactly where you're struggling. You will benefit from learning to think logically and precisely. For many that's a step too far but some can handle it and certainly it helps in all sorts of ways so is worth the effort. You look as if you may get there if you try.

All that said, it seems you've certainly done what you could at this stage to present your problem as well as you can so I'll do what I can to help. That will be limited as I need to understand you perfectly and by half way down I already had a number of points that lacked clarity - for me at least. So, I'll just have to do the best I can with the limited understanding I've got of your problem.

Firstly, like houses built on sand, it's never a good idea to do things differently because you struggle to get the design right. Subforms are made to be linked. Link them unless you want to spend all of your time and energy fighting against Access instead of letting it take the strain for you.

When you have such a setup stop and review what you have. Identify any issues that may still need looking at. Deal with those by reporting here clearly and concisely what your problem is.

I hope that makes sense. Structured thinking can be your friend. It helps both we doing your work in the first place, as well as reporting issues that are much easier for others to help with when you do need extra help.

Good luck.

[behedwin]

I think i see what you are saying.
I realize that if i do link the forms with the normal way this problem of creating a new record might go away.

But then the question is. How do i make an on click event that works for the listbox.
"if row is selected in listbox in profile_frm, then go to that rows ID number record in contract_frm."

[NeoPa]

Remember my earlier advice - Do the preparation first and then look at what you have.

Anticipating problems ahead of the process is a waste of your time and energy. When you actually get there you will know if it's even a problem or not by then and you will also be in a much better position to express your issue accurately and precisely.

Something like the following :
"I've done as you suggested but I find now I'm here that I have a problem ensuring only the correct records are shown in the subform. (Including extra helpful details of course)"

You may never need to, of course. That's another reason to take it step by step.

[behedwin]

I found a solution that seems to work.

I have changed the relationship between the contract_frm and profile_frm. So now i have the following:
link master: profile_id
link child: profile_id_sk

this allowed me easily to create new records by setting this in the "create new record" buttons click event

    DoCmd.GoToRecord , , acNewRec
 

then i could add this code to the listbox after update event and i got the function to populate the textboxes with the data from the selected record in the listbox.

Set rs = Me.Child22.Form.RecordsetClone
rs.FindFirst "[contract_id] = " & Me.ContractList.Column(0)
If Not rs.NoMatch Then Me.Child22.Form.Bookmark = rs.Bookmark
 

[NeoPa]

Sounds good that you have what you need.

If you always want to create a new record then you may want to consider setting the DataEntry property of the form to True.