subform problem...

[matee]

hi..
i have a problem with subform filter..

i have 2 tables: called 'comp' with companies info, and subform 'news' with news about this company.

company has ID as a primary key, and some others.
news has ID_NEWS as PK and ID_COMP as foraign key from company, DATE of news and others.

then i have main form called "super" and subform "new" which shows news about this company.

i would like to have a textbox in which i enter the date, and find all news from that date.

i tried to use filtering.
i have textbox called find_date and button "find"

Private Sub find_Click()
DoCmd.ShowAllRecords
Dim criteria As String
criteria = find_date.text & "=" & DATE
DoCmd.ApplyFilter , criteria
End Sub

this doesent work..
any ideas? maybe totally diferent solution than filtering?

thanks.
mateusz.

 

[WayneRyan]

mateusz,

Your:

criteria = find_date.text & "=" & DATE

Should be : find_date & " = #" & DATE & "#;"

You don't need the find_date.text as that only applies to your
screen control not the data in your table.

Wayne

 

[matee]

hello..
thanks for help Wayne.

filter is working.. and not =(
it is working, but not showing any data (form is blank).

maybe you can't filter a subform??

what i need is finding data in a subform connected with main form.

when i have one company, and loads of news i need to find news from this comp. from given date.

 

[adam_fleck]

subform filter trouble

You could try changing the RecordSource for the subform. Something like this:

Private Sub find_Click()
On Error Resume Next

Dim str_SQL As String, dbl_Date as double

'If the entry in the date box isn't a date, there nothing to do
'though you could show a message

If Not IsDate(Me.txt_date) Then
'MsgBox "That isn't a date", vbCritical, "User Error"
Exit Sub
End If

'Build the subform's SQL statment (see note 1)

str_SQL = "SELECT news.*" & _
" FROM news" & _
" WHERE item_date = " & CDate(Me.txt_date) & ";"

'Reset the subform's recordsource (see note 2)

Me.news_subform.Form.RecordSource = str_SQL

End Sub

Note 1
This assumes the table containing news items is called "news" and that the date of a news item is stored in a field called "item_date"
It also assumes that there is is a text box control on your form called "txt_date" and that users will type dates into this.

Note 2
This assumes that the subform control on your main form has been called "news_subform". To find out what it's called, use View, Properties then turn to the Other Tab.

 

[kissinger]

subform use query not table for subform

First make a query that uses the same table as the subform now uses.
In criteria refer to fields of the main form.
e.g. [Forms]![MyMAINFORM]![MyCompany]
e.g. >=[Forms]![MyMAINFORM]![Mystartdate]
then change the subform datasource to the query you made.
this works real fine....