Form control

[JST]

Hi,

I am using a form (Form_cus) to display records of a table (Table_cus) through a query (qry_cus).

I want to filter the records based on field (cus_nm) value typed in a text box on the form.

What should be the VB code in the "on change" event so that it displays the records based on cus_nm value(text) I type in the text box.

regards

 

[GinaWhipp]

Something like...

     Me.Form.Filter = "cus_nm Like '*" & Me.txtTextBoxName.Text & "*' "
     Me.FilterOn = True
     Me.txtTextBoxName.SetFocus
     Me.txtTextBoxName.SelStart = Len(Me.txtTextBoxName.Text)

...in the On_Change event procedure of txtTextBoxName

 

[arnelgp]

if you filter on cus_nm and you want to further filter your data using the column headings, your previous filter will be lost. so instead of filtering, modify your record source on the fly:

private sub textbox_change()
me.recordsource = "select * from table where cus_nm like '*" & me.textbox & "*';"
end sub

 

[JST]

Hi Arnel: Many thanks.

There is a problem while executing. When I type a text in the text-box, it searches the column, but the cursor goes to 1st column of 1st row and I have to click back in the text-box for typing the second letter. say for example, if I type 'A' of your Arnel, then the cursor goes to the first record in the form and I have to click in the text-box gain to type "r". And the cursor goes back again to first record and so on......

 

[arnelgp]

then place your code in the after update event of your textbox and delete the code from your change event.

 

[JST]

thanks Arnel.

 

[GinaWhipp]

Actually, if you use the last two lines of my code the cursor will not jump.

Side note: Using the Form Filter does not negate the Recordsource Where criteria.

 

[JST]

and one more small help. I want to open the form through a module. I have used the following code in module

Private Sub Form_Open()

DoCmd.OpenForm "Form-dt"

End Sub

I want the form to be loaded through module upon opening the database.

rgds

 

[arnelgp]

you can save yourself with that. remove the code you have right now, and go to Option->Current Database->Display Form, put your form name there.

 

[JST]

Arnel, I have been using this since ACCESS 2007 times. Just wanted to try something different by opening the form thru VB code. If you could help pl.

 

[arnelgp]

create a public Function in a Module, ie:

Public Function fnOpenForm(strFormName As String)
DoCmd.OpenForm strFormName
End Function

now, create a macro, use RunCode:
=fnOpenForm("Form-dt")

save and name your macro Autoexec.

exit the database to keep changes.
open it again to test.

 

[JST]

Thanks Arnel, it's been a lot of learning