Entering Today's Date

[Alex Motilal]

Hello everybody,


I do have a Daily Cotton Auction database, in which cotton purchases are entered on auction basis. I have a table ‘tblCottonMarketDate, which has DateID (Auto number)& MktDate (Date)as fields.




At present maket date is entered manually through a Form. What I want is, when the Switchboard opens, a message box should appear asking ‘ Do you want to enter today’s Date?’ If Yes button is clicked, system date should be entered in the table. If again the database is opened on the same date, a message box should appear stating, ‘Date already entered’ with OK button.


Please help.

[June7]

You want to automatically create record and enter date, eliminating the manual entry form? Need another field in the table for DateEntered. So code in the Switchboard open event would check table if any DateEnter matches current date and if does tell user 'Date already entered', otherwise prompt user for date, then use SQL INSERT statement to enter new record with the market date and current date.

[Alex Motilal]

Dear June7,

Can you please post me the SQL Insert Statement. I presume the IIf function has to be used.

Alex

[NoellaG]

Hi Alex,

presuming your table with the dates is named tblMarketDates:

Dim rst As New ADODB.Recordset
    
    If DLookup("DateID", "tblMarketDates", "MktDate = #" & Date & "#") > 1 Then
        MsgBox "Sorry the current date is already inserted"
    Else
        rst.Open "select Mktdate from tblMarketDates", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
        With rst
            .AddNew
            !MktDate = Date
            .Update
            .Close
        End With
    End If
    Set rst = Nothing

grNG

[June7]

I may have misunderstood part of what you want to do. You ask user if they want to enter current date - what should happen if they say no, just quit? I thought you wanted to allow them to enter an alternate date. If not, then my suggestion for a DateEnter field is not relevant and NoellaG's code should serve.

[Alex Motilal]

Thank U NoellaG,

Your solution worked fine. Thanks June7 for your response.

Alex