data validation in the form

Hi,

I would like to perform data validation before the insertion of a new record 
into the table in Access database. 

So in the form, i have a few fields that need to be checked to see if they 
are empty or entered. I have the following codes in the event of 
"Form_BeforeUpdate"

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim mesg, Title As String
    
    'Check if all the required fields are entered
    If Me.Stud_ID <> "" Then
        If Me.Department_ID <> "" Then    
                'warning message
                 mesg = "Data has been changed in this record. Do you want 
to proceed?"
                 Title = "Warning !!!"
    
                 If MsgBox(mesg, vbYesNo, Title) = vbNo Then
                        'cancel the update action
                        Cancel = True
                         'undo all the data as well as the beforeupdate event
                         Me.Undo
                 End If
        End If
    End If 
End Sub

This code behaves the way i want it to but still the record gets inserted 
into the table even when the department_ID field is empty. This is not right. 
How do i get around this problem from the form level?

Thank you in advance 
0
Utf
12/3/2007 5:43:01 AM
access.forms 6864 articles. 2 followers. Follow

1 Replies
700 Views

Similar Articles

[PageSpeed] 44

Associates,

Your code ios testing the Stud_ID and Department_ID field for the 
presence of a zero-length string "" which is very unlikely.  Not only 
that, but it seems to be backwards, i.e. I assume you do not want to 
cancel the update if there is data in these fields.  Try it like this:

     If IsNull(Me.Stud_ID) Then
         If IsNull(Me.Department_ID) Then

.... or, possibley simpler:
    If IsNull(Me.Stud_ID) Or IsNull(Me.Department_ID) Then

(Sorry, I am not 100% clear of what you really want to happen, but 
hopefully that will help you re-work it)

-- 
Steve Schapel, Microsoft Access MVP

Associates wrote:
> Hi,
> 
> I would like to perform data validation before the insertion of a new record 
> into the table in Access database. 
> 
> So in the form, i have a few fields that need to be checked to see if they 
> are empty or entered. I have the following codes in the event of 
> "Form_BeforeUpdate"
> 
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>     Dim mesg, Title As String
>     
>     'Check if all the required fields are entered
>     If Me.Stud_ID <> "" Then
>         If Me.Department_ID <> "" Then    
>                 'warning message
>                  mesg = "Data has been changed in this record. Do you want 
> to proceed?"
>                  Title = "Warning !!!"
>     
>                  If MsgBox(mesg, vbYesNo, Title) = vbNo Then
>                         'cancel the update action
>                         Cancel = True
>                          'undo all the data as well as the beforeupdate event
>                          Me.Undo
>                  End If
>         End If
>     End If 
> End Sub
> 
> This code behaves the way i want it to but still the record gets inserted 
> into the table even when the department_ID field is empty. This is not right. 
> How do i get around this problem from the form level?
> 
> Thank you in advance 
0
Steve
12/3/2007 8:42:11 AM
Reply:

Similar Artilces:

How do I compare data from 2 worksheets to find duplicate entries
I am a novice Excel user. I have to worksheets that have data, some of it is similar (ie. Item #'s, etc.) I want to automatically compare the reports and highlight data on one worksheet that is identicle to the data on the other worksheet. If I can't do this, I'm going to have to manually compare the data. Jack, A coded solution is probably the way to go for your challenge. You can iterate through each item on the first sheet and check to see if it exists in the second sheet before copying it to a new (report), third sheet. -- http://HelpExcel.com 1-888-INGENIO 1-888...

Moving data from a string
I have a rather complex problem, at least it is for me. I have a column full of addresses. 1234 common way, city, state zip I want to take the column and split it into at least 2. Such that the street address and the CSZ are in seperate columns. A B 1234 common Way, city, state zip I have zero experience with basic and almost none with excel so can someone please help me? Thanks On Thu, 2 Oct 2008 19:47:58 -0700 (PDT), Lacertadeus@gmail.com wrote: >I have a rather complex problem, at least it is for me. > >I have a column full of addr...

access 2003 jumps to first record of form when switching to another application
Hi, does anyone know what causes access (2003) to jump to the first record of a form when you switch to another application? I have a main form with a tab control. Each tab has various forms/subforms. This behavior is happening with single forms, continuous forms, and datasheet forms. It's really disconcerting when your working on a record to have the application jump to another record everytime you switch to another app. The database is split into front/backend. Many Thanks ...

Error Message "valid win32 application"
I run Windows Vista and have recently purchase and installed Microsoft Office 2007. When I try to open files that I know to have been created in standard Office applications, and in this particular instance Word, I get an error message that the file is "not a valid WIn 32 application". I can however open Word 2007 application and then open the file and this works, however it's a pain in the ____ and I don;t want to do it like that - I want to double click on the file name straight from my data stick etc and be magically transported to my proffessors next exciting ass...

pasting data and macros
I am copying large blocks of data into excel and want to run a macro that 1. allows me to paste special, text the data. 2. then automatically moves to the cell below the last data cell so I can paste the next batch of data. Thanks a ton!!! ...

Adding only vertical error bars to a single data point in a series
Hi, I need to add only vertical error bars to a single data point in a series on a scatter chart in Excel 2007. http://office.microsoft.com/en-us/ex...x#AddErrorBars is worded in a way that sounds like it is possible to do that in Excel 2007 yet I don't appear to have the option in the properties for a particular data point or be able to specify only vertical error bars instead of both (horizontal and vertical) or none. How can I add only vertical error bars to a single data point in a series in Excel 2007? Thanks, Chris Chris, I could not follow you URL since you abbreviated ...

Summing up records in a Form
(Sorry for cross-posting. I accidentally posted to the "General Questions" track, but it fits obviously better here:) I have a table with four fields: (1) a unique ID, (2) a filter field, (3) a text (not unique) and (4) a number. In my form I want to filter the table by a combox selection (in this case the value "f1") and collapse the remaining records with identical text and show the sum of the number. Records (myId, myFilter, myTxt, myNum): 1, f1, texta, 9 2, f1, texta, 8 3, f2, texta, 7 4, f1, textb, 6 .... I want to filter only the "f1" records and see:...

Validation Rule 06-02-07
Hi everyone, I was wondering if anyone can help me with this validation rule: >L<CCCCCCCCCCCC which means first letter with be capital and the rest lower case, but i want to enter Blahblha-Blah (ie another uppercase letter), I can't figure out how to make my validation rule allow this. Can anyone help me? Hamzah On Sat, 02 Jun 2007 20:13:57 GMT, "M. Hamzah Khan" <hamzah@hamzahkhan.com> wrote: >Hi everyone, > >I was wondering if anyone can help me with this validation rule: >>L<CCCCCCCCCCCC which means first letter with be capital and the rest &g...

Charting different data formats in the same chart
Not sure if this is possible or not. I’ve got a sheet with three columns A=total number of items B=is the percentage of each row to the grand total of items in column A C=is a dollar amount. I’m trying to create a bar graph that reflects all three columns. Can this be done or do I need to make three separate charts and if that’s the case is it possible to combine bar charts? Milo, Whenever I've had to do this type of graph, I would do the total as a column graph using the left axis, the dollar amount as a line using the right axis, then I'd use the x-y labeler (see link below...

how to flip paired data in a table, so last pair appears at top o.
i have paired data in a columnar table. how would i flip the table, so the last data pair appear @ the top & the first @ the bottom? Hi in B1 enter: 1 in B2: 2 select both cells and copy down. Now sort with column B (descending) -- Regards Frank Kabel Frankfurt, Germany jim mcgrath wrote: > i have paired data in a columnar table. how would i flip the table, > so the last data pair appear @ the top & the first @ the bottom? ...

Show Picture in a form
I have a table where I store the employee picture. I have a form where I would like to have the picture show when the employee is choosen from a combo box. Is this done with Object frames? Can some one help me set this up please. thank you victor. Victor, I recommend against actually storing the picture in the database. This will bloat your database incredibly, and, depending on the size of your company, and the size of the images, could cause you to rapidly approach the maximum file size (2GB for 2003) of an Access database. Instead, I recommend storing the file name in the databa...

How to pass data from main appliaction to CDocument class
Hi, I am working on an MDI application. In this aplication, I have some global settings/configurations in the main aaplication (A dialog is used to set the configurations). I am adding Doc/View/Frame to applicatin from different DLLs (by using AddDocTemplate (fnGetMenu (), fnGetDoc (), fnGetFrame (), fnGetView ()) method. In this aaplication, I want to set some parameters in main application, and then pass these paramters to any new document created. Can anybody tell me how can I do this? You could do it in OnNewDocument() m_pSomeData = ((need to cast here to your main app class)AfxGet...

Subforms, Continuous Forms, and Locking Columns oh my...?
Hi, Ok, let me see if I can explain this... We're moving from Excel to Access. In Excel we could lock the first 5 columns in place. Great. In Access the only way to lock columns is to use the datasheet view. The problem with that is the user can change the width of the fields; also, I can't dynamically hide fields. I also have a requirement to have custom field labels. If I use the datasheet view, I'll get the grey boxes with fieldnames. I can blank them all out (I'm willing to live with this), but I can't add a Form Header to the datasheet view which allows me to ...

Column of data
I have a column of numbers that have been copied from another spread sheet and each box has a green triangle in the upper right corner. The column will not sort properly as some of the boxes do not have the triangles in the upper right corner. I can get ride of these by going to the box and double clicking but I have approximately 4000 to do . Is there a way to do this with one command ? Hi, maybe some numbers have blank spaces, try something let's say your numbers are in column A in another column enter =Trim(A1) copy formula down, then copy this column an...

format in MS Access chart data table
iI created a chart from access table. The format of data is stored in percentage with 2 dec. format. When I creat the chart with data table displayed, the 9.8% diplay as .098 in the data table. Even I changed the number in data sheet to %, it appears as 9.8% in design view, but when I switch to the data view, ir changed back to .0980. I managed to change the axis and data label to % format, but it will not work with the data table. I have another chart with currency in 6 dec. format, the data in the data table shows in scientific format. Is anyone willing to help me to fix this ...

how can I post to GL from custom form ?
Hello there is there any guide, or example of a posting from a custom form ? I needs ot have a cusotme form to take some data from another DB or customer tables and then save it in some journal then post it .... ? anybody done that before / or is it a plain crazy idea ? You may want to look at eOne Solutions' version of Extender (Enterprise Edition). It allows one to create forms easily and tie them in to various transactions in Great Plains. They provide a number of sample application that will give you an idea of the functionality and how to build it. http://www.eonesoluti...

OLE Objects display mode in form
I have a form in which the OLE Objects (saved emails) display as only "package". I have the field settings as size mode: clip and Display Type:Icon. Why won't the objects display in the Icon format wth the email file name displayed? ...

Upload & Download Form. pls help
Hi,I need to do a form that I can use to download and upload files from/to my database.first I want to know if this is possible? (the entire database will be on a server).Second thing,how do I do it? After viewing the helpfile on these, does this seem like what your after: DoCmd.TransferDatabase DoCmd.TransferText DoCmd.TransferSpreadsheet "M!$HU" wrote: > Hi,I need to do a form that I can use to download and upload files from/to my > database.first I want to know if this is possible? (the entire database will > be on a server).Second thing,how do I do it? ...

Search for data using 'contains'
Good morning I have two columns of data which differ slightly, and would like to setup a Lookup type of formula that will compare the two columns and return a value when the data from column A is contained in column B. Any ideas? Thanks in advance. JaB- =IF(COUNTIF(B1,"*"&A1&"*"),"found","not found") HTH Jason Atlanta, GA "JaB" wrote: > Good morning > > I have two columns of data which differ slightly, and would like to setup a > Lookup type of formula that will compare the two columns and return a value > whe...

Date validation #4
I need to make sure that the date entered in column B is equal to o greater than column A and that no date can be entered in "b" if "a" i blan -- pata ----------------------------------------------------------------------- patam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=319 View this thread: http://www.excelforum.com/showthread.php?threadid=40148 Use data validation with a custom formula of =AND(A2<>"", B2>=A2) -- HTH RP (remove nothere from the email address if mailing direct) "patam" <patam.1uq...

Delete row where there is duplicate data in Column E
I have a report I download every day and each day the row count is different. I need to perform all kinds of editing so that I can run this spreadsheet against another spreadsheet. I created a macro to perform most of the editing and have my page setup, header, footer, font all set in that macro. Now I want to add code that looks for duplicate entries in Column E in 2 consecutive rows, anyplace in the document, and if found delete the first of the two rows. Normally there are about 10 of these duplicate rows where I need to delete the first on of every time it occurs, is this even p...

parsing data
Hello; I have two data string types that I am doing a search on. ex:1 """ACCESS, NON-STANDARD SET-UP CLEAN""" I would like to strip all of the quotes from the front and then the same number of quotes from the back of the string so that it looks like ACCESS, NON-STANDARD SET-UP CLEAN How can I do this, since the number of quotes may vary between search string. also ex:2 """FITTING ALLOW 0.75""""/EA""" Note that in ex:2 should look like FITTING ALLOW 0.75" /EA Thanks for your time and effort in this.....

Aggregating data for a chart
Hello. I wonder if someone can help me. I want to create a bar chart for a stock but the raw data I have is "price" and "time" and I need to aggregate that into high, low close format. The high and low seems simple enough - I created a pivot table and used min and max for each time interval. The part I'm having trouble with is getting the "close" (which is the last price in any given time interval) number for each time interval. It seems like the solution is on the tip of my tongue but I can't seem to get it out. Any help is most appreciated! ...

How can I parse this kind of data...?
I have a field with names in it and I would like to get the first and last names, but the data looks like this. Jim Bob Brown Jim Billy Bob Brown Jim B Brown If I can assume that the first name is Jim and the last name is brown I could live with that. I don't really need the middle part(s). The names are separated by space. I'm using Access 2003. Any great ideas on how to parse this date into first and last names? Thanks Kelvin You can use Instr() to get the first space, or InstrRev() to get the last space. Then use Left() or Mid() to parse the word from the field. Alterna...

Extracting Data from Outlook forms
Hi I would like to create a Form for Staff to complete when they want a file brought out of the firm's archives. Not too much problem creating the form, but I was wondering if it is possible for the data contained in the form to then be extracted to populate a database with the detail contained in the form eg File Name, File Number, Requested By etc etc. Can it be done? Thanks Dave It can be done, but you'll have to write the code to create the database = records. See http://www.outlookcode.com/d/database.htm for examples.=20 --=20 Sue Mosher, Outlook MVP Author of Mi...