Using a Recordset to pass Values from One form to Another

[ProNetGroup]

Help!!! I have been trying to recall for a while now how to take data from a table, filter it based on the users preferences (build a sql statement based on the inputs of the form), and then run a recordset to filter out what is needed. I have the recordset working fine...i.e. I have it set to show a message box each time it finds a record, but have yet to figure out how to actually pass that value to another form to display the results. Can this be done through a recordset??? I can get it to display the last record it found, but not all of them. Here is the code I have so far (just the recordset part) and the strSQL is a sql statement that was built before the code and works properly:

DoCmd.Close acForm, "frmViewUtilities"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
MsgBox "No matches found, please try again."
Exit Sub
End If

Do Until rs.EOF
'MsgBox "Utility:" & rs!UtilityName & " County:" & rs!County
Forms![frmViewUtilitiesResults]![txtUtilityName] = rs!UtilityName
Forms![frmViewUtilitiesResults]![txtCounty] = rs!County
Forms![frmViewUtilitiesResults]![txtRegion] = rs!Region
rs.MoveNext
Loop

rs.Close

I have also tried to pass the values other ways, but nothing seems to work, like setting the recordset to the built sql statement.

Does anyone out there have any suggestions for me??? I would greatly appreciate them!!! Thanks!

 

[thornmastr]

How about,

me.recordsource = strSQL Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[ProNetGroup]

That didn't work when I tried it. It is possible that I was doing something wrong, but it didn't work. Here is the entire routine that I had:

Public Sub cmdSubmit_Click()

Dim strWhere As String
Dim strSQL As String
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb()

strSQL = "SELECT * FROM tblProperties WHERE 1=1"

'MsgBox strSQL

If Not IsNull(Me![txtUtilityName]) Then
strWhere = strWhere & " AND UtilityName = '" & txtUtilityName & "'"
'MsgBox strWhere
End If

If Not IsNull(Me![txtCounty]) Then
strWhere = strWhere & " AND County = '" & txtCounty & "'"
'MsgBox strWhere
End If

If Not IsNull(Me![txtRegion]) Then
strWhere = strWhere & " AND Region = '" & txtRegion & "'"
'MsgBox strWhere
End If

If Not IsNull(Me![txtCustomerPopulation]) Then
strWhere = strWhere & " AND CustomerPopulation = '" & txtCustomerPopulation & "'"
'MsgBox strWhere
End If

If Not IsNull(Me![txtOrg]) Then
strWhere = strWhere & " AND Org = '" & txtOrg & "'"
'MsgBox txtOrg
End If

If Not IsNull(Me![txtStreet]) Then
strWhere = strWhere & " AND Street = '" & txtStreet & "'"
'MsgBox strWhere
End If

If Not IsNull(Me![txtCity]) Then
strWhere = strWhere & " AND City = '" & txtCity & "'"
End If

If Not IsNull(Me![txtTitle]) Then
strWhere = strWhere & " AND Title = '" & txtTitle & "'"
'MsgBox txtTitle
End If

If Not IsNull(Me![cboOrderBy]) Then
If Me!cboOrderBy = "Utility Name" Then
strWhere = strWhere & " ORDER BY UtilityName"
'MsgBox strWhere
End If

If Me!cboOrderBy = "County" Then
strWhere = strWhere & " ORDER BY County"
'MsgBox strWhere
End If

If Me!cboOrderBy = "Region" Then
strWhere = strWhere & " ORDER BY Region"
'MsgBox strWhere
End If

If Me!cboOrderBy = "Population" Then
strWhere = strWhere & " ORDER BY CustomerPopulation"
'MsgBox strWhere
End If
End If

strSQL = strSQL & strWhere

MsgBox strSQL

DoCmd.OpenForm "frmViewUtilitiesResults"

Forms![frmViewUtilitiesResults].RecordSource = strSQL

'DoCmd.Close acForm, "frmViewUtilities"

'Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
'If rs.RecordCount = 0 Then
'MsgBox "No matches found, please try again."
'Exit Sub
'End If

'Do Until rs.EOF
'MsgBox "Utility:" & rs!UtilityName & " County:" & rs!County
'Forms![frmViewUtilitiesResults]![txtUtilityName] = rs!UtilityName
'Forms![frmViewUtilitiesResults]![txtCounty] = rs!County
'Forms![frmViewUtilitiesResults]![txtRegion] = rs!Region
'rs.MoveNext
'Loop

'rs.Close

End Sub

With the recordset part commented out, I tried to change the record source to reflect strSQL, but it really does nothing. All that it does is open the new form with no data in it.

Any more thoughts? Can you see what I might be doing wrong?
Thanks again!

 

[cmmrfrds]

Pass the sql string to the Form you are going to open.

strSQL = strSQL & strWhere
MsgBox strSQL
DoCmd.OpenForm "frmViewUtilitiesResults",,,,,,strSQL

In the OnOpen event of the Form.
Me.RecordSource = Me.OpenArgs

Open args will contain the sql string when the form opens.

 

[ProNetGroup]

That option does not work either. That's not to say that it doesn't work in general, but something that I am doing is just not right, because when it opens the form, there is nothing in it. It does say that there are 5 total records though (the correct number), it's just that all 5 records are blank. And my strSQL is pulling correctly as seen in the msgbox and when I tried using a recordset it would loop through and display the correct records.

DOES ANYONE KNOW WHAT I AM DOING WRONG??? I AM DESPARATE!!! =)

 

[cmmrfrds]

If you take the sql that is generated from the msgbox and paste it into the sql view in the query builder and run the query, do you see the results you expect. Check the field names.

Are the field names in the query bound to the Form?

 

[thornmastr]

Sorry I missed some of the previous dialog. You have a select clause with an extensive where clause as well. you want to end up with one SQL string. Once you have your two strings joined, set that to the recordsource of the form. Although yours is considerably more complex, it should be something along the lines of the following air code.

dim strsql as string

strsql = "select * from sometable where pk = 210"
me.recordsource = strsql
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[ProNetGroup]

The SQL clause is fine. I used to do it in college all the time when I first learned it (only within a year, i know very sad).
I think that my problem might be that the results form where I want to display the information is not bound. How do you make the form bound to something without having it automatically display all the records, like if it was bound to a table?

 

[CVigil]

Quick answer to last question by Eubanks: "How do you make the form bound to something without having it automatically display all the records, like if it was bound to a table?"

1)
Make sure you have a SQL statement or query or table that displays/contains only the record or records you want displayed. A filter (WHERE clause) is the usual way to do this.

2)
Set the form's RecordSource to the SQL statement or query or table you want displayed. A form may be bound to a table, a pre-compiled query, or a SQL statement which it will interpret and execute on the fly. If you use a table, but want only certain records shown, you can either: A) Use a SQL statement/query with a filter on the table instead, OR B) You can use the tablename as the RecordSource and set the Filter property equal to a WHERE clause without the word "WHERE" (a string); then in the form code, such as the Form_Open event handler, set the form's FilterOn property to True, which will immediately apply the filter. If you won't be changingyour filtering conditions on the fly during form run, I recommend A).

3)
Put controls on the form to show only the fields you want displayed.

4)
If you only want one record at a time displayed, rather than multiple records ("copies of the form&quot one after the other, set the DefaultView property (Format tab of Properties sheet) to "Single Form". Then the records are "all there", but the user would need to proceed from one record to the next to see the information in each.


I hope I have answered the question you actually intended to ask, Eubanks .

(P.S. It isn't sad that you only learned Access a year ago. There are many people who haven't at all, who haven;t any need to, and who have learned more recently. Remember, for *everyone* who learned Access before today, there was a time when they only learned it the day before.)

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[thornmastr]

Many of my applications deal with a high volume of data using SQL Server, MY SQL, or Ocelot. Even when I am using only JET, I hardly ever bind to a table because my personal feeling that any recordset of over 100 records is just too big.

When I create a form, I do bind to the table. This lets me design the form and not worry about any underlying recordset. The reason I do this is because at design time, it gives me access too all the fields on each record and I need that. So given a table, say containing 750,000 records +, it costs me nothing to “bind” to it at design time. Once the form is designed, I remove the assigned record source. What I now have is an unbound form mapped to data elements I know I will be displaying.

I build the record source, as a string, in the open event of the form and then assign it using:

Me.recordsource = strsql.

This allows me to use the same form for many record sets, and insures the size of the recordset is kept to a minimal volume.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[ProNetGroup]

Thanks everybody, I think I FINALLY figured it out!

And CVigil, thanks for the support, but what I meant was that it is sad that I can't remember things that I knew SO well about a year ago...I am trying to recall it and it is frustrating that I can't remember much!!!