Multi-user form advice

[PThomas]

Hi All

I have an Access 2003 database with front/backend, this is for simple Hotline call logging. I need for 3-4 staff to be able to create new calls as well as review old calls.

It is all working fine apart from creating new calls, I was previously advised by somebody to have a unique autonumber field for each record and then have an automatically incrementing field (coded) so that each time a user creates a new record it gives it the next number in sequence.

On the form I have designed the user just click the last record button and then clicks next to automatically creates a new entry. If two users do this at the same time they both get the same numbered record and because the field is set to unique, one of them fails to save.

Here is the code I currently use:-

Private Sub Form_Current()
Dim intnewrec As Integer
intnewrec = Me.NewRecord

If intnewrec = True Then
Me.ServiceCallNo = Nz(DMax("[ServiceCallNo]", "[tbl_Hotline_Call]"), 0) + 1
End If
End Sub

How should I tackle this so that users can create new records simultaneously without either having the same unique ref number or leaving numbers unused (If they exit without saving)


Thanks in advance for any assistance given.

[OBP]

Hello, I would remove the standard Record Navigation Controls and replace them with Navigation Command Buttons.
If you have a "New Call" type button for entering new records you can use a Recordset to -
1. check that the last Record has actually been used or completed and if it isn't, then use it for the new record.
2. If it has been used then create a new record with the incremented number, "Update" the table and then use that as the new record.

Alternatively stop using the Manually incrmented number and just go with the Autonumber.

[PThomas]

Hi

I already have manual buttons for these functions.

Going with your suggestions:-

1. How do I stop the user from clicking next on the last record and creating a new one?

2. If I wish to continue using a incremented number, how can get it to work? (Perhaps New call button will open a new record and save it straight away so that it exists with the next number?)

3. From your suggestion, how do I check if has been used/completed etc.

Thanks

Paul

[OBP]

Paul, you can stop the user from moving to a new record in 2 ways.
The first method involves turning off and on the Form's "Allow Additions" property.
The second involves using the recordset to check if you are on the last record and if you are sets the "Next" button to "Enabled" = No.
You can create a new record and try saving it.
Do you know how to use "RecordSets"?

[PThomas]

Do you know how to use "RecordSets"?

No I haven't used recordsets before (Although I am a quick learner!)
My current code for next record is:-

Private Sub NextCall_Click()
On Error GoTo Err_NextCall_Click

DoCmd.GoToRecord , , acNext
Forms![frm_Hotline_Call]![CustomerName].Requery
Forms![frm_Hotline_Call]![CustomerSite].Requery
Forms![frm_Hotline_Call]![CustomerSiteContact].Requery
Forms![frm_Hotline_Call]![Equipment].Requery
Forms![frm_Hotline_Call]![EquipmentType].Requery
Exit Sub

Err_NextCall_Click:
If err_number = 2105 Then Exit Sub
End Sub


So I can use something like this:-

If intCanEdit = IFalse Then
With frm
.AllowAdditions = False
.AllowDeletions = False
.AllowEdits = False
End With
Else
With frm
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
End If

To stop the user adding a record manually, and instead use a New Record button which turns the .AllowAdditions to True.

Could I save the record immediately so that the incremented record exists in the database so that nobody else can use the same number?
In that scenario would I need to somehow refresh the other users database view to ensure it was 'current' before creating a new record each time?

Hope that makes sense?

[OBP]

Paul, the answer to your questions looks like Yes.
The Recordset or Recordset Clone can be used to manipulate the Form's data in the background. You can increment the Record and then add it to the Table thus creating a new record without the current user knowing anything has happened, but subsequent users will get the incremented number as the last record.

[PThomas]

Thanks for the response.

Can you explain how I would use recordset/recordset clone to create the record?
Also how I can refresh the users view of the database so that it is always current?

Thanks

[OBP]

Paul, have a look at this example database.
It has a simple Table/Query/Form and a second query to show the last record based on the highest number.
Open the Form and the rest should be self explanetory, the second button can be used to show that the record has been created and the number incremented.
The message box is not necessary, it just shows how the Number field value is incremented.

[PThomas]

Thanks for the example, after a brief read I think it makes sense.

When 2 users are simultaneously adding new records, how do I force a refresh to ensure that it is seeing the up-to-date last record before creating a new one?

What happens if they decide not to save the record, would I need to ask when closing the form (perhaps disable the navigation buttons while adding?) - perhaps I'm over complicating it?

Cheers

Paul

[OBP]

Paul, that should be taken care of by the Query, as soon as the first user clicks for the new record the table is updated and then any one esle will get the next value, if you look at the query that is opened by the query button you will see what I mean.
If they decide not to save the record you have a major problem because the value has been incremented and the record created. You would literally have to delete the record. BUT if someone else has added a record then another increment would already have been made and you would have a gap in your numbering.
Does it matter either way, you cold add a "Voided" field to the records so that if someone does change their mind after clicking the new record it sets the Field to Voded so that averyone knows it is not a genuine record. You could overwrite it with the next new record, but it would be getting very complicated.

[PThomas]

Thanks for the reply.

I am busy redesigning my database to use a query instead of directly accessing the table.

As part of my on-screen controls I have a next and previous record button. How can I stop the user from being able to go to the last record and then choosing next to create a new one?

My code for next:-

Private Sub Btn_NextCall_Click()
On Error GoTo Err_Btn_NextCall_Click
DoCmd.GoToRecord , , acNext
Forms![frm_Hotline_Call]![CustomerName].Requery
Forms![frm_Hotline_Call]![CustomerSite].Requery
Forms![frm_Hotline_Call]![CustomerSiteContact].Requery
Forms![frm_Hotline_Call]![Equipment].Requery
Forms![frm_Hotline_Call]![EquipmentType].Requery
Exit_Btn_NextCall_Click:
Exit Sub
Err_Btn_NextCall_Click:
MsgBox Err.Description
Resume Exit_Btn_NextCall_Click

End Sub

Thanks

Paul