Can i change recordsourse of search field in separate form? (1 Viewer)

[loyalty]

hello !

I've a form contain a search box (find_name) on the formheader
it work without problems with its recordsourse through this vba

Private Sub find_name_Change()
Dim ct As String * 1
Dim ss As String * 1
ct = """"
ss = "*"
Me.find_name.Tag = Me.find_name.Text
Me.RecordSource = "SELECT * FROM persons WHERE persons.[personName] = '" & "Keven" & "' and persons.job LIKE " & ct & ss & (Nz(Me.find_name.Tag)) & ss & ct & ""

If Me.RecordsetClone.RecordCount = 0 Then
Me.Detail.Visible = False
Me.RecordSource = ""
Else
Me.Detail.Visible = True
End If
Me.find_name = Me.find_name.Tag
Me.find_name.SetFocus
SendKeys "{f2}"
End Sub

the search form is opened through click buttons in other form (general) and this also work through
DoCmd.OpenForm "search", , , "[personName]= '" & "Keven" & " '"

but i want each button not only open on specific name in the search form but also search only in his own data in the search box (find_name) so,

how can i change recordsourse of the search box [find_name] in search form when i click the button in the general form .. i tried this code but doesn't work


Forms!search.find_name.RecordSource ="SELECT * FROM persons WHERE persons.[personName] = '" & "Keven" & "' and persons.job LIKE " & ct & ss & (Nz(find_name.Tag)) & ss & ct & ""

Can i find help!!

 

[Ranman256]

why set the tag, when you can use the .text?
you cant query on a .tag.

const Q = """"
Me.find_name.Tag = Me.find_name.Text
Me.RecordSource = "SELECT * FROM persons WHERE persons.[personName]='Keven' and persons.job LIKE " & Q & "*" & Nz(Me.find_name.Tag) & "*" & Q

instead of changing the rowsource, just filter ALL records:

me.filter = "[personName]='Keven' and persons.job LIKE " & Q & "*" & Nz(Me.find_name.Tag) & "*" & Q
me.filterOn = true

 

[loyalty]

thanks Ranman256
you are true but the problem is not in the filter at the same form (search)

i want that filter when click a button in other form (general) so,

the vba me.filter or Me.RecordSource not work

thanks for your reply but now you understood me .. right ?

 

[Minty]

You can set the Rowsource on a control on another form but
a) The form has to be open
b) The control need to be referenced correctly.

See here http://access.mvps.org/access/forms/frm0031.htm for the correct syntax. I think for your form it should be
Forms!YourFormName!find_name.Rowsource =

 

[loyalty]

unfortunately doesn't work

how can i write the required code in other form not in the same form ??