I have build a database with which I can create quite a lot of reports. When creating them I run out of memory with run time error "2004".
When investigating I detected that a lot of memory is used when creating the reports in PrintPreview mode. When closing the reports the memory is not automatically freed.
Is there a way to enforce garbage collection? Local variables of called routines I set to Nothing.
I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc., child form look like datasheet view.
When i select the record from the list depending on the sale_id all the collection records shown in datasheet view form. If no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.
If more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment. How and also show notes depending on collection number and sale_id.
I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine.
I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:
Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)
However, it keeps giving me the error 'Type mismatch'.
Hi there!
I'm trying to use an Invoice number from an Invoice form to populate a table of Invoice Lines with relevant records (linking by Invoice number). I realise the following SQL code isn't an append query, but I've simplified it to make it easier to explain
SELECT [Forms]![fInvoices]![SPInvNumber] AS MTInvNumber, tInvoices.SPInvNumber
FROM tInvoices
WHERE (((tInvoices.SPInvNumber)=[Forms]![fInvoices]![SPInvNumber]));
The output I get for this query is something along the lines of:
MTINvNumber | SPInvNumber
쉠 | 49760
THe MTInvNumber should read the same as the SPInvNumber, but it doesn't. If you can't see the above, its some sort of weird chinese/japanese character I think!
I thought maybe it was a display error, but I tried using it in my append query, and it inserted it as garbage too. The weirdest bit is it actually regards the form information as equalling the SPINvNumber from the tInvoices table! :S
Any clues on how I get it to format correctly??? I'm very tired now so it's bound to be something simple!
How do you calculate usage? For example...
If I've got a field named Time, then a drop-down box with "Morning", "Afternoon" and "Evening"
How do I make a query so it displays the number of each time...
For example...
There's two times for Morning, three for afternoon and one for evening.
I tried using the 'Sum' total and the criteria of "Morning" but it says data mismatch. That was merely a guess, so I would like to know how to do it.
Like...
Time
Morning: Afternoon: Evening:
2 3 1
Thanks.
Dear All,
Please see attached word document with a screenshot of my tables and their relationships. I'm trying to link from Table:Line,Field:Line to Table:Shift-Line and similarly with the shift table and access is not able to maintain referential integrity.
Can anyone explain why? and generally what I've done wrong with my relationships please?
Much Appreciated
Keji
Over the last 12 months my database has grown in size.
I'd like to delete queries that are no longer used. Is there a utility I can use that will list where all my queries are used, ie used in other queries + reports.
Does anyone know?
Thanks
I have a continuous form with an unbound text box (for search critera) and a command button to kick off the search, which simply applies a filter.
When a user enters text in the unbound text box and hits "Enter", I want the search to be kicked off (instead of having to tab over or mouse over to click the "Search" command button. This is the default functionality of an HTML <form>, so users kind of expect it. But I'm having trouble implementing it in MsAccess.
I've tried using the KeyDown property of the unbound textbox, but no luck, since the KeyDown method occurs before the data in the box is saved. I'm guessing this is related to detecting what key has been pressed but my issue seems to be the order of events occurring on the form.
Any suggestions? This *must* be common situation, but I've found no related topics in searching the forum.
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
Thanks for any help.
Hi,
I have a form based on a query. In this query I sorted the columns year and month in a chronological order. However, on the form this does not appear that way on the form. How can I enforce this?
Thank you
Stacey
hi Guys,
need help in using query, plz let me know that how to use designed query in Reports ?
thanks in advnce.
So I have a query which prompts the user for a username to search by, GetUsername.
Im used to TSQL usage which would be something like:
exec GetUsername 'blah'
My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.
How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).
I have a form from which people view records out of a db.
I would like to log the date/time and the key record they view into a table
The usage table has a date/time field & a string field for the viewed record.
I can run an append query to update the Usage table but can't figure out how to get the date/time into the table ?
Is it easier to write the whole lot out to a file ?
Hello again,
Does anyone know if this is possible and is there an easy way to set it up?
My db has many relationships, hence if you attempt to delete a record from the table, it will not allow it if there are related records.
This is great, however.
My intention is to disable any record deletions. Instead, once a record is no longer live, the status of that record is set to disposed,exipired, etc.
I want to force the same rules as if attempting to delete this record but set to the status field.
Example message.
ie, Warning! You cannot dispose of this PC. There a related records in the Software License and Contracts Tables. Please reassign them and try again!
Any clues would be great.
Thanks All.
Hi,
I opened the relationship window to do the relationships among my tables; however, the check box 'Enforce Referential Integrity' is disabled. I don't understand why is that? And is there anyway to change it?
Also, where should relationships be implemented? is it the FE or BE?
Any help will be very much appreciated.
B
Hello everyone. I'm not sure should I post this question here or at SQL Forum? However, my problem is this. I have 2 Tables, Table1 has AutoNumber as primary key, my second Table2 has index key field. I have one-to-many relationship on those two fields, with enforce referential integrity, cascade update and delete
The access program works fine until I upsized to SQL server. [u]Then I was unable to perform cascade update or delete. I have check SQL table (which I have little knowledge about) and seems to be okay, the relationship exists. But at ms-access I was unable to make the cascade update and delete.
I appreciate any help …. Thanks.
Hello All,
Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?
I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?
Thanks for any help provided!:)
Hello..
I have a simple database of 4 tables.
luTblJobTitle
JobTitleID (pk)
JobTitle
luTblReason
ReasonID(pk)
Reason
tblDateReason
DateReasonID(pk)
EntryNumber(fk)
Date
Reason
Entered
tblEmpMain
EntryNumber(pk)
EmployeeNumber
Etc//
I have a 1 to many setup between tblEmpMain and tblDateReason.
When I try to setup the relationship between the "reason" field in tblDateReason and luTblReason, Referential Integrity along with the other two check boxes are greyed out.
It still shows one to many but it will not let me enforce referential intergrity.
Why is this? Did I setup the database wrong? Or am I trying to do something I shouldn't be doing??
I have attached the db.. It is split with both the backend and the front end.
R~
I am building a new project which has some core tables..
The key tables are:
Countries
CountryID
CountryName
Businesses
BusinessID
BusinessName
CountryID
Clients
ClientID
ClientName
BusinessID
CountryID
I want to enforce the rule that a Business can only have clients within the same country.
Can I enforce this through referential integrity in Access ? Do I need to redesign my tables ?
Hi
About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.
However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.
I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?
I have asked the users and (of course) they don't know.
TIA
This is very strange. I have converted two computers from 2003 to 2007. I have also installed 5 2007's on to new computers. All of these are Professional XP sp2 setups. I copied over a database to woek on those computers. Originally the performance was very good, on all of the computers (but not the lowest specified converted laptop ) exitting from a form containing a subform takes 37 seconds. The word Calculating... is on the status bar. The laptop takes 3 seconds par with what it used to do.
I have transferred the 'Data' database, the 'Apps' database, Utility.mda and another .MDA. I have also transferred all the Reference executables to one of the other computers. This has not made a jot of improvement.
Has anyone any pointers. I have exhausted all the resources trying to find a solution to this one.
I have written a large database for the school I used to work for. In the main table there is a field named "Active" which is text with yes or no choices.Is there a way to find in what objects this field is being used in the database?
For the life of me I can't remember why I put this field in or if its even used now.This database has had many versions over the years.
I have searched many of the queries but can not find where this field is used.If there isn't a way I plan to backup, remove field from table and test all the features of the program to see which crashes. However I'm hoping there might be a quicker way.This was one of the first DB I wrote and didn't pay much attention to mapping or keeping notes back then.
I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code:
Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly)
Set Me.Recordset = rs
Set rs = Nothing
I have a form where my users can enter labor line items for various jobs. FOr each line item, they can specify the product and color to be installed. Now, I realize that the CORRECT way to do this is to have a table for products. However, since I am not touching inventory with this application, I am setting it up for them to simplye type it into a text box.
Is there anyway to set up the text box with "memory" so that as they type in the entry, it will start to "guess" what they want by previous entries? Similar to the way a combo box operates.
Thanks,
Hammy
I have seen this problem somewhere but could not find the solution.
I have dbase opened with db window hidden. When exiting the db, access application resides in the memory.
I can see it in task manager, processes.
the code i have is:
Private Sub cmdExit_Click()
CloseCurrentDatabase
DoCmd.Quit acQuitSaveAll
End Sub
What is wrong with it ?
Im building a new database using Access 2003 SP2 on windows XP SP2 with 1 GB RAM.
When i try to execute the following line of VBA code I get an "out of memory" error:
Form_Confirmation.txtDept = cboDept.Text
Before this line of code I don't query any data or open any DAO connections, Im only checking the entered values in various text boxes and combo boxes on a form. I have also tried rebooting my computer and it still gets stuck at the same line.
What i basically want to do is just move data entered into text boxes on a shipping order form i created and present that data on another form to confirm the entered data. The error occurs when i try to move data from one form to another. The database is still only 1MB with only 5 tables, each with only a few records in them just for testing.
Do i need to install a service pack or download something to fix this error?