Code problems

[Maria]

Hi there!

I have some problems with moving data from form to
exceldocument. I have the following code, but I get the
errormessage "To few parameters, expected 2." Does anyone
of you know what's wrong? Please help me if you can!!!

Private Sub ExcelKnp_Click()
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add

ExcelBook.Worksheets(1).Cells(1, 1).Font.Size = 10
ExcelBook.Worksheets(1).Cells(1, 1).Font.Bold = True
ExcelBook.Worksheets(1).Cells(1, 1).Value = "e2upnr"

ExcelBook.Worksheets(1).Cells(1, 2).Font.Size = 10
ExcelBook.Worksheets(1).Cells(1, 2).Font.Bold = True
ExcelBook.Worksheets(1).Cells(1, 2).Value = "e3vgnr"

Set oRS = CreateObject("ADODB.RecordSet")
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "DRIVER={Microsoft Access
Driver (*.mdb)}; DBQ=C:\M\MM.mdb"
bSucess = oConn.Open

Dim q
q = " SELECT Forms!AktUpn!e2upnr, Forms!AktUpn!
AktUpnUnd!e3vgnr"

Dim i
i = 2

Set oRS = oConn.Execute(q)
Do While Not oRS.EOF

ExcelBook.Worksheets(1).Cells(i, 1).Value = oRS
("e2upnr")
ExcelBook.Worksheets(1).Cells(i, 2).Value = oRS
("e3vgnr")

oRS.MoveNext
i = i + 1
Loop

Set oRS = Nothing
bSucsess = oConn.Close
Set oConn = Nothing

ExcelBook.SaveAs "C:/Majsan.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing
End Sub

Cheers!

 

[Emilia Maxim]

I have some problems with moving data from form to
exceldocument. I have the following code, but I get the
errormessage "To few parameters, expected 2." Does anyone
of you know what's wrong? Please help me if you can!!!

Maria,

you don't say, but I suppose the error comes from this line:

Set oRS = oConn.Execute(q)

I'd say, the SELECT statement is not correct. You should have
displayed it either by setting a breakpoint or with a MsgBox
statement.

q = " SELECT Forms!AktUpn!e2upnr, Forms!AktUpn!
AktUpnUnd!e3vgnr"

First of all from which table should the data be selected from? You
have to write this in the statement, Access cannot guess it:

Then, I'm not sure what do you want to select. As it is now, you're
telling Access to select from the database two fields which are on a
form. This is not correct, in an SQL statement you can only select
(and load in a recordset) _table_ fields. Maybe you want to use the
form controls as a criteria?

If so, the statement would look like this:

q = "SELECT MyTableField1, MyTableField2 FROM MyTable" _
& " WHERE SomeField =" & Forms!AktUpn!e2upnr _
& " AND SomeOtherField =" & Forms!AktUpn!AktUpnUnd!e3vgnr

Or contain those two controls the needed field names?

Please look up Help item on SQL SELECT statement, as well as the
examples. You can also experiment by creating a query in design view,
then switching to SQL view and taking a good look at the string.

And let me give you another advice: You may want to insert in every
module's header the statement:
Option Explicit

and explicitly use a Dim statement for every variable you use in a
procedure. Otherwise every mistyped variable will be handled by Access
as a new one, and the resulting errors are among the most difficult to
find. (There is also a global option for new modules under Tools
/Options, either in the DB window for A97 or in the VBE for A2000.)

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de