two front end users was able to populate same record number

[Freeda]

I split my database (A2003) and followed all the instructions in the other
posts. The back-end is stored in the server. There are only 2 front end users
so far... Stored the mdb file in c:\documents and settings\all
users\application data\filename.mde. So we started testing it and it we were
able to populate the same record number without the other locking the record.
So we were able to populate both record numbers with different data! I
already checked this --we are both on - tools-option-advanced-open mode
shared-default record locking no lock. Please help. Thanks.

 

[blue]

I found this:
Set options for a shared Access database
1.On the Tools menu, click Options.
2.Click the Advanced tab.
3. Do one or more of the following:
Specify the record locking strategy
When a user edits a record, Microsoft Access can automatically prevent
others from changing that record until the user has finished editing it.
Giving one user exclusive access to a record is called locking.

Under Default record locking, do one of the following:

To prevent locking of records while you edit them, click No locks.

To lock all records in the form or datasheet (and the underlying tables)
while you edit them, click All records.

To lock only the record you're editing, click Edited record.



Hope this helps...Blue

 

[Freeda]

Thanks for your suggestion. I tried doing that too but I forgot to mention
that my record numbers are not auto-numbers. The user have to input in
manually (being a new user, I already populated my database so I can't change
it anymore). Does this have to do anything with the records not being locked
by the first user?

 

[Rick Brandt]

I split my database (A2003) and followed all the instructions in the
other posts. The back-end is stored in the server. There are only 2
front end users so far... Stored the mdb file in c:\documents and
settings\all users\application data\filename.mde. So we started
testing it and it we were able to populate the same record number
without the other locking the record. So we were able to populate
both record numbers with different data! I already checked this --we
are both on - tools-option-advanced-open mode shared-default record
locking no lock. Please help. Thanks.

If you don't have a unique index on your record number and/or are not using
that as the primary key then your two users created two different records
that just happen to have identical record numbers. Locking doesn't even
enter into this.

How were you expecting two users to manually enter record numbers without
accidentally using the same values?

 

[Freeda]

Hi Rick

Is there any way I can work around this? Thanks.

 

[Rick Brandt]

Hi Rick

Is there any way I can work around this? Thanks.

"Rick Brandt" wrote:

Normally you let the databases calculate and assign the next number as each
record is inserted. Using the BeforeUpdate event of the form makes it
pretty unlikely that the same value will be grabbed for more than one user.

The first thing you need to do is put a unique index on that field (or make
it your primary key) so that duplicates are impossible. Then you can worry
about how to assign the values. I use DMax() in thre BeforeUpdate with no
problems...

If Me.NewRecord Then
Me!FieldName = Nz(DMax("FieldName", "TableName"), 0) + 1
End If

 

[Freeda]

Thanks Rick. I already made the ID as a unique index. I also put the code on
the Before Update event on the ID. I get the run-time error '2001'. What
does this mean?

 

[Freeda]

Rick,

I forgot to mention that I also have this code:
Private Sub Form_Current()

If Me.NewRecord Then
Me.txtLenderID.Enabled = True
Else
Me.txtLenderID.Enabled = False
End If

Exit Sub

I don't know if this will affect the code you gave me. I created my
database mainly from help from the forum (thanks!). I am just new and I
didn't make my LenderID as an autonumber when I started on my database.

 

[Rick Brandt]

Thanks Rick. I already made the ID as a unique index. I also put the
code on the Before Update event on the ID. I get the run-time error
'2001'. What does this mean?

If you had a unique index on the field then your two users would not have both
been able to create records using the same number. Do you mean you created a
unique index on the field after this happened?

Post the exact code you have.

 

[Freeda]

I have this code on my form since my Lender ID is not autonumber, I had
problems (before) updating my records..

Private Sub Form_Current()

If Me.NewRecord Then
Me.txtLenderID.Enabled = True
Else
Me.txtLenderID.Enabled = False
End If

Exit Sub

End Sub

Is there any way to make a unique id so the front-ends cannot update the
same record at the same time? Thanks again.