create record in many table after the one table is entered

[shelron]

I am trying to create a record in a many table from a form.



user creates a new record in the one table via a form, then I want them to be able to open a new form so they can enter the details for that table.

problem is that I need to create a record in the many table before I send them to the many form.

Can someone get me started?? Access 2002

Ron

 

[JoyInOK]

You need to be sure the one table record is saved before you open the many record. (Access only saves when you leave the current record or force it to save)
DoCmd.runcommand acCmdSaveRecord
Now when you open your new form with the filter set to match the one record, a blank form will open. You can use code to set the values in some of the fields (perhaps the field that matches the one record, and today's date). Again, the many record will not be saved until the user leaves the current record in that form.

 

[Orion45]

I'm not sure I understand the problem. Did you try coding the open form statement to open to a new record? Just set the DataEntry property to yes on the many form.

 

[shelron]

I don't want the user to have to enter the information twice.

I have a header (in the form header of the many form) I have the one side displayed so that when the user opens the form, he can visually confirm that he is adding information to a specfic project.

Let me try to restate:

A user creates a new project via a form. I have a save button and a save cmd set on exit.

Once the user creates the project, there are several other forms that require entry. So what I have set up is, the user creates the project, then clicks a cmd button to go to another form and continue entering additional details about the project (many tables).

I want to code it so that, when the user clicks the cmd button to go to the other forms, a record is created in the many side, so when the form opens, they see the project description (one) information on the form header, and then they can add data to the detail section (many table)and the primary key (project number) is already the current record.

hmmmmmmmmm, that makes me wonder,, would it be easier to just open the detail form with the project info in the header, and code that form to start a new record with the primary key inserted via a function??


does that explain the issue clearer? I hope I am not spinning anyones head with a poor explanation.


So I think what I need to do, is when the user saves the project info,

first save to the one side in the project table,

next, create a new record in the many table, (just the primary key will do)

then, open the detail form linking criteria from the project form.

Ron

 

[shelron]

Private Sub cmdSpecialConsiderations_Click()
On Error GoTo Err_Command19_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SPECIAL CONSIDERATIONS"

stLinkCriteria = "[SPROJ_NO]=" & "'" & Me![ProjNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub




okay, the above code opens the many form, how can I edit this code so that if the form opens without finding corresponding criteria, it will pass the values from the project they were working on in the project form to this form and create a new record?

In english, if no sproj_no exists, use the current value of forms![frmproject]![proj_no] and create a new record in the RtblSpecial table.

This seems to be the easier road?

Ron

 

[MelindaPeirce]

You might want to try using a sub-form for this purpose. create your second form using the 'one' side table as the dataset, then create a subform in the detail section and your linked fields (proj_no) should automatically be updated when the user enters new data there..

make sense?

 

[shelron]

Yes Melinda, it does make sense, normally,

I just have a unique situation here. (as usual due to the longggggg story, it never all gets posted for people to consider)

I solved this by doing a runsql command and creating the new record.

Thanks to all who helped.

It's appreciated.

Ron