Cascade of records

[Brando]

Hello,

I am a relative novice in Access, but have successfully built several functioning databases.

On my current project, I have a search form with multiple search criteria that displays the results in a subform. After inputting the desired criteria into several text boxes, the user clicks a command button which builds a VB filter. The user can then double click on a resulting record, which opens a detail form of that record. All of the above works great.

Here is the question: what approach should I use to code a command button on the search form that will bring up the detail form with all of the results of the filter? In other words, if there are 10 search results, I want the command button to open the detail form and be able to use the record selectors to navigate only those 10 results.

Thanks in advance for any help.

Brando

 

[pbaldy]

How do you do those first 2 functions? I'm thinking you should be able to combine them for this. If you're indeed building a filter, you can use it with OpenForm to get the same filtering, instead of the single record wherecondition I'm guessing you build now.

 

[Brando]

I am following your suggestion (thank you) but getting a syntax error when it gets to the filter (where condition).

Could this have something to do with the fact that this is an OpenForm command, since the filter has "Me.txtBox.." references to the text boxes in the search form?

 

[pbaldy]

It's hard to figure out where the error is coming from without seeing the code.

 

[Brando]

Below is the code for the command button. Again, the idea is to view the results of the search form on a record detail form. Thanks for having a look.

Private Sub cmdCascade_Click()
On Error GoTo Err_cmdCascade_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDetailCascade"
stLinkCriteria = "SELECT * FROM qryBothTables " & BuildFilter

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCascade_Click:
Exit Sub

Err_cmdCascade_Click:
MsgBox Err.Description
Resume Exit_cmdCascade_Click

End Sub
_______________________________________________________
Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter

If Me.txtFileNo > "" Then
varWhere = varWhere & "[File Number] LIKE ""*" & Me.txtFileNo & "*"" AND "
End If

If Me.txtPrimaryName > "" Then
varWhere = varWhere & "[Primary Name] LIKE ""*" & Me.txtPrimaryName & "*"" AND "
' varWhere = varWhere & "[Primary Name] LIKE ""*" & Forms!frmSearch!txtPrimaryName & "*"" AND "
End If

If Me.cboCustomerClass > "" Then
varWhere = varWhere & "[Class Description] LIKE ""*" & Me.cboCustomerClass & "*"" AND "
End If

If Me.cboRating > "" Then
varWhere = varWhere & "[Rating Definition] LIKE ""*" & Me.cboRating & "*"" AND "
End If

If Me.txtHO > "" Then
varWhere = varWhere & "[Responsibility Code Desc] LIKE ""*" & Me.txtHO & "*"" AND "
End If

If Me.chkAction < 0 Then
varWhere = varWhere & "[Action] = " & Me.chkAction & " AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[pbaldy]

Well, there are two main problems, both related. The wherecondition argument should be an SQL "WHERE" clause without the word "where". Therefore, you don't want to add the "SELECT..." bit to the results of the function. Secondly, that function is adding the word "WHERE", so you either need to change the function so it doesn't add it or trim it off before using it here. Change those and let's see where we are; I haven't really looked at the function itself yet.

 

[Brando]

Thanks for your help. However, I'm confused. Are you talking about the subroutine or the BuildFilter function? The Sub already has an SQL "WHERE" clause without the word "where". Also, the BuildFilter function works perfectly when used on the form to sort the records into the subform (I was hoping to call the same BuildFilter function to populate the detail forms). Can you please clarify where the two changes need to be made? Thank you in advance.

 

[pbaldy]

This:

stLinkCriteria = "SELECT * FROM qryBothTables " & BuildFilter

adds more than the "WHERE clause without the word where", so presuming the function returned the proper clause you'd just want:

stLinkCriteria = BuildFilter

Because the function includes the word "where" and you don't need it to here, but it sounds like you need it to for other things, try this:

stLinkCriteria = Right(BuildFilter, Len(BuildFilter) - 6)

In fact now that I think of it, I'd dump BuildFilter into a string first so it's only called once instead of the twice it would be called with that. Or maybe

stLinkCriteria = Replace(BuildFilter, "WHERE ", "")

or

stLinkCriteria = Mid(BuildFilter, 7)

 

[Brando]

I tried each of your suggestions:

stLinkCriteria = BuildFilter
stLinkCriteria = Right(BuildFilter, Len(BuildFilter) - 6)
stLinkCriteria = Replace(BuildFilter, "WHERE ", "")
stLinkCriteria = Mid(BuildFilter, 7)

There were no error messages, but the detail form didn't display any resuls (#Name? in each field). As stated above, the code in question is behind the command button on the search form. Should I have something in the Record Source of the detail form? If I make it the query (qryBothTables) I get a blank detail form.

Thank you.

 

[pbaldy]

The technique you're using presumes that frmDetailCascade has a record source that returns all records. The BuildFilter function builds a wherecondition to restrict those records accordingly.

 

[Brando]

If I understand you, you're saying that the code on the command button (on the search form) will filter the records that the detail form displays when opened. Therefore, no Record Source is needed in the detail form property. Correct?

 

[pbaldy]

No:

The technique you're using presumes that frmDetailCascade has a record source that returns all records.

All the wherecondition does is filter the records. There must be records there to filter.

 

[Brando]

Thank you. I think I am close now. I have used:
stLinkCriteria = Replace(BuildFilter, "WHERE ", "")
as you suggested above, and I've also given the detail form the appropriate record source.

BuildFilter is working properly as usual, as evidenced by the records being acurately filtered on the subform. However, clicking cmdCascade opens a blank form (no records). I must be missing something simple (but beyond me). Any other thoughts?

Thanks again!

 

[pbaldy]

Can you post the db, or a representative sample?

 

[Brando]

Attached are the relevent portions of the database. You'll see cmdCascade at the bottom of frmSearch. Thank you for helping.

 

[pbaldy]

I changed

stLinkCriteria = stLinkCriteria = Replace(BuildFilter, "WHERE ", "")

to

stLinkCriteria = Replace(BuildFilter, "WHERE ", "")

I filtered the first form on an option that displayed 4 records. Clicking on the button then opened another form with those 4 records.

 

[Brando]

That worked perfectly! Thank you for all of your patience and help! I really appreciate it.

 

[pbaldy]

No problemo. I assume it was a copy/paste error that got the extra bit in there.