Filter issue 1

[44nato44]

Hi

I have a form with a sub form, which is used to make a search/filter list with.

After this the user will click the filtered sublist, by clicking on the ID field

No matter which field is clicked on then the top value on the list is chosen.

Anybody has a good idea ?

Cheers

 

[MajP]

??? No idea what you just said. Please reword clearly, and provide all applicable code. Well written questions get good answers.

 

[44nato44]

sure )

I have abutton on the main form, which is do a requery on the sub form.. which then run the below code to change the filter on the sub form.

I hope this is a bit more clear ?

This is on Current on the sub form

Private Sub Form_Current()
Dim fltstr As String

' Build SQL String

If Len(Forms!FRM_InvoiceList!txtfltInvoiceNo) > 0 Then
fltstr = " [InvoiceID] = " & Forms!FRM_InvoiceList!txtfltInvoiceNo & " AND "
End If

If Len(Forms!FRM_InvoiceList!txtfltamount) > 0 Then
fltstr = fltstr & "[Amount] = " & Forms!FRM_InvoiceList!txtfltamount & " AND "
End If

If Len(Forms!FRM_InvoiceList!cbofltsupplier) > 0 Then
fltstr = fltstr & "[Supplier] = '" & Forms!FRM_InvoiceList!cbofltsupplier & "' AND "
End If

If Len(Forms!FRM_InvoiceList!cboCostElementCode) > 0 Then
fltstr = fltstr & "[CostElement] = " & Forms!FRM_InvoiceList!cboCostElementCode.Column(0) & " AND "
End If

If Len(fltstr) > 1 Then
fltstr = Left(fltstr, Len(fltstr) - 4)
MsgBox fltstr
Me.Filter = fltstr
Me.FilterOn = True
Else
Me.Filter = "[InvoiceID] > 0"
Me.FilterOn = True
End If
End Sub

 

[MajP]

That was about the only part I understood.

I have no idea what this means:

After this the user will click the filtered sublist, by clicking on the ID field
No matter which field is clicked on then the top value on the list is chosen

What list?
What do you expect to happen?

 

[44nato44]

the filter will generate a datasheet list in the subform, I made it so if the user click on the ID field then it will take that ID field that the user click on... at the moment for my testing it just open a msgbox

What happen now is that it picks the value on the top of the list and not the value which is being clicked on

I hope this makes more sense ?

 

[dhookom]

This is on Current on the sub form

Private Sub Form_Current()

I can't imagine why the code would be in the On Current of the subform. This would set the filter every time you moved from record to record in the subform.

Typically you will set all the filter values in the main form and have code in the main form that sets the filter or record source of the subform.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What is the code?

 

[44nato44]

Greeeat thanks a lot.

I allways put it in the sub form and it has allways given trouble.. so you opened my eyes.

I hope you have a good day.