Form and SubForm Problem

I have a table tblPhones with a PDkey as the Primary Key.

I have another table tblPB with four fields as follows:
DDID as the Primary Key
PDkey, a lookup field to tblPhones.
DirectoryID, a lookup field to a table tblDirectory
DeptID, a lookup field to a table tblDepartment

The Main Form uses tblPhones as it's record source
The Sub Form has tblPB as it's record source

Link Child Fields = PDkey
Link Master Fields = PDkey

I can create a new record in tblPhones from the Main Form, WITHOUT
entering data in the SubForm.

How do I make sure data is entered into the SubForm when adding a new
record in the Main form?

Same Question (a different way):
How do I make sure tblPhones is not updated until data is entered in
tblPB?

0
iamnu
1/25/2008 4:10:01 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
593 Views

Similar Articles

[PageSpeed] 19

You could use VBA code to save the main form's data and to move the cursor 
into the subform as soon as the new record is created in the main form. Then 
you could prevent the user from leaving the subform until data have been 
entered into the subform.

Post back if you would like more details, and tell us more information about 
the names of the form and the subform control, and of the controls in the 
subform, etc.
-- 

        Ken Snell
<MS ACCESS MVP>



"iamnu" <iamnubw@gmail.com> wrote in message 
news:e6a798e3-0ab1-4800-9c4c-1617b11fbc73@f47g2000hsd.googlegroups.com...
>I have a table tblPhones with a PDkey as the Primary Key.
>
> I have another table tblPB with four fields as follows:
> DDID as the Primary Key
> PDkey, a lookup field to tblPhones.
> DirectoryID, a lookup field to a table tblDirectory
> DeptID, a lookup field to a table tblDepartment
>
> The Main Form uses tblPhones as it's record source
> The Sub Form has tblPB as it's record source
>
> Link Child Fields = PDkey
> Link Master Fields = PDkey
>
> I can create a new record in tblPhones from the Main Form, WITHOUT
> entering data in the SubForm.
>
> How do I make sure data is entered into the SubForm when adding a new
> record in the Main form?
>
> Same Question (a different way):
> How do I make sure tblPhones is not updated until data is entered in
> tblPB?
> 


0
Ken
1/25/2008 7:53:22 PM
On Jan 25, 12:53 pm, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> You could use VBA code to save the main form's data and to move the cursor
> into the subform as soon as the new record is created in the main form. Then
> you could prevent the user from leaving the subform until data have been
> entered into the subform.
>
> Post back if you would like more details, and tell us more information about
> the names of the form and the subform control, and of the controls in the
> subform, etc.
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "iamnu" <iamn...@gmail.com> wrote in message
>
> news:e6a798e3-0ab1-4800-9c4c-1617b11fbc73@f47g2000hsd.googlegroups.com...
>
> >I have a table tblPhones with a PDkey as the Primary Key.
>
> > I have another table tblPB with four fields as follows:
> > DDID as the Primary Key
> > PDkey, a lookup field to tblPhones.
> > DirectoryID, a lookup field to a table tblDirectory
> > DeptID, a lookup field to a table tblDepartment
>
> > The Main Form uses tblPhones as it's record source
> > The Sub Form has tblPB as it's record source
>
> > Link Child Fields = PDkey
> > Link Master Fields = PDkey
>
> > I can create a new record in tblPhones from the Main Form, WITHOUT
> > entering data in the SubForm.
>
> > How do I make sure data is entered into the SubForm when adding a new
> > record in the Main form?
>
> > Same Question (a different way):
> > How do I make sure tblPhones is not updated until data is entered in
> > tblPB?

Yes Ken, more details please.

I have changed the name of many of the tables and fields, so now here
is the current information.

Main Form: PhoneBook, which has PBKey as the Primary Key
SubForm: PhoneBookSubForm which has the following Record Source:

SELECT tblPBlink.PBlinkKey, tblPBlink.PBkey, tblPBlink.DirectoryID,
tblPBlink.DeptID, zDepartments.DeptName
FROM zDepartments INNER JOIN tblPBlink ON zDepartments.DeptID =
tblPBlink.DeptID;

Link Child Field = PBkey
Link Master Field = PBkey

Everything works as I want, except that I am able to create a record
in the Main Form without a matching record in the SubForm.  When I DO
create a record in the SubForm, everything is linked properly.

If you can show me how to save the main record, then force me to enter
data into the SubForm (I suppose when the PBkey in the SubForm is
null), I would be quite happy.  Thanks for your help.

If you need additional information, I'll provide whatever I can.

Bernie
0
iamnu
1/25/2008 9:55:29 PM
"iamnu" <iamnubw@gmail.com> wrote in message 
news:1b18a0ee-9446-41f1-9475-8df5207cb258@y5g2000hsf.googlegroups.com...
> On Jan 25, 12:53 pm, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> You could use VBA code to save the main form's data and to move the 
>> cursor
>> into the subform as soon as the new record is created in the main form. 
>> Then
>> you could prevent the user from leaving the subform until data have been
>> entered into the subform.
>>
>> Post back if you would like more details, and tell us more information 
>> about
>> the names of the form and the subform control, and of the controls in the
>> subform, etc.
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> "iamnu" <iamn...@gmail.com> wrote in message
>>
>> news:e6a798e3-0ab1-4800-9c4c-1617b11fbc73@f47g2000hsd.googlegroups.com...
>>
>> >I have a table tblPhones with a PDkey as the Primary Key.
>>
>> > I have another table tblPB with four fields as follows:
>> > DDID as the Primary Key
>> > PDkey, a lookup field to tblPhones.
>> > DirectoryID, a lookup field to a table tblDirectory
>> > DeptID, a lookup field to a table tblDepartment
>>
>> > The Main Form uses tblPhones as it's record source
>> > The Sub Form has tblPB as it's record source
>>
>> > Link Child Fields = PDkey
>> > Link Master Fields = PDkey
>>
>> > I can create a new record in tblPhones from the Main Form, WITHOUT
>> > entering data in the SubForm.
>>
>> > How do I make sure data is entered into the SubForm when adding a new
>> > record in the Main form?
>>
>> > Same Question (a different way):
>> > How do I make sure tblPhones is not updated until data is entered in
>> > tblPB?
>
> Yes Ken, more details please.
>
> I have changed the name of many of the tables and fields, so now here
> is the current information.
>
> Main Form: PhoneBook, which has PBKey as the Primary Key
> SubForm: PhoneBookSubForm which has the following Record Source:
>
> SELECT tblPBlink.PBlinkKey, tblPBlink.PBkey, tblPBlink.DirectoryID,
> tblPBlink.DeptID, zDepartments.DeptName
> FROM zDepartments INNER JOIN tblPBlink ON zDepartments.DeptID =
> tblPBlink.DeptID;
>
> Link Child Field = PBkey
> Link Master Field = PBkey
>
> Everything works as I want, except that I am able to create a record
> in the Main Form without a matching record in the SubForm.  When I DO
> create a record in the SubForm, everything is linked properly.
>
> If you can show me how to save the main record, then force me to enter
> data into the SubForm (I suppose when the PBkey in the SubForm is
> null), I would be quite happy.  Thanks for your help.
>
> If you need additional information, I'll provide whatever I can.
>
> Bernie

To ensure that the user does not "move away" from a just-created record in 
the main form, turn off the Navigation Buttons in the main form. Put a 
command button on the main form that the user will click to save the main 
form's new record. Use code like this for that button's Click event:

Private Sub ButtonName_Click()
Me.Dirty = False
Me.NameOfSubformControl.SetFocus
Me.NameOfSubformControl.Form.NameOfControlOnSubform.SetFocus
End Sub

Then put this code in the Exit event of the subform control (the control 
that actually holds the subform):

Private Sub NameOfSubformControl_Exit(Cancel As Integer)
If Me.NameOfSubformControl.Form.RecordsetClone.RecordCount = 0 Then
    Cancel = True
    MsgBox "You must enter data in the subform!", vbExclamation, _
        "Enter Data!"
End If
End Sub

-- 

        Ken Snell
<MS ACCESS MVP>



0
Ken
1/27/2008 2:54:09 AM
Reply:

Similar Artilces:

Problem with Downloaded statements in Money 2006 identified
Based on the traffic in this group, many of you are struggling with the issue of downloaded statements since upgrading to Money 2006. I have had this problem since upgrading from 2005 to 2006 in October. I had tried every solution identified from both Microsoft and others that were lucky enough to get things to work, but was not able to get anywhere. After a significant investment in time, I was finally able to isolate the issue to downloaded statements that have a specific character in them. In my case, these are always part of a credit voucher where the dowloaded file has a <br> in...

Reservation Form
I have an active website for my business. I built it in Frontpage 2002. It no longer will send email through the settings I used ( Reservation form) and I cannot get it to take any of the settings I give it. I do not think that my knowledge is sufisticated enough. And, I don't think that I am entering the information completely enough. I am using a simple form with a single enter button. Can you help? May be possible. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "FrontPage 2002 on windows XP" <FrontPage200...

Problems with pasting
I recently installed Publisher 2000. When I copy a picture and then paste it into a Publisher 2000 document, instead of the copied picture appearing a white box with a large black cross appears. Is there a solution to this problem so I can actually paste the picture that I copied? View, picture, detailed display -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "njj200" <njj200@discussions.microsoft.com> wrote in message news:3478031A-01CE-4260-8680-DCC3EF0AA6AF@microsoft.com... >I recently installed Publisher 20...

Auto populate a form form an open form
Hi I have a main form with customer names and addresses and would like to add a control button on this form which opens up another form which is used for recording telephone calls on. What I have been trying to do is auto populate this call log form with information from the main form ie name and address? Any thoughts Thanks Chris Chris, Please don't re-post the same question twice. You have to be patient and wait for a reply. Responders are volunteers, and you may not get an answer right away. Posting twice causes some people to respond to the first post, while others are ...

copy properties Under forms
Hi, I'm wondering if someone can help me with something on copying properties. I've checked the access help but can't find anything there. This is what i want to do. I'm copying text boxes, labels that has caption, name and other information in the properties that i want to keep when i copy the information to another tab on a form. Is there a way i can copy the property information without doing alot of manual work by going into each text or label box. I hope this makes sense. thanks for you help in advance. Keith When you copy a control, everything about the cop...

i have problem code 646 windows update encountered an unknown erro
how to fix it ? Hello komang, > how to fix it ? Reading other posts, and searching Google, Yahoo, Bing etc. etc. When requesting help in a newsgroup or a forum, please state your full Windows version, edition, and service pack level, IE version? and perhaps what other antivirus Security suite and firewall or antimalware software you may have installed. Please do so next time. <http://support.microsoft.com/kb/555375> -=- Take a look at this post and see if it helps- <http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&lang=en&...

form pozition on screen
I would like to find form position on screen. It's similar to DoCmd.movesize, but I want discover "parameters' for form position (left corner, top corner, etc) Are you saying that you want to find out what the current position IS? If so, you can use something like this: MyFormLeftVariable = Me.Left MyFormTopVariable = Me.Top MsgBox "My Form is at " & MyFormLeftVariable & VbCrLf & MyFormTopVariable Or however you want to use it. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com ___________...

What is the purpose of 'Dummy' window in the Command forms
I am wondering why GP has Dummy window with title "~internal~" for the Command forms. For example Command_Financial form has Dummy window Do I need to create Dummy window for my Command forms. If so why I need it. Any comments appreciated!!! Hi Mandeep, The Dummy window is there as a placeholder. It really doesn't do anything, but should be there in order for the form to work properly since your form needs to be open for the commands to be available. The title ~internal~ just prevents this window from showing up in the Security assignment windows. Make sure that...

Cannot open the Insert object (Pdf file) form Excel 2000(9.0.8968 SP-3)
Dear All, I am no idea why I cannot open the embedded object (Pdf file) inside Excel file which using Windows ME. Once clicked the object icons, then it shows the error message likes " the embedded object cannot be opened by the source application" I used the Adobe reader 6.01 as the source application. Actually, I searched the related forum from the Internet. However, I cannot solve the problem. Anyone helps? Thanks tlee Hi, I can't test 2000 in ME, but it works fine in 2003 under XP. -- If this helps, please click the Yes button. Cheers, Shane Devenshire &q...

Form class module for non-existent form
Hi, After copying a form with a class module, something went wrong. I now have a class module for the new form, but the form no longer exists. I am unable to remove the in-limbo class module. I want to use that name for my form, which is not possible as long as that class module is in the way. How do I get rid of the in-limbo class module? I've tried repairing tha DB as well as "msaccess /decompile" but it hasn't helped. Anything else? Thanks, Kjell Kjell, A class module is not part of a form, but a separate module. It sounds like your 'in-limbo' module is...

Setting the Windows Cursor during Form Processing
hiya gurus I would like to force the mouse cursor to immediately become an hourglass when some long processing/quering commences on a form. it appears that it takes some time for Access to show the hourglass and usually only after the user clicks the form a couple of times because there appears to be nothing happening... Is there an easy way to do this from within Access.. I have done it in VB6 using Me.MousePointer = vbHourglass but this does nto work in Access.. any suggestions? cheers Jeff mmmmm Screen.MousePointer = 11 guess this does it? "jeff" <jeff@work...

Look up problem
Part 1 I have this look up in cell H18. =LOOKUP(RIGHT(I18,2),$G$68:$H$75) Cell I18 is formated as text but contains numbers like 123405. The RIGHT(I18,2) takes the last 2 digits (the 05) and does the lookup. This works except when a number that is not in the lookup range is entered in I18. For example, if 95 is entered the lookup returns the lookup for 05. My first question is how can I return only valid input in the LU range. Part2 If nothing is in Cell I18 then #N/A appears in H18. The #N/A looks awful. The second question is how can I show nothing in H18 when I18 is blank. I have Exc...

Position 2nd Form according to 1st Form
I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, I want it to position itself about 15cm form the Left and 20cm from the top of the 1st Form. I am using Access 2003, Thanks. "AY" <AY@discussions.microsoft.com> wrote in message news:CFA3A4B5-36D9-4A20-AB57-601290155335@microsoft.com... >I have 2 Forms. When the 1st Form is openned and want I open the 2nd Form, >I > want it to position itself about 15cm form the Left and 20cm from the top > of > the 1st Form. I am using Access 2003, Thanks. You should be able to use Nicole Calinoiu...

Problem with OpenFile in VC++
Hi all, when i clicked into menu\File\Open in VC++, then VC++ die, it exit automatical itself. I am finding some info in registry to clean, so OpenDialog of VC++ will get default path. If it were me I'd uninstall VS and reinstall it again. Any number of things could be wrong and you'll probably take more time figuring it out then you would reinstalling it. Tom "Duy Trinh" <duy.trinh@mobinex.com> wrote in message news:eI1AWHNdHHA.5044@TK2MSFTNGP05.phx.gbl... > Hi all, > > when i clicked into menu\File\Open in VC++, then VC++ die, it exit > autom...

TOURNAMENT FORMS
Need tournament draw sheet single elimination template. You may find some old NCAA basketball tournament forms out on the web. Try http://google.com HHSTENNIS wrote: > > Need tournament draw sheet single elimination template. -- Dave Peterson ec35720@msn.com ...

Run Macro only if certain form is open
I have programed a maco to open one of various forms depending on criteria on an open form. Is there a way to run a "follow up Macro" based on which form opens? In other words can I run a macro based on wheather a certain form is active? -- Thanks, Chuck Take a look at the RunMacro Method of the DoCmd object. You can have it as part of the OnLoad of the form in question. Chuck wrote: >I have programed a maco to open one of various forms depending on criteria on >an open form. Is there a way to run a "follow up Macro" based on which form >opens? In other w...

Problem changing parent Business unit
WE have re-arranged some business units into a more appropriate hierarchy, however, we have noticed that a user in a role with deep privileges (own business unit and children I think) to read users can only see users in his own business unit and only one of the child business units (there are 2 child business units). We cannot enable him to see all the users in both child business units. We tried reassigning the parent business unit to the one child that is not appearing, but it doesn't seem to make any difference. WE also tried adjusting the privileges up and down (i.e. Global and local...

Modal form opens on startup
Evenin' - I hope this doesn't come across as stupid, but I'm almost completely unfamiliar with Access. A client of mine has an Access 2000 database containing some business information, and he'd like to have me import it into Quickbooks. The problem I have is that I cannot get to the information to export it from Access. When the file is opened, I get a (I presume) modal form, configured as a 'splash screen' type of thing. Has my client's logo, and the name of the fellow who wrote the Access app. Click that, and it closes, then opens another form for...

Forms and Input Data
I created a report form on Sheet 1 and a set of input data on subsequen Sheets 2, 3, ... link to the report. The report is to show informatio on either of the data I select. Are there simple steps for me to pick either set of data I want t display on the report? I've thought of a pull-down menu so that if select "1", the report will show data set "1", and so on. Yet I don' know how to do it. I tried the "replace" command by highlighting al the cell in the Report sheet then type "2" in place of "1" to replac the data set then hitting...

Trying to delete form border, and it deletes all borders
I made a form with several individual areas that have borders. At the end I decided to try a border around the whole form. Now that I don't like it, I was trying to delete the form border, but it deletes all the borders in the form as well. How can I just delete the border around the form and not the rest of the individual borders? I'm actually on a different computer, because of the printing, will I be able to undo my way out of it maybe, or do I have to be on the computer where I made the form. One more question, will the form retain the undo feature for ever, or will it stop ...

Problems with syncing with MSN since udating Bank of America
I am a Bank of America customer so I updated the account per last weekends instructions. Every since then I cannot sync with MSN Money on line. Also a number of other accounts no longer updata including ING Direct, Chase, Circuit City. I uninstalled and reinstalled Money 2006 Deluxe. I tried earlier versions of my back up files to before the update but nothing works. Oh by the way I installed the Beta of IE 7 but have since uninstalled it. I am not sure if the problems started when I did the Bank update or when I did the IE 7 beta. I have tried phone support but no luck. Any sugg...

Question -Problem
Has anybody ever seen an error message (0x800cccF6) message? I cannot configure my Outlook to retrieve Hotmail. Works with other profiles on this computer running winxp. Stan-The-Man-2006 wrote: > Has anybody ever seen an error message (0x800cccF6) message? I cannot > configure my Outlook to retrieve Hotmail. Works with other profiles > on this computer running winxp. If you have a free Hotmail account, note that it won't work anymore (even your other profiles will eventually have problems) - see http://www.slipstick.com/addins/services/online.htm#hotmail ...

Temporary Form with 2 Sub form Update Main Form And its Sub form ?
I have 2 table both are exactly same PatientRecordTransTb And PatientRecordTb I know many programmer don’t like TempTb, but in my case I don’t trust some one to mess with my Main record table. So simple solution is make tamp table and after review I click to transfer all records from temp Table to main Table So I have two forms . PatientRecTransFm record sources = PatientRecordTransTb PatientRecordMainFm record source = PatientRecordTb PatientRecTransFm has 2 button AddRecord And Delete Record Here is a short story of my code (NOT A Actual Code) mySQLString = &quo...

how to solve "Non-modal forms cannot be displayed in this host application"
Hi, I am trying to make a modalless form in VB6 that is compiled as an ActiveX DLL. When i use VC6 to instance the activex DLL, my problem appears that "406 Non-modal forms cannot be displayed in this host application from an ActiveX DLL, ActiveX control or Property Page." I have my form in modalless style in VB6 and they should be instanced in VC. Does anyone know how to get this to work ? Many Thanks, Xiao http://support.microsoft.com/default.aspx?kbid=176468 -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "arbidol" <arbidol@tom...

Customization to a Contact Form Does NOT Stick
Everytime I try to add a field in a Contact form, the change does not stick. I have administrator rights, followed the instructions to customize a form to the t. The instructions entailed going to: Home -> Settings Settings tab -> System Customization Record Management -> Contacts Add Fields -select fields to add ok - > save work On the CRM server Start -> Programs -> Microsoft CRM -> Deployment Manager Right click: Deployment Manager -> All Tasks ->Publish Customization Select form customized Add Finish OK Restart IIS After performing these steps, I get b...