Solved Record Selection in Continuous Form

[markmywords]

Greetings All -

I have a data form that includes a search subform for contracts (unique "ContractID"). The subform is continous. It uses an unbound text field that dynamically filters the returned records on key up. Whether before or after filtering - if a record is clicked in the subform, the main form is filtered to that record using "ContractID". All works just fine as-is. What I'd like to explore is the possibility of automatically "clicking" the record in the continuous subform when the record count =1. Seems logical enough, but I have yet to figure it out. Any input would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

What does "clicking" the record in the subform do? Do you have some VBA code in the subform for that? If so, you can simply call it.

 

[markmywords]

Simple enough, clicking on the contract number in the search subform filters the parent form like this:

Private Sub ContractNumber_Click()
Me.Parent.Form.Filter = "[ContractID] = " & [ContractID]
Me.Parent.Form.FilterOn = True
Me.Parent.Refresh
End Sub

I'd just like that to be automatic when the number of records filters down to just one.

 

[theDBguy]

Then, you could try something like:

If Me.SubformControlname.Form.Recordset.Recordcount = 1 Then
    Call Me.SubformControlname.Form.ContractNumber_Click()
End If

If you get an error, you could try changing the Private Sub into a Public Sub.

 

[markmywords]

Slight modification:
Installed this on the AfterUpdate event of the search box in the header of the subform:

Private Sub txtNameFilter_AfterUpdate()
If Me.Recordset.RecordCount = 1 Then
Call ContractNumber_Click
End If
End Sub

Works like a champ, thanks very much!

 

[Pat Hartman]

An unbound text field in a continuous form always shows the same value. I would simply use a DS view subform and use the Access filters that come with the DS view.

Since the forms seem to open with the subform unfiltered and therefore showing all the records, the simplest solution is to have the mainform bound to a query that includes a where clause that references the subform.

Where MyID = Forms!myform!Mysubform.Form!MyID

The subform loads first and the first row will be "current" and so your mainform should open to that record. Then when you click a specific field in the subform, just requery the main form.

This method of filtering will be fine for small recordsets with ACE BE's. Since I generally work with SQL Server, I would not use this method of having the subform start by showing all the records.

 

[theDBguy]

Slight modification:
Installed this on the AfterUpdate event of the search box in the header of the subform:

Private Sub txtNameFilter_AfterUpdate()
If Me.Recordset.RecordCount = 1 Then
Call ContractNumber_Click
End If
End Sub

Works like a champ, thanks very much!

Good job! Good luck with your project.