Using SubForm criteria to Open a Main Form

[AngeloO]

I want to open a Form using criteria from a subform.

I have a form that has two fields:
* Log# (AutoNumber)
* SubmittedBy.
SubmittedBy is automatically populated with the user ID associated with the person who originally created the record.
The current user's ID is sUser.

Right now, when someone enters the form, the docmd.openform criteria looks for the person's User ID and only opens the records this person submitted.

Here is the current working criteria:
stLinkCriteria = "SubmittedBy = '" & sUser & "'"

-----------
I created a subform and connected it to the mainform by the Log#.
The subform also has a field for alternate submitters.

I would like to open records based on the SubmittedBy Criteria "and" if the person is an Alternate Submitter.

The main form is named [frmDipAddrecords].
The Subform is named [AlternateSubmitters_SubForm]
The Alternate Submitter field name is [cmbUserId]


Here's my attemp for the docmd.openForm search criteria to try to open records for the Alternate Submitter.

stLinkCriteria = "[Forms]![frmDipAddrecords]![AlternateSubmitters_SubForm].Form![cmbUserId] = '" & sUser & "'"

The criteria does not bomb out. It just comes up saying there are no records for this criteria.

Thanks so much!!

Angelo

 

[Remou]

What is the RecordSource of the subform?

 

[AngeloO]

The subform is based on a table that has Log# and UserID.
The UserId was converted into a combobox based on a UserId/Name table.

Thanks for looking at this!!

 

[Remou]

It would be much easier if you posted the RecordSource, however, I will attempt a sketchy reply. In the AfterUpdate event of cmbUserId:

[tt]Me.RecordSource = "Select * From tblTable " _
& "WHERE UserID='" & Me.Parent.UserID & "' OR UserID='" _
& Me.cmbUserId & "'"[/tt]

 

[AngeloO]

In trying to simplify the probelm, I may have gone too far.

Here are the steps I'm looking at.

1. A New Record Button is pressed and the user opens the Main Form on a new record with a new Log#. His/her userID is placed in the SubmittedBy control.
2. The user can input an Alternate Submitter who can update the information. This user info is placed in the subForm - [AlternateSubmitters_SubForm].Form![cmbUserId] using the cmbUserId combo box (this box has a list of possible Alternate Submitters).
3. Our tblAlternateSubmitter table now has a record in it with Log# and UserId.
4. The Form is closed.
5. The alternate Submitter than opens the same Main Form to review any records he/she is associated with. That's where the filtering code comes in.

The Alternate Submitter wants to see all Log#s that he/she has submitted or is listed as an Alternate Submitter.

So, I'm trying to look into the subform to see if the current user is an Alternate on any records. If they are, then I want that to be one of the records I open for review.

Does that help??

Thanks!@!

Angelo

 

[Remou]

Try:

[tt]Me.RecordSource = "Select * From tblTable " _
& "WHERE UserID='" & Me.Parent.UserID & "' OR AlternateUserID='" _
& Me.Parent.UserId & "'"[/tt]

You will, of course, need to edit to suit your application.

 

[AngeloO]

Thanks so much!!

I'll give that a try!!