Query Works But With Errors?
Jun 25, 2007
I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
Is this the query? Or the coding?
Thanks
View Replies
ADVERTISEMENT
Aug 5, 2014
I'm scanning bar codes into the record search box, most of the time it works as it should but occasionally I get multiple "cannot open any more databases" errors. It still finds the product record but the error messages are super annoying. I am sure to clear the memory on the scanner each time before I scan in a new barcode but it doesn't seem to matter. Also sure to only have one or 2 forms open at a time, that doesn't seem to matter either. Most of the time I don't get the errors but when I have to click through like 10+ error messages (all identical). I only have one database open so I'm not sure what it even means. Also I have tried this in 2 different forms and it's the same for both forms. Seems like I get the errors in my initial scan and then after clearing them it seems to work without errors.
View 4 Replies
View Related
Oct 4, 2006
Hi,
I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));
View 5 Replies
View Related
Oct 1, 2007
i posted about this the other day, thought i sorted it and now it keeps coming back to haunt me
i have an append query in my database it is based on 3 other queries
when the query is run using vb
DBEngine(0)(0).Execute "QRY_ImportDuplicatesTopLevel", dbFailOnError
or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL
i get an error message saying too few paramters - expected one
i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field
the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do
does anyonw have any idea why this situation could occur?
View 4 Replies
View Related
Jun 27, 2014
I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)
here's the problem:
when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.
to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100
when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100
some details:
the average field has this code: Average: CInt(Nz([Grade]))
the query looks like this:
SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));
I'm on access 2007
View 4 Replies
View Related
Oct 19, 2011
I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.
I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.
View 8 Replies
View Related
Aug 23, 2013
I am creating a search function to search a name. The DB stores it as [First Name] , [Last Name].... This query works
Code:
SELECT *
FROM tblVolunteers
WHERE ((([First Name] & " " & [Last Name]) Like "*bob j*"));
It will show me records for Bob Johnson, Bob James and Bob Jones....
I am trying to duplicate this with a DoCmd.OpenForm statement... Replacing the static name with a field variable...
Code:
Dim whereClause As String
whereClause = "[First Name]" & " " & "[Last Name] Like " '*" & volunteerName & "'*""
DoCmd.OpenForm "Volunteers", acNormal, , whereClause, , , False
View 4 Replies
View Related
Nov 7, 2012
I have a Report that uses a query as it's record source. I have the query ordered by a field, which works as expected in the table view of the query. In the report, however, the ORDER BY clause does not seem to carry through. The field is not sorted Ascending. What's going on?
View 5 Replies
View Related
Jul 26, 2007
Hi all,
Resolved the date range issue, thanks, now I have a problem with the query results.
I have a query based upon several other queries that have the StaffID and the linking factor. When I combine some of the queries it works fine, but with others, seemingly those that have no valves or data in the tables, the query brings back no results what so ever.
The queries are running a simple count of records so they have, StaffID input, date range and it counts the records for that staff member within the date range. The second query gives a sum of the count of the first query.
But when I try to combine the queries so that I can produce a report for multiple work areas for audit, I hit problems, is there another, easy way for me to do this, maybe creating a report from invididual query results and only combining them for the report itself.
HELP! Can not attach actual db because of sensitive nature of the information contained, but would be willing to create a like db as sample.
Thanks for any assistance!
View 3 Replies
View Related
Aug 7, 2013
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
View 3 Replies
View Related
Sep 7, 2005
I am running a report based on a query. When the query returns zero rows, the count function in the report gives an error. Probably a simple solution but I've searched and can't find an answer. What do I do to stop the report when there is no data?
View 1 Replies
View Related
Feb 17, 2008
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
View 2 Replies
View Related
Jun 19, 2013
I have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit
Table: Master Log Data
Criteria: 1 or 2 or 3
Works fine!
Field: Unit
Table: Master Log Data
Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
View 1 Replies
View Related
Feb 21, 2008
I am using this function
Expr2: Mid$([ItemInventoryAssemblyRefFullName],(InStrRev([ItemInventoryAssemblyRefFullName],"-")+1),(InStr([ItemInventoryAssemblyRefFullName],":"))-(InStrRev([ItemInventoryAssemblyRefFullName],"-"))-1)
to parse this string
4150-ATH:GRP_54X8/BL
into this
ATH
It works great until I get to a string that looks like this
4110-SPECIAL:OLY:LONG-TRACK:REPAIRS
Which I need to return the word SPECIAL but it gives me #Error in the Query.
It would seem that when there are multiple : it is giving the error but when there is only one it works fine. Is there anyther way to atchieve what I am doing?
I am doing this in an update query to get data from Quickbooks into a table in Access.
View 2 Replies
View Related
Nov 14, 2013
I am working on a database that contains patient demographic information. I have a form that prompts the user to enter either the medical record number or part of the patient name. Once you click search it'll then display a 2nd form with a list box outputting the results. Then from there the user can click on one of the entries in the listbox and it'll display the full demographic information on a 3rd form.
Now my problem is on the search part, it completely ignores if I have a medical record number entered. It continues to search by name only.
In my query for med rec # I have
[Forms]![frmSearch]![txtHistn]
where txtHistn is the text box field passing into the query. And for patient name column in the query under OR I have the following:
Like "*" & [forms]![frmSearch]![txtPname] & "*"
So I am confused why it ignores the medical record number entirely.
I have a test database attached. This is just a sample with dummy data entered and not designed pretty. Through this up for another issue I had yesterday that has been resolved but now discovered this query one. The frmSearch is how it begins. If you search by Smith it'll bring up the two Smiths I have entered. If I leave the field blank and enter 1 for the medical record number it treats it as null and displays everything. How can I fix this?
And while on the topic of query, the true database I am working with resides on an Power I series (formerly AS400s) and only linking to their tables. The data is entered in all Caps in the tables. How can I force whatever the user enters into the search screen that it will automatically uppercase the letters before performing the search? Without having the user to remember to enter with their cap locks on.
View 14 Replies
View Related
Dec 3, 2014
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
View 9 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
Aug 23, 2005
There is an Access application somewhere in shared drive.
100 users open this application using a batch file which copies this database on their c: . They start using this database and the master database gets replicated real time. I am not sure if all the database are replicated as it is copied everyday ( so that each one of them has latest version of the application )
Is it possible ( I know it is :) ) but i am not able to figure out how it is happening.
Please help :confused:
View 2 Replies
View Related
Dec 9, 2005
What would trigger an error to occur if there has been no changes to a DB.
My error # is 2427 (You entered an expression that has no value).
This error occurs when I click on a command button to open a report. When I debug, it sends me to an IF statement that I have loaded in the On Format of the report.
This worked perfectly fine before - the If statement is simple, if a value is true, then it changes a box to bold and if the value is false, the box in the report remains the same weight.
I am not understanding why it is saying that I have entered an expression with no value when the IF statement reads both the true and the false of a chkbox and adjusts the box accordingly.
Can someone explain why this is occuring? Im litteraly stuck.
View 14 Replies
View Related
Dec 27, 2005
Hi,
I have a form which has two separate subforms in it. When the user selects a record in the first subform, I want the values of that record to write to the second subform's fields. OnCurrent works when there is more than record in the subform. My problem is two fold, one I don't want my copy code to run when the form loads and I need it to copy a record when there is also only one record in the subform.
Which event do you recommend?
Also, does anyone have a suggestion on how to allow the user to tab through a form, but really only record by record (with the keyboard) and not having to go through all the fields before getting to the next record.
Thank you,
Colette
View 1 Replies
View Related
Jun 30, 2006
Hi, I'm a little confused here. I'm using the following on one DAP and it works just fine. When I add it to another page, it does not work. Any ideas?
<SCRIPT language=vbscript event=onclick for=Save>
MSODSC.CurrentSection.DataPage.Save()
msgbox "Record Saved - you may continue to edit or exit your browser.",64,"Saved"
</SCRIPT>
Thanks...Doug
View 1 Replies
View Related
Apr 18, 2006
Hi All,
I have a text box (memo field) that the user enters a description. This is sometimes more then one paragraph. In a text box you cant hit enter and goto the next line (or paragraph). I would like to to this? can it be done?
Thanks
View 1 Replies
View Related
Apr 18, 2006
Hi All,
I have a text box (memo field) that the user enters a description. This is sometimes more then one paragraph. In a text box you cant hit enter and goto the next line (or paragraph). I would like to to this? can it be done?
Thanks
View 5 Replies
View Related
May 12, 2005
Can someone please help me. I was in the code view of my database and accidentally deleted a few lines of code and now everything i do is coming up with the same error message
Module Not Found
For every form I try to open it does this and even my buttons to close form and to exit system do not work.
Has anybody had this problem before?
Would anybody mind taking a look at it if I sent a zipped version? my email and msn is nicholaseary@hotmail.com
View 2 Replies
View Related
Aug 16, 2005
I have installed on my PC, WindowsXP sp.1, MSaccess versions 97 and XP.
After I have tested some databases, MSaccess 97 don't works successfully more, but XP version works good. I can't modify files mdb. Access works like in run-time mode: bars and commands reduced, standard icon of access absent, database window absent, ecc.
If macro autoexec or a form that play at the start of mdb are present, mdb works but it is impossibile to modify it. Otherwise mdb dont works, I can see access window with only menu files and window.
I have removed access and after yet installed it, but it don't work successfully.
maybe other application leaved files (or modification in file registry) that install procedure of access97 cant rewrite? And that dispose access 97 to work in run-time mode (or like)?
I am hopeless. Can You help me? Can you give me a list of files to remove, or list of modification to do in registry?
Danit :
View 4 Replies
View Related
Oct 24, 2005
I'm using Access 2000 along with Oulook 2003. Since we changed email servers, the send report no longer works. The addresses no longer appear to be valid. I can manually put all the names in every time an email is sent, but that is defeating the purpose!
I even tried changing the addresses to what an outsider would use rather than our internal name list; (milko@valspar.com instead of Mary Ilko) but this didn't work either.
Here's the code:
Private Sub cmdMailApproval_Click()
OnError GoTo Err_cmdMailApprovalClick
Dim stDocName As String
stDocName="rptForApprovalReport"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acViewPreview,, "[Batch#]=forms![frmInbound]![Batch#]"
DoCmd.SendObject acSendReport, StDocName, acFormatSNP, "FirstName Lastname",,,"Pre SHip Approval", "Please see the attached."
DoCmd.SendObject
Thanks in advance for your help!
Mary :o
View 1 Replies
View Related
