472,982 Members | 2,554 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,982 software developers and data experts.

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


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.
Jun 29 '18 #1
9 1419
32,548 Expert Mod 16PB
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.
Jun 29 '18 #2
Should i create a new post or keep this one?
Jun 29 '18 #3
32,548 Expert Mod 16PB
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.
Jun 29 '18 #4
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acNewRec
in contract_frm startup (on load event)
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.OpenArgs) Then
  2.     Me.contract_id = Me.OpenArgs
  3. 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:

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 :)

Attached Images
File Type: jpg Screenshot_1.jpg (50.1 KB, 194 views)
File Type: jpg Screenshot_2.jpg (42.4 KB, 173 views)
File Type: jpg Screenshot_3.jpg (38.1 KB, 177 views)
File Type: jpg Screenshot_4.jpg (29.1 KB, 185 views)
File Type: jpg Screenshot_5.jpg (36.8 KB, 158 views)
Jun 30 '18 #5
32,548 Expert Mod 16PB
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.
Jun 30 '18 #6
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."
Jul 1 '18 #7
32,548 Expert Mod 16PB
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.
Jul 1 '18 #8
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

Expand|Select|Wrap|Line Numbers
  1.     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.

Expand|Select|Wrap|Line Numbers
  1. Set rs = Me.Child22.Form.RecordsetClone
  2. rs.FindFirst "[contract_id] = " & Me.ContractList.Column(0)
  3. If Not rs.NoMatch Then Me.Child22.Form.Bookmark = rs.Bookmark
Jul 1 '18 #9
32,548 Expert Mod 16PB
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.
Jul 1 '18 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: greesh maheshwari | last post by:
i am successfull to Create the Project on the Remote Web Server, now i want to add a Class Library in same project, but the following Error Occur. 1)The project is not filly trusted by the .net...
by: Andreas Klemt | last post by:
Hello, I have an ASPX Page with 8 Web User Controls on it. Some are Visible = False and some are Visible = True Now I wrote in each Web User Control code: Sub Page_Load() If Me.Visible...
by: Shapper | last post by:
Hello, I need to add a new record to an ACCESS database. I get the error: Syntax error in INSERT INTO statement. I have no idea what am I doing wrong. This is my code: ' Set Connection...
by: Joe | last post by:
Hi, I have a simple thing I need to do but just doesn't work in VB.NET. I have a string with HTML code and I want to load it into a HTMLDocument object or something similiar to it so I can...
by: kaleshire | last post by:
I have a form that has two combo boxes which are used to find a record for the subform. An AfterUpdate event on the second combo box runs ... Me.vtrack.Requery this part works perfectly, and when...
by: libra10581 | last post by:
Hi , In my project i need to maintain online users. But some one close the browser with out clicking the logout. So i face the problem to find who is in online currently. Plz tell me the way how...
by: amherst8600 | last post by:
I have a text box in my main form which will show something. I need to build a text box in my subform also show the same thing as it in main form. Thanks
by: MZ | last post by:
Hello, I have a webcam connected to the Internet. I can access the current static picture of the camera using an url link. It shows the picture with high resolution 1600x1200. I would like to...
by: Chocolade | last post by:
Im using win32_printer and im adding to listbox1 a list of the printer properties. but then when im scrolling down in the listbox1 and getting to the last property i see the list of properties start...
by: Hinson3 | last post by:
Hello all, I need a solution similar to the Microsoft Customize Quick Access Toolbar dialog. Where you select the item from one side | Click Add and the selected item is copied to the selected...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.