Date as Primary Key

[gaccess]

I've been trawling all morning and have found not much. Some say yes some say no. So I have tried to copy code I have found. I want to have the Date increase by one on each new recordset. Kind of like a diary really. Except on current brings up error "method or datamember not found on the 3rd last line , my control Date2Day

Private Sub Form_Current()
If Me.NewRecord Then
MsgBox "Test New Record"
Dim dtmNextDate As Date
Dim dtmLastDate As Date
dtmLastDate = Nz(DMax("[Date2Day]", "tblDistr"), _
DateAdd("d", -1, Date))
dtmNextDate = DateAdd("d", 1, dtmLastDate)
Me.Date2Day = dtmNextDate
End If
End Sub

I'm stumped sample zip attached

 

[John Big Booty]

Why not simply use the AutoNumber facility to provide your Primary Key

 

[gaccess]

well I could and have the Date as another control on my form but I want it to be consecutively populated with the date. Hell I cant expect staff to work out which date to put in.

"Human intervention, the curse of computing"

 

[NickHa]

If you open your form in design view, you'll see the control name is "ID" (same as the table field name). In your VBA, change Me.Date2Day to Me.ID and it will work.
How do you cater for multiple entries created on the same day? Do you allow duplicate keys? If you need to make keys unique, you could add a sequence number (starting at 0 or 1 for each new date) and use both date and sequence fields as the compound key.
Nick H

 

[gaccess]

mmm there is only to be one entry per control per day so the date is unique to that record set, also yes I think an autonumber as ID and if only I could have the date auto populate in sequence

 

[John Big Booty]

Simply set the Default value for the field to Date(). The will give you the current date for each entry. Assuming all along that there is only ever one entry per day, and that they are done on that day.

 

[gaccess]

looking at this from another tack, in the form can I have a search to take me to a date. The right click filters to only that date and I have to clear filters to show all again and the navigation search is really clunky