refreshing a subform

[snoopy22]

Hello everyone,

I've got a main form with a search field and a subform. what I would like to do,
is when I hit the "search" button, the data on the subform will be changed
according to the search filter, I've got no problems with the sql expression.
my problem is: How can I change the recordsource of the subform from
the main form?

My code:
subform:

Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = "select * from Transaction order by details"
End Sub
---------------------------------------------------------------------------
Main form:

Private Sub cmdSearch_Click()
Forms!subTrans.recordsource = "select * from transaction where Name like " & """" & "*" & txtSearch & "*" & """" <--- error massage

subTrans.Requery
End Sub

 

[ByteMyzer]

You can not make reference to the subform's name directly from the main form; the subform itself in this case is not loaded except as a child object of the main form.

Assuming that the name of the subform control in your main form is subTrans, then your main form code needs to be modified thus:

Me.subTrans.Form.RecordSource = "select * from transaction where Name like " & """" & "*" & txtSearch & "*" & """"
Me.subTrans.Requery

 

[modest]

I think Byte may be right. But your SQL may need changed as well... you shouldn't use so many confusing quotes.

= "SELECT * FROM [Transaction] WHERE Name Like '*" & txtSearch & "'*"

If it really needs those double quotes then

= "SELECT * FROM [Transaction] WHERE Name Like ""*" & txtSearch & "*"""
or
= "SELECT * FROM [Transaction] WHERE Name Like " & vbQuote & "*" & txtSearch & "*" & vbQuote

vbQuote can be replaced with chr(34)

 

[snoopy22]

thanks guys

I've already got It working!

but thanks for your comments, you were right!

 

[modest]

Which was the problem, how you called it? Or was it a problem with the query?

 

[snoopy22]

the problem

was,that I wrote:

Forms!subTrans.recordsource

instead of:

Me.subTrans.Form.RecordSource

the query works fine.