Use selected record in Form B (acDialog Form) as go to criteria in Form A

[Ragbrai]

Howdy all,

I have a form for entering data (Form A), on which is a cmd button that opens Form B in acdialog mode to allow me to enter/edit records that are being displayed on Form A.

After adding/editing a record on Form B (opened in acDialog mode), I want a cmd button on Form B to close Form B and then have Form A automatically take me to the record that was added/edited in Form B (Form A has a subform that I will then enter data into).

My experience with VBA is very limited so please provide details and descriptions.

My cmd button on Form B currently does everything I want it to do except for this issue. The code for the cmd button on Form B currently is:

*************** *************** *************** ******
Private Sub cmdCloseEventFo rm_Click()
On Error GoTo Err_cmdCloseEve ntForm_Click

'Set criteria record for frm_Camas_Data upon return
'*** STILL WORKING ON THIS--HELP

DoCmd.Close
'Refresh frm_Camas_Data
Forms!frm_Camas _Data.Requery
'Match cboEventName to Event Name
Forms!frm_Camas _Data!cboFindEv entAll = Forms!frm_Camas _Data![EventName]

Exit_cmdCloseEv entForm_Click:
Exit Sub

Err_cmdCloseEve ntForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseEv entForm_Click

End Sub
*************** *************** *************** ***********

Thanks for the help!!!

[NeoPa]

I'm not sure exactly what's wrong here, but my first instinct would be to move the 'Requery' after the line where you set up 'Forms!frm_Cama s_Data!cboFindE ventAll'.
Please ignore this advice (see below) - it's wrong.

[MMcCarthy]

I'm not sure exactly what's wrong here, but my first instinct would be to move the 'Requery' after the line where you set up 'Forms!frm_Cama s_Data!cboFindE ventAll'.

No the order is right.

The Form A (frm_Camas_Data ) has to be requeried before the new record can be found.

To add the record you need to insert it into the table or query behind the aforementioned form.

eg.

Dim strSQL As String

strSQL = "INSERT INTO TableOrQueryNam e (Field List separated by commas) VALUES (" & Me.Value1 & ", " & Me. Value2 ... etc. & ");"

DoCmd.RunSQL

[NeoPa]

Oh yes - my bad.
I understand the logic now - thx.

[Ragbrai]

Thanks for the respones but I don't think this is what I'm trying to make happen.

What needs to happen is that when the cmd button in Form B is clicked, Form B needs to close (which is does) and then in Form A (which remained open in the background since Form B was opend with acDialog) I want the form to automatically be updated with the record that was either edited/added when in Form B.

In other words, add record XYZ in Form B, click on cmd button and return to Form A with Form A showing record XYZ. Or edit record XYZ in Form B, click on cmd button and return to Form A with record XYS showing.

I would also add that the current code DOES update the records in Form A, thus I'm not sure the suggested code you provided is necessary.

Any other thoughts?

Thanks again!

[MMcCarthy]

Forms are just a visual interface for the records in the tables and queries they are based on.

If Form B adding or updating the same record source then you just need to requery form A and use the DoCmd.FindRecor d or DoCmd.GoToRecor d.

You will have to set focus on Form A. Use one of the above to go to the record based on unique value on Form B while its still open then close Form B.

Thanks for the respones but I don't think this is what I'm trying to make happen.

What needs to happen is that when the cmd button in Form B is clicked, Form B needs to close (which is does) and then in Form A (which remained open in the background since Form B was opend with acDialog) I want the form to automatically be updated with the record that was either edited/added when in Form B.

In other words, add record XYZ in Form B, click on cmd button and return to Form A with Form A showing record XYZ. Or edit record XYZ in Form B, click on cmd button and return to Form A with record XYS showing.

I would also add that the current code DOES update the records in Form A, thus I'm not sure the suggested code you provided is necessary.

Any other thoughts?

Thanks again!

[Ragbrai]

This makes sense but how do I use criteria (the specific record value from one f the fields) from Form B (which closes) as the criteria in the FindRecord or GoTo commands. This is really the problem that I can't figure out. Is there a way to store the value of the Form B field in a temporary holding field so that it can then be used to find the identified record in Form A.

Please bear with me as my experinence with VBA is limited at best.

[NeoPa]

There is only one way that I can think of that provides what you're after.
Design a public function in a (ordinary) module which allows you to set AND get the values you need.
I have one you're welcome to use but it was designed for sharing an unspecified number of parameters with a report.

'RptParms sets and returns a set of parameters required by a report.
'Call with intSetGet=0 to set various parameters.
'Call with intSetGet>0 to return an individual parameter.
Public Function RptParms(intSetGet As Integer, _
                         ParamArray avarParams() As Variant) As Variant
    Static avarParms() As Variant
    Dim intIdx As Integer
 
    RptParms = 0
    If intSetGet = 0 Then
        intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
        If intSetGet < 1 Then
            ReDim avarParms(1 To 1)
            avarParms(1) = "Error"
            Exit Function
        End If
        ReDim avarParms(1 To intSetGet)
        For intIdx = 1 To intSetGet
            avarParms(intIdx) = avarParams(intIdx - 1)
        Next intIdx
    Else
        'If outside bounds then it drops through and is set to "Error"
        On Error Resume Next
        If avarParms(intSetGet) = "Error" Then
            RptParms = "Error"                  'On Error
        Else
            RptParms = avarParms(intSetGet)
        End If
    End If
End Function

[Ragbrai]

SOLUTION!

I must admitt that I couldn't follow the above advice, however, after much trial and error here is the solution that worked to solve the problem.
_______________ _______________ _______________ ______________
Private Sub cmdCloseEventFo rm_Click()
On Error GoTo Err_cmdCloseEve ntForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Save

stDocName = "frm_Camas_Data "
stLinkCriteria = "[EventName]='" & Me.[EventName] & "'"

'This command updates database with current record
Me.Recordset.Mo ve (0)

'Opens frm_Camas_Data and goes to last record displayed in frm_Events
DoCmd.OpenForm stDocName, , , stLinkCriteria

'Close frm_Events
DoCmd.Close acForm, "frm_Events ", acSaveYes

Exit_cmdCloseEv entForm_Click:
Exit Sub

Err_cmdCloseEve ntForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseEv entForm_Click

End Sub
_______________ _______________ _______________ _______________ _