Form with 2 Subforms, Continuous Form not Filtering

[akrshaw]

Hello!~

I have the following:

Edit Personnel Form

Form Header

First Name(Unbound Text Box) Last Name (Unbound Text Box) Filter (button) Clear Filter (button)

Detail

SubformWorker SubformResume


When you type the first or last name in and hit the filter button the SubformResume changes to the first record with that name. The SubformWorker should limit the list to just the names filtered. But it is not.

Any help is GREATLY appreciated!~

 

[PHV]

What is the code of the Click event procedure of the filter button ?

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

 

[akrshaw]

Here it is:

Private Sub Command14_Click()


Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.FirstName) Then
strWhere = strWhere & "([First Name] Like ""*" & Me.FirstName & "*"") "

End If

If Not IsNull(Me.LastName) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.LastName & "*"") "

End If

lngLen = Len(strWhere) - Len(Trim(andOr)) - 1
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True


End If
End Sub

 

[MajP]

Your code filters the main form. So how are the subforms linked to the main form? What are the Link Master Fields and Link Child Field properties of the two subforms? My guess is there is not a link, and thus no reason that the subform would filter.

 

[PHV]

So, what are the Master/Child relations between SubformWorker & SubformResume and the main form ?

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

 

[akrshaw]

The master and the subforms are linked through the WorkerID.

Now the form is showing only the first record, not the complete list of records.

And when there is more than one record with that criteria its not showing them.

 

[dhookom]

Are the subforms continuous or datasheet?

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

I have tried in continuous and datasheet...they are both only showing the first record when the form opens.

When you search it is bringing up only the first record.

 

[akrshaw]

OK...I have thought about it and re-read your question.

Now I have a sensible answer!~

The SubformWorkerID I have tried in Datasheet and Continuous view and it only shows one record.

The SubformResume shows Single Form view but if there were multiple records shows the record locator.

 

[dhookom]

Is your issue resolved?

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

Duane -

No, I had to take an unexpected trip to Hungary, my daughter made the US Field Archery Team.

I have just gotten back into the database, and I am still as confused as ever.

The MainForm searches, the Subforms populate the data of the first record that matches...but they are not showing the other records using continuous forms or datasheet view...or in the record selector in the other subform.

I can't for the life of me figure out why?

Thanks sooo much for your help!~

 

[dhookom]

If you have the link master/child properties set, this will filter the subforms.

What do you see in the debug window?

What is "andOr"?

Have you set a break point and stepped through the code?

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

It filters the subforms...but it does not show all the records.

Both subforms have master/child links to WorkerID from the main form.

The only issue I get for debug is when I type in a first AND last name and hit the filter button.

Then it has issues with:

Me.FilterOn = True

At the end of the code.

I have even tried taking the single subform out of the form and the list subform is still only showing one record.

 

[dhookom]

What is the primary key of the subform that you expect to see multiple records? Are there more than one records with the same WorkerID?

Why can't you tell us what you see in the debug window?

Set the break point near the top of the code and watch what happens as you step through the code using the F8 key.

The code and setup suggests the main form is bound. Is this correct?

What are the signficant fields and relationships between the main form and subforms?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

Private Sub Command14_Click()
Dim strWhere As String
strWhere = "1=1"
If Trim(Me!FirstName & "") <> "" Then
  strWhere = strWhere & " AND [First Name] Like '*" & Replace(Me!FirstName, "'", "''") & "*'"
End If
If Trim(Me!LastName & "") <> "" Then
  strWhere = strWhere & " AND [Last Name] Like '*" & Replace(Me!LastName, "'", "''") & "*'"
End If
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

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

 

[akrshaw]

OK, I used PVH's code...it resolves the first and last name issue.

BUT

When I put in a record that doesn't exist NOW it shows me all the records in the Continuous Form...but only one record in the Single Form.

Before it would just go blank.

It still does not show me multiple records for either form.

Duane -

Anytime I set a break point and try and use F8 to walk through the code it gives me a ding...and does nothing.

The WorkerID is a primary key.

The only information in the Main Form is the record source:

SELECT TblWorker.WorkerID, TblWorker.[First Name], TblWorker.[Last Name] FROM TblWorker;

Which is apparently the thing that is limiting the listing on the continuous form.

The only other thing on the Main Form is the 2 unbound text boxes and the filter buttons.

The Main Form and the SubForms are only bound by the WorkerID fields.

 

[dhookom]

Why are you filtering the main form and why does your main form have a record source if you don't display any field values in your main form. If the main form is only a search form, then don't use a record source or filter.

I think you want to remove the link master child and apply the filter to the subform records.

AGAIN, "What are the signficant fields and relationships between the main form and subforms?"

Duane
Hook'D on Access
MS Access MVP

 

[akrshaw]

If I take the record source out of the mainform then I get the records listed in the continuous subform and I see the records in the record locator on the single form.

Now the filters do not filter.

The only relationship that the 2 subforms have is the WorkerID.

SubformWorkerID houses the First and Last Name

SubformResume houses the Address, etc

There are no other relationships that they share.