By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Filter form by records that have subforms

P: n/a
I have a table called RescheduleIns and another called Shop Complete
Table that are opened together on another form called fAll Shop Orders.
On this form, all the Shop Complete Table data is displayed as the
main form and the RescheduleIns data is displayed as a subform. I
would like to open another form called fPurchasing that opens fAll Shop
Orders, but filterd based on records that have subforms. I have the
following code that loads on the fPurchasing form, but it does load
properly and I'm not sure what I'm doing wrong.

Can anyone help?

Private Sub Form_Open(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim frmCurrentForm As Form

stDocOpen = "fAll Shop Orders"

stLinkCriteria = "Forms![fAll Shop Orders]![RescheduleIns]![Top
Level Req]=" & "'" & "[Shop Order #]= '"

DoCmd.Close

DoCmd.OpenForm stDocOpen, , , stLinkCriteria

Set frmCurrentForm = Screen.ActiveForm

frmCurrentForm.Label66.Caption = "PURCHASING ISSUES"
frmCurrentForm.Label66.Width = 3780
frmCurrentForm.Label66.BackStyle = 0
frmCurrentForm.Label66.ForeColor = 0
frmCurrentForm.Caption = "fPurchasing Issues"

End Sub

I haven't created any relationships for the two tables. Should I have
created a relationship between the two?

Thanks,

Ronnie

Jul 27 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I haven't created any relationships for the two tables. Should I have
created a relationship between the two?

Thanks,

Ronnie
Why not? Maybe I've been reading too many NG posts in the last few
days, but what's the point of relational databases without
relationships? (Well, unless you want to write your own code to handle
all the update/delete/orphan records violations)

Do you mean filter form for records thant have no child records? Like
a Customer that has no Invoices? (Kind of doesn't make sense, I know,
but you can do something like that in NorthWind...)

SELECT CustomerID
FROM Customer
WHERE NOT Exists (Select CustomerID FROM INVOICE)

or use an outer join... or...

Jul 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.