OpenRecordSet problems

R

rexmorgan

Registered User.
Local time
Yesterday, 17:38
Joined
May 31, 2010
Messages
47
Good morning everyone,

I have a bit of code that I have recycled on a few buttons that all work fine. However this one bit of code associated with this particular button I cannot seem to get to work properly. As I said I have used code nearly identical to this on other buttons that works just fine. I seem to be missing something very obvious but for the life of me I cannot seem to figure out the problem. Below is the code that I am having trouble with. Specifically the line

'Set MyRS = MyDB.OpenRecordSet(QryOrTblDef)'

Code: 
Private Sub cmdPlotAllInCounty_Click()
    
'This creates a text file with a KML extension that Google Earth can read
'Created by Rex Morgan 6/1/2010 with help from World Access Forums


'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "MyQuery"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML help/QryKmlcboCounty.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>SpottersInCounty.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <description><![CDATA[]]></description>"
    
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "]]></description>"
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(1) & " " & MyRS.Fields(2) & "</name>"
            Print #iFile, strText
            
        
            
        Print #iFile, "<Point>"
        strText = "<coordinates>" & MyRS.Fields(20) & "," & MyRS.Fields(19) & "</coordinates>"
        Print #iFile, strText
        Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    'Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML help\QryKmlcboCounty.kml", vbNormalFocus)

    
End Sub

The error that I get is-
Run-time error '3061':
Too few parameters. Expected 1.

Any help is greatly appreciated. Thank you all for having a look at this.
 
Shouldn't it be

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
 
Thanks for the reply. I had already tried adding the DAO but still without any luck. I am just not sure why this does not work. It works on several other buttons. The only difference between this one and the other is that this is using a combo box to determine the Query and the others simply use pre-built queries. I do not know why that should make a difference. The query itself works just fine. Just for some unknown reason the 'Set MyRS.OpenRecordset(QryOrTblDef)' does not want to work out. The funny thing is that it says it is missing parameters. But clearly the parameter is (QryOrTblDef).
 
rexmorgan said:
The only difference between this one and the other is that this is using a combo box to determine the Query and the others simply use pre-built queries.
Click to expand...

In your code you set QryOrTblDef = "MyQuery", do you have a stored query called MyQuery? I'm not sure that a saved parameterquery works in VBA.
Why not build a SQL string in your code based on the combobox like:

Code: 
QryOrTblDef = "Select * from [COLOR="darkred"]YourTableNameHere [/COLOR]where [COLOR="darkred"]YourControlFieldNameHere [/COLOR]= [COLOR="SeaGreen"]'[/COLOR]" & me.[COLOR="DarkRed"]YourComboControlNameHere[/COLOR] & "[COLOR="SeaGreen"]'[/COLOR];"
Cange the red parts to your situation the green single quotes ' are only required if the bound column of te combobox is text.
 
Thanks for the reply Peter. The query is a dynamic query that is created in another piece of code. That code calls the above code to create the kml file based on the dynamic query called 'MyQuery'.

The query itself runs just fine from the button click of cmdPlotQrycboCounty. When this runs the dialog box asking if 'Would you like to map this query in Google Earth?' appears.

Upon clicking 'Yes' it should call cmdPlottAllInCounty_Click() event and then create the kml file and then launch Google Earth with the plots.
 
Have you tested your code to find exactly where the error occurs. In the query or with the data the query returns ?
Add MsgBox's to the code to verify where the error is.
 
Just an wild untested guess. Does the query make references to controls on a form? If so that might be the problem.

The query itself is planned by Access which reads the controls and inserts their values before passing it to the database engine to be run.

I suspect OpenRecordset does not understand the references to the form because the query is passed directly to the database engine.

I am basing this on an analogy to the difference between DoCmd.RunSQL which supports form references and CurrentDb.Execute which only understands tables and simple queries.
 
@PNGBill- The the query runs just fine. The data displayed in the query is also just as expected. The problems lies somewhere when I am trying to create the kml file that Google Earth will read. For some reason the line in the code 'Set MyRS = MyDB.OpenRecordset(QryOrTblDef)'

@Galaxiom- The query does make references to controls on the form. The query however is working just fine. As far as the OpenRecordset control the exact same line works just fine for other buttons.

I am going to include some code below that is working for another button, which is nearly identical to the code that is not working for me.

Code: 
Private Sub Command89_Click()
'This creates a text file with a KML extension that Google Earth can read
'Created by Rex Morgan 6/1/2010 with help from World Access Forums
'This is a hidden command button and is launced from the 'visible' Search button

'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "MyQuery"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML help/QryKmlLastName.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    'Print #iFile, " <name>Daniels County</name>"
    'Print #iFile, " <open>1</open>"
    'Print #iFile, " <Snippet maxLines=""2"">Whatever</Snippet>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "]]></description>"
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(1) & " " & MyRS.Fields(2) & "</name>"
            Print #iFile, strText
            
        
            
        Print #iFile, "<Point>"
        strText = "<coordinates>" & MyRS.Fields(20) & "," & MyRS.Fields(19) & "</coordinates>"
        Print #iFile, strText
        Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML help\QryKmlLastName.kml", vbNormalFocus)

End Sub

As I said the code just above is working just fine which is nearly identical to the first bit of code I posted that is not working.
 
What about using the code that does work and confirm it will work on that form.

Then edit the code, in stages, if possible, and see where the error throws.

May not be practical but if the code is nearly the same it may be a quick solution.
 
I have confirmed that the issue I outlined above is indeed the case. OpenRecordset cannot use a query that refers to a control on a form.

The query can be opened in Access because it is being processed by the Application before being sent to the database engine with the value from the control inserted.

The detail of the commands used with the usually omitted default objects included will make it clear.

The full syntax of DoCmd.OpenQuery is actually:
Code: 
Application.DoCmd.OpenQuery

The important point is "Application".

The full reference of CurrentDb is actually:
Code: 
DBEngine.Workspaces(0).Databases(0)

This refers directly to the database engine (JET/ACE). The engine has no knowledge of the form and hence throws an error for a missing parameter when it encounters the form reference.

To open a recordset you need to either add the parameter to the querydef or feed the complete SQL as the argument.
 
BTW: Why do you include a hidden button for the code just to run it from a visible button?
 
@PNGBill- The code that does work is on the same form. So I am not sure what the problem is. I may still go through it again and see if I can find a typo or something.
 
@Galaxiom- The reason for the code referring to a hidden button is that having the button visible does little to no good that I can determine. The code was written to be modular and works well for several other buttons on the same form. Perhaps if I include the code for both the visible and the hidden button this may become more clear. Or maybe I am breaking up this code for no reason at all. It just seemed to me to make more sense to have it split up this way. I am using nearly identical code on 3 other buttons. So here goes first the code (from one of the ones that work) for the visible button that the user is supposed to click on.

Code: 
Private Sub cmdFirstNameSearch_Click()
    
    'Written by Rex Morgan with help from Access World Forums
    'this version as of 8-21-2010
    'Declaring the variables
    Dim db As DAO.Database
    Dim QD As DAO.QueryDef
    Dim where As Variant

    'assigning value to db
    Set db = CurrentDb

    'Delete existing dynamic query, trap error if it does not exist.
    On Error Resume Next
    'deleting the last version of "MyQuery"
    db.QueryDefs.Delete ("MyQuery")
    On Error GoTo 0

    ' ensuring the variable where contains no value or garbage left over
    where = Null

    'Note single quotes surrounding text fields [txtFirstName]
    'Note NO Single quotes surrounding Numeric field [Employee ID]
    ' [txtLastName] is the name of the text box on the frmReports form
'     [LastName] is the field name of the Last Name in the Spotter table.
'    This is for use in the SQL ->
'    SELECT * FROM Spotter WHERE (((Spotter.[LastName])='where variable name'));
'    The way the structure is the [LastName] is dyanmic in that it can be swapped out
'    using the SQL statement above just substitute [LastName] with some other field
'    name from the database.  Such as [FirstName] or [City] or [State] or whatever.
'
    If Not IsNull(txtFirstName.Value) Then
        If Len(where) > 0 Then
            where = where & (" AND [FirstName]= '" & Me![txtFirstName] & "' ")
        Else
            where = where & (" [FirstName]= '" & Me![txtFirstName] & "' ")
        End If
    End If


    'assigning value to QD
    Set QD = db.CreateQueryDef("MyQuery", "SELECT * FROM Spotter WHERE " & where & ";")

    'executing the command to Open a Query titled "MyQuery"
    DoCmd.OpenQuery "MyQuery", acViewNormal

    'setting up the message box asking whether or not you want to map the results
    kmlAnswer = MsgBox("Would you like to map this query in Google Earth?", vbApplicationModal + vbYesNo + vbDefaultButton2, "Map in Google Earth")
    
    If kmlAnswer = vbYes Then
        Command89_Click
    End If

    'executing the command to Close the form
    DoCmd.Close acForm, Me.Name

End Sub

Now for the other piece of code that this one calls (Command89_Click)

Code: 
Private Sub Command89_Click()
'This creates a text file with a KML extension that Google Earth can read
'Created by Rex Morgan 6/1/2010 with help from World Access Forums
'This is a hidden command button and is launced from the 'visible' Search button

'export data to text file

Dim MyDB As Database
Dim MyRS As Recordset
Dim fld As Field
Dim strText As String
Dim MyTableName As String
Dim QryOrTblDef As String
Dim iFile As Integer
    QryOrTblDef = "MyQuery"
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    iFile = FreeFile
    
    Open "//ggw-s-win/home/rex.morgan/My Documents/KML help/QryKmlLastName.kml" For Output Shared As #iFile
    
    Print #iFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #iFile, "<kml xmlns=""http://earth.google.com/kml/2.1"">"
    Print #iFile, "<Document>"
    Print #iFile, " <name>KMLTest.kml</name>"
    Print #iFile, " <Folder>"
    Print #iFile, " <name>Spotters</name>"
    Print #iFile, " <open>1</open>"
    Print #iFile, " <Folder>"
    Print #iFile, " <description><![CDATA[]]></description>"
    With MyRS
        Do Until .EOF
            Print #iFile, " <Placemark>"
            strText = " <description><![CDATA[Name: " & MyRS.Fields(1) & " " & MyRS.Fields(2) & "<br>City: " & MyRS.Fields(6) & "]]></description>"
            Print #iFile, strText
            
            strText = " <name>" & MyRS.Fields(1) & " " & MyRS.Fields(2) & "</name>"
            Print #iFile, strText
            
        
            
        Print #iFile, "<Point>"
        strText = "<coordinates>" & MyRS.Fields(20) & "," & MyRS.Fields(19) & "</coordinates>"
        Print #iFile, strText
        Print #iFile, " </Point>"
            Print #iFile, " </Placemark>"
            .MoveNext
        Loop
    End With
    Print #iFile, " </Folder>"
    Print #iFile, " </Folder>"
    Print #iFile, " </Document>"
    Print #iFile, "</kml>"
    Close #iFile
    MyRS.Close
    Set MyRS = Nothing
    Set MyDB = Nothing
    Call Shell("explorer.exe " & "z:\My Documents\KML help\QryKmlLastName.kml", vbNormalFocus)

End Sub

I hope I haven't rambled on too much. Just I am a bit under the weather right now and trying to get this darn thing working. Thanks again for all the help so far.
 
I noticed on your first post you refer to Recycling Code. I know this doesn't make sence but sometimes Command Buttons can get corrupted and the issue never identified.

Maybe some corruption came about with a copy and past??

Try a new form / command button with just the vba code pasted ?
 
rexmorgan said:
The reason for the code referring to a hidden button is that having the button visible does little to no good that I can determine. The code was written to be modular and works well for several other buttons on the same form.
Click to expand...

In that case you would not use an event procedure for the code but a simple sub or function. The other button event procedures would simply call that function or run the sub. There is no reason to have the code in a button click event procedure since the user can't click an invisible button.
 
There is no reason to be creating a query as the basis of the recordset. Simply enter the SQL directly as the argument for the OpenRecordset command.

To determine why the OpenRecordset fails check the complete SQL the procedure has created by using Debug.Print to show it in the Immediate Window or view the variable in the Locals Window at a break point.
 
You must log in or register to reply here.

Similar threads

MsAccessNL
Outlook import csv error
Replies
5
Views
512
MsAccessNL
MsAccessNL
P
Solved Adding blank rows to Grouped Report
Replies
14
Views
1,217
arnelgp
arnelgp
F
Solved AddNew method with field from variable (array)
Replies
3
Views
251
Fodo
F
essaytee
Why messagebox appears then closes on successful run of DoCmd.OutputTo....pdf
Replies
5
Views
334
Mark_
M
C
Sendkeys not working anymore
Replies
7
Views
1,807
NauticalGent
NauticalGent

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom