Need Help With Rich's Error Trapping Routine

Apr 9, 2007

Hi guys, I was looking for a way to trap err.number 3314 (when required field is null) before Jet generates its warning. I came across an old post from Rich (below), but I couldn't make it work as yet. In the calling form, under the Form_Error event I wrote the following:

Dim f As Form
Set f = Me
Call fnValidateForm(f)

Could anyone please tell me where my error lays here? I have several forms which have several text and/or combo boxes bound to required fields and I would want to have a generic code, like the one here to trap errors before Jet shows it's Error Message.

Thanks in advance
Regards
Jaime Premy - Belém-Brasil

******************Rich's Function********************
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
Dim Msg, Style, Title, Response, MyString
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") > 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If

If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If
End If
End If

Next
End Function

View Replies


ADVERTISEMENT

Simple Error Trapping By Error Code

Dec 6, 2005

hi all

i have the following peice of code ...


Private Sub NextApplication_Click()
On Error GoTo Err_NextApplication_Click
DoCmd.GoToRecord , , acNext

Exit_NextApplication_Click:
Exit Sub

Err_NextApplication_Click:
If Err.Number = 2105 Then
MsgBox "Cannot navigate to the next record. This is the last record."
Else
MsgBox Err.Description
End If

Resume Exit_NextApplication_Click

End Sub


but even when this error occurs nothing is being properly handled the way i specified - any ideas ?

View 3 Replies View Related

Error Trapping

May 14, 2005

I have the following in the after update event in a Combo bound to a tbl on a Main form;
Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Vendor] = '" & Me![Combo8] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The Combo gets its data from a table and I use the Not In List to add data which is not in the Tbl and of course Limit To List is set to Yes.
It works ok with one exception, when data is entered, but not saved, then deleted And the form closed. In this set of circumstances I get an error in the code above. Granted that normally a user will not delete data from the combo then exit the form but it does happen occasionally. I have tried adding On error goto.... but with no luck. Any suggestions?

View 2 Replies View Related

Trapping Error 3022

Jul 31, 2005

My table has an auto generated key as a string.

So on the offchance that two clients are trying to autogenerate a key at exactly the same time, I'm trying to trap the 3022 error raised when a duplicate key is entered so that I can repeat the operation with a goto.

I'm raising the error with requery like this:


DoCmd.Save acForm, Me.Name 'next line triggers 3022
DoCmd.Requery


After I realised that the normal handler wouldn't trap it I got the database from this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=77529&highlight=3022

But it doesn't seem to work for me:

http://www.imagedump.com/index.cgi?pick=get&tp=288631

Anyone?

View 12 Replies View Related

Trapping A Validation Error

Jun 22, 2006

Hi,

I have a VBA code that runs an append query to update a table. Due to relationship integrity a validation error shows (a type does not exist in the connected table): What I want to do is trap the error and write something that the user can understand. Help !


The Code:
Code:Private Sub Report_Deactivate()Dim Msg, Style, Title, Response, MyStringMsg = "Click OK to Import Verified Data to the Invoice Table"Style = vbYesNo + vbMsgSetForegroundTitle = "Verify Import"Response = MsgBox(Msg, Style, Title)If Response = vbYes Then 'This is the problem line DoCmd.OpenQuery "qry_Step_5_appending_invoices", acViewNormal, acEdit DoCmd.OpenQuery "Qry_Step_6_update_fob", acViewNormal, acEditElse MyString = "No"End IfEnd Sub

appreciate any help, thanks

View 6 Replies View Related

Error Trapping Problem

Jan 2, 2007

I have a "homemade" switchboard that I am using and the first page allows the choice of administrator or user. I have used access user level security to deny all but administrators access to the administrator "path" in my switchboard.

When a user clicks on it, they get the runtime error 2603 saying that they don't have access. I am trying to modify the on error event so that the error won't show up and a custom message box I create will show up instead, however I cannot seem to get my on error code to do anything. I always get the standard access runtime error return box. Does anyone have any ideas??

View 1 Replies View Related

Error Trapping Problem

Aug 6, 2007

I have an "Error trapping" problem.
I am relatively new to VBA and wondered if someone would be so kind and point out the error in my code.

I have a form "frmSelectInvoices" that opens another form, "frmInvoice".

I use the Select Invoices form to select completed jobs that are not invoiced, based on a query.

The user would then select a job that he/she wants to invoice, and "frmInvoice" opens in front of "frmSelectInvoice".

"frmInvoice" is based on "tblInvoices" and a couple of the required fields, (customer, JobID, etc) are filled-in automatically using the information from a couple of fields from "frmSelectInvoice"

"frmInvoice" also has an InvoiceNumber field which tblInvoices needs as it is set as a required field with no duplicates, (this is NOT the primary key).

I am trying to notify the user that the InvoiceNumber has already been used, and to reset the record, so they can enter a new Invoice Number.

The code I have so far is as follows;
Code:Private Sub Notes_Exit(Cancel As Integer)On Error GoTo Err_SameNumber Dim iResponse As Integer iResponse = MsgBox("Do you wish to Print an Invoice", vbYesNo, "SELECT AN OPTION") If iResponse = vbYes Then DoCmd.RunCommand acCmdSaveRecord Forms!frmSelectInvoice!Invoiced.Value = True Forms!frmSelectInvoice.Requery DoCmd.Save acForm, "frmInvoice" DoCmd.OpenReport "rptInvoice", acNormal, "", "[pkInvoiceID]=[Forms]![frmInvoice]![pkInvoiceID]" Me.btnClose.SetFocus Exit Sub End If If iResponse = vbNo Then MsgBox "You can Print it out later from the Invoices Main Menu", , "You Selected NO" End If DoCmd.RunCommand acCmdSaveRecord Forms!frmSelectInvoice!Invoiced.Value = True Forms!frmSelectInvoice.Requery Me.btnClose.SetFocusExit_SameNumber:DoCmd.RunCommand acCmdUndo DoCmd.GoToRecord , , acNewRec Me.fkBookingID.Value = Forms!frmSelectInvoice!pkBookingID.Value Me.fkCustomerID.Value = Forms!frmSelectInvoice!fkCustomerID.ValueMe.InvoiceNumber.SetFocusExit SubErr_SameNumber: MsgBox "Invoice Number has already been used", vbInformation, "Please use a New Invoice Number" Resume Exit_SameNumberEnd Sub
When I test the form with a number I know already exists, or even a number that has not been used before, the error message comes up and it then sets the focus to InvoiceNumber, but when I try and close the form it still keeps coming up and the only way I can get rid of it is to close the application using Windows Task Manager.
Any help on this would be appreciated.
Thanx

View 2 Replies View Related

Cancel Query / Error Trapping

Jul 24, 2007

Ok, I have a delete query that I use to delete records form a table. I have created a form that I run that query from. It has Delete button that runs the query. After I get the message that says "You are about to delete 56 records" and I click No, I get a "run-time error '3059'. Operation Cancel by user"

I did some searching on the forum and thought I could trap this error. But I still continue to get the error at

"DoCmd.OpenQuery stDocName, acNormal, acEdit"

What am I doing wrong and how can I prevent this error from appearing when I click NO or cancel the operation.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Dim stDocName As String

stDocName = "qryDeleteRecords"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdDelete_Click:
Exit Sub

cmdDelete_Error:
Select Case Err.Number
Case 3059
MsgBox "You have canceled the delete operation.", vbOKOnly, "Delete Canceled"
Exit Sub
Case Else
MsgBox "Error number: " & Err.Number & " has occurred. " & Err.Description, vbOKOnly, "Error"
End Select

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

View 3 Replies View Related

Navigation Button Error Trapping

Jun 1, 2005

I've made my own record navigation buttons on a form. they work fine, except when i get to the first record. if i click 'previous' then the 2105 error pops up "you can't go to specified record". the buttons were created through the wizard, and have error trapping in them. for some reason, it never actually goes to the error handler. the error message comes up, i press debug, and the 'DoCmd.GoToRecord , , acPrevious' is highlighted.

any ideas why the error trapping isn't working? here's the code:

Private Sub cmdPrevJob_Click()
Dim x As Variant
On Error GoTo Err_cmdPrevJob_Click
DoCmd.GoToRecord , , acPrevious
Exit_cmdPrevJob_Click:
Exit Sub
Err_cmdPrevJob_Click:
If Err.Number = 2105 Then stop
MsgBox Err.Description
Resume Exit_cmdPrevJob_Click
End Sub

View 14 Replies View Related

BeforeUpdate Error Trapping Not Working

May 11, 2006

I've got my form working, and all of the record updating is working fine, so now I am working on error trapping. I need to check if any of the two textboxes are empty, or nothing has been selected from the combobox. I am using the BeforeUpdate method. I am not getting any syntax errors. I am using an INSERT INTO SQL statement. Thanks.

related DB fields:
SOPNumber (text)
RevisionNumber (text)
TrainingDate (date/time)


Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.cboSOPNumber) Then
Cancel = True
MsgBox "The SOP number is required", vbOKOnly, "Notice"
Me.cboSOPNumber.SetFocus
End If

If IsNull(Me.txtRevisionNumber) Then
Cancel = True
MsgBox "The Revision Number is required", vbOKOnly, "Notice"
Me.txtRevisionNumber.SetFocus
End If

If IsNull(Me.txtTrainingDate) Then
Cancel = True
MsgBox "The training date is required", vbOKOnly, "Notice"
Me.txtTrainingDate.SetFocus
End If

View 7 Replies View Related

Modules & VBA :: Error Trapping For Data Validation

Feb 15, 2015

I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:

Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).

Or there are column name spellings and in such cases, I get no error and the simply code hangs.

Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.

'Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb
On Error GoTo Macro1_Err
DoCmd.SetWarnings False
' RunSQL executes a SQL string

[Code] .....

View 7 Replies View Related

Modules & VBA :: Running Multiple Queries In A Routine - Error Handling

Oct 9, 2013

I am using access 2010. I use basic error handling in my routines:

Code:
On Error GoTo errHandler ... exitHere: ... errHandler: MsgBox "Error " & Err.Number & ": " & Err.Description

The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.

View 2 Replies View Related

Forms :: Trapping Date / Time Field Error

Jun 22, 2013

What's the best way to trap the error I get when I don't input the time correctly in a date/time control?? I have a the following as an input mask: 99:00 >LL;0;_...02:30 PM

View 3 Replies View Related

General :: 3075 Error When Using Rich Text Highlighting

Jul 29, 2013

My error: Syntax error (Missing operator) in query expression

Followed by <div> and other HTML code. Ending with a "3075".

I get this error after adding highlighted text to my memo fields. No other rich text formatting seems to trigger it. Specifically, the error is triggered when moving from my subform to another subform or the main form.

View 2 Replies View Related

Routine Name

Jun 2, 2006

Is there a set variable or property that can tell you which Sub you are in?

I want to use this to log errors. i.e pass the module and sub routine name to the error handler log file.

The only way I can do this at the moment is to physically hard code the sub routine name into each error handler.

View 2 Replies View Related

Need Help With Export Routine

Nov 17, 2006

Ok I am exporting some data to a plain text file and one of the fields I am outputing can vary in the number of characters and I need they to have the same number of characters all the time.

Ex: 123.00 => 000012300
456789.00 => 045678900

I had heard of a command, but I can't remember what it was...I could do a loop till LEN() = 9 but that would cause undue clock cycles (aka performance hit) on the machine when running through the export routine.

This is a huge export of data.....

Thanks,
Richard

View 4 Replies View Related

Unique Username Routine

Jun 8, 2006

Hello,
I was wondering if you have one or have seen a routine to generate unique
username from first, middle and last names. Here is my requirement -
by default first initial and last name of a user is their username.
But if the expected or default username is already taken then if the
user have middle initial, then the username for this user would be
first initial, middle initial and lastname. If the user does not have
middle name then the username for the user would be first two letters
of first name and lastname and if that's already taken then first
three letters of first name and last name and so on.

Any help or pointer is greatly appriciated.

View 2 Replies View Related

Scheduling Access To Open And Run A Routine

Mar 6, 2006

Hi,

I was wondering is it possible to schedule in windows somewhere to open up my syncfrontend.mdb and then run a routine that i have written inside of that mdb.

The routine is a huge database syncronisation task that needs running every hour, it's all there ready to be run but just need a way to get to it and run it automatically on the hour every hour.

Any ideas or help would be greatly appreciated!

Thanks in advance

View 2 Replies View Related

Forms :: Controls Not Repainting After VBA Routine

Jul 23, 2014

Access 2007

I have a subroutine which cycles through all controls on a form, and for controls with applicable tags, either enables or disables the control.

The routine accepts a form as the parameter (frm), and is called from a button on the form itself.

Despite trying frm.repaint in the generic routine, and, me.repaint from the calling form, the controls do not seem to repaint until the mouse moves over them. A command button, for example, will be grey-ed out, despite being enabled, but will un-grey when the mouse goes over it.

View 8 Replies View Related

Modules & VBA :: Passing Variables To A Sub Routine

Jan 13, 2014

I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.

Code:
Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub

[Code]....

View 4 Replies View Related

Modules & VBA :: Calling Function From Sub Routine

Aug 10, 2013

I have 28 combo boxes on a form, which I want to insert data into the table as they are changed. Each one will pass the same sets of data just with different parameters which come from the form.

Rather than putting the same code to insert on each of the 28 combo boxes I thought it would be easier to create a function to do it and pass the parameters to it through a sub on the AfterUPdate event of the combo box.

I need to pass 4 parameters, if I only put 1 in there it works fine, but when I start putting more in it doesnt work and I get compile errors or syntax errors.

Sub routine:

Code:
Private Sub cboMonday1_AfterUpdate()
If Me.cboMonday1 = 1 Then
Me.cboMonday1.BackColor = vbGreen
Me.cboMonday1.ForeColor = vbBlack

[Code] .....

View 9 Replies View Related

Trapping Unique ID

Nov 8, 2005

I've a form in which one of the fields the user enters is supposed to be a unique id. The unique id is an alphanumeric text box.

However, it's possible that another user enters an id that has already been previously used.


I'm thinking that I need to run a Find Duplicates Query with the text box entry and a table that holds all the previously entered ids prior to the form being saved so that I can inform the user of the possible duplication.

My question is are there any ideas on how I can do this or code it?

Thanks

View 2 Replies View Related

Change Output File Name During Send Object Routine

Apr 3, 2007

I have a query that runs based on a parameter a user selects on a form.

I want to dynamically create the sendobject file name based on the parameter the user selects.

Example:

My form has a parameter named medicine. When the user checks the checkbox next to medicine it runs a query for records labeled as medicine but sends the e-mail output as the query name. I want something like medicine.txt etcc...

thanks

:confused:

View 1 Replies View Related

Modules & VBA :: Routine To Output Access Report To PDF Using Fields

Jul 3, 2013

I have a routine that outputs an MS Access report as a pdf file which works OK. It does not open the file which is what I want.

DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents
ptTransactionInvoiceExc VAT.pdf", False

What I would like to do is output the pdf file with a Customer name and number from the field Me.txtAmazonBuyer. I have tried:

DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents" + ([Me.txtAmazonBuyer]) + ".pdf", False

But no joy.

View 7 Replies View Related

Tables :: Calling Subroutine From VBA Routine To Append Record

Jun 21, 2013

I am trying to call a subroutine from a VBA routine to append a record to an existing table. Below is the code for opening my recordsets for reading followed by the code creating my table to append to:

Public Sub UpdateLotsizes()
Dim curDatabase As DAO.Database
Dim ProdPlanDB As DAO.Database
Dim rstQryLot As DAO.Recordset
Dim rstInvLoc As DAO.Recordset
Set curDatabase = CurrentDb

[Code] ....

To call my subroutine, I simply put in the code:

ExceptionReportAddRecord

And this is the code of the subroutine:

Public Sub ExceptionReportAddRecord()
Dim rstExcRpt As Recordset
Set rstExcRpt = curDatabase.OpenRecordset("tblExceptionReport")
With rstExcRpt
.AddNew
.Fields("item_no").Value = txtItemNumber

[Code] .....

I am receiving "Run-time error 434: Object required" at the line I marked with ...

View 4 Replies View Related

Modules & VBA :: Delete And Insert Data From A Table In One Routine

Oct 10, 2013

Access 2010. Can vba code be written to delete and insert data from a table in one routine.

Something like Delete * Insert into select from where order by....

I tried it with a query but have to write two.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved