473,701 Members | 2,882 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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_BeforeUpda te.

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
6 3124
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_BeforeUpda te
(or elsewhere) for the information I'm needing at this point.

Dec 8 '05 #2
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
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_BeforeUpda te.

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
6462
by: Deborah V. Gardner | last post by:
I have a form frmViolations with a subform sfrmViolations. There is a one-to-many relationship between the two. On the subform I have a date field and a duration field. Both can be blank but if the date field has a date then the duration field must be filled in with some value. Each violation has a violation description. I use a combo box to fill in the description.
15
24846
by: Rey | last post by:
Howdy all. Appreciate your help with several problems I'm having: I'm trying to determine if the Visit subform (subformVisits) has a new record or been changed, i.e. dirty. The form that contains the subform is named Clients. I have this code in the Add Client btn: If Forms!Clients.subformVisits!VisitDirty = True Then MsgBox "Visit subform is dirty!"
3
15916
by: Dalan | last post by:
I apparently need a bit of assistance regarding the structure of some validation code on the BeforeUpdate or AfterUpdate event on a form for several fields that need to controlled. I did search the archives, but found nothing precise enough to address my specific needs. 1. There are three text box fields on the form and one of the three need to have a text value provided. I simply need to ensure that one field has data, not two, not...
2
2831
by: R Bolling | last post by:
I am using a routine to check to see if a phone number (PK) has alread been entered, and takes the user to that record if it is found -- as follows: Private Sub Contact_telephone___BeforeUpdate(Cancel As Integer) Dim rs As DAO.Recordset Dim iAns As Integer Set rs = Me.RecordsetClone rs.FindFirst " = '" & Me! & "'" If Not rs.NoMatch Then
25
10248
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
5
3487
by: Susan Bricker | last post by:
Hi. I have a Main form with a subform that has a subform. I have an 'Add Event' button on the Main Form that issues the following instruction: DoCmd.GoToRecord , , acNewRec If the record being displayed BEFORE the 'Add Event' button is clicked does not have data to be displayed (no associated linked records) then when the 'new record' is displayed, the innermost subform looks just fine (all the controls are there and they are empty).
0
1652
by: Deano | last post by:
My beforeupdate event on the main form fires when a change has been made and the record has not been saved (2 variables are used to track this). Works great but if they make a change in my subform which is in one tab of a tab control, and then they select a different tab, the beforeupdate event fires. So they get prompted as to whether they want to save the record - however the idea is that the event only fires if they try to close the...
4
8841
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form which allows me to edit the record in the subform. What I want to happen is for subform with the new edits to be updated on the main form when I close the popup. I'm sure this is a very small bit of code in the the 'On close' event for the popup...
0
1844
by: jwbird | last post by:
I have a main form with multiple Subforms that are all displaying Datasheet information related to the Main form record. When I make changes/adds to the records(s) in the subform and tab to another subform, it saves those previous subform records fine, but when I make changes to the subform records and then change the mainform criteria, it doesn't save those subform records. I can use an event to prompt for saving the records on the...
0
8738
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8649
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9233
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8936
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7829
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6575
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4411
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3104
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2399
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.