Allow Edit but not To Insert Record

[aiikahn]

I'm sure this probably has a simple solution but somehow I'm having some issues trying to figure out how to prevent a form to insert a record once it has been updated that day; yet still allow edit

For example:

If a record has been entered for today and someone tried to add a new one later that same day, it would prevent that user to add another record but allow them to edit that record if necessary.

The only time I would like for users to insert a record is on a different date in the future.

I've tried using BeforeUpdate and BeforeInsert but it won't let me save in the end and gives me errors.

My form has the following fields:

Date, Orders, Weight, Costs

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim dToday As Date
dToday = Format(Now(), "Medium Date")

If Me.txtDate.Value = dToday And Not IsNull(txtOrders) And Not IsNull(txtWeight) And Not IsNull(txtCost) Then
MsgBox "Record has been updated TODAY, " + Format(Now(), "dd mmmm yyyy"), vbOKOnly
End If

DoCmd.GoToRecord , , acLast
DoCmd.CancelEvent

End Sub

Any assistance would be greatly appreciated. Thank you!

 

[gwunta1907]

I have used a table called 'RunOnce' that stores the date that a macro, query or some instruction has run. You create an insert query into the table, specifying the field, entering the date like this:

CurrentDb.Execute "INSERT INTO tbl_RunOnce ( DateRun ) Select #" & Date & "#"

Just substitute the 'DateRun' above for some other field name that would represent the field you want to only have changed once per day. Then create an 'On Focus' event has some code like this:

If IsNull(DLookup("DateRun", "tbl_has_run", "DateRun=#" & Date & "#")) Then
'allow an update to occur i.e. do nothing
Else
MsgBox "Cant change record today"
DoCmd.GoToRecord(acDataForm,form_name,acPrevious) 'take user back to previous record
End If

 

[John Big Booty]

Just a quick observation avoid the Now() function unless you specifically need a time component on your date, use the Date() in all other cases.

 

[aiikahn]

Thank you gwunta. I will surely try that. I'm somewhat rusty with running SQL in access so I'm hoping I'll be able to do this. I just thought this was possible from the form itself.

@booty thanks for pointing out to use Date() instead. I will change it.

 

[gwunta1907]

You can do it all from the form using VBA aiikahn. The code that I gave you works from within the form code.