Scubaman said:Good evening,
I have a form in which I can add records. I would like to limit the
number of records to two. Is there a (simple) way to do that, as I
don't know any VBA?
Sharkbyte said:I'm not sure there is any way to do this without some coding. Sorry.
But it is fairly easy.
Initialize a variable (at form open), and set it to 0.
When you click Save, run something like this...
If [Variable1] >= 2 then
msgbox "You are only allowed to create 2 records, per session.
You have reached this limit."
Else
[SQL Statement for Save]
[Variable1] = [Variable1] + 1
End If
So, as your user(s) save records, the variable counts. And the If
statement stops them after 2 records.
Dirk Goldgar said:Sharkbyte said:I'm not sure there is any way to do this without some coding. Sorry.
But it is fairly easy.
Initialize a variable (at form open), and set it to 0.
When you click Save, run something like this...
If [Variable1] >= 2 then
msgbox "You are only allowed to create 2 records, per session.
You have reached this limit."
Else
[SQL Statement for Save]
[Variable1] = [Variable1] + 1
End If
So, as your user(s) save records, the variable counts. And the If
statement stops them after 2 records.
It didn't occur to me that it might be a per-session limit. If that's
the case, then the code I posted isn't appropriate.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Sharkbyte said:And if it is, Dirk, then my code fails, as well. Simply close the
form, and re-open, and you have reset the variable, unless it is
created as a Global Variable. =-/
Thank you guys, but the last few answer are too far for me, I am ratherDirk said:Ah, but you get around that by defining "session" carefully! If a
"session" = one opening of the form, then your solution meets spec. <g>
Scubaman said:Thank you guys, but the last few answer are too far for me, I am
rather new to Access.
I would like to see the message when someone is trying to add more
than 2 records. Will try it out.
Thank you very much
Thanks you for the understanding. Itried to copy the code after CompareDirk said:Sometimes we get a little carried away.
Let us know how it goes, by replying to this thread.
You're welcome.
Scubaman said:Thanks you for the understanding. Itried to copy the code after
Compare database, but when I run the form it says there is a double
name found (hope I translated it correct in Form_before insert.
And at the same time the form is not running correct. Guess I am doing
something wrong
Good morning,Dirk said:It sounds like you already had an event procedure for the form's
BeforeInsert event. I assumed that you wouldn't, because you said you
are unfamiliar with VBA code. Are you modifying a form that somebody
else created?
If you'd like to copy and paste the complete form module code, we may be
able to tell you what to do to fix the problem.
Scubaman said:Good morning,
I created my database myself, and didn't understand things like
dlookup, so O asked around and had some great help from memebers of
the newsgroup. Just now I am trying to make the database more
"handyer" so that's why I would like to limit the records that can be
added. Here's the code
Option Compare Database
Private Sub Form_AfterInsert()
End Sub
Private Sub Form_AfterUpdate()
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.StudentId = Forms.student.StudentId
End Sub
Private Sub Form_Current()
Me.KeuzeLand = DLookup("HotelLand", "Hotels", "HotelId = " &
Me.Hotelid) Me.Hotelid.RowSource = "SELECT HotelId, Hotelnaam FROM
Hotels WHERE HotelLand = '" & _
Me.KeuzeLand & "' ORDER BY Hotelnaam"
Me.Hotelid.Requery
End Sub
Private Sub KeuzeLand_AfterUpdate()
Me.Hotelid.RowSource = "SELECT HotelId, Hotelnaam FROM Hotels WHERE
HotelLand = '" & _
Me.KeuzeLand & "' ORDER BY Hotelnaam"
End Sub
Private Sub Record_toevoegen_Exit(Cancel As Integer)
End Sub
Private Sub Vorige_record_Click()
On Error GoTo Err_Vorige_record_Click
DoCmd.GoToRecord , , acPrevious
Exit_Vorige_record_Click:
Exit Sub
Err_Vorige_record_Click:
MsgBox Err.Description
Resume Exit_Vorige_record_Click
End Sub
Private Sub Volgende_record_Click()
On Error GoTo Err_Volgende_record_Click
DoCmd.GoToRecord , , acNext
Exit_Volgende_record_Click:
Exit Sub
Err_Volgende_record_Click:
MsgBox Err.Description
Resume Exit_Volgende_record_Click
End Sub
Private Sub Record_toevoegen_Click()
On Error GoTo Err_Record_toevoegen_Click
DoCmd.GoToRecord , , acNewRec
Exit_Record_toevoegen_Click:
Exit Sub
Err_Record_toevoegen_Click:
MsgBox Err.Description
Resume Exit_Record_toevoegen_Click
End Sub
Good morning,Dirk said:As I thought, you already have a Form_BeforeInsert procedure. Instead
of just adding one, we'll have to modify the existing one. This is what
you currently have:
'----- start of original event procedure -----
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.StudentId = Forms.student.StudentId
End Sub
'----- end of original event procedure -----
Modify it so that it looks like this:
'----- start of revised event procedure -----
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Recordset.RecordCount >= 2 Then
Cancel = True
MsgBox "Sorry, you've already entered two records."
Else
Me.StudentId = Forms!Student.StudentId
End If
End Sub
'----- end of revised event procedure -----
That should fix the problem.