trapping user's leaving new record b4 entring reqd data

[Guest]

i created an add record button atop my a2k data entry form which uses the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until the
user has taken care of entering the data in the controls reg#, study# and
on-study date. i know this takes a msgbox and i know we have to trap for the
failure on the user's part, the trick is identifying how to tell vba that's
what the user's trying to do. i tried using the ".oldvalue" feature and
testing for the starting and ending values but that didn't seemt to fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Allen Browne]

Access fires the BeforeUpdate event of the *Form* (not control) just before
the record is saved. This is the only way to programmatically test whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you open
the table in design view and set the Required property of the field to yes.

 

[Guest]

hey allen,

thanks a heap for showing me this new way to get things done. it's getting
to be dinner time in these parts of the 'States, but i'll jump on the
keyboard 1st thing tomorrow and try 'er out. is what's happening as the code
gets into gear that each field is given its own message box? i should perhaps
have added that the first two fields are part of the table's PK (date is not,
but it is required). now, here's where i really show off my newbie
smarts....what do i do about the add record code i constructed?

a million thanks.

best wishes,

-ted

Access fires the BeforeUpdate event of the *Form* (not control) just before
the record is saved. This is the only way to programmatically test whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you open
the table in design view and set the Required property of the field to yes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i created an add record button atop my a2k data entry form which uses the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until the
user has taken care of entering the data in the controls reg#, study# and
on-study date. i know this takes a msgbox and i know we have to trap for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature and
testing for the starting and ending values but that didn't seemt to fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Guest]

hi allen,

i am at my desktop again...i coded the vba you proposed into the
'BeforeUpdate' event and things aren't going as planned. here are some of the
consituent parts of the puzzle.

on my form, there's an 'Add Record' button which launches the OnClick event
below:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim response As Integer
Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Forms("Screening Log").AllowAdditions = False
Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i put a debug brekapoint in the vba BeforeUpdate code i added (below):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study Number is required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Registration Number is required." & vbCrLf
End If

If IsNull(Me.On_Study_Date) Then
Cancel = True
strMsg = strMsg & "On Study Date is required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete the data or press <Esc> to undo your
entry."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If

End Sub

and i notice that when i click the Add Record button the first 'place' it
goes to is the statement under this

If IsNull(Me.StudyNumber) Then

which (hovering over StudyNumber) tells me that StudyNumber is not null --
it's equial to the value of the control on the record i was viewing when i
clicked the cmdbtn! the same's true of the two other controls....so the tests
are never satisfied

can you help me....i don't quite know why it goes to this event before
adding the new record?

-ted

Access fires the BeforeUpdate event of the *Form* (not control) just before
the record is saved. This is the only way to programmatically test whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you open
the table in design view and set the Required property of the field to yes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i created an add record button atop my a2k data entry form which uses the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until the
user has taken care of entering the data in the controls reg#, study# and
on-study date. i know this takes a msgbox and i know we have to trap for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature and
testing for the starting and ending values but that didn't seemt to fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Allen Browne]

I guess I don't really understand what your AddRecord button is supposed to
do.

If it is intended to go to a new record, but you are already at a new record
(which has not been saved yet), the If line would not execute, so onthing
would happen. You could avoid this by explicitly saving first:
If Me.Dirty Then
Me.Dirty = False
End if

But what I don't understand is that you allow addtions, move to the new
record, and then change your mind that the new record is not allowed?
Nothing has been entered between these 2 things, so you are telling Access
it is now not allowed to have a new record after you moved the user there?
Sorry, I can't follow what you are intending.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hi allen,

i am at my desktop again...i coded the vba you proposed into the
'BeforeUpdate' event and things aren't going as planned. here are some of
the
consituent parts of the puzzle.

on my form, there's an 'Add Record' button which launches the OnClick
event
below:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim response As Integer
Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Forms("Screening Log").AllowAdditions = False
Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i put a debug brekapoint in the vba BeforeUpdate code i added (below):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study Number is required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Registration Number is required." & vbCrLf
End If

If IsNull(Me.On_Study_Date) Then
Cancel = True
strMsg = strMsg & "On Study Date is required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete the data or press <Esc> to undo your
entry."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If

End Sub

and i notice that when i click the Add Record button the first 'place' it
goes to is the statement under this

If IsNull(Me.StudyNumber) Then

which (hovering over StudyNumber) tells me that StudyNumber is not null --
it's equial to the value of the control on the record i was viewing when i
clicked the cmdbtn! the same's true of the two other controls....so the
tests
are never satisfied

can you help me....i don't quite know why it goes to this event before
adding the new record?

-ted

Access fires the BeforeUpdate event of the *Form* (not control) just
before
the record is saved. This is the only way to programmatically test
whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you
open
the table in design view and set the Required property of the field to
yes.

i created an add record button atop my a2k data entry form which uses
the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until
the
user has taken care of entering the data in the controls reg#, study#
and
on-study date. i know this takes a msgbox and i know we have to trap
for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature and
testing for the starting and ending values but that didn't seemt to
fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Guest]

well, let me see if i can try to put together what i envisaged when i first
set out to create the form. i think first off, i didn't want the user to be
able to use the '*' button at the bottom of the form next to the left and
right arrows, so i disabled the ability to add records in the form's property
settings. what i wanted was to 'force' the user to be able to use the
spanking new spiffy 'Add Record' button i generously added to the form. i
used the cmd button wizard (i think) to create it and proceeded to add the
code you see before and after the add record command in order to undo the
property setting (hopefully) long enough to add a new record after which the
property setting would return to no additions allowed. (is this making any
sense). at one point i had added code to the add record vba which would
'pre-populate' the values of the three controls (study, req number and
on-study date) with 'ENTRY REQUIRED' and '01/01/1900' respectively but took
that out when i got your vba. in the underlying table's design, study and reg
#s go into making the (composite) PK and on-study date IS a required field. i
would like to compel the user to have to enter valid data before being able
to a) add another record or b) move to a pre-xisting recrod once (s)he's
clicked the 'Add Record' button.

does this make my intentions any clearer.

I guess I don't really understand what your AddRecord button is supposed to
do.

If it is intended to go to a new record, but you are already at a new record
(which has not been saved yet), the If line would not execute, so onthing
would happen. You could avoid this by explicitly saving first:
If Me.Dirty Then
Me.Dirty = False
End if

But what I don't understand is that you allow addtions, move to the new
record, and then change your mind that the new record is not allowed?
Nothing has been entered between these 2 things, so you are telling Access
it is now not allowed to have a new record after you moved the user there?
Sorry, I can't follow what you are intending.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hi allen,

i am at my desktop again...i coded the vba you proposed into the
'BeforeUpdate' event and things aren't going as planned. here are some of
the
consituent parts of the puzzle.

on my form, there's an 'Add Record' button which launches the OnClick
event
below:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim response As Integer
Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Forms("Screening Log").AllowAdditions = False
Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i put a debug brekapoint in the vba BeforeUpdate code i added (below):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study Number is required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Registration Number is required." & vbCrLf
End If

If IsNull(Me.On_Study_Date) Then
Cancel = True
strMsg = strMsg & "On Study Date is required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete the data or press <Esc> to undo your
entry."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If

End Sub

and i notice that when i click the Add Record button the first 'place' it
goes to is the statement under this

If IsNull(Me.StudyNumber) Then

which (hovering over StudyNumber) tells me that StudyNumber is not null --
it's equial to the value of the control on the record i was viewing when i
clicked the cmdbtn! the same's true of the two other controls....so the
tests
are never satisfied

can you help me....i don't quite know why it goes to this event before
adding the new record?

-ted

Access fires the BeforeUpdate event of the *Form* (not control) just
before
the record is saved. This is the only way to programmatically test
whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you
open
the table in design view and set the Required property of the field to
yes.

i created an add record button atop my a2k data entry form which uses
the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until
the
user has taken care of entering the data in the controls reg#, study#
and
on-study date. i know this takes a msgbox and i know we have to trap
for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature and
testing for the starting and ending values but that didn't seemt to
fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Allen Browne]

Ted, I think I have given you everything you need.

You have 2 ways to prevent the user moving on without filling in the data:
- the Required property of the field in the table, or
- the BeforeUpdate event of the form.

If you also want a new record button, that is independent of the above (i.e.
Access will still fire Form_Beforeupate, or respect the Required property.)
The new record button would just contain:

If Me.Dirty Then
Me.Dirty = False
End If
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If

Blocking the form's AllowAdditions serves no useful purpose. In fact it
could jam things up royally if the form had no records (e.g. filtered.)

Time for me to move on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

well, let me see if i can try to put together what i envisaged when i
first
set out to create the form. i think first off, i didn't want the user to
be
able to use the '*' button at the bottom of the form next to the left and
right arrows, so i disabled the ability to add records in the form's
property
settings. what i wanted was to 'force' the user to be able to use the
spanking new spiffy 'Add Record' button i generously added to the form. i
used the cmd button wizard (i think) to create it and proceeded to add the
code you see before and after the add record command in order to undo the
property setting (hopefully) long enough to add a new record after which
the
property setting would return to no additions allowed. (is this making any
sense). at one point i had added code to the add record vba which would
'pre-populate' the values of the three controls (study, req number and
on-study date) with 'ENTRY REQUIRED' and '01/01/1900' respectively but
took
that out when i got your vba. in the underlying table's design, study and
reg
#s go into making the (composite) PK and on-study date IS a required
field. i
would like to compel the user to have to enter valid data before being
able
to a) add another record or b) move to a pre-xisting recrod once (s)he's
clicked the 'Add Record' button.

does this make my intentions any clearer.

I guess I don't really understand what your AddRecord button is supposed
to
do.

If it is intended to go to a new record, but you are already at a new
record
(which has not been saved yet), the If line would not execute, so onthing
would happen. You could avoid this by explicitly saving first:
If Me.Dirty Then
Me.Dirty = False
End if

But what I don't understand is that you allow addtions, move to the new
record, and then change your mind that the new record is not allowed?
Nothing has been entered between these 2 things, so you are telling
Access
it is now not allowed to have a new record after you moved the user
there?
Sorry, I can't follow what you are intending.

hi allen,

i am at my desktop again...i coded the vba you proposed into the
'BeforeUpdate' event and things aren't going as planned. here are some
of
the
consituent parts of the puzzle.

on my form, there's an 'Add Record' button which launches the OnClick
event
below:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim response As Integer
Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Forms("Screening Log").AllowAdditions = False
Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i put a debug brekapoint in the vba BeforeUpdate code i added (below):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study Number is required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Registration Number is required." & vbCrLf
End If

If IsNull(Me.On_Study_Date) Then
Cancel = True
strMsg = strMsg & "On Study Date is required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete the data or press <Esc> to undo your
entry."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If

End Sub

and i notice that when i click the Add Record button the first 'place'
it
goes to is the statement under this

If IsNull(Me.StudyNumber) Then

which (hovering over StudyNumber) tells me that StudyNumber is not
null --
it's equial to the value of the control on the record i was viewing
when i
clicked the cmdbtn! the same's true of the two other controls....so the
tests
are never satisfied

can you help me....i don't quite know why it goes to this event before
adding the new record?

-ted

:

Access fires the BeforeUpdate event of the *Form* (not control) just
before
the record is saved. This is the only way to programmatically test
whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you
open
the table in design view and set the Required property of the field to
yes.

i created an add record button atop my a2k data entry form which uses
the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until
the
user has taken care of entering the data in the controls reg#,
study#
and
on-study date. i know this takes a msgbox and i know we have to trap
for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature
and
testing for the starting and ending values but that didn't seemt to
fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?

 

[Guest]

thanks for your bandwidth, allen. let me go ahead and give it a go.

-ted

Ted, I think I have given you everything you need.

You have 2 ways to prevent the user moving on without filling in the data:
- the Required property of the field in the table, or
- the BeforeUpdate event of the form.

If you also want a new record button, that is independent of the above (i.e.
Access will still fire Form_Beforeupate, or respect the Required property.)
The new record button would just contain:

If Me.Dirty Then
Me.Dirty = False
End If
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If

Blocking the form's AllowAdditions serves no useful purpose. In fact it
could jam things up royally if the form had no records (e.g. filtered.)

Time for me to move on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

well, let me see if i can try to put together what i envisaged when i
first
set out to create the form. i think first off, i didn't want the user to
be
able to use the '*' button at the bottom of the form next to the left and
right arrows, so i disabled the ability to add records in the form's
property
settings. what i wanted was to 'force' the user to be able to use the
spanking new spiffy 'Add Record' button i generously added to the form. i
used the cmd button wizard (i think) to create it and proceeded to add the
code you see before and after the add record command in order to undo the
property setting (hopefully) long enough to add a new record after which
the
property setting would return to no additions allowed. (is this making any
sense). at one point i had added code to the add record vba which would
'pre-populate' the values of the three controls (study, req number and
on-study date) with 'ENTRY REQUIRED' and '01/01/1900' respectively but
took
that out when i got your vba. in the underlying table's design, study and
reg
#s go into making the (composite) PK and on-study date IS a required
field. i
would like to compel the user to have to enter valid data before being
able
to a) add another record or b) move to a pre-xisting recrod once (s)he's
clicked the 'Add Record' button.

does this make my intentions any clearer.

I guess I don't really understand what your AddRecord button is supposed
to
do.

If it is intended to go to a new record, but you are already at a new
record
(which has not been saved yet), the If line would not execute, so onthing
would happen. You could avoid this by explicitly saving first:
If Me.Dirty Then
Me.Dirty = False
End if

But what I don't understand is that you allow addtions, move to the new
record, and then change your mind that the new record is not allowed?
Nothing has been entered between these 2 things, so you are telling
Access
it is now not allowed to have a new record after you moved the user
there?
Sorry, I can't follow what you are intending.

hi allen,

i am at my desktop again...i coded the vba you proposed into the
'BeforeUpdate' event and things aren't going as planned. here are some
of
the
consituent parts of the puzzle.

on my form, there's an 'Add Record' button which launches the OnClick
event
below:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim response As Integer
Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Forms("Screening Log").AllowAdditions = False
Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i put a debug brekapoint in the vba BeforeUpdate code i added (below):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study Number is required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Registration Number is required." & vbCrLf
End If

If IsNull(Me.On_Study_Date) Then
Cancel = True
strMsg = strMsg & "On Study Date is required." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete the data or press <Esc> to undo your
entry."
MsgBox strMsg, vbExclamation, "Invalid Data"
End If

End Sub

and i notice that when i click the Add Record button the first 'place'
it
goes to is the statement under this

If IsNull(Me.StudyNumber) Then

which (hovering over StudyNumber) tells me that StudyNumber is not
null --
it's equial to the value of the control on the record i was viewing
when i
clicked the cmdbtn! the same's true of the two other controls....so the
tests
are never satisfied

can you help me....i don't quite know why it goes to this event before
adding the new record?

-ted

:

Access fires the BeforeUpdate event of the *Form* (not control) just
before
the record is saved. This is the only way to programmatically test
whether
fields have been filled in. Cancel the event to prevent the save.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.StudyNumber) Then
Cancel = True
strMsg = strMsg & "Study number required." & vbCrLf
End If

If IsNull(Me.RegNumber) Then
Cancel = True
strMsg = strMsg & "Reg number required." & vbCrLf
End If

'etc
If Cancel Then
strMsg = strMsg & "Complete the data, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid Data
End If
End Sub

Of course, you could achieve the same outcome without any code if you
open
the table in design view and set the Required property of the field to
yes.

i created an add record button atop my a2k data entry form which uses
the
following vba code:


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Forms("Screening Log").AllowAdditions = True
If Not Me.NewRecord Then DoCmd.GoToRecord , , acNewRec
Me.StudyNumber.Value = "ENTRY REQUIRED"
Me.RegNumber.Value = "ENTRY REQUIRED"
Me.[On-Study Date].Value = #1/1/1900#
DoCmd.GoToControl "StudyNumber"
Forms("Screening Log").Dirty = False
Forms("Screening Log").AllowAdditions = False

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.description
Resume Exit_AddRecord_Click

End Sub

i don't want the user to be able to move away from this record until
the
user has taken care of entering the data in the controls reg#,
study#
and
on-study date. i know this takes a msgbox and i know we have to trap
for
the
failure on the user's part, the trick is identifying how to tell vba
that's
what the user's trying to do. i tried using the ".oldvalue" feature
and
testing for the starting and ending values but that didn't seemt to
fit. i
found a blurb about the '.newrecord' property value in the Help
documentation....is this the way to go?