How to Add New Record to Main Form and Subform Simultaneously? 2

[southbean]

Hello Experts,

A seemingly simple task has turned out to be unexpectedly difficult. Here’s what I’m trying to do:

I have a main form with a subform in it. I have a command button on the main form for users to add a new record to the main form (DoCmd.GoToRecord , , acNewRec). No problem.

However, I would also like to add a new record (with a certain value) in the subform the same time a user is creating a new record to the main form.

Here’s what I have to insert the new value into the subform:

    'sets the focus on the subform
    Me![sfrm_Details].SetFocus
    Me![sfrm_Details]![Details_Text].SetFocus
    'go to new record
    DoCmd.GoToRecord , , acNewRec
    Me![sfrm_Details].SetFocus
    Me![sfrm_Details]![Details_Text].Value = "New Request"

The problem is I can’t seem to get both working in the same command button.

Any/all help or suggestions would be greatly appreciated!!!

- Tom

 

[Remou]

Maybe:
Me![sfrm_Details].Form![Details_Text].Value = "New Request"

 

[southbean]

Remou,

Thanks for your reply!

The problem isn't the code to enter the new record into the subform. That works fine. The problem is getting the command button to do both - Enter a new record into the Main form and the Subform at the same time.

Here's what I've got that's NOT working:

    DoCmd.GoToRecord , , acNewRec
    
    'DoCmd.GoToRecord , , acNewRec
    'Me![MainForm_Field].Value = " "

    'sets the focus on the subform
    Me![sfrm_Details].SetFocus
    Me![sfrm_Details]![Details_Text].SetFocus
    'go to new record
    DoCmd.GoToRecord , , acNewRec
    Me![sfrm_Details].SetFocus
    Me![sfrm_Details]![Details_Text].Value = "New Request"

Thanks again for your reply!

- Tom

 

[Remou]

What error are you getting?

 

[southbean]

I get an error message that says "Microsoft Access can't move the focus to the (subform) control"

 

[TheAceMan1]

How are ya southbean . . .

It doesn't appear as if your [blue]form/subform[/blue] are linked with [blue]Master/Child Link[/blue] properties. If they were the subform would automatically goto a new rec when you goto a new rec on the mainform.

Is your Form/subForm linked with these properties?

See Ya! . . . . . .

 

[southbean]

Hi-ya TheAceMan1,

Thanks for your reply.

The Master/Child Link properties are correctly established on the forms (MainForm-PK = Subform-FK).

If I click the command button to add a new record to the main form (commenting out the subform code) I can manually enter a new record in the subform.

The subform code adds the "New Request" value to the subform table - but it doesn't carry over/insert the FK for the record in the subform table. So. it's an orphan record.

The problem I'm having is getting the command button to create a new record in the main form (by inserting a value in a field) then doing the same in the subform.

I should be able to combine the two events in the same command button, yes?

Thanks again for your response!

- Tom

 

[PHV]

Perhaps this ?
DoCmd.GoToRecord , , acNewRec
Me![MainForm_Field].Value = " "
[!]DoCmd.RunCommand acCmdSaveRecord[/!]
'sets the focus on the subform
Me![sfrm_Details].SetFocus
Me![sfrm_Details].Form![Details_Text].Value = "New Request"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[southbean]

Hi PHV,

Thank your for your post!

I think we're getting closer. I now get an error message saying:

"The field (main form PK) can not contain a null value because the Required property for this field is set to True. Enter a value in this field"

Apparently it's not populating the primary key first. Very odd!

Thanks again!

- Tom

 

[TheAceMan1]

and this:

[blue]DoCmd.GoToRecord , , acNewRec
Me![MainForm_Field].Value = " "
DoCmd.RunCommand acCmdSaveRecord
'sets the focus on the subform
Me![sfrm_Details].SetFocus
[purple][b]Me![sfrm_Details].Form![Details_Text].SetFocus[/b][/purple]
Me![sfrm_Details].Form![Details_Text] = "New Request"[/blue]

See Ya! . . . . . .

 

[Pampers]

can't you just put the default value of Details_Text in the subform to "New Request"??

Pampers
Just let it go...

 

[southbean]

Remou, TheAceMan1, PHV,

Thank you all for your postings and help!!!
I very much appreciated it.

I finally figured out the problem. Another field on the Main form was a required field and was indexed (no duplicates).

D'oh!

After I included that field in the VBA - it worked.

Thank you all again for you help. I couldn't have done it without your help!

- Tom

 

[southbean]

Hello All,

Thanks to the help of several experts in this forum, I was finally able to get the following code (below) in a command button to add a record to my main form and sub form simultaneously. It works great!

However, I would like to use the values selected in a ComboBox drop-down list on a popup form I’ve created instead of the InputBox (highlighted line of code).

I’ve tried many times, but just can’t seem to get it to work!

DoCmd.GoToRecord , , acNewRec
[highlight]Me![MF_Combo1].Value = InputBox("Enter Name", "Name")[/highlight]
Me![MF_Number].Value = InputBox("Enter New Number", "Number")
Me![MF_Title].Value = InputBox("Enter Title", "Title")
DoCmd.RunCommand acCmdSaveRecord
'set the focus on the Subform
Me![SF_Details].SetFocus
Me![SF_ Details].Form![Details _Text].SetFocus
Me![SF_ Details].Form![Details _Text] = "New Request"
'set the focus back to the Main form
Me![MF_Combo2].SetFocus

Any and all help and/or suggestions would be greatly appreciated!

Thanks,

- Tom

 

[TheAceMan1]

southbean . . .

New subject calls for new thread so all may bebefit! . . .

See Ya! . . . . . .

 

[southbean]

TheAceMan1,

Sorry. Was not aware of that protocol. Will re-post.

Thanks,

- Tom