Hi,

I have a second problem I have to solve too and I can't seem to get the answer...

I have a form with 5 text boxes called DateZone1, Datezone2,... till DateZone5. (text boxes are short date type)

The form on which these text boxes are located is called "InlogBA01".

Know what I would like to have is that when this form opens, 5 values are looked up in the underlying table.

The underlying table is called "tblBubbles".

For the DateZone1 it should look up the date in the table where the "Afdeling" is "BA01" and the "Bubblenr" is "1". For the Datezone2 it should look up the date where "Afdeling" is "BA01" and "Bubblenr" is "2",...

The date that should be returned in the text boxes is the date that is the closest to the present.

The code I have so far, but still generates an error is:

Private Sub Form_Load()

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtDate As Date

sSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=1) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtDate = rs("Datum")

Me.DateZone1 = dtDate

sSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=2) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtDate = rs("Datum")

Me.DateZone2 = dtDate

sSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=3) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtDate = rs("Datum")

Me.DateZone3 = dtDate

sSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=4) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtDate = rs("Datum")

Me.DateZone4 = dtDate

sSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=5) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtDate = rs("Datum")

Me.DateZone5 = dtDate

End Sub

The line in the code that creates the error is:

rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic (the first of the 5 ones, I guess they are all 5 wrong)

Does anyone have an idea where the error on the code is located and what it should become?

hi there.. .you post a similar error in another thread... do you have an open connection before you try to open the recordset???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========

Also try establishing a connection to the data source using a connection string constant.
Ex:
'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\location of databse file;"

Next try Setting a property to automatically create a connection between the rs and the connectionStr
Ex:
rs.ActiveConnection = ConnectStr

Try writing the sql when you open the recordset instead of in a seperate variable
Ex:
rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=1) AND ((tblBubbles.Afdeling)=BA06)) ORDER BY tblBubbles.Datum DESC"

These actions may establish a connection that you are possibly lacking.
Also try using a loop to do the search instead of typing all that 5 times.

Hope this helps.

You never realize what you didn't know untill you learn it.

I changed the code to:

Private Sub Form_Load()

Dim rs As ADODB.Recordset
Dim dtDate As Date

'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"

rs.ActiveConnection = ConnectStr

rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=1) AND ((tblBubbles.Afdeling)=BA01)) ORDER BY tblBubbles.Datum DESC"

dtDate = rs("Datum")

Me.DateZone1 = dtDate

'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"

rs.ActiveConnection = ConnectStr

rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=2) AND ((tblBubbles.Afdeling)=BA01)) ORDER BY tblBubbles.Datum DESC"

dtDate = rs("Datum")

Me.DateZone2 = dtDate

'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"

rs.ActiveConnection = ConnectStr

rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=3) AND ((tblBubbles.Afdeling)=BA01)) ORDER BY tblBubbles.Datum DESC"

dtDate = rs("Datum")

Me.DateZone3 = dtDate

'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"

rs.ActiveConnection = ConnectStr

rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=4) AND ((tblBubbles.Afdeling)=BA01)) ORDER BY tblBubbles.Datum DESC"

dtDate = rs("Datum")

Me.DateZone4 = dtDate

'Set up connection string
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"

rs.ActiveConnection = ConnectStr

rs.Open "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Bubblenr)=5) AND ((tblBubbles.Afdeling)=BA01)) ORDER BY tblBubbles.Datum DESC"

dtDate = rs("Datum")

Me.DateZone5 = dtDate

End Sub

according to what you said.

But I still have some questions... Do I have to declare the
Const ConnectStr = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source = C:\Documents and Settings\Vincent\Desktop\IKEA Trinity.mdb;"
like the Dim dtDate As Date?

When I run the code know, I get an error on the second 'ConnectStr ='
with the error 'Compile error: Duplicate declaration in current scope.'

You said to loop the 5 parts. How do I have to do this. Sorry for asking but I'm not really used to work with VB...

Because the 2 problems I had seemed to come form the same code, I changed this one to:

Private Sub Form_Load()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtDate As Date

'Open the table for DateZone1 and lookup the value
Set db = CurrentDb
strSQL = "SELECT TOP 1 [tblBubbles.Datum] FROM [tblBubbles] WHERE ((([tblBubbles.Afdeling])= BA01) AND (([tblBubbles.BubbleNr])= 1)) ORDER BY [tblBubbles.Datum] DESC"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

dtDate = rst("Datum")
Me.DateZone1 = dtDate

rst.Close
Set rst = Nothing
Set db = Nothing

'Open the table for DateZone2 and lookup the value
Set db = CurrentDb
strSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Afdeling)=BA01) AND ((tblBubbles.BubbleNr)=2)) ORDER BY tblBubbles.Datum DESC"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

dtDate = rst("Datum")
Me.DateZone2 = dtDate

rst.Close
Set rst = Nothing
Set db = Nothing

'Open the table for DateZone3 and lookup the value
Set db = CurrentDb
strSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Afdeling)=BA01) AND ((tblBubbles.BubbleNr)=3)) ORDER BY tblBubbles.Datum DESC"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

dtDate = rst("Datum")
Me.DateZone3 = dtDate

rst.Close
Set rst = Nothing
Set db = Nothing

'Open the table for DateZone4 and lookup the value
Set db = CurrentDb
strSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Afdeling)=BA01) AND ((tblBubbles.BubbleNr)=4)) ORDER BY tblBubbles.Datum DESC"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

dtDate = rst("Datum")
Me.DateZone4 = dtDate

rst.Close
Set rst = Nothing
Set db = Nothing

'Open the table for DateZone5 and lookup the value
Set db = CurrentDb
strSQL = "SELECT TOP 1 tblBubbles.Datum FROM tblBubbles WHERE (((tblBubbles.Afdeling)=BA01) AND ((tblBubbles.BubbleNr)=5)) ORDER BY tblBubbles.Datum DESC"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

dtDate = rst("Datum")
Me.DateZone5 = dtDate

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

but I still have a problem with this one...

When I open the form I get an error message:

Run-time error '3061':
Too few parameters. Expected 1.

The line that gives the error when I click on debug is the first:

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Does anyone know what the problem is?

Thanks