Each of the 5 buttons opens a different form that is based on a different table. The forms have the data entry set to yes, so only new records can be added.

All buttons should be enabled. I only want the most recent date (that is coming from each underlying table) to be shown under each button.

If there is a way that only enables the button on wich the date is most far away and disables all other 4 buttons, then I am ready to use that one. But when the database will be used at the start, the 5 buttons need to be enabled, because no dates will be inputted yet... Furthermore, if the most recent date equals todays date, then only the button of that underlying table needs to be enabled.

So what I wanted to do is just have the dates displayed under the buttons and use the If statement in VBA to create every possible scenario... Takes many If's, but this I know how to do once I have the dates under the buttons...

Is this clear for you now?

I need you to answer one more question: Is the data in the dates under each button dynamic (it is updated while the form is open) or static (you take a picture of it as the form is opened, and it doesn't change while the form is open?)


mmcdonal

It will be static. The form I need the dates on will be closed when data will be inputted through another form. When this second form is closed with the action button it closes the second data input form and reopens the menu form I want the dates on...

So I guess we will need the on open event for this one...

the form I need the dates on is called "frmInlogBA06" if you need it...

Okay, the first thing you do is create a module and declare 5 date variables:

Public pDate1, pDate2, pDate3, pDate4, pDate5 As Date

Then put this code on the button that launches the second form:
(This puts ONE of the five dates in the first Public variable, you will need to duplicate this code to do all 5)


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

    sSQL = "SELECT TOP 1 Table1.SampleID, Table1.SomeDate FROM Table1 ORDER BY Table1.SomeDate"

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

    dtDate = rs("SomeDate")

    pDate1 = dtDate

    rs.Close

     sSQL = "SELECT TOP 1 Table2.SampleID, Table2.SomeDate FROM Table2 ORDER BY Table2.SomeDate"

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

     dtDate = rs("SomeDate")

    pDate2 = dtDate

   rs.Close

   ... etc ...


    DoCmd.Close 'close first form

    DoCmd.OpenForm "Form2" 'open second form

Then on the On Load event of the second form add this:

Me.Text0 = pDate1
Me.Text2 = pDate2
Me.Text4 = pDate3
Me.Text6 = pDate4
Me.Text8 = pDate5

Or whatever names you are using.

You can reuse the recordset for each of the 5 calls.

Did that work?

mmcdonal

Sorry, I did TWO of the five dates here...

mmcdonal

I get an error when I want to open the second form when clicking ok on the first one. Here is the code I have for the ok action button. I marked the line that gives the error in red...

Private Sub cmdOK_Click()

Dim spass, sdoc As String

spass = Me.PasswordInput

If IsNull(Me.PasswordInput) Or Me.PasswordInput = "" Then
    MsgBox "Gelieve een wachtwoord in te voeren.", vbCritical, _
        "Onvoldoende gegevens"
    Exit Sub
End If

If spass <> Me.Password Then
    MsgBox "Het ingevoerde wachtwoord is ongeldig.", vbCritical, "Ongeldige invoer"
    Exit Sub
Else
    Select Case UsernameInput.Value
        Case "BA01"
            sdoc = "frmInlogBA01"
        Case "BA02"
            sdoc = "frmInlogBA02"
        Case "BA03"
            sdoc = "frmInlogBA03"
        Case "BA04"
            sdoc = "frmInlogBA04"
        Case "BA05"
            sdoc = "frmInlogBA05"
        Case "BA06"

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

            sSQL = "SELECT TOP 1 tblBubblesBA06Bubble1.Datum FROM tblBubblesBA06Bubble1 ORDER BY tblBubblesBA06Bubble1.Datum"

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

            dtDate = rs("Datum")

            pDate1 = dtDate

            rs.Close

            sSQL = "SELECT TOP 1 tblBubblesBA06Bubble2.Datum FROM tblBubblesBA06Bubble2 ORDER BY tblBubblesBA06Bubble2.Datum"

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

            dtDate = rs("Datum")

            pDate2 = dtDate

            rs.Close

            sSQL = "SELECT TOP 1 tblBubblesBA06Bubble3.Datum FROM tblBubblesBA06Bubble3 ORDER BY tblBubblesBA06Bubble3.Datum"

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

            dtDate = rs("Datum")

            pDate3 = dtDate

            rs.Close

            sSQL = "SELECT TOP 1 tblBubblesBA06Bubble4.Datum FROM tblBubblesBA06Bubble4 ORDER BY tblBubblesBA06Bubble4.Datum"

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

            dtDate = rs("Datum")

            pDate4 = dtDate

            rs.Close

            sSQL = "SELECT TOP 1 tblBubblesBA06Bubble5.Datum FROM tblBubblesBA06Bubble5 ORDER BY tblBubblesBA06Bubble5.Datum"

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

            dtDate = rs("Datum")

            pDate5 = dtDate

            rs.Close

            sdoc = "frmInlogBA06"
        Case "BA08Bed"
            sdoc = "frmInlogBA08Bed"
        Case "BA08Textil"
            sdoc = "frmInlogBA08Textil"
        Case "BA09"
            sdoc = "frmInlogBA09"
        Case "BA10"
            sdoc = "frmInlogBA10"
        Case "BA40"
            sdoc = "frmInlogBA40"
        Case "BA50"
            sdoc = "frmInlogBA50"
        Case "Family"
            sdoc = "frmInlogFamily"
        Case "ZB"
            sdoc = "frmInlogZB"
        Case "BAM"
            sdoc = "frmInlogBAM06"
    End Select
End If

DoCmd.OpenForm sdoc, acNormal
DoCmd.Close acForm, "frmHoofdmenu"
DoCmd.Close acForm, "frmLogin"
DoCmd.Maximize

End Sub

I don't get why I get this error because he doesn't give one on the first 4 lines that are identical to this one.

I tried to make this work, but I still haven't found the answer...

On the on load of the form I want the dates on I have put this code. I have put it on this form because if I have to put this on every form that comes back to this form it would make it a lot of code in a case select every time...

this is the code I have:

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")

pDate1 = dtDate
Me.DateZone1 = pDate1

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")

pDate2 = dtDate
Me.DateZone2 = pDate2

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")

pDate3 = dtDate
Me.DateZone3 = pDate3

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")

pDate4 = dtDate
Me.DateZone4 = pDate4

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")

pDate5 = dtDate
Me.DateZone5 = pDate5

End Sub

The line that gives the error is put in red. Any suggestions?