By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,912 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Validation q's - subform BeforeUpdate event (and SQL)

P: n/a
Hi,

I have a form with a continuous subform. I am working on putting
validations in for the subform's required fields. Being somewhat new
to Access (or rather, an antiquated mainframe programmer), I finally
figured out that the place to put the validations is in
Form_BeforeUpdate.

I have 2 questions:
1) once I determine there is an error and display a message box with
vbokonly, then what do I do? Logic continues after the msgbox
statement. Do I exit from the sub? But then flow will be in the
middle of BeforeUpdate, and that seems an awkward place to be. Do I
force logic to go somewhere else (but where?).

2) One of my table's fields is sequence number, and before Inserting a
new record, I need to find the highest sequence number for a given
field combination. I have set up a string that contains a Select
statement I need within BeforeUpdate, but not sure of the logic
involved to use it (and is 'select' correct in this event?).

I can provide code if it would help.

Thanks,
Lori

Dec 8 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I have the answer to my Question #1. Using Cancel=True, the msgbox,
and focus on the field in error, the logic takes care of itself.

Now I just need to know how to query the table from Form_BeforeUpdate
(or elsewhere) for the information I'm needing at this point.

Dec 8 '05 #2

P: n/a
you use DMAX() to find the highest number in your sequence field. Then
you add 1 to it. Something like:

dim lngNextValue As Long
lngNextValue = DMAX(FieldName,TableName)+1

MySequenceField=lngNextValue

or of course, just
MySequenceField = DMAX("FieldName","TableName")+1

Dec 8 '05 #3

P: n/a
Br
lorirobn wrote:
Hi,

I have a form with a continuous subform. I am working on putting
validations in for the subform's required fields. Being somewhat new
to Access (or rather, an antiquated mainframe programmer), I finally
figured out that the place to put the validations is in
Form_BeforeUpdate.

I have 2 questions:
1) once I determine there is an error and display a message box with
vbokonly, then what do I do?

Logic continues after the msgbox
statement. Do I exit from the sub? But then flow will be in the
middle of BeforeUpdate, and that seems an awkward place to be. Do I
force logic to go somewhere else (but where?).
In the BeforeUpdate code put:

Cancel = True

This will cause the BeforeUpdate event to end and return you to the
record/form.
2) One of my table's fields is sequence number, and before Inserting
a new record, I need to find the highest sequence number for a given
field combination. I have set up a string that contains a Select
statement I need within BeforeUpdate, but not sure of the logic
involved to use it (and is 'select' correct in this event?).

I can provide code if it would help.

Thanks,
Lori


Would it be easier to set the defaultvalue of this sequence number field
on your form to something like:

=DMax("lngSeqNo", "tblMyTable") + 1

(check the help for the syntax as I'm just going by memory)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Dec 8 '05 #4

P: n/a
Thank you.
This works great. The only problem I had was with NextValue defined as
Long. When there were no records to meet the criteria, I got an
'invalid use of null' error. So I changed it from Long, to just:
'dim NextValue'
with no 'AS' clause defining it further.
Not sure what this did but it worked?
Do you know how a field is defined if you leave off the AS clause?

thanks a lot!
Lori

Dec 8 '05 #5

P: n/a
Br
lorirobn wrote:
Thank you.
This works great. The only problem I had was with NextValue defined
as Long. When there were no records to meet the criteria, I got an
'invalid use of null' error. So I changed it from Long, to just:
'dim NextValue'
with no 'AS' clause defining it further.
It then reverts to the default which is type 'variant'.
Not sure what this did but it worked?
Because variants allow NULLS.
Do you know how a field is defined if you leave off the AS clause?

thanks a lot!
Lori


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Dec 8 '05 #6

P: n/a
Thanks, Bradley. I've got this piece working fine now. Thanks for
the explanation about VAR, and about the Cancel=True (had discovered
that earlier today and it did indeed solve the problem).

Thanks for all the help!

Dec 9 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.