I recently came across this really strange behaviour and was wondering if anyone else knows this bug or knows a better workaround than mine.
I've attached a little Events DB, which has a search interface. I've included an export button, which runs a macro that does an OutputTo (xls) of the search query.
Now if you do an export once, everthing is fine. The bug (at least I think it's a bug) occurs if you export a *second* time and *overwrite* the file you created earlier. The search query (which sits under the "Queries") tab is now empty and you get an error message.
My workaround idea was to dump the query to a new table every time you export and then do an OutputTo with this table. Not very elegant and you also have to do the whole error handling (e.g. user presses cancel) manually.
Does anyone have a better idea? Or am I doing somethng completely wrong and this is actually "by design"?!
I have a simple query based on two fields that saysComponent4 total = no of hours sleep/no of hours in bed * 100where no of hours sleep = 9no of hours in bed = 10therefore component 4 total should = 90 instead it equals 89.999999How can this be?For all the other records the answer is correct down to the very last decimal place.When I right click on the query for this component 4 total and change the format from general number to standard it will correctly respond to 90 but only because the numbers are being rounded up.PS. THe numbers are definitely being stored and recalled as 9 and 10 and arent being rounded up because I inputed it as 9.0 and 10.0.
I enter values into a table with 2 decimal places.
I have a query that applies percents to the values - I have set the results to have 2 decimal places.
I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.
It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.
I have one table that deals with project details. Then I have one table that deals with project history. Obviously each project has a bunch of unique details and several related records detailing the project history.
My goal is to be able to read details of any project along with the last history entry.
1-I have one query that gives me the last history comment. It creates a table. 2-Then I have another query that gives me the details of the project along with all the project history. It creates another table. 3-Then I have a select query that joins the first table and the second table comparing the project history comments and only showing the records that have the same comment. Therefore I get exactly what I need; The details of a project along with the last history comment.
The problem: I get the correct records selected, however 1) if the first entry in the project history table in one or several records is one word, then the query results include those records repeated a random numebr of times. 2)If the first entry in the project history table in one or several records is two words, then the query results include those records repeated a random number of times, but less times. 3) If the first entry in the project history table in one or several records is three words, then the query results include those records repeated a random number of times, but only a few times. 4) If the first entry in the project history table in one or several records is three words or more (on average), then the query results include only the record I wanted once.
What is going on?!??!?!?!
It almost looks like the query is runing once and again until a certain number of words are compared....
I've encountered another weird error when applying an append query through a button. Here's the code:
Private Sub cmdSaveChanges_Click()
On Error GoTo Err_cmdSaveChanges_Click 'Saves the new Inspector information into the information table. 'Adds the two references created by adding a new inspector into the XREF_FILE_INSPECTOR table. 'This is the case that the references are formed by adding a completely new inspector. If (IsNull(cmbInspector) Or Me.cmbInspector = "") Then
Else 'This is the case where the user chooses an inspector from the provided combo box. DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR " _ & "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _ & "(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & cmbInspector.Column(0) & " );" End If Exit_cmdSaveChanges_Click: Exit Sub
Err_cmdSaveChanges_Click: MsgBox Err.Description Resume Exit_cmdSaveChanges_Click End Sub
The problem is, when I run this I get a "Syntax error in INSERT INTO statement". This seems to only be the case for the first part of the IF statement, as the second part's append query works fine.
however, i realised that the format of the excel spreadsheet (excel version 5.0/95) that i have exported is slightly different from the format of the spreadsheet if i had exported by clikcing on FILE, EXPORT...
how can i specify the excel version for the exported file? i wan it to be in the latest excel version else i am unable to perform some of the marcos i have written in the latest excel format?
I’m working on a report that I want to show on the web as a Snapshot file. I created a Macro using OutputTo method to export the report. If I type the file name and location on the Output File it works perfectly, but the problem is that I want Macros to read the file name from a combo box since the file name will change every day. Here is the code I got so far.
C:Test””&FORMS!frm_FORM2!cbo_Name&”.snp”
Every time I run this Macro I got the following message:
“The report snapshot was not created because you don’t have enough free disk space for temporary work files.”
Now!, when I type the file name to that specific location Macro does the job perfectly.
Is there any way I can get the File name from a combo box located in FORM2.
Thank you so very much in advanced. Your help is always a blessing.
Hi, I am having trouble with the OutputTo function. I woul like to Output a report in html format but I would like Access not to record the file under the same name each time. So if my file is named 1_report.html I would like the second time I run the OutputTo function that it is name 2_report.html. Or as an alternative have the name include data from a form/table (In my case an order number).
I have a sample of the code from the converted macro: Code:'------------------------------------------------------------' Output order to file''------------------------------------------------------------Function EnvoyerCommande_EnvoyerCommande()On Error GoTo EnvoyerCommande_EnvoyerCommande_Err ' Envoi la commande DoCmd.OutputTo acReport, "EnvoiCommande", "HTML(*.html)", SaveAs, False, ""EnvoyerCommande_EnvoyerCommande_Exit: Exit FunctionEnvoyerCommande_EnvoyerCommande_Err: MsgBox Error$ Resume EnvoyerCommande_EnvoyerCommande_ExitEnd Function
Note: here I have used the 'SaveAs' instead of specifying a location. This is not really what I want as I would prefer no user interraction...
I want to use Cascade Delete on a main table that has relationships with a few tables. The type of these relationships are weak and the delete will work perfectly I think. But, I am concerned about how I can only delete the FK from one of the tables because it contains a non-prime FK.
For example: Customer buys House. So Customer PK is in House as FK. If I delete customer, I don't want house deleted. Is there anyway that is native to access in how I can only delete the FK from tblHouse without deleting the house record.
They mentioned it sa for Access 97, but I thought it relates to what I was facing. I took off the Modal and Pop-up properties back to 'No'. Still the same problem. Then I substituted the above 2 commands with DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord
But now I get the error message
The command or action 'DeleteRecord' isn't available now..
Have you seen this before? Any help is greatly appreciated.
Some of my users do not have access to all of our servers. In trying to automate I've hit one user whose system hangs up when Access tries to write to a server folder she does not have write (or even read) permissions for (Error 52, Bad file name or number). See the "Me.CitationType > 500" line below.
Code: Private Sub comboStatus_AfterUpdate() Dim hDate As String Dim sFile As String
[Code]....
It gets as far as "If Len(Dir(hDate, vbDirectory)) = 0" and then hangs with Error 52. Testing for Dir() sooner doesn't work either.
how to either test for read-write permissions or trap Error 52. "On Error GoTo ErrorHandler" never fires to even test for 52, unless I'm missing something.
what I really want is for the data to be without all the bars "|" and dashes etc. and even without headers. I just want a straight text output, maybe delineated by spaces or commas only.
I want to run a report, I need to provide an option to export the output to an excel file or provide a print view. Where would I put the DoCmd.OutputTo command in the report to acheive this??
where, in the case of a child, its ParentID contains the PersonID of its parent. In the Relataionships window I put two such tables, Family and Family_1 and I joined Family.ParentID to Family_1.PersonID. I set referential integrity, cascading updates and cascade deletes. I made a Parents form (by setting the ParentID criteria to Is Null), and added a Children subform (linking the ParentID of the subform to the PersonID of the main form).
The cascading updates works when I add a new child but when I try to delete a parent I get the error: Could not update; currently locked. Why doesn't the cascade delete work?
I have a problem where a button is deleting all the records related to one record, I was hoping somebody could help. Here's the problem:
OrderID is an attribute in table tblOrders. It is related (with Cascade update related records) to OrderID in tblTurkeys. In a form with a button which deletes a particular OrderID from tblOrders, when this button is clicked, if there are records in tblTurkeys with that OrderID, the record in tblOrders cannot be deleted. And if I changed the relationship type to cascade delete, the record in tblTurkeys will be deleted. Basically, I want the button to, before deleting the record in tblOrders, make the value of the OrderID attribute of any records in tblTurkey with the same OrderID as the one being deleted to have no value.
Eg. Record in tblOrders is being deleted. It's OrderID is 2. There are two records in tblTurkeys which have OrderID attributes of value 2, so when the record in tblOrders is being deleted, the values of the OrderID attributes previously mentioned as being in tblTurkeys now have no value.
That's what I need it to do! If anyone can help, I'd be greatly thankful!
I have something very bizarre happening that I cannot figure out.
I have a form with a subform on it. These are not linked. On the first form is one combo box, the records in the subform are requeried every time the combo box changes with the criteria for the subform based on the selection in the combo box.
I haven't got these linked because I also have an Edit option group selection on the main form which changes the subform from Data Entry = False and AllowAdditions = False to Data Entry = True and AllowAdditions = True. This allows me to use the form to enter new records or to edit existing ones by changing the edit mode.
I also have a delete button on this form with the generic code to delete a record:
The record source for this subform is a primary table on the one side of a one to many relationship.
When I am in the new record mode (Data Entry = True and AllowAdditions = True) and I delete the record I am currently entering it works as expected.
When I am in edit mode (Data Entry = False and AllowAdditions = False ) and I delete the record I am on. It deletes the record but any record in the many side (the secondary table with the foreign key) is also deleted as well. Now I'd expect it to delete the foreign key on the many side and wipe the field but this actually deletes the whole record the foreign key is in from the secondary table.
I have checked my relationship set up for these tables and they are fine:
One To Many Relationship Referential Inegrity Checked Cascade Update Records Checked Cascade Delete Records Checked
I get the usual access warning telling me it is about to delete one record. usually you would also expect the warning to include the comment about this deletion will also delete any related records in other tables, but I don't get that.
I cannot for the life of me figure out why the whole record from the secondary table is being deleted and not just the related field.
Does anyone have any ideas?
I hope I have explained it properly. (Tis always hard to put into words a problem)
When compacting a particular db deletes 1000s or records from one particular table. I can't see a problem with the data in that table, but I also get error messgaes if I try to append the data from that table into another table or to import it into another db.
We've had to go back to a restored copy and have lost 1 days worth of data. Not the end of the world, but I have no idea what is causing this.
I have a series of reports that are built to replicate the order forms of our suppliers. On each I have a text box for Air Freight that I only want visible when the airfreight ID is ticked.
I have this working fine using an if statement on the Load Event of the reports. However when I use the OutputTo command in VBA it seems to bypass the Load Event. Any event that is triggered using OutputTo.
I have also tried setting the .visible in VBA but can't get the coding to work as I am using a variable report name depending on the SupplierName and when I try to use the Reports! nomenclature it will not accept the Report name being a variable (ie. Reports!ReportName![Airfreight].Visible where ReportName is a variable).
I have a form with a Count textbox. It's control source is a field named "Audit Count" in the table "DB Audits". The idea of the textbox is to display a running count of how many audits a specific auditor has completed that day. To do this I have the textbox set up with a default value of:
Assuming I'm using the DCount function correctly, this is supposed to count the number of [Loan Number] records entered in the "DB Audits" table by the auditor (whose name is found using fOSUserName()) on today's date. So, for example, when opening up the form at the beginning of the day the Count textbox would read 1 and when the auditor clicked Save and New it would increase to 2.
The problem I am having is something I encountered while doing some random tests of the form. If an auditor submitted an audit (let's say the 1st of the day), the form correctly displays the next count as 2. However, if the auditor were to delete the record from the underlying "DB Audits" table while the form was still open, if they were to enter a new record, the Audit Count field would display 2 even though it should be 1 (since the 1st record had been deleted).
How can I have the Count textbox refresh whenever someone deletes a record from the table while the form is still open? I tried a Requery command using the AfterDelConfirm event but I couldn't get it to work.
I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database Dim FocusBln As Boolean Private Sub Identificeer() Me.[Datum Aangemaakt].Visible = True Me.[Datum Aangemaakt].SetFocus If Me.[Datum Aangemaakt].Text = "" Then
I have many queries in a DB. they work fine, but *sometimes* some of my queries are wiped - when i try to run them they say "query must contain at least one cell" or something..
when i check the query then, its empty!
I have saved out the SQL code into a word doc because this has happened a few times now.. so i just copy and paste the query back in - but this is very inconvenient! what is going on?
I have 2 unbound boxes in the footer of my continuous form. Both total up separate sets of fields using DSum and do it correctly. I have used Conditional Formatting on Total1 to make the numbers turn bold and red whenever Total1 is greater than Total2.
Here comes the weird part. It will always work whenever Total1 is greater than Total2, but sometimes when Total2 is bigger, Total1 is still red, even after I attempt to modify it and it requeries, or I go to a different record and come back. Any thoughts??
I have a weird issue going on...I'm running a "Find Duplicates" query on one of my tables, I'm only checking for duplicate values in 2 fields and outputting a third. now this runs fine, unless I use the total's button to group them. When I group them that access instance stops responding. The reason I'm doing this is the third column can have duplicate values, for my purposes it doesn't matter, but I need to be able to see it.
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says "Function is not available in expressions in tabel-level validation expresions" on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
