Pulling up record in a popup form based on table join to the current form (record) 1

[larrydavid]

Hello,

You've all been so helplful with my recent barrage of questions so I promise I only have one more then I will rest (for awhile).

I have a popup form (using acDialog) which is working fine. On load, the popup form takes two unique values from the calling form and saves this into another table. Now, when I go back to the record on the main form, instead of a new record I want to pull up the popup with the same unique values (ID1 and ID2) tied back to the main form. In the DoCmd.OpenForm I have a WhereCondition argument but unless I am mistaken it doesn't look like I can pass filter criteria in based ona join. In any case, here is the code I have so far:

In the calling subform:

Private Sub REFUND_REASON_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As String

If [REFUND REASON] = "MCR" Then
Me.ComboTestSource.RowSource = "tbl_TEST Source"
'Pass and populate XXX in the test (popup) form with OpenArgs.
'The opened form will grab the XXX value from this calling form.
Me.Dirty = False
stDocName = "frm_Input_Popup"
If IsNull(Me.XXX) = False Then
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acDialog, Me.XXX
Else
MsgBox ("Please enter a valid XXX to proceed to test entry")
End If
End If

End Sub

In the popup form:

Private Sub Form_Load()

DoCmd.GoToRecord , , acNewRec

If IsNull(Me.OpenArgs) = False Then
Me.XXX.Value = Me.OpenArgs
'Concat the two IDs so we can tie back to the record
Me.ID1 = Forms![frm_MAINFORM]![frm_SUBform].Form.ID1
Me.ID2 = Forms![frm_MAINFORM]![frm_SUBform].Form.ID2
Else
MsgBox "XXX is required" 'required on previous page also but just in case
Exit Sub
End If

Here is the join I have for the two tables where the main and the popup form records are saved:

SELECT dbo.popup_records.*
FROM dbo.subform_records INNER JOIN
dbo.popup_records ON dbo.subform_records.ID1 = dbo.popup_records.ID1 AND
dbo.subform_records.ID2 = dbo.popup_records.ID2

So, it seems this could be handled through the VBA code passing values between the forms or by passing the IDs to a SQL statement, but I'm not sure what the best approach is, or if perhaps there is a better way altogether.

Any help is greatly appreciated as always.

Thanks,
Larry

 

[PHV]

it doesn't look like I can pass filter criteria in based ona join
Why ?
A starting point for a WhereCondition argument:
"ID1=" & Me!ID1 & " AND ID2=" & Me!ID2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[larrydavid]

Hi PH,

Sorry, but I'm a bit confused. I know I can pass in two ids in the WHERE condition, but there has to be a join behind that doesn't there? Or can I include the join in that condition? Sorry if that's a lame question.

Also, what I need to do is if the user clicks on a new row (in the subform), the popup (acDialog) form is set at acNew so that can enter a new record. But if the user goes to a previous record where the popup form has already been filled out, I want that form to populate with those values automatically.

Is there some routing logic I can use with the row (or record) source for this? If so, would it be possible to do this in the AfterUpdate() event I have above?

Thanks,
Larry

 

[larrydavid]

Hi All,

I got it working. Just one more thing I have to get help with, whcih I'll do in a new post. I know I said no more for awhile, but I have a data type mismatch which is killing me

Thanks,
Larry