Search Results in to display in subform

[Hayton]

Hi everyone I have a form (frmSearch) which has a subform fsubInfoPull. On activating the Get Records command button a query (qryInfoPull) shows various records.

The code that does is detailed below
Set qryDef = dbNm.QueryDefs("qryInfoPull&quot
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
DoCmd.OpenQuery "qryInfoPull", acViewNormal

I would like to show these records in the subform (fsubInfoPull) rather than in a query. I tried using the code below to substitute the DoCmd code above. However this code does not work.

DoCmd.OpenForm "fsubInfoPull", acFormDS, , , acFormReadOnly, acWindowNormal

Any suggestions wpould be appreciated




Hayton McGregor

 

[Eupher]

Try this if you don't need to use the querydef elswhere...

Me!fsubInfoPull.Form.RecordSource = strSQL & " " & strWhere & "" & strOrder

Or if you want to use the querydef as the record source...

Set qryDef = dbNm.QueryDefs("qryInfoPull")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
Me!fsubInfoPull.Form.RecordSource = "qryInfoPull"

This assumes that the subform is unbound, that the field names in your query are static, and that you've specified the query field names in the control source for the corresponding fields on the subform.

Ken S.

 

[Hayton]

Thanks Ken, I used the second option and it works like dream.

Hayton McGregor

 

[djbigben]

I have the same kind of problem although with my DB it opens a form rather than a query.

' open results form if records found, otherwise return false
If lngCount = 0 Then
ExecuteSearch = False
Else
ExecuteSearch = True

DoCmd.ShowAllRecords
DoCmd.OpenForm "Main_Subform1", , , strSQLWhere

'Me!Main_Subform1.Form.RecordSource = myrecordsource
End If

Does anyone have any ideas?

 

[Eupher]

djbigben, where is this code executing? I gather you want the subform to open, displayed within the subform control on the main form, not in its own form window? If so, DoCmd.OpenForm is not what you want. You want to change the source object of the subform control, i.e.

Me!subformControlName.SourceObject = "Main_Subform1"

...where "subformControlName" is the name of the subform field on your main form.

Post back with more details if I have misunderstood your intent.

Ken S.

 

[djbigben]

This is the code under the event click function of my command button "cmdsearch". The main form is called "Form1" and the subform displaying the records is called "Main_Subform1" The text box which the user enters text is named "txtsearch".

Private Sub cmdsearch_Click()

On Error GoTo Err_cmdsearch_Click

Dim strMsg As String
Dim intBoxStyle As Integer

intBoxStyle = vbOKOnly + vbInformation

If txtsearch = "" Then
strMsg = "Please enter search criteria before clicking 'Search'."

MsgBox strMsg, intBoxStyle, "Missing Information"

Else

If Not ExecuteSearch(txtsearch) Then

strMsg = "Your search either returned no results or created an error."

MsgBox strMsg, intBoxStyle, "Search Failed"

End If

End If

Exit_cmdsearch_Click:

Exit Sub

Err_cmdsearch_Click:

MsgBox Err.Description
Resume Exit_cmdsearch_Click
End Sub

Private Function ExecuteSearch(strCriteria As String) As Boolean

On Error GoTo Err_ExecuteSearch

Dim strSQL As String
Dim strSQLWhere As String
'Dim myrecordsource As String
Dim lngCount As Long

strSQL = "SELECT * FROM main"

strSQLWhere = "Software_Title IN(SELECT Software_Title " _
& "FROM main WHERE " _
& "Software_Title LIKE '*" & strCriteria & "*'" _
& "OR Version LIKE '*" & strCriteria & "*'" _
& "OR Location LIKE '*" & strCriteria & "*')"

strSQL = strSQL & " WHERE " & strSQLWhere

' find out if there are any matching records
lngCount = FindRecord(strSQL)

If lngCount = 0 Then
ExecuteSearch = False
Else
ExecuteSearch = True
'myrecordsource = strSQL & ExecuteSearch & " ORDER BY [Software_Title]"
DoCmd.ShowAllRecords
DoCmd.OpenForm "Main_Subform1", , , strSQLWhere

'Me!Main_Subform1.Form.RecordSource = myrecordsource
End If


Exit_ExecuteSearch:

Exit Function

Err_ExecuteSearch:

MsgBox Err.Description
ExecuteSearch = False
Resume Exit_ExecuteSearch

End Function


Public Function FindRecord(ByVal strSearchString As String) As Long

On Error GoTo Err_FindRecord

Dim dbSearch As DAO.Database
Dim rsSearch As DAO.Recordset

Set dbSearch = DBEngine.Workspaces(0).Databases(0)
Set rsSearch = dbSearch.OpenRecordset(strSearchString, dbOpenSnapshot)

With rsSearch
If (.BOF And .EOF) Then
FindRecord = 0
Else
.MoveLast
FindRecord = .RecordCount
End If
End With

Exit_FindRecord:

rsSearch.Close
dbSearch.Close

Exit Function

Err_FindRecord:

FindRecord = 0
Resume Exit_FindRecord

End Function

Hope this helps!

 

[djbigben]

can noone help? :O(

 

[KenReay]

Hi

There is not much to learn, in the DoCmd.Openform you put a string conatining the data you wish to pass to the 'opened' form

In the on openEvent of the 'opened' form you retrieve the sring by refering to me.OpenArgs

depending on the complexity of the string you either use it directly, or parse it using Left(), Right(), Mid(), Instr() type functions to extract the various data elements

Something like:

DoCmd ....,"XXXXYYYY"

in the on open event of the opened form

DIm p1 as String
Dim p2 as String

P1 = Left(Me.OpenArgs,4)
P2 = Mid(Me.OpenArgs,5,4)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[djbigben]

I kind of get what you mean but i am not trying to open a separate form. Just display all the results from the search function in the subform (Main_Subform1) on the main form (Form1) rather than opening up a new form with the results in.

I tried putting

Private Sub Form_Open(Cancel As Integer)
Dim strSQLWhere As String
strSQLWhere = Me.OpenArgs
End If
End Sub

into the open event of the subform but nothing happens

 

[djbigben]

Do i need to set the Subform's recordsource as something?

 

[Eupher]

Sorry for being dense, but I'm still confused about where you want the search results displayed: in the main form, or the subform?

Ken S.

 

[djbigben]

the search reults need to be displayed in the subform which is on the main form rather than after hitting the command button a new form with the results opening up. on the main form (Form1) i have a couple of combo boxes which work fine with the subform (Main_Subform1) and an unbound text box (txtsearch) with a command button (cmdsearch). The subform is displayed below them on the form.

 

[KenReay]

Hi

The subform recordsource is the query qryInfoPull isn't it?

Assuming yes, in the show results button on_click event you should just need MySubFormControlname.requery where MySubFormControlName is the name of the subform control containing the sub form in which the results are to be displayed

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[djbigben]

I am not using a query, just the FindRecord function as shown above.

the subform Main_Subform1 recordsource is:

SELECT [Main].[ID], [Main].[Software_Title], [Main].[Category_ID], [Main].[Company_ID], [Main].[Version], [Main].[Location], [Main].[Quantity] FROM Main WHERE ((([Main].[Category_ID]) Like IIf(nz(Len([Forms]![Form1]![combocat]),0)>0,[Forms]![Form1]![combocat],"*&quot) And (([Main].[Company_ID]) Like IIf(nz(Len([Forms]![Form1]![combocomp]),0)>0,[Forms]![Form1]![combocomp],"*&quot)) ORDER BY [Main].[Software_Title];

 

[Eupher]

It looks like you tried to change the subform's recordsource and it didn't work, and so you commented it out. Unless I'm missing something, I believe it didn't work because of the "& ExecuteSearch &" clause in the "myrecordsource" variable. That's going to insert a -1 value into your SQL string, and I don't see how that can do anything in this context except hose it up. So give this a try:

If lngCount = 0 Then
    ExecuteSearch = False
    Else
        ExecuteSearch = True
        myrecordsource = strSQL & " ORDER BY [Software_Title]"
        DoCmd.ShowAllRecords
        Me!Main_Subform1.Form.RecordSource = myrecordsource
End If

HTH...

Ken S.

 

[djbigben]

ive posted the file if anyone would like to look at it
Go to search for a cd from the main menu (ignore the reset button for now)

http://homepage.ntlworld.com/l337.h4x0r/benjie/CD Directory.zip

 

[djbigben]

I did what you said Eupher and added the Dim myrecordsource As String in at the start too but after testing it, all the boxes appear with #Name? in them??

 

[djbigben]

My mistake i had the
myrecordsource = strSQL & " ORDER BY [Software_Title]"
line still commented out! doh!

Thanks a lot for your help Ken