UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Forcing Parent Form To Assign An Id If Subform Is Selected., Access 2013    
 
   
Tsurugi-Ijin
post Jan 8 2016, 08:20 AM
Post#1



Posts: 73
Joined: 30-January 14
From: Brackley, UK


Ok I promise I tried to find an answer for this before groveling to you guys..

I have a Form_Request linked to Tbl_Request.
On the Parent form I also have a Subform_List linked to Tbl_List; which in itself is a one to many relationship to Tbl_Request.

I am trying to make it so that when someone opens Parent form and decides they want to fill out the List items before the rest of the form, it automatically creates a new record on Form_Request (and Tbl_Request).

At first the problem was that any entry to the Subform would just made new records without any links to the main table.. I tried to change this by making the field required. This did not auto link to a new record on the main table like I had hoped.

I also tried:
CODE
    
Private Sub Subform_List_Enter()

    If Me.Dirty = False Then
        Me![a Combobox on Parent Form] = " "
    Else
        GoTo Sub_Exit
    End If
    
Sub_Exit:
    Exit Sub

End Sub

This half worked as it did force the parent to create a new ID, however if you then re-entered the Subform it would replace whatever was in the Combobox with " " again.. cryhard.gif


Effectively
I am aware that if you select any other field on the Parent form and enter even one character then it creates a new record with an ID number, not just (New).
I am trying to get my Subform to replicate this action but just.. Can't! crazy.gif
Go to the top of the page
 
azolder
post Jan 8 2016, 09:09 AM
Post#2



Posts: 2,428
Joined: 12-February 15
From: SW AZ


I believe the issue has more to do with the main record needing to be saved prior to creating child records (in the subform). Remember Access automatically fills in the foreign key/link field in the child/subform record.

I'm not sure creating the child records prior to the main record being completed, including data validation, is a good idea. Besides, you'd have to add extra code run by a command button to accomplish this, when you can just go to the main form to create the new main record, then proceed to the subform.
Go to the top of the page
 
BruceM
post Jan 8 2016, 09:12 AM
Post#3


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


For starters, are you using the Link Master and Link Child properties of the subform control (the "box" that contains the subform)? Also, have you chosen to enforce referential integrity in the table relationships?
Go to the top of the page
 
missinglinq
post Jan 8 2016, 09:46 AM
Post#4



Posts: 4,637
Joined: 11-November 02



Creating a child record prior to a main record is a terrible idea...which is why Access won't allow it, assuming that there is, indeed, a one-to-many relationship! Doing so would make the possibility of creating an 'orphan record' all too possible, meaning that if the Form were closed without the creation of a parent record, the child record would never appear in the Form again! And if you had to complete a parent record, anyway, before quitting data entry, what would be the point?

Linq ;0)>
Go to the top of the page
 
Tsurugi-Ijin
post Jan 11 2016, 03:21 AM
Post#5



Posts: 73
Joined: 30-January 14
From: Brackley, UK


One to Many relationship is set up and the field for the child-parent is set to Required.

I'm a bit of a perfectionist.. So if a user opens the "data entry" form to create a new record, when they select any combobox or textbox and input information it will automatically create an ID number and therefore a record; this is fine!
However when they select the subform to input information (Which to them obviously just looks like another part of the main form) it doesn't automatically create the ID and so a message box appears saying:
"You must enter a Value in the 'Tbl_Request.PID_Request' field."

This of course looks [censored] to the user who will have No idea what this means!

Which is why I'm hoping that I can work out how to have the main form/table create a record when the subform has information entered IF one does not already exist i.e. the ID is showing as (New).

Hope this clears things up a bit and makes me sound a little less like a moron!
Go to the top of the page
 
Tsurugi-Ijin
post Jan 11 2016, 06:18 AM
Post#6



Posts: 73
Joined: 30-January 14
From: Brackley, UK


Ok so I've found a solution which seems to work but I was wondering if you guys could pick holes in it for me? (I.e. If you can see this causing big issues due to my novice status)

The subforms are set to Visible = False when the form loads, only when the user has put their name against the request do they become visible.
I have text that sits behind the subform that says that they will be able to add list items once they have filled out above information which obviously then gets covered once the subforms are visible again!

Please let me know if you can see any problems that may sprout from this method.. smirk.gif

Gin x
Go to the top of the page
 
mike60smart
post Jan 11 2016, 07:12 AM
Post#7


UtterAccess VIP
Posts: 13,419
Joined: 6-June 05
From: Dunbar,Scotland


Hi Gin

As everyone as pointed out If you have set your Relationships correctly then Access will automatically enter a Foreign Key in the Subform for you.

It may help if you can upload a zipped copy of your Db so we can see your structure
Go to the top of the page
 
BruceM
post Jan 11 2016, 07:24 AM
Post#8


UtterAccess VIP
Posts: 7,971
Joined: 24-May 10
From: Downeast Maine


QUOTE
only when the user has put their name against the request do they become visible.

If this check is performed in the Current event as well as in the After Update event of the name field (if that is what you are doing now) it should help prevent attempts to add subform records. However, if you are performing the check only in the After Update event of the Name control the subforms would remain visible when you go to a new record. In general, if you are asking for review of a system for doing something you need to provide details of the system you are using.

However, if you are managing this from the form alone it will be very difficult to manage every contingency. The database structure needs to enforce the requirement to have a parent record before related records can be added.

Again, are you enforcing Referential Integrity in the table relationships? And have you set the Link Child and Link Master properties of the subform control?
Go to the top of the page
 
azolder
post Jan 11 2016, 08:23 AM
Post#9



Posts: 2,428
Joined: 12-February 15
From: SW AZ


That would be OK if you're only using the form for data entry (only add new records). If it's also used to edit records you should test for the record being new in the form's On Current event. If the record is new then set the subform's Visible property to false, otherwise true.
Go to the top of the page
 
Tsurugi-Ijin
post Jan 11 2016, 09:07 AM
Post#10



Posts: 73
Joined: 30-January 14
From: Brackley, UK


Hey Guys, thanks so much for the comments..

The form is only for data entry with only a "Save record" or "exit and delete" buttons so no chance to look through other records.

Referential integrity has been set up from the start as well as making sure Link Child and Link Master properties are in place.
The main problem was that if the form was newly opened and the subform selected straight away there was no parent record for it to link to (As it still saw it as a (New) record and so empty) which caused an error.

By forcing the user to enter information on the main form before being able to see the subforms has both made sure that there is a record for the subform to automatically link to as well as making sure I have a log of who has been entering information.
Go to the top of the page
 
Tsurugi-Ijin
post Jan 11 2016, 09:09 AM
Post#11



Posts: 73
Joined: 30-January 14
From: Brackley, UK


I will also looking into uploading a copy, I just need to make sure I take sensitive information off..
Go to the top of the page
 
mike60smart
post Jan 11 2016, 09:21 AM
Post#12


UtterAccess VIP
Posts: 13,419
Joined: 6-June 05
From: Dunbar,Scotland


Hi Gin

Glad you now have this working
Go to the top of the page
 
gemmathehusky
post Jan 11 2016, 09:35 AM
Post#13


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


QUOTE
I am trying to make it so that when someone opens Parent form and decides they want to fill out the List items before the rest of the form, it automatically creates a new record on Form_Request (and Tbl_Request).


this is the issue though.

assuming you are initially creating a "dummy" request - then if they just create the details (the subform bit) and abandon the creation of the main bit - you end up with incomplete main records.

as linq says, it's a bit of a bad idea to let the users work in an illogical way - even if they want to.
Go to the top of the page
 
yyy
post Sep 12 2019, 07:04 PM
Post#14



Posts: 18
Joined: 28-May 19



combining the parent and child tabels in a query
and
adding a default value
or with code me.YourField = YourSomething
to 1 of the fields in the parent record
does the trick.

the problem will be if you want to make the parent a single form and the child a continuous form
and the only way to do it is with the subform control.

then you have to DISABLE / LOCK / VISIBLE-FALSE the sub form before entering ino in parent form.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th October 2019 - 02:40 PM