Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / July 2008

Tip: Looking for answers? Try searching our database.

Maintaining one-to-many relationships between main forms and subforms

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
tm_6187 - 05 May 2008 08:39 GMT
I want to open my subforms from my main forms by using command buttons.  My
command buttons work well and successfully open the appropriate subforms,
however, the parent-child or one-to-many relationship is not maintained as it
is if I work off of a nested view where both parent and child are visible at
the same time.  For example I want to use an autonumber as my primary key and
then use a number with the same field name as my foreign key in the subform.
Thus, when I click the command button to open the subform, I would like the
primary key's autonumber to populate automatically in the subform's foreign
key field.  How do I accomplish this?  please help...I am stuck.
ruralguy - 05 May 2008 15:22 GMT
SubForms are displayed on MainForms with the use of a SubFormControl that has
LinkChild/MasterFields properties that does this work for you behind the
scenes.  In order to accomplish the same effect with a separate form, you
will need to pass the ForeignKey to the other form and then use maybe the
Dirty Event to populate the FK field.

>I want to open my subforms from my main forms by using command buttons.  My
>command buttons work well and successfully open the appropriate subforms,
[quoted text clipped - 5 lines]
>primary key's autonumber to populate automatically in the subform's foreign
>key field.  How do I accomplish this?  please help...I am stuck.

Signature

RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Ron2006 - 05 May 2008 17:52 GMT
Another way:

1) Mainform 1   - the one you currently have with the buttons
  A) as part of the code for the button put    if me.dirty = true
then me.dirty = false
       This will save the record.

2) Create a second "main Form" that has the same table/query as the
record source. The only field necessary is that recordID field

3) Place the subform that you already have designed on this second
mainform and have it use up all the space (make MainForm2 just large
enough to hold the subform)
4) establish the parent/child relationship with this mainform2 ID
information. This will initiate all of the automatic logic of parent
child forms.

5) Go back to the calling buttons and change the form calls to include
(using your field names) the criteria of
         "[ID] = " & me.ID

Ron.
tm_6187 - 16 May 2008 07:09 GMT
Ron,

Thanks for your help.  Steps 1-4 were applied.  Works great so far except
step 5.  I do not know what you mean by "form calls".  I don't know where to
enter that.  Right now, parent-child relationship is maintained while working
on the subform, but when I added a second or new record in the main and then
went to the subform to enter records it did not populate the ID for the
second record of the new form.

EX... My mainform primary key is CaseID, which is the foreign key on my
subform.  The primary key is autonumber and foreign key is number.  Thus,
when I entered the first record on the main - the CaseID field assigned "1"
and when I clicked on the control button for the subform (steps 1-4 were
applied) CaseID on subform populated "1".  When I entered a new record on the
mainform CaseID field assigned "2" but when I clicked the control button for
the subform CaseID remained "1" and did not change to "2".  Probably because
I was not smart enough to accomplish your 5th step?

>Another way:
>
[quoted text clipped - 18 lines]
>
>Ron.
Ron2006 - 16 May 2008 14:00 GMT
5) Go back to the calling buttons and change the form calls to
include
(using your field names) the criteria of
         "[ID] = " & me.ID

In the ONClick event of the button to call the other form you will
find something like this

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "Form2"

   DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command21_Click:
   Exit Sub

Err_Command21_Click:
   MsgBox err.Description
   Resume Exit_Command21_Click

End Sub
====================

What you want to do is to add the criteria..
so it will become something like this.

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "Form2"

   stLinkCriteria = "[ID]=" & Me.ID
   DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command21_Click:
   Exit Sub

Err_Command21_Click:
   MsgBox err.Description
   Resume Exit_Command21_Click

End Sub
============================

Without that extra little bit of criteria, you were simply looking at
ALL of the records in the subform.
With the criteria you will only get the records that belong to the
main form.

You almost had it.    But you will NEVER forget it once you see it
working.......

Ron
tm_6187 - 08 Jul 2008 03:08 GMT
Ron, this worked great buy I have another question.  To make these forms as
idiot proof as possible I need to add something.  During testing, I entered
test data through my forms and noticed out of habit I often hit the enter key
instead of the tab key to move from one field to the next.  I typed the date
in the first fied, hit enter, and it created a new record instead of going to
the next field in the form.  Do you have code that will cause the form to
treat the enter key as if it were the tab key and send the cursor to the
following field, or even to disble the enter key all together.  The users for
this database range from novice to less than novice.

>Ron,
>
[quoted text clipped - 19 lines]
>>
>>Ron.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.