[RESOLVED] Whats wrong with this code

[alexanderjames]

Hi

Please can someone tell me what is wrong with this code, it id for my data report that is connected to a DataEnviroment, i use an access database and i the report is working but i am trying to report only on customer names that match the current date, but i keep getting errors.

vb

Private Sub DataReport_Initialize()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim sSQL As String
Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\quick-res\data\data.mdb;"
MyConn.Open
sSQL = "SELECT [Customer Name] FROM Guests Where [Arrival Date] = #" & Format(Date, "yyyy-mm-dd") & "#"

MyRecSet.Open sSQL, adOpenStatic, adLockReadOnly

Set DataReport1.DataSource = MyRecSet

MyConn.Close
MyRecSet.Close


End Sub

Please can someone advise me.

Many thanks

[dee-u]

What is the description of the error are you getting and in what line does the error occurs?

[alexanderjames]

Hi Dee-U

MyRecSet.Open sSQL, adOpenStatic, adLockReadOnly

In that line i get "Arguments are of the wrong type or are out of acceptable range"
then if i take that line out then here

MyConn.Close
MyRecSet.Close

i get "Not allowed when object is Closed".

Many thanks

[dee-u]

In the 2nd argument it should be the connection object right?

[mabbas110]

the correct statement is

MyRecSet.Open sSQL,connectionSQL, adOpenStatic, adLockReadOnly

here connectionSQL must be the connection object which u r using for ur project

[alexanderjames]

Hi

Thank you for the advice, i am fimiliar with vb but very new to sql statements, i still get an error "Arguments are of the wrong type or are in conflict with one another"

Private Sub DataReport_Initialize()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim sSQL As String
Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\quick-res\data\data.mdb;"
MyConn.Open
sSQL = "SELECT [Customer Name] FROM Guests Where [Arrival Date] = #" & Format(Date, "yyyy-mm-dd") & "#"

MyRecSet.Open sSQL, connectionSQL, adOpenStatic, adLockReadOnly

Set DataReport1.DataSource = MyRecSet

MyConn.Close
MyRecSet.Close


End Sub

Would this be the correct way to pull a report only showing names that match the current date or is there another way.

Many thanks

[alexanderjames]

I was also wandering about something, the datareport is already connected to the dataEnvironment, and now i code the connection again, could this be causing the problem, i am really confused as to how i should do this.

Many thanks

[brucevde]

The variable ConnectionSQL has not been declared and its value has not been set. You open a Connection Object so use it.

MyRecSet.Open sSQL, MyConn, adOpenStatic, adLockReadOnly

Don't Close the Connection or the Recordset object. The DataReport is still using them.

I would remove the DataEnvironment connection from the DataReport.

[alexanderjames]

Hi BruceVde

Thank you for the reply, i have tried removing the dataEnvironment, i have fields in the report, so now when i run it i get an error "Cant find customer name field" as this was put there by the data environment. Its rather confusing

[brucevde]

Remove/Delete the report from the project and start over.

[alexanderjames]

Thanks BruceVde

I'll test it out this evening, and let you know

[alexanderjames]

Hi BruceVde

Thank you, you are right the DataEnvironment, causes problems. Once it was deleted the report works like a charm. Thank you once again.