recordset problems

[bigmac]

hello all, can you help please, I have the following code to try and make a recordset,
when it gets to [Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)] it keeps throwing up an error "runtime error 3061 to few parameters, expected 1"
what am I doing wrong and how do I rectify this please :banghead::banghead:

   Dim SQL As String
    Dim rs1 As DAO.Recordset
    Dim i As Integer
   
    
   
    'SQL statement to retrieve data from database
    SQL = "SELECT Table1.ID, Table1.id1, Table1.WeldNo, Table1.Material_Cast_Heat_No1, Table1.Item1, " _
    & "Table1.Material_Cast_Heat_No2, Table1.Item2, Table1.Schd_Thickness, Table1.Process, Table1.WPS, Table1.Dia, " _
    & "Table1.Material, Table1.WelderID, Table1.Weld_Date, Table1.Visual_Insp_By, Table1.Visual_Rep, Table1.DP_Result, " _
    & "Table1.DP_Report, Table1.MP_Result, Table1.MP_Report, Table1.RT_Result, Table1.RT_Report, Table1.UT_Result, " _
    & "Table1.UT_Report, Table1.Other_Res, Table1.PWHT, Table1.PWHT_Report_Graph, Table1.RT_Result_after_PWHT, " _
    & "Table1.UT_Result_after_PWHT, Table1.row_No, Table1.Lot_Heat_BatchNo_s, main.Client, main.Project, main.Drawing, " _
    & "main.[Drawing Link], main.[Report link]" _
    & "FROM main LEFT JOIN Table1 ON main.ID1 = Table1.id1 " _
    & "WHERE (((Table1.id1)=[Forms]![Mainform]![subform]![id1]));"
  Debug.Print SQL
    
     Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
      
   With rs1
   Debug.Print .RecordCount
   .Close
   End With

 

[Minty]

You have a space missing at the end of this line;

  & "main.[Drawing Link], main.[Report link[COLOR="Red"]]"[/COLOR] _

And you need to escape the reference to the Forms in this line - SQL at interpreter level does not understand what Forms! is referring to. So assuming you are running this from the form try this;

 & "WHERE (((Table1.id1)=[COLOR="red"] " & [Forms]![Mainform]![subform]![id1] & " ));"[/COLOR]

If you looked at the immediate window , your debug.print would show what was trying to be executed.

And have you really called your forms MainForm and Subform? that will get very confusing. Give them and your tables meaningful names without spaces in them.

 

[sneuberg]

If you have a reference in a query that you want to use for a recordset you need to add parameters and set it up differently. This web page describes the process.

 

[bigmac]

Minty , thank you for your reply, but this did not solve the issue ,
hello sneuberg , thank you for your reply also , but this is beyond my skills and don't know how to implement this to try it .

 

[sneuberg]

What are you trying to accomplish? Maybe there's a less complicated approach. For example if all you want is the number of records then you could probably do this with DCount instead of using a recordset.

 

[bigmac]

hello sneuberg, what I am trying to achieve is this , I want to export the contents of a query to excel under a set name and format the spreadsheet to a set format
, I know I can export via a macro but this wont let me format the spreadsheet ,i have found out how to TransferSpreadsheet to a spreadsheet with a name from a text box on my form , but again i don't know how to format the spreadsheet as i require .i have found this site http://accessjitsu.com/export-data-from-access-to-excel/ and if you look at videos 2 and 3 this is what i want to achieve i am trying to get the code in video 2 to run on my database but this is where i get the errors coming up.
thinking about it is there a way to use the tranferspreadsheet method to save the data into a excel sheet and then format the spreadsheet afterwards ???

 

[bigmac]

i hope this helps

 

[Cronk]

The syntax
[Forms]![Mainform]![subform]![id1]
is not the right way to reference a control on a sub form. Use

[Forms]![Mainform]![subform].Form![id1]

 

[sneuberg]

thinking about it is there a way to use the tranferspreadsheet method to save the data into a excel sheet and then format the spreadsheet afterwards ???

Yes, just open the exported spreadsheet using Excel Automation but learning Excel Automation is probably going to require more learning on your part than learning how to work with recordsets with references.

 

[sneuberg]

The syntax
[Forms]![Mainform]![subform]![id1]
is not the right way to reference a control on a sub form. Use

[Forms]![Mainform]![subform].Form![id1]

What's wrong about it? I tried both and they seem to be equivalent.