Hi Opal,
It really depends on how you want the Form to Lock and to prevent inserting a
new record.
They're many options available. For example, if the advance option for the
"Edited Record" is check, this will prevent others from editing this record.
So, you'll need Form's AfterUpdate event to set the "AllowEdits = No" like.....
'set this hidden checkbox
Me.CheckBox = False
The current event of the Form will handle the other properties.
As for the "Allow only one record per day", you need set the Form's
RecordSource to a proper query like...
"SomeDate" is the Column in the Table where the Default is set to "Date()" or
if you prefer, set it on the Form where "SomeDate" is the control.
Select col1, col2...... From Table Where SomeDate = Date()
And in the current event a code the handle if there are no records like......
If Me.RecordsetClone = 0 then
Me.AllowAdditions = True
Else
Me.AllowAdditions = False 'do not allow insert
End If
However, the above code will not prevent two or more concurrent users
attempting to insert a new record on the same day if there are no records.
So, a simple (but not really a good one) is to save the record if the first
user starts to insert a new record.
Another is to use a scheduler to insert a record just before any user starts
using the database. This requires the database as the back end.
Oh boy... I feel I am getting in way over my head here.....
This was a database I created in a couple of hours one afternoon to
replace an excel spreadsheet that cannot do what Access can do. Many
of my users are not well versed in Access and I am on a very sharp
learning curve when it comes to VBA in Access. The database was
created so that 4 separate shops could go in and enter pertinent
information about how their shops ran throughout the day so that my
boss (and theirs) could report out to his boss at the end of the day
about how things went. Each shop has its own form to enter their
information. I gave them separate forms bound to 4 separate tables
because the information gathered from each shop is unique. Each form
opens with the following code:
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
DoCmd.OpenForm "ShopDailyfrm", , , "txtDate = Date()"
End Sub
So that they always see each day's information and can go in multiple
times throughout the day to
add and update data / comments. The database then creates one report
containing the information
for all 4 shops that my boss uses to report out to his boss.
Problem is that there are multiple users adding information to the
tables. I have told the users within each shop that there should only
be one person in the shop specific form at a time. I have one shop
that does not always adhere to this and some days I end up with
multiple records. My hope is to prevent this by "locking-out" the
form to one user at a time. I really cannot depend on this group to
check a box upon entering the form that is why I thought by checking
the "Edited Record" radial on the advanced Options tab would elminate
multiple records, but it hasn't.
So although I read your last post, I am having trouble getting my head
around it (newbie), that is why
I wrote this rather long post so that you could better understand my
dilemma and direct me accordingly.
