Using 2 forms to update table related to table behind another form.

[cdgeer]

I have a main form named [Program Management] with a RecordSource of tblMPL with a primary key field [Record_No] which is also a txtBox on the form. On this main form I have a combo box where if the selection "ReDirect" is made then a second form opens [ReDirect Reason] which has a RecordSource of a different table tblReDir with a field of Record_No that I have set as a one to one relationship with tblMPL. This second form has a few chkboxes which I want to use to create a record in tblReDir with the same [Record_No] as the main form's currently displayed [Record_No] but I can't seem to get it to work.

Also, with the second form chkboxes, I want to open a third form with OnClick events and this form has the same RecordSource as the second form - tblReDir and this form has a few chkboxes that I want to populate the same record as the second form did.

So, basically the second and third forms will create a record in tblReDir that has the same [Record_No] as the current record of the main form. I attached a screen shot just to give some idea of what I'm talking about.

Thanks for any help!

 

[dhookom]

I would use the OpenArgs of DoCmd.OpenForm method to send the Record_No value to the second form. Use code in the On Open event of the second form to set the default value of Record_No to the value from Me.OpenArgs.

I would never have two forms open at the same time editing the same record. Why are you opening the third form rather than editing "a few chkboxes" on the second form?

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

The third form is to provide additional details that correspond to each chkbox selected on the second form. So, I have 8 - (third forms). One each chkBox on the second form.

I can't get the OpenArgs thing to work. I tried:

Private Sub ProjStatus_AfterUpdate()
If Me![Project_Status] = "Re-Direct" Then DoCmd.OpenForm "ReDirect Reason", acNormal, , , , , Me.Record_No
End Sub

Then in the OnOpen of the Second form... what would I put to get the Record_No passed over from the first form?

 

[cdgeer]

OK, I tried this instead...

DoCmd.OpenForm "ReDirect Reason", acNormal, , , , , Record_No.Value

Then on the OnOpen of "ReDirect Reason" form:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.Record_No = Me.OpenArgs

End If
End Sub


But, when I try it out I get a "You can't assign a value to this object." Run time error.

 

[dhookom]

You need to set the DefaultValue

Me.Record_No.DefaultValue = Me.OpenArgs

I would also question the use of lots of check boxes. These often suggest un-normalized table structures particularly when there are one-to-one relationships.

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

I tried this and it still doesn't work. Can't figure out why.

Private Sub ProjStatus_AfterUpdate()
If Me![Project_Status] = "Re-Direct" Then DoCmd.OpenForm "ReDirect Reason", acNormal, , , , , Record_No.DefaultValue

End Sub

---------Then on "ReDirect Reason" form...

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.Record_No.DefaultValue = Me.OpenArgs
End If
End Sub

 

[MajP]

You can do it several ways.
1) Open args like already shown
2) Since you are not opening it in acdialog you can control it from the parent form

Private Sub ProjStatus_AfterUpdate()
 If Me![Project_Status] = "Re-Direct" Then DoCmd.OpenForm "ReDirect Reason", acNormal, , , , , Record_No.DefaultValue
 Forms("Redirect Reason").Record_Number.defaultvalue = me.Record_No
End Sub

3) Or since you will always open this form with the main form you can have the form reference the main form

Private Sub Form_Open(Cancel As Integer)
   if currentproject.allforms("Program_Management").isloaded then
       Me.Record_No.DefaultValue = forms("Program_Management")
 End Sub

 

[dhookom]

Try the following which assume Record_No is numeric.

Private Sub ProjStatus_AfterUpdate()
    If Me![Project_Status] = "Re-Direct" Then 
        DoCmd.OpenForm "ReDirect Reason", acNormal, ,"Record_No=" & _
              Me.Record_No , , , , Me.Record_No
    End If
End Sub

---------Then on "ReDirect Reason" form...

Private Sub Form_Open(Cancel As Integer)
    If Len(Me.OpenArgs & "") > 0 Then
        Me.Record_No.DefaultValue = Me.OpenArgs
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Thanks Duane! I got it to work with that last code you gave me. But, the problem I had was that I had the ControlSource of Record_No on the "ReDirect Reasons" form set to the Table ReDirectReasons.Record_No field. I had to make it unbound in order for it to work.

So, now the problem is that the form doesn't update the Record_No in the table along with the chkBox updates to the record.

Would I have to do some kind of UPDATE query when the form is closed to create the record in the table with the proper Record_No from the form included?
If so, what if I have up to 8 forms with chkBoxes that are fields within the same table (and when forms are opened,updated,then closed) should all together create one record in the table with the Record_No from the main form.?

 

[dhookom]

The text box must be bound.

Duane
Hook'D on Access
MS Access MVP