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 Replies
ADVERTISEMENT
Dec 10, 2014
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
View 4 Replies
View Related
Aug 6, 2015
I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried
Code:
On Error Resume Next
but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.
Code:
Dim mydatabase As DAO.Database
Dim rst As DAO.Recordset
Set mydatabase = CurrentDb
Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset)
Dim rsBN As String
Dim rsDose As Double
[code]....
View 4 Replies
View Related
Jan 12, 2014
I am studying error handling. I am using VBA Developers Hand Book and some Google stuff. I have been over the material several times and cannot find my mistake. When I click cmdFindState it give me "compile error" " label not defined",and stops at:
Code:
"On Error GoTo Err_cmdFindState_Click"
Code:
Private Sub cmdFindState_Click()
On Error GoTo Err_cmdFindState_Click
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
'-----------------------------------------
' 51 Vermont
'------------------------------------------
DoCmd.GoToControl "ComboState"
[Code] .....
View 14 Replies
View Related
Aug 19, 2013
(I'm not sure if this belongs in the macro or query forum)
I have a macro that opens 15 queries and ends with opening a report.
The final query asks the user to type in beginning date, then an end date. Now when there's a data mismatch (i.e., he spells feburary), the macro halts. No harm no foul, they can just click the command button again, but I'd rather not.
What I'd like to do is place a msgbox (i.e., "Check Your Spelling") and then reroute them back to the query.
View 2 Replies
View Related
Aug 14, 2014
I am setting up a form to send multiple attachments to an email.I have a query "qry_DwgEmail" that has an ID and a path column. On the form I have a button with the code below in the Click Event.The code below all works fine unless the path is incorrect, so I would like some error handling to be able to either:
1) Stop the code from running and display a message box with my message.
2) Continue the loop and then have the message box display what files could not be found (Preferred).
Note, the following code was taken from one of the forums I am not 100% on what it all means.
Code:
Private Sub btn_CreateEmail_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
Dim TheAttachment As String
[code]....
View 3 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 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Dec 20, 2005
I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly.
Thanks,
a_brooks
View 5 Replies
View Related
Jan 16, 2014
I have vba code that creates the following SQL:
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
View 4 Replies
View Related
Aug 18, 2015
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
View 8 Replies
View Related
Mar 21, 2014
I am having an issue with my running total query.
It consists of a running total per vehiclenum. All data comes from one table.
It works properly only on the first vehiclenum of the query. After that, the first "previous" odometer reading of each subsequent vehiclenum starts at some erroneous number, throwing the remainder of each vehiclenum running total.
Here is the code for the query,
SELECT qry_ODO_TotalSub.ID AS OdomAlias, qry_ODO_TotalSub.ODate, qry_ODO_TotalSub.VehicleNum, qry_ODO_TotalSub.Odometer, Nz(DLast("Odometer","qry_ODO_TotalSub","[ID] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference, Nz(DFirst("Odometer","qry_ODO_TotalSub"),0) AS StartOD, [Odometer]-[StartOD] AS RunningSum
FROM qry_ODO_TotalSub
ORDER BY qry_ODO_TotalSub.ID;
View 4 Replies
View Related
Mar 16, 2013
Query is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
View 3 Replies
View Related
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
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
Nov 23, 2006
I have recently upsized an Access database to SQL Server. The .adp is for the most part working fine.
However, when an end user tries to create a duplicate record in one of the Primary key columns of a Form he gets a SQL Server error message as follows:
"Violation of Primary Key constraint. Cannot insert duplicate key in object myTable".
This is obviously not a very useful error message. What would be the best way to ensure that a more useful error message is returned?
I have considered adding code to the "After_Update" event of the cell in question. This however seems long winded. Is there an easier option?
Thanks
Kabir
View 1 Replies
View Related
May 12, 2005
Hi all
The following code only half works. I want to stop an ugly error occuring when primary keys are duplicated. In the following code the error handler picks up and displays a simple message when a user attempts to add duplicate values. BUT if the users adds a record, the record is successfully added followed with the same error message. I think I've put the error handler in the wrong place, but I can't figure out where it should go. Any ideas are much appreciated.
Damon
Private Sub cmdUpdateGoMaths_Click()
'update Go Maths
On Error GoTo error
Dim adors As ADODB.Recordset
Dim sSQL As String
' sSQL = "Select * from datGoMaths;"
' Set rsADO = New ADODB.Recordset
' rsADO.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
sSQL = "dataGoMaths"
Set adors = New ADODB.Recordset
adors.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With adors
.AddNew
.Fields("EQID") = Me.lstGoMathsStudentName
.Fields("IDGoMaths") = Me.lstUnitNo.Column(0)
.Fields("Score") = Me.txtScore
.Update
End With
Set adors = Nothing
MsgBox Me.txtStudentName & " Added"
Me.lstGoMathsStudentName = ""
Me.txtScore = ""
error:
MsgBox "test"
DoCmd.CancelEvent
End Sub
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
Apr 30, 2006
I currently have a macro that sets the value of several fields on a "lost focus " event; however there is an error that I would like access to ignore, is there any way to have this macro process this function "On Error Resume Next" ?
Carbo
View 1 Replies
View Related
Mar 2, 2005
I have a form where the user enters a year then clicks on a command button to open a report.
If a year that there is no data for is entered an error 2427 occurs.
I have tried putting the following code in the on error event of the form with now success:
Select Case DataErr
Case 2427
MsgBox "There are no records for the year you entered" & Chr(13) & "Make sure you entered the correct year”
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
Does anyone know how to trap this error?
Thanks
View 2 Replies
View Related
Aug 8, 2006
On a form i have a textbox to enter a date and multiple command buttons to open other forms.
After entering a date value, followed by either a tab or clicking on one of the command buttons, i want to validate the entered date value. If the date is invalid i want to display an error message and the cursor positioned on the textbox field.
The LostFocus event of the textbox is as follows:
Private Sub txtDateField_LostFocus()
If Not IsDate(txtDateField.Value) Then
MsgBox "Date invalid."
txtDateField.SetFocus
End If
End Sub
If entering an invalid date is followed by a tab, the error is displayed but the cursor is not positioned on the textbox field.
If entering an invalid date is followed by clicking on one of the command buttons, the error is displayed but the expected form is opened.
How to position the cursor appropriate and how to prevent opening the form?
Who helps?
View 1 Replies
View Related
Aug 8, 2006
On a form i have a textbox to enter a date and multiple command buttons to open other forms. After entering a date, either followed by a tab or clicking on one of the command buttons, i want the date validated. In case of an invalid date an error must be displayed and the cursor positioned to the textbox.
Therefor the LostFocus event is as follows:
Private Sub txtDateField_LostFocus()
If Not IsDate(txtDateField.Value) Then
MsgBox "Date invalid."
txtDateField.SetFocus
End If
End Sub
If entering an invalid date is followed by a tab the error is displayed but the cursor is not positioned to the textbox.
If entering an invalid date is followed by clicking on one of the command buttons the error is displayed but a form is opened.
How to position the cursor correctly and prevent opening of a form?
View 2 Replies
View Related
Oct 25, 2006
How do you do an Access/VBE split screen? I want to be able to watch the code execute as I enter data into the form.
I have redesigned a database, essentially building a new one from scratch. I have all of the forms created and they are working beautifully. :) Now, I'm error-handling to create user-friendly messages. I have a superb Access programming book, but it only briefly mentions error handling and doesn't describe at all how to do it. Most of my forms are pretty simple, and I've been able to complete all of the error-handling tasks using the Form Error event as well as coding other events with the error message number. However, the main form is complex and includes a function in a standard code module and a custom sub routine.
TIA!
AA
View 2 Replies
View Related
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
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
Oct 12, 2007
Just a quick one.
I'm not usre how to handle the error when cancelling a report. so when I hit a control to bring up a report and say I cancel it before I put in any parameters for example I get the dialog box " runtime error" "OpenReport action was cancelled" etc.
Could anyone help me on how to handle this with VBA so that it's at least a more user friendly dialog box?
thanks
View 3 Replies
View Related
