Open a form using a VBA Sql statement

[Deleted member 8621]

I have the following code:

Dim rs As DAO.Recordset
Dim vrmo As Integer
Dim mdl As String
Dim snum As String
Dim strSQL As String

mdl = Forms!frmAdminEntry!Text45
snum = Forms!frmAdminEntry!Text47
vrmo = Forms!frmAdminEntry!Text26

strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((Model)='" & mdl & "') AND ((SN)= '" & snum & "')) "
strSQL = strSQL & "ORDER BY Date DESC;"

Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenForm "frmViewMeterData", , strSQL

Do Until rs.EOF
Debug.Print rs!SN
rs.MoveNext
Loop

My Debug.Print rs!SN shows the correct # of records in the imediate window.
Problem
My DoCmd.OpenForm returns all the records the WHERE clause refers to without paying any attention to the TOP clause.

Where and how do I need to open the form. It doesn't seem to be here???

thanks
matthew (the posting king

 

[cogent1]

You're a comma short in the DOCmd.OpenForm, methinks.

Also, you can use the line continuation character instead of constantly refeferring to strSQL, thus:


strSQL= "SELECT......" _
& "FROM...." _
& "WHERE...." _ etc

 

[Deleted member 8621]

I'm not sure exactly what your saying. I tried moving the strSQL one comma further into the "where condition" section but that produced an error saying that my sql statement was incorrect. With the statement where it is now (same place as my original post) it runs the query but won't do my TOP & vrmo records part, but in my imediate window in VBA it returns the correct # of records...???

thanks
me

 

[cogent1]

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

wherecondition A string expression that's a valid SQL WHERE clause without the word WHERE.


An SQL string must go after the third comma, not the second.

Your form is opening with its inherent recordsource and ignoring the argument because it isn't the name of a query. It won't work with the third comma because your SQL contains the word WHERE.

.What is the recordsource of your form? . Please post. If you want it to open using your SQL Statement you have to change its recordsource property to the SQL statement first and then open it without any additional arguments

 

[Deleted member 8621]

I am looking at microsofts help file for Access using "OpenFrom" as my search and I am seeing the same thing that you are for the Where Condition. You are totally correct. If all I needed to do was match my returned records with my selected Model and Serial number I could use just the where clause but I need it to select the same model and serial number and then only the top [n].
The definition I'm seeing for "Filter Name" is:
FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

So I Figure that this will evaluate the whole sql statement and not just the where clasue. If it weren't for me needing the TOP [n] using the where condition would work great.

The from "frmViewMeterData" is based on the table "tblMeterData".

thanks
matthew

 

[cogent1]

Yes, that's why you need to replace the existing recordsource (your table) with your complete SQL by putting a statement like Me.Recordsource=strSQL in the code before you open the form.

Me may need to be replaced by the full form reference if this is in a standard module

 

[Deleted member 8621]

I guess I don't understand what you are saying.

I have opened the form and taken out the recordsource, I have used:
strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((Model)='" & mdl & "') AND ((SN)= '" & snum & "')) "
strSQL = strSQL & "ORDER BY Date DESC;"

Set rs = CurrentDb.OpenRecordset(strSQL)
Forms![frmAdminViewMtrs].RecordSource = strSQL
DoCmd.OpenForm "frmAdminViewMtrs"

It tells me that it can't find the form frmAdminViewMtrs. When I rem out the Forms!frmAdminViewMtrs.Recordssource line it opens the form. So I know I'm spelling the form correctly.

I'm not certain what I'm doing wrong.

help. THis is my first experience with doing a sql statement in VBA so I'm probably totally missing something.

thanks for sticking with me
matthew

 

[cogent1]

It's because the form must be open, but hidden, when that line is executed. Shift the OpenForm line above the statement. Use the Hidden windowmode to open it. then use Forms!myForm.visible=true to display it

 

[Deleted member 8621]

Finally

Thank you for sticking with me. The following code works as you said it would:

Public Function modSqlViewMtr()
On Error GoTo Err_modSqlViewMtr

Dim rs As DAO.Recordset
Dim vrmo As Integer
Dim mdl As String
Dim snum As String
Dim strSQL As String

If IsNull(Forms!frmAdminEntry!Text45) Then
MsgBox "No Hardware Selected!", 64, "No Selection!"
GoTo Exit_modSqlViewMtr
End If

mdl = Forms!frmAdminEntry!Text45
snum = Forms!frmAdminEntry!Text47
vrmo = Forms!frmAdminEntry!Text26



strSQL = "SELECT TOP " & vrmo & " Model, SN, Date, [1stMeter], [1stMtrRollover], "
strSQL = strSQL & "[2ndMeter], [2ndMtrRollover] "
strSQL = strSQL & "FROM tblMeterData "
strSQL = strSQL & "WHERE (((Model)='" & mdl & "') AND ((SN)= '" & snum & "')) "
strSQL = strSQL & "ORDER BY Date DESC;"

Set rs = CurrentDb.OpenRecordset(strSQL)
DoCmd.OpenForm "frmAdminViewMtrs", , , , , acHidden
Forms!frmAdminViewMtrs.RecordSource = strSQL
Forms!frmAdminViewMtrs.Visible = True

'Do Until rs.EOF
'Debug.Print rs!SN
'rs.MoveNext
'Loop

Exit_modSqlViewMtr:
Exit Function
Err_modSqlViewMtr:
MsgBox Err.Description
Resume Exit_modSqlViewMtr
End Function


I don't understand why opening a form with a filtername value isn't the same as setting the record source? I would think they would have the same effect. Why would one use the filtername argument?

THanks for all your help cogent1
matthew

 

[cogent1]

A filter is really a query or a subquery. But in this context it's not the same as an SQL statement because it's saved and appears in the database window. Say you have a table and you want to frequently look at Customers from Alabama, Baltimore and China. If you don't want to enter parameters each time the query is run, you save the query 3 times with the different criteria. That's what "filtername" refers to:- one of these queries.