search cmd on a form for a subform on a different form

[Will G]

Hello i am new to access dabase developement and i am trying to sort a table
subform that's on a form, but the sort if made through a click action on a
search cmd on anther form.
details:
subform name.......SubformItems(a table or query)
frm that contains the subform.....frmSearchItems
frm with the search command......frmMain

i tried this code bellow but it didnt work. this code should go on the
record source of the subform. i dont know what's wrong with my code. can
anyone help

strSQL="Select * from qrMatt where"
strSQL=strSQL & " ModelNumber=" & main!ModelNumber
Forms!frmSearchItems!SubFormName.Form.RecordSource=strSQL

any suggestion....

 

[Ofer Cohen]

Try running requery

strSQL="Select * from qrMatt where"
strSQL=strSQL & " ModelNumber=" & main!ModelNumber
Forms!frmSearchItems!SubFormName.Form.RecordSource=strSQL
Me.SubFormName.Requery

 

[Will G]

Hi,
thanks hugely for your help, it seems to work at the beginning, but i guess
other code that i have in the search event of the cmd button is crossing it
and it's giving an error. the code that i have at the beginning is to search
for the ModelNumber and other items on the form. but now that i am
introducing that new form that contains that subform datasheet the error is
directing me to one of the lines in the code. the code is:
Private Sub Search_Click()
Dim strModelNumberRef As String
Dim strSearch As String
'Check txtSearch for Null value or Nill Entry first.
DoCmd.OpenForm "frmSearchItems"
If IsNull(Me![TxtSearch]) Or (Me![TxtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![TxtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
DoCmd.ShowAllRecords
DoCmd.GoToControl ("ModelNumber")
DoCmd.FindRecord Me!TxtSearch
' DoCmd.FindRecord froms!frmSearchItems!ModelNumber
ModelNumber.SetFocus
strModelNumberRef = ModelNumber.Text
TxtSearch.SetFocus
strSearch = TxtSearch.Text
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control
If strModelNumberRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
ModelNumber.SetFocus
TxtSearch = ""
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
TxtSearch.SetFocus
End If
End Sub

and what i really want to do, is to type any letter in the search text area
and the subform should come up sorted with that initial letter or letters
entered and when i select one of the items from the datasheet, the records on
the main form should come up and the datasheet should close.

i know this is a lot , and that's the reason is breaking my head, can you
help with this please.