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 Replies
ADVERTISEMENT
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
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
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
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
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
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 5 Replies
View Related
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
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
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
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
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
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
Nov 7, 2006
Hi there,
I like having the action query warning appear with what I am doing - "You are about to update xxx rows". The problem that I am having is that when my RunSQL statement in the code executes and selects no to the warning, I get the error -
Run-time Error '2501'
The RunSQL action was canceled.
How can I trap the no selection so that this vba error does not appear?
View 1 Replies
View Related
Jun 29, 2005
Hi guys i got a form and sub form. When i leave the field hours empty in the sub form and try to click on close or some where in the form i get this error:
MsgBox "Primary key violation", , "Explanation"
instead of
MsgBox "Violation of Not Null constraint", , "Explanation"
i want to trap not null error and prompt the user that he has to enter value for hours field but aparently it does not. i be happy if some one help me fix this error .Thanks
picture of form in action:
http://i5.photobucket.com/albums/y1...tnullerror1.jpg
http://i5.photobucket.com/albums/y1...tnullerror2.jpg
code for on error :
Code:Private Sub Form_Error(DataErr As Integer, Response As Integer)Select Case DataErr Case 3162 MsgBox "Violation of Not Null constraint", , "Explanation" Case 3146 MsgBox "Primary key violation", , "Explanation" Case 3155 MsgBox "Trigger violation: 'Wages of the employee in this project is not existed. You must enter data of hourly wage first'", , "Explanation" Case Else MsgBox "Other errors" End SelectEnd Sub
View 1 Replies
View Related
Sep 26, 2013
We are currently developing a simple Point Of Sale Form which consists of 2 text boxes and a list box.We need to trap numeric input from either the keyboard or a barcode scanner at form level,then setfocus on a textbox and enter the input into the textbox.So far have used the keydown event on the form to trap numeric input and setfocus using the following code.
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode >= 48 And KeyCode <= 57 And KeyCode >= 96 And KeyCode <= 105 Then
Me.Input.SetFocus
End If
End Sub
This does not however enter the keyboard or scanner input into the textbox.Is there a method to trap the input,set the focus and enter the input into the textbox once it has recieved the focus.
View 2 Replies
View Related
Apr 7, 2015
I am writing a small database using MS Access 2007. I have in it a function for running queries and printing reports. Printing works just fine if any one of the multiple query criteria is give.
I also want to trap errors and present a message if the user clicks the "Print" button without a query criteria. At the moment I only get a run-time syntax error message which has a "debug" button for accessing the entire code. I want a message that will say "There was no search criteria".
This is the code for printing:
Code:
Private Sub cmdPrint_Click()
Dim varWhere As Variant
varWhere = BuildFilter
' Update the record source
Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere
[code]....
View 7 Replies
View Related
Jun 7, 2006
:confused: Anyone know how to trap/prevent the msg "The DoMenuItem action was cancelled" after answering no to deleting a record? I followed the instructions at the following thread but no luck:
http://www.access-programmers.co.uk/forums/showthread.php?t=53206&highlight=DoMenuItem+action+cancelled
Here's the code:
----------------------------------
Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Delete_Click:
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub
-----------------------------------
Private Sub Form_Delete(Cancel As Integer)
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbNo Then
Cancel = True
Else
MsgBox "Record deleted!"
End If
End Sub
-------------------------------------
Thanks in advance.
View 2 Replies
View Related
Jan 28, 2004
Hi,
Im new to asp and access and have been having this problem for serveral weeks.
Every couple of days, all the asp pages on my site that communicate with the database start having 500 internal errors. i turned off the "Show friendly error messages" and one page gave me this specific error:
Microsoft JET Database Engine error '80004005'
Unspecified error
/admin/submitlogin.asp, line 8
I have tried a million things and have no idea why this is happening. Im not sure what other information i should post in order to see the problem. Any help would be greatly appreciated. Thank you,
Patrick
View 3 Replies
View Related
Jul 13, 2012
How I can get rid of Disk or network error with error code 3043? What this error indicates.
View 4 Replies
View Related
Mar 31, 2006
Does anybody know what this error message refers to?
"Reserved Error (-1517); there is no message for this error."
It just started happening today, and I haven't even made any changes to the database. It occurs when I hit a button I have to run a macro.
the macro does the following:
1) Shows all records
2) Requery
3) ApplyFilter.
The Where Condition for the filter is:
Right([tblContracts].[JobNum],4)=Right([Forms]![FrmContProc].[txtFindJobNum],4)
The weird thing is that it only occurs if the Form window is taller than 1/2 of my viewable area. If the Form window is 1/2 the viewable area or shorter, it works OK. This was running fine earlier today, but about 4:00 pm (03/31/06) this started happening.
If anybody knows what this error means, or how to get rid of it (I really need to use this window in full-screen) then please let me know.
-Thanks, Sean
View 10 Replies
View Related
Aug 3, 2006
Okay, I'm kind of stumped here.I have a subform that has a button that sends a user to a "sibling" subform on another tab page, pass some information to ensure they are adding more details to the same records rather than creating two separate record.First time I programmed it, I got an error 3022 (keys cannot have duplicate values). I checked the query of the sibling subform and saw that the ID is from the one side table. I changed it so many table's foreign key is used. Second try, I got an error 3341 (there isn't a matching key in one side table).After some thinking, it also occured to me that I had set the query this way to allow addition of new record which wouldn't be possible if I had the query pulling the many side key, not the one side key.How do I get the subform to accept the ID that is being passed and create a new record using that ID?:confused:
View 5 Replies
View Related
Sep 1, 2014
Code:
Private Sub Consolidate_Click()
Dim temp As Variant
Excel.Application.Visible = True
temp = Dir(CurrentProject.Path & "Inputs")
Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
View 5 Replies
View Related
Sep 24, 2005
Every form has an on error property.
Is it enough for error handling to code the on error property for each form?
With enough I mean error handling which lets you resume the program.
Ontherwise I have to code (or call a procedure) for each coded event which i wouldn't prefer
For instance now I'm putting error handling in each event but would consider it more efficient if it can be placed once in each form
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
Dim stDocName As String
stDocName = "rptOfme"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdReport_Click:
Exit Sub
Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click
End Sub
View 3 Replies
View Related
Feb 1, 2006
Hello All,
I have been developing my database all one seems to be well exept for an error message which is attched.
If anybody can help me trap this error or offer some advice i would be greatfull.
Alastair
View 6 Replies
View Related
Aug 5, 2005
SQL Issue ...
ERROR: Runtime error 3061 - Too few parameters. Expected 1.
------------------------------------------------------------------------
Not sure how to work in the '* ROLL *' into this SQL statement. The query statement works fine ... I have tried different quotation methods ( Not Like " & " '
* ROLL * & ' " & " ) AND .... )
sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([DT: InventoryExtend].Description) Not Like '* ROLL *') AND (([forms]![signpro sign estima parameters]![combo14])=1));"
ANY HELP WOULD BE APPRECIATED ...
Cheers,
QTNM
View 14 Replies
View Related
