How to sometimes open form to new record?

I have a parent form which is then linked to a child form. I want to
have two buttons on the parent form, one to open the child form and
display all the records with the corresponding foreign key, and one
button to open the child form to a new record with the corresponding
foreign key already inserted. I have figured out how to open the child
form to a new record, but I cannot figure out how to insert the parent
ID into the child foreign key. Help! Thanks.

0
meghanwh
9/20/2007 2:15:47 AM
access.forms 6864 articles. 2 followers. Follow

5 Replies
723 Views

Similar Articles

[PageSpeed] 31

Megan,
    You don't have to enter the ParentID in the Child subfrom records...

    Establish a one many relationship between the Parent table and the Child 
table (via the ParentID).
    Set up the form for a Parent/Child relationship between the main form 
and the subform (via ParentID also).

    Because of those realtionships, any new record added to the subform will 
have it's ParentID set to the ParentID value on the main form.
    That's the beauty of a relational database...
-- 
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

<meghanwh@gmail.com> wrote in message 
news:1190254547.030229.230740@22g2000hsm.googlegroups.com...
>I have a parent form which is then linked to a child form. I want to
> have two buttons on the parent form, one to open the child form and
> display all the records with the corresponding foreign key, and one
> button to open the child form to a new record with the corresponding
> foreign key already inserted. I have figured out how to open the child
> form to a new record, but I cannot figure out how to insert the parent
> ID into the child foreign key. Help! Thanks.
> 


0
Al
9/20/2007 4:36:34 AM
On Sep 20, 12:36 am, "Al Campagna" <newsgro...@comcast.net> wrote:
> Megan,
>     You don't have to enter the ParentID in the Child subfrom records...
>
>     Establish a one many relationship between the Parent table and the Child
> table (via the ParentID).
>     Set up the form for a Parent/Child relationship between the main form
> and the subform (via ParentID also).
>
>     Because of those realtionships, any new record added to the subform will
> have it's ParentID set to the ParentID value on the main form.
>     That's the beauty of a relational database...
> --
> hth
> Al Campagna
> Microsoft Access MVPhttp://home.comcast.net/~cccsolutions/index.html
> "Find a job that you love... and you'll never work a day in your life."
>
> <megha...@gmail.com> wrote in message
>
> news:1190254547.030229.230740@22g2000hsm.googlegroups.com...
>
> >I have a parent form which is then linked to a child form. I want to
> > have two buttons on the parent form, one to open the child form and
> > display all the records with the corresponding foreign key, and one
> > button to open the child form to a new record with the corresponding
> > foreign key already inserted. I have figured out how to open the child
> > form to a new record, but I cannot figure out how to insert the parent
> > ID into the child foreign key. Help! Thanks.

Thanks Al. The childform isn't actually a subform, it's an entirely
separate form. i did this because the detail goes down to 6 levels,
and to have that many subforms would be quite cumbersome. Perhaps I've
used the wrong terminology. So is there a way to program a button to
open a form to a new record and insert a field from the old record
into the new, while at the same time allowing that form to be opened
with a different button to the first record? Thanks.

0
meghanwh
9/20/2007 11:22:03 AM
On Sep 20, 7:52 am, NKTower <NKTo...@discussions.microsoft.com> wrote:
> I'll use some generic names here, but you should be able to adapt to your
> situation:
>
> "frmMain" - PK is named MainPK
>
> "frmOther" - has a field linked to a foreign key named OtherFK
>
> Add this code to frmOther
> Option Compare Database
> Option Explicit
>
> Dim frmMainHandle As Form
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
>   If IsNull(frmMainHandle.MainPK) Then
>     MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
>     Cancel = True
>     Exit Sub
>   End If
>   Me.OtherFK = frmMainHandle.MainPK
> End Sub
>
> Private Sub Form_Close()
>   Set frmMainHandle = Nothing
> End Sub
>
> Private Sub Form_Open(Cancel As Integer)
>   If Not CurrentProject.AllForms("frmMain").IsLoaded Then
>     MsgBox "This form should not be opened without frmMain being open.",
> vbOKOnly, "Error"
>     Cancel = True
>     Exit Sub
>   End If
>   Set frmMainHandle = Forms![frmMain].Form
> End Sub

OK this is the code for the second form (frmLevel2Goals):
Option Compare Database
Option Explicit

Dim frmMainGoalsHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
  If IsNull(frmMainGoalsHandle.GoalID) Then
    MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
    Cancel = True
    Exit Sub
  End If
  Me.MainGoalID = frmMainGoalsHandle.GoalID
End Sub

Private Sub Form_Close()
  Set frmMainGoalsHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
  If Not CurrentProject.AllForms("frmMainGoals").IsLoaded Then
    MsgBox "This form should not be opened without frmMain being
open.",
vbOKOnly , "Error"
    Cancel = True
    Exit Sub
  End If
  Set frmMainGoalsHandle = Forms![frmMainGoals.Form]
End Sub

Private Sub Level3Btn_Click()
On Error GoTo Err_Level3Btn_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmLevel3Goals"

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

Exit_Level3Btn_Click:
    Exit Sub

Err_Level3Btn_Click:
    MsgBox Err.Description
    Resume Exit_Level3Btn_Click

End Sub
Private Sub NewLevel3Btn_Click()
On Error GoTo Err_NewLevel3Btn_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmLevel3Goals"

    stLinkCriteria = "[Level2ID]=" & Me![Level2ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "GoToNew"


Exit_NewLevel3Btn_Click:
    Exit Sub

Err_NewLevel3Btn_Click:
    MsgBox Err.Description
    Resume Exit_NewLevel3Btn_Click

End Sub
Private Sub BacktoMainGoals_Click()
On Error GoTo Err_BacktoMainGoals_Click


    DoCmd.Close

Exit_BacktoMainGoals_Click:
    Exit Sub

Err_BacktoMainGoals_Click:
    MsgBox Err.Description
    Resume Exit_BacktoMainGoals_Click

End Sub

I am getting an error when I try to open it with the button from the
main form (frmMainGoals) that is saying there's a syntax error in the
Private Sub Form_Open (Cancel as integer) line.

0
meghanwh
9/20/2007 12:06:46 PM
re: Syntax error - I suspect that the MsgBox line "wrapped" when it 
shouldn't.  The line starts with MsgBox and ends with "Error" - all on one 
line. That could be what's giving you the syntax error.

re: Which records - the previous response  only guarantees that a new record 
in frmLevel2Goals will have the foreign key value that matches the PK of 
frmMainGoals.  

To make frmLevel2Goals displayed records stay in synch with the current  
position of frmMainGoals  when you move to a different record you need to 
have the main form cause a requery of the "sub' form (although it isn't 
really a subform in Access terminology).  You do it via the On Current event, 
 something like this. ( I've had to use pseudo code as I don't have your 
table and column names handy.):

in frmMainGoals

Private Sub Form_Current()
   Dim frm_Level2GoalsHandle As Form

  ' check to see if the Level2Goals form is loaded as per other example
 If   CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
     Dim SQL As String

   SQL = "SELECT * FROM tblLevel2GoalsSourceTable WHERE (linkToMainFK = " &  
Me.PKfield & ")"

   Set frm_Level2GoalsHandle = Forms![frmLevel2Goals].Form
   frm_Level2GoalsHandle.Recordsource = SQL
  frm_Level2GoalsHaneld.Requery
Set frm_Level2GoalsHandle = Nothing
End If

re: Button on MAIN form to go to new record

Private Sub btn_Level2GoalsNewRecord_Click()
  Dim frm_Level2GoalsHandle As Form

 If   CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
   Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
  frm_Lvel2GoalsHandle.Filter = "([PK] = -1 )"
  ' the above is impossible, so you will only get the new record
 frm_Level2GoalsHandle.FilterOn = True
set frm_Level2GoalsHandle = Nothing
End If
End Sub

Private Sub btn_Level2GoalsAllRecords_Click()
  Dim frm_Level2GoalsHandle As Form

 If   CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
   Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
 frm_Level2GoalsHandle.FilterOn = False
  End If
End Sub




"meghanwh@gmail.com" wrote:

 
> I'm getting a syntax error on the Private Sum Form_Open (Cancel as
> Integer) line when I try to open the form. And will this make it so
> that one button on the main form (frmMainGoals) opens frmLevel2Goals
> to a new record while another button opens it to the beginning record?
> Thanks.
> 
> 
0
Utf
9/20/2007 1:04:00 PM
On Sep 20, 11:46 am, NKTower <NKTo...@discussions.microsoft.com>
wrote:
> OK so add filter to the button for "ALL" such that it really isn't ALL level
> 2 records, it is just all records that match the PK.  This will handle that
> initial display - the On Current event will keep it in synch for subsequent
> moves.
>
> Private Sub Level2Btn_Click()
> Dim frmLevel2GoalsHandle As Form
>
> If Not CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
>    DoCmd.OpenForm "frmLevel2Goals", acNormal
>  End If
>  DoEvents
>  Set frmLevel2GoalsHandle = Forms![frmLevel2Goals].Form
>  frmLevel2GoalsHandle.Filter = "(  [level2FK] = " & Me.PK & " )"
>  frmLevel2GoalsHandle.FilterOn = TRUE
>  End Sub

That's great. Thanks so much!

0
meghanwh
9/20/2007 6:02:04 PM
Reply:

Similar Artilces:

Tracking shared records
I need to create a report that shows which records are shared and with whom. Can someone guide me in the right direction? I can't seem to find it anywhere online. thank you Sharing is recorded in the PrincipalObjectAccess table in the SQL database. Advanced find does not allow you to access this so you will have to create a report on the directly on the database. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Extended Entity and Plug-in browser: http://www.patrickverbeeten.com/maps/CrmTool.aspx "Bernardina" wrote: > I need to create a report that shows whi...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Cannot open Excel attachment from e-mail when Excel is already ope
This is driving me crazy. If I already have Excel open (with or with out a spreadsheet open) I cannot open an Excel spreadsheet attached to an e-mail. If I close Excel and retry it will open Excel and the spreadsheet. Any ideas? I hate having to close all my open spreadsheets when I want to open a spreadsheet that's been e-mailed to me! One of these usually works with a similar problem starting workbooks by double clicking them in Windows Explorer. Maybe one will work for you and your email problem. Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close E...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Outlook 2000 not opening.
I am having a major problem with outlook. Can anyone tell why Outlook will not open when selecting it. When I check in the processes there is an instance of Outlook.exe there but it is not doing anything. When a different user logs onto the machine Outlook will open fine with no problems. I have deleted the users profile and recreated it but to no use. I have tried comparing the local registry keys of the two users and they seem to be the same. Can anyone please help me as I have ran out of things to try. Thanks in advance. Justin Does Outlook start in /safe mode? (I'm assumi...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

cursor missing from form textboxes
I believe that I know 98% of the little intricacies of Publisher, but I'm stumped on this one. I created a website with a contact page and none of the standard textboxes will show a cursor to indicate with field you are in (I've even made the site live to double check). If you tab between textboxes, the tabbing works, as does typing, there is just no visual cursor to indicate where you are. If you click on the first textbox and type, all is good you can tab to the next box and type, and it's fine. I'd like to have the visual cursor show and can't get it. I also have ...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

VBA form
Im using a form/macro that i linked to through another thread and a tailoring it to my needs. I basically have it doing what i need but cant get a button to do what i want. When the user starts the macro i brings up the form with a message and 2 buttons (Continue/Cancel). Whe continue is pressed i want the message on the form to change to aniothe message and so on until all messages are shown. Ive attacehed what i working on and apprecaite any help offered. Thank yo +------------------------------------------------------------------- |Filename: Excel.zip ...

Setting Defaults in New Item Wizard
How can I set a default value in the New Item Wizard? I want to always use a barcode type that is about 10 down from the top and I want Taxable Item to always be checked. Help... Good question, I need an answer too. "A StanTech Associate" wrote: > How can I set a default value in the New Item Wizard? I want to always use a > barcode type that is about 10 down from the top and I want Taxable Item to > always be checked. Help... You can set the default sales tax under Manager, File, Configuration, Sales Tax, Default item tax group. To set a default barcode type yo...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...

Cannot Drag Open Desktop Screen
Hi: I have a friend running XP MCE (2005) SP3 and he cannot drag an open screen on his desktop by Left clicking and holding cursor on the top of screen. Any suggestions as to how to remedy this problem of not being able to drag a screen will be appreciated Thanks, ColTom2. On Sun, 14 Mar 2010 16:48:43 -0400, "ColTom2" <noemailaddress@nomail.com> wrote: >Hi: > > I have a friend running XP MCE (2005) SP3 and he cannot drag an open >screen on his desktop by Left clicking and holding cursor on the top of >screen. > > A...

calendar sometimes opens in month view
every once in a while (sorry that i can't be more precise), i find that clicking on calendar, under 'all folders' , causes it to open in month view. i can go back to inbox, and again click on calendar and get month view again. for most of a day. then it goes back to opening on current day. which it does most of the time. i don't remember if this happens after a reboot, standby, or just for no apparent reason. actually i prefer month view and wish i could make it open this way all the time. any ideas about what it happening here? ...

new activity not in the appropriate queue
Help! I installed the Rollup 3 for MS CRM 4 and since then all created activities are redirected to the users assigned folder in queue and not in progress(my current activities) folder! It's not logical because a new activity is not yet assigned to a different user to be in the assigned folder !! I can't believe this is on purpose and I DON'T have a workflow on this. Can someone tell me how to fix this? ...

Opening Excel email attachments
When downloading excel files AOL says cannot find suitable program.I then go online to find Excel but only get Uopdates. PC is XP version with Works Suite 2002. A fe months back my dear wife was downloading Excel an got board with waiting and shut down the PC! Any suggestions. Thanks Karl Hi there's no legal way to download Excel!! You have to buy< an Excel version and install it on your PC (or try to open the file with MS Works - but live with the restricted functionality) >-----Original Message----- >When downloading excel files AOL says cannot find >suitable progra...

Scroll Wheel effecting a form
Is there any way to keep a mouse scroll wheel from having an effect on a form? This is a single form for data entry and if are part way thru the form and for what ever reasom use the scroll wheel all the fields on the form are effected (they dissapear - like going to the next new record). My suggestion to get a mouse without a scrool wheel was not met with enthusiasm. :) Any help here will be appreciated. Thanks in advance The scroll wheel is moving you through the records. Nothing except your view of the records is disappearing. The records are still there. I wouldn't think y...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

Office 2008 "Office Installer" could not be opened
Trying to install Office 2008... When I double click the Office Installer icon....I get the following message: The document �Office Installer� could not be opened. I'm on a MacBook Pro. I've never had problems installing programs before. This is new. Thanks for any help. On 2/26/08 2:03 PM, in article ee8edf5.-1@webcrossing.caR9absDaxw, "Dags@officeformac.com" <Dags@officeformac.com> wrote: > Trying to install Office 2008... > > When I double click the Office Installer icon....I get the following message: > > The document �Office Installer� could ...

Can not open additional mailboxes in my mailbox after a exchange 2003 rebuild database
To Exchange 2003 Gurus I have a exchange server that serve a lot of users. 3 week ago I had to repair the database with great success. But now I noticed a problem that in the past I was able to acomplished I can not open additional mailboxes in my mailbox.(right click on my mailbox>properties>advanced>Advanced tab> and when I click on add I get THE NAME COULD NOT BE RESOLVE. THE ACTION COULD NOT BE COMPLETED. This message appear eventhough I did not type anything Here is more info -I am able to open additional mailboxes if I use a computer with my profile that I created before I r...

"Freeze panes" settings lost after opening file in two windows
I'm using Excel 2003. I've got a big Excel file with multiple tabs. Many of them use "freeze panes" to keep headings in place. I needed to flip back and forth between multiple tabs, so I opened the file in a second windows. Now, when I save it, all the "freeze pane" settings are lost. Any suggestions? Thanks. That Freeze panes setting is assocated with a window. Maybe you're not looking at the correct window or maybe you closed the window that had freeze panes applied. I'd try: Window|Arrange|horizontal to see if you still have multiple windows assoc...

hyperlink open in a new page
After reading all the other posts, and inserting "HTML Code Fragment" here's my prob. I don't want folks to see the actual HTML text. I got a pic of a calendar, and, I want it to simply open my yah00 calendar in another window. I've figured out how to patch a pic to a link, but (of course) I want it to open in a new window, again, without the HTML text being visible. What am I missing? Reference: Create a hyperlink in Publisher that opens in a new window: http://office.microsoft.com/en-us/publisher/HA011587451033.aspx Use the ECMAScript code approach, and insert ...