Synch Listbox RowSource With Form's RecordSource

J

JamesJ

I'm trying to filter a form with a listbox based on text typed into an
unboun text box.
I type the text and then click the filter button that has the following
code:

If IsNull(Me!txtFilterName) Then
MsgBox "Cannot apply filter, text required!", vbOKOnly, "Filter Info"
Me!txtFilterName.SetFocus

Else

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True
Me!lstCd.RowSource= Me.RecordSource
Me!txtFilterName.SetFocus

End If

I'm attempting to synch the form and the list box with this line:
Me!lstCd.RowSource= Me.RecordSource
Buit it isn't working.

Any help will be appreciated.
James
 
J

Jeanette Cunningham

Hi James,
something like this untested air code-->
Dim strSQL as String

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True

strSQL = "Select yadda, yadda " _
& "From Table " _
& "Where [CdTitle] Like ""*" & Me.txtFilterName & "*"""
Debug.Print strSQL
Me!lstCd.RowSource = strSQL
Me!txtFilterName.SetFocus
End If


Note: replace yadda and Table with your names

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
J

JamesJ

Don't know why but I get an error:

'End If without block If'

Uoty of curiositty I remarked out the End If. No errors but it
didn't retrurn any records.
The form appears to be getting filtered properly. I have an unbound textbox
that returns the number of records properly when I remark out the End If.

James

Jeanette Cunningham said:
Hi James,
something like this untested air code-->
Dim strSQL as String

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True

strSQL = "Select yadda, yadda " _
& "From Table " _
& "Where [CdTitle] Like ""*" & Me.txtFilterName & "*"""
Debug.Print strSQL
Me!lstCd.RowSource = strSQL
Me!txtFilterName.SetFocus
End If


Note: replace yadda and Table with your names

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


JamesJ said:
I'm trying to filter a form with a listbox based on text typed into an
unboun text box.
I type the text and then click the filter button that has the following
code:

If IsNull(Me!txtFilterName) Then
MsgBox "Cannot apply filter, text required!", vbOKOnly, "Filter Info"
Me!txtFilterName.SetFocus

Else

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True
Me!lstCd.RowSource= Me.RecordSource
Me!txtFilterName.SetFocus

End If

I'm attempting to synch the form and the list box with this line:
Me!lstCd.RowSource= Me.RecordSource
Buit it isn't working.

Any help will be appreciated.
James
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

You are right to comment out the End If - it isn't needed.
If you get no records in the listbox, there must be a problem with its row
source or the number of columns (including any hidden column).
Use the debug.print line to check on the row source for the list box.
After you click the filter button, press Ctl + G.
You will see the sql string written out in the immediate window.
Copy and paste it into the sql view of a new query.
Switch the query to data sheet view and see what errors access gives you.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


JamesJ said:
Don't know why but I get an error:

'End If without block If'

Uoty of curiositty I remarked out the End If. No errors but it
didn't retrurn any records.
The form appears to be getting filtered properly. I have an unbound
textbox
that returns the number of records properly when I remark out the End If.

James

Jeanette Cunningham said:
Hi James,
something like this untested air code-->
Dim strSQL as String

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True

strSQL = "Select yadda, yadda " _
& "From Table " _
& "Where [CdTitle] Like ""*" & Me.txtFilterName & "*"""
Debug.Print strSQL
Me!lstCd.RowSource = strSQL
Me!txtFilterName.SetFocus
End If


Note: replace yadda and Table with your names

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


JamesJ said:
I'm trying to filter a form with a listbox based on text typed into an
unboun text box.
I type the text and then click the filter button that has the following
code:

If IsNull(Me!txtFilterName) Then
MsgBox "Cannot apply filter, text required!", vbOKOnly, "Filter Info"
Me!txtFilterName.SetFocus

Else

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True
Me!lstCd.RowSource= Me.RecordSource
Me!txtFilterName.SetFocus

End If

I'm attempting to synch the form and the list box with this line:
Me!lstCd.RowSource= Me.RecordSource
Buit it isn't working.

Any help will be appreciated.
James
Click to expand...
Click to expand...
Click to expand...
 
J

JamesJ

Works ok now.

The imemdiate window read:

Select Yadda, Yadda....

I guess I'm watching too much Seinfeld.

Thanks much,
James

Jeanette Cunningham said:
You are right to comment out the End If - it isn't needed.
If you get no records in the listbox, there must be a problem with its row
source or the number of columns (including any hidden column).
Use the debug.print line to check on the row source for the list box.
After you click the filter button, press Ctl + G.
You will see the sql string written out in the immediate window.
Copy and paste it into the sql view of a new query.
Switch the query to data sheet view and see what errors access gives you.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


JamesJ said:
Don't know why but I get an error:

'End If without block If'

Uoty of curiositty I remarked out the End If. No errors but it
didn't retrurn any records.
The form appears to be getting filtered properly. I have an unbound
textbox
that returns the number of records properly when I remark out the End If.

James

Jeanette Cunningham said:
Hi James,
something like this untested air code-->
Dim strSQL as String

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True

strSQL = "Select yadda, yadda " _
& "From Table " _
& "Where [CdTitle] Like ""*" & Me.txtFilterName & "*"""
Debug.Print strSQL
Me!lstCd.RowSource = strSQL
Me!txtFilterName.SetFocus
End If


Note: replace yadda and Table with your names

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I'm trying to filter a form with a listbox based on text typed into an
unboun text box.
I type the text and then click the filter button that has the following
code:

If IsNull(Me!txtFilterName) Then
MsgBox "Cannot apply filter, text required!", vbOKOnly, "Filter
Info"
Me!txtFilterName.SetFocus

Else

If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = "[CdTitle] Like ""*" & Me.txtFilterName & "*"""
Me.FilterOn = True
Me!lstCd.RowSource= Me.RecordSource
Me!txtFilterName.SetFocus

End If

I'm attempting to synch the form and the list box with this line:
Me!lstCd.RowSource= Me.RecordSource
Buit it isn't working.

Any help will be appreciated.
James
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering data after clicking in a listbox 2
Can't change a form's recordsource 1
Help with row movement/filtering, and function calls 2
Filter form for minimum network traffic 4
Filtering 1
Listbox filtering on a form 7
Synchronizing current record with list box 6
strFilter format 3

Top