Hi,

I have a form where some dates are looked up when the form loads. Here is the code:

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.autonumber, tblBubbles.Datum, tblBubbles.Hoofdgroep, tblBubbles.Subgroep, tblBubbles.Afdeling, tblBubbles.BubbleNr, tblBubbles.[OK?], tblBubbles.Actie, tblBubbles.DoorgegevenDoor,tblBubbles.DoorgegevenA an, tblBubbles.DoorgegevenOp 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.autonumber, tblBubbles.Datum, tblBubbles.Hoofdgroep, tblBubbles.Subgroep, tblBubbles.Afdeling, tblBubbles.BubbleNr, tblBubbles.[OK?], tblBubbles.Actie, tblBubbles.DoorgegevenDoor,tblBubbles.DoorgegevenA an, tblBubbles.DoorgegevenOp 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.autonumber, tblBubbles.Datum, tblBubbles.Hoofdgroep, tblBubbles.Subgroep, tblBubbles.Afdeling, tblBubbles.BubbleNr, tblBubbles.[OK?], tblBubbles.Actie, tblBubbles.DoorgegevenDoor,tblBubbles.DoorgegevenA an, tblBubbles.DoorgegevenOp 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.autonumber, tblBubbles.Datum, tblBubbles.Hoofdgroep, tblBubbles.Subgroep, tblBubbles.Afdeling, tblBubbles.BubbleNr, tblBubbles.[OK?], tblBubbles.Actie, tblBubbles.DoorgegevenDoor,tblBubbles.DoorgegevenA an, tblBubbles.DoorgegevenOp 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.autonumber, tblBubbles.Datum, tblBubbles.Hoofdgroep, tblBubbles.Subgroep, tblBubbles.Afdeling, tblBubbles.BubbleNr, tblBubbles.[OK?], tblBubbles.Actie, tblBubbles.DoorgegevenDoor,tblBubbles.DoorgegevenA an, tblBubbles.DoorgegevenOp 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

If IsNull(Me.DateZone1) Or Me.DateZone1 = "" Then
    Me.cmdZone1.Enabled = True
ElseIf Me.DateZone1 = Date Then
    Me.cmdZone1.Enabled = True
    Me.cmdReedsIngevuldZone1.Enabled = True
ElseIf IsNull(Me.DateZone2) Or Me.DateZone2 = "" Then
    Me.cmdZone2.Enabled = True
ElseIf Me.DateZone2 = Date Then
    Me.cmdZone2.Enabled = True
    Me.cmdReedsIngevuldZone2.Enabled = True
ElseIf IsNull(Me.DateZone3) Or Me.DateZone3 = "" Then
    Me.cmdZone3.Enabled = True
ElseIf Me.DateZone3 = Date Then
    Me.cmdZone3.Enabled = True
    Me.cmdReedsIngevuldeZone3.Enabled = True
ElseIf IsNull(Me.DateZone4) Or Me.DateZone4 = "" Then
    Me.cmdZone4.Enabled = True
ElseIf Me.DateZone4 = Date Then
    Me.cmdZone4.Enabled = True
    Me.cmdReedsIngevuldeZone4.Enabled = True
ElseIf IsNull(Me.DateZone5) Or Me.DateZone5 = "" Then
    Me.cmdZone5.Enabled = True
ElseIf Me.DateZone5 = Date Then
    Me.cmdZone5.Enabled = True
    Me.cmdReedsIngevuldeZone5.Enabled = True
ElseIf Me.DateZone1 < Me.DateZone2 Then
    Me.cmdZone1.Enabled = True
ElseIf Me.DateZone2 < Me.DateZone3 Then
    Me.cmdZone2.Enabled = True
ElseIf Me.DateZone3 < Me.DateZone4 Then
    Me.cmdZone3.Enabled = True
ElseIf Me.DateZone4 < Me.DateZone5 Then
    Me.cmdZone4.Enabled = True
ElseIf Me.DateZone5 < Me.DateZone1 Then
    Me.cmdZone5.Enabled = True
End If

End Sub

It took me a lot of time, but it works now... except for 1 little thing.

The database will start as a blanc database so no records will be in the database at startup. If I put some data in the database, the dates are looked up correctly and displayed as I want them to.
If the database starts without any data in the table I get the error

Run-time error '3021': No current record.

Is it possible with a little change in this code to obtain that if no values are found I don't get a VBA error message, but the text boxes where the dates need to appear just remain empty?

If rst.RecordCount <> 0 Then
 dtDate = rst("Datum")
 Me.DateZone1 = dtDate
Else
 Me.DateZone1 = "No Data"
End If

This is assuming that Me.DateZone1 - 5 are not date formats. If they are, and this does not work, let me know.

mmcdonal

It is actually a date format, but I changed it to

If rst.RecordCount <> 0 Then
 dtDate = rst("Datum")
 Me.DateZone1 = dtDate
Else
 Me.DateZone1 = ""
End If

and this seems to work...

Thanks again for your help

Hi Vince

You are doing a lot of work for something quite easy. Try using a query like this, and handling all 5 in a single recordset:

SELECT tblBubbles.BubbleNr, MIN(tblBubbles.Datum) AS Datum FROM tblBubbles WHERE ((tblBubbles.Afdeling) = 'BA01')) GROUP BY tblBubbles.BubbleNr ORDER BY tblBubbles.BubbleNr;

This gives a single recordset of each of the 5 BubbleNrs in order, together with the earliest date for each. This way, you only have one recordset to check for no rows found. It also has the big advantage of being easily adapted to 4, or 6, or any other number in the future.

Use a While Not rst.EOF loop to process the records, and Me.Controls("DateZone" & rst("BubbleNr")) = rst("Datum") to set the dates into the controls.

Good luck!