Subform wont display main form search criteria

[pauljkeenan]

Hi Guys

I am a newbie to Access and have bitten off more than I can chew with a project Im doing. I am in dire need of your expertise and would be eternally grateful (really) for any advice you can give.

I have a main form (frmVisaSearchMain), which contains various textboxes and combo boxes and a search button. Below this form there is a subform (frmVisaSearchsub) which should display the results of the search in datasheet view. At the moment it only displays a record table. Clicking on the search button should result in the subform displaying the appropriate record sheet. At the moment Im trying to do it for simple search, so just by a text box called "ref no". But it still doesnt work. Any ideas please guys?

I think the problem is with the refer to subform, because even when I try put the entire original table in ths sSql variable and display this, still nothing happens

private Sub Search_Click()
On Error Resume Next

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
Dim example As String


'This code is for a specific search where you will need to enter the exact string
'The source for this code can either be from a table or query
If Me![txtRefSearch] <> "" Then
sCriteria = sCriteria & " AND qrySearchVisaSub.Ref No like """ & txtRefSearch & """"
End If


If Nz(DCount("*", "qrySearchVisaSub", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT [Ref No]from qrySearchCriteriaSub " & sCriteria

Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.RecordSource = sSql
Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.Requery
End If

End Sub

 

[Remou]

What you have is:
[tt]SELECT [Ref No]from qrySearchCriteriaSub WHERE 1=1 AND qrySearchVisaSub.Ref No like "txtRefSearch"[/tt]

As you can see, a space is missing between '[Ref No]' and 'From' and the Ref No in the Where statement is missing square brackets. You may or may not be missing wildcards, depending on the contents of txtRefSearch. You may wish to consider:
[tt]Like '*" & Replace(Me.txtRefSearch,"'","''") & "*'"[/tt]

Why 1=1?

 

[pauljkeenan]

Hi Remou

Thanks a million for the reply. I corrected the spacing and brackets but it seems that sSql string is just empty each time, no matter what I try to do. the WHERE 1=1 is from an example program Im trying to follow, it was at the end of the sql statement for the query table thats used in the subform. I removed it.

I tried, just to control the program, putting the entire records table in the sSql variable and then display in subform but still nothing.

 

[Remou]

Go ndéana sé maith duit

Is this problem with the subform or the SQL? Does the subform display records if you alter the record source manually? What happens if you cut and paste the sql from the immediate window into the query design screen? Is Rec No a numeric field? If so, Like is not such a good idea. Did you add wild cards? They are generally needed with Like.

 

[pauljkeenan]

Go raibh maith agat, a chara

ta tu an deas ar fad, an bfhuil tu i do chonai in Eireann?

I think the problem is with the SQL, I can change the subform record set to either a qryTable or a table and view them as datasheet. I used the same sql on another similar search form but there was no subform so it must be how Im referring to the subform.

I changed the search to search for names instead of the numbers but still nothing. I also tried to just display a complete table in the subform by doing this

sSql = "SELECT * FROM [tblRecordInfo]"
Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.RecordSource = sSql
Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.Requery

but it didnt change.

Go raibh mille maith agat for your help so far.

 

[Remou]

Yes, but I do not even have the cúpla focal.

Is it possible that there is some slight difference in the names of the controls? This would cause such a problem. You need to use the name of the subform control, not the name of the form contained: the two are often the same, but not always. It is often useful to type Me. (Me dot) and see if intellisense comes up with the name of the control that you expect.

 

[pauljkeenan]

ah sure we only ever know a few cupla, is maith liom subh

I was checking the control names they seem to be fine, Ill just keep looking Im sure something rediculously simple is causing the problem.

Can you just verify the following for me to make sure the syntax is correct then and Im not going down the wrong path?
example:
the mainform is called frmMain
the subform is called frmSub
if we want to display everything we have in a table "Tablename" in the subform by clicking on a button "fullList" it would look exactly like this

Private Sub fullList_Click()
Dim sSql as String
sSql = "SELECT * from [tblTablename]"
Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.RecordSource = sSql
Forms![frmSearchVisaMain]![frmSearchVisaSub].Form.Requery

would this be correct considering all the info?

thanks again for your help

 

[Remou]

The name of the subform is not needed, what you need is the name of the subform control. Also, it may help to use Me, if possible:
[tt]Me.[Name of Subform Control].Form.RecordSource = sSql[/tt]

You may wish to look at:
Forms: Refer to Form and Subform properties and controls

http://www.mvps.org/access/forms/frm0031.htm