473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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

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.
Jun 29 '18 #1
9 1449
NeoPa
32,556 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
NeoPa
32,556 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:
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 :)





Attached Images
File Type: jpg Screenshot_1.jpg (50.1 KB, 200 views)
File Type: jpg Screenshot_2.jpg (42.4 KB, 180 views)
File Type: jpg Screenshot_3.jpg (38.1 KB, 182 views)
File Type: jpg Screenshot_4.jpg (29.1 KB, 190 views)
File Type: jpg Screenshot_5.jpg (36.8 KB, 164 views)
Jun 30 '18 #5
NeoPa
32,556 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
NeoPa
32,556 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
  2.  
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
  4.  
Jul 1 '18 #9
NeoPa
32,556 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

1
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...
0
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...
3
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...
3
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...
1
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...
3
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...
2
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
7
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...
3
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...
36
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.