Unbound search form, trouble in VB coding 3

[underpaidadmin]

I have a unbound form I am creating to do searches on multiple fields in multiple tables.

I have the SQL string stored in a string variable called:

strSQL

Problem is I don't know how the steps to make that string actually open a form with the results of the query. I also have a record set created using the same string but it was hijacked code and not entirely sure why I have it there.

 

[gmmastros]

This is a very generic question that could have multiple solutions, each none better than any other.

Here's how I would do this...

Create a form (call it frmShowResults). In the form, have a public subroutine...

Public Sub ShowResults(byval RS as ADODB.Recordset)
[green]' put code to show the data[/green]
Call Me.Show(vbModal)
End Sub

Then, from the other form, you can populate your recordset from the database. Call the ShowResults method of the frmShowResults form.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[underpaidadmin]

Wow that was incredibly fast response. Thank you. I forgot to say some things. The code I used to generate the SQL string is a button that I would also like to open and populate the subform using the string it created. From what I understand of the code you wrote I would have to open the other form, frmShowResults, manually or with some other bit of code?

Just for a small reference I will post a little snippet of the code:

Private Sub btnSearch_Click()
Dim dbf As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

strWhere = "WHERE "
strSQL = "SELECT tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName,tblNames.Company , tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN FROM (tblCases LEFT JOIN tblNames ON tblCases.CaseNum = tblNames.CaseNum) LEFT JOIN tblTracers ON tblCases.CaseNum = tblTracers.CaseNum "

If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCases.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
If IsDate(Me.txtOpenDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate >= " & "#" & Me.txtOpenDateFrom & "#"
End If
'----------
If IsDate(Me.txtOpenedDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate <= " & "#" & Me.txtOpenedDateTo & "#"
End If
If Len(strWhere) < 8 Then
strWhere = ""
End If

strWhere = strWhere & " GROUP BY tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName, tblNames.Company, tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN"
strSQL = strSQL & strWhere & ";"

'2 Debug lines below, delete when working
Debug.Print strSQL
MsgBox strSQL

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSQL)

Hope that explains it a little better.

 

[gmmastros]

You can use the same button to open the new form.

Ex....

[green]' lines removed from code to 
' make this post shorter[/green]
'2 Debug lines below, delete when working
Debug.Print strSQL
MsgBox strSQL

Set dbf = CurrentDb
Set rst = dbf.OpenRecordset(strSQL)

[red]Call frmShowResults.ShowResults(rst)[/red]

So, the button will create the SQL String, populate a recordset from the database, and open the results form. All this is done from within the button click event.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[underpaidadmin]

Please bear with me I am new to this. As such, I think I didn't properly call the resulting fields, which crashed Access. I created a form for results prior to any of this, called sbfBrowse with the controls I would like to display for the user to click on. Having said that I was hoping you could direct me to somewhere where I could see how to put the proper information from the results into the controls on the form. I imagine it must be pretty basic but I never have seen or done it before and not too certain what I should be looking for.

Thank you so much for all the help and patience.

 

[gmmastros]

I have a couple suggestions...

You have this....

If Not IsNull(Me.txtCaseNum) Then

I assume that txtCaseNum is a text box. Since you don't specify the property of the text box, it's default property is used (which happens to be the .text property). However, the text property cannot ever be null, so this is probably not doing what you hoped. Instead, you should have...

If Trim(txtCaseNum.Text) <> "" Then


I see that you are doing a debug.print on the sql string. I suggest you open a sql window in access, copy/paste the sql from the immediate window in VB to the SQL window in Access. Run the query in there. You will get a better error message (if there is any).

Since you don't say what type of control you are using, I can't advise on how to put the data in to the control.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[underpaidadmin]

Thank you, I am sorry for my naivety. I got an error,

Can't reference a property or method for a control unless the control has the focus.

Although it seemed to work fine before, the string it created didn't take information from any blank field, most of which are text, except 4 date controls.

I use Debug.Print to do exactly what you said. The strSQL it outputs currently picks up the proper records for any information any of us has thought up to enter into the form.

I reverted back the "If Not IsNull..." since it is working for now and added this new line at the end:

Call sbfBrowse.ShowResults(rst)

It now generates an error message saying "Object Required". I don't know what or how it is supposed to call an object. I did create a procedure in my premade form, sbfBrowse:

Public Sub ShowResults(ByVal RS As ADODB.Recordset)
  ' put code to show the data
  Call Me.Show(vbModal)
End Sub

I am not sure what you meant by code to show the data though since the form already has controls on it which relate to the tables this SQL query should be pulling data from I just don't know how to populate those controls with the returned data.

 

[RoyVidar]

By the errormessages you get, I'm assuming you are working whithin Access, not from VB. This forum is for VB and Databases -> for Access quesions, rather use one of the seven dedicated Access fora on the site (forum search at the top).

If this is Access, and not VB, then for this question - you could perhaps pass the string as openargs, but I'm not sure how much it can take (length of string, that is). Oh, why not just try - at the end of your code

[tt]docmd.openform "frmMySearchDisplayThingie",,,,,,strSql[/tt]

then in the on open event of the search form

[tt]if len(me.openargs) then
me.recordsource = me.openargs
end if[/tt]

If the complete sql string is too long, then build the main sql string within the form where you wish to display the results, and just the where part where you build it currently, then pass only the where clause, and concatinate and assign to the recordsource in the display form.

Roy-Vidar

 

[BobRodes]

<If Not IsNull(Me.txtCaseNum) Then

<I reverted back the "If Not IsNull..."

If the code is working with this in it, you can safely remove it completely, since it's always true.

Bob