[RESOLVED] Whats wrong with this code

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 Code:

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

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

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

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

the correct statement is

MyRecSet.Open sSQL,connectionSQL, adOpenStatic, adLockReadOnly

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

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

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

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.

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

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

Thanks BruceVde

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

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.