vba to limit record entry

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Erm, it fires the instant the user types a keystroke into a blank new
record, no?

TC


Cheryl Fischer said:
Junior,

Try putting your code in the Before Insert event of your form. Before
Insert is the event that fires before a record is inserted into a table.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
 
Only if you equate "before the record is created", with "before a record is
inserted into the table"! But I see those as two different things. You can
create a record but then >not< insert it into the table.

I guess I was just trying to ensure that the OP was aware of the fundamental
difference between BeforeInsert and the the other form-level "before" event,
BeforeUpdate.

When BeforeUpdate fires, it proves that the user has committed to saving the
record. (Perhaps by pressing shift enter, or trying to move off the record.)
When BeforeInsert fires, it does >not< prove that the user is, or will be,
committed to saving the record, at all. He/she might type a few characters,
then press escape to cancel the record.

The other option is to code BeforeUpdate but check for Me.NewRecord. Then,
you >do< know that the user has committed to saving the new record. That is
what I personally would think of, when I read of an event that fired "before
a [new] record is inserted into the table".

Cheers,
TC
(off for the day)


Cheryl Fischer said:
From VBA Help on Before Insert:

"The BeforeInsert event occurs when the user types the first character
in a new record, but before the record is actually created."

I think that makes both of us correct.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

TC said:
Erm, it fires the instant the user types a keystroke into a blank new
record, no?

TC


Cheryl Fischer said:
Junior,

Try putting your code in the Before Insert event of your form. Before
Insert is the event that fires before a record is inserted into a table.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7
records
is permissable. how can i code to not allow more than 7 records. i.e.
allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
 
Junior,

Try putting your code in the Before Insert event of your form. Before
Insert is the event that fires before a record is inserted into a table.

hth,
 
cheryl - super thanks

Cheryl Fischer said:
Junior,

Try putting your code in the Before Insert event of your form. Before
Insert is the event that fires before a record is inserted into a table.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
 
From VBA Help on Before Insert:

"The BeforeInsert event occurs when the user types the first character
in a new record, but before the record is actually created."

I think that makes both of us correct.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

TC said:
Erm, it fires the instant the user types a keystroke into a blank new
record, no?

TC


Cheryl Fischer said:
Junior,

Try putting your code in the Before Insert event of your form. Before
Insert is the event that fires before a record is inserted into a table.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I notice that you posted a very similar question in the TableDesign group in
which you wanted to assure that a particular table had 10 or fewer records.
John Vinson advised setting an Integer Primary Key, with a validation rule
of
= 1 AND <= 10
Click to expand...

That solution would also work well for you also.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
 
Cheryl - yes, thanks - I try not to double post -
the situation was alittle different - it is helpful knowing how to limit
records from a table or from a form.
really appreciate your sharing.

Cheryl Fischer said:
I notice that you posted a very similar question in the TableDesign group in
which you wanted to assure that a particular table had 10 or fewer records.
John Vinson advised setting an Integer Primary Key, with a validation rule
of
= 1 AND <= 10
Click to expand...

That solution would also work well for you also.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
 
You're welcome, Junior. And, good luck with your project.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
Cheryl - yes, thanks - I try not to double post -
the situation was alittle different - it is helpful knowing how to limit
records from a table or from a form.
really appreciate your sharing.

Cheryl Fischer said:
I notice that you posted a very similar question in the TableDesign
Click to expand...
group
in
which you wanted to assure that a particular table had 10 or fewer records.
John Vinson advised setting an Integer Primary Key, with a validation rule
of
= 1 AND <= 10
Click to expand...

That solution would also work well for you also.


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Junior said:
I want to limit the number of records a user can enter into a lookup table
via a form. The table currently has 5 records; however, a max of 7 records
is permissable. how can i code to not allow more than 7 records. i.e. allow
changing current records but not allowing more than 7 total.
see below code i entered in the before updte event for the primary key
field. but this doesnt delete the entry??
hlp plse.

Private Sub Loc_ID_BeforeUpdate(Cancel As Integer)
Dim intCnt As Integer
intCnt = DCount("[LocID]", "[tlkpLocation]")
If intCnt = 7 Then
MsgBox " You may not enter more than 7 locations "
Cancel = True
Exit Sub
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top