I've inherited a database with multiple users, massive redundancies, and strict policy limitations:
1. The existing table structures must remain, despite redundancies that create problems constantly, because of multiple client programs.
2. Data entry must not be programmatic: no data can be generated by code.
I can, however,
A. Create new, interdependent tables, or
B. Validate data with criteria or VBA.
I have two rules I'd like to enforce:
Rule 1: This one is easy. Certain redundant Locations must be identical. So, all the Locations in Table A must appear in Table B as well (although B may contain Locations not in A). No Big Deal.
Rule 2: An absolute rule for every Location is that each must occur between the boundaries established for that particular Track and Section.
So, given that I have a Table with Records of Track, Section, StartLocation, EndLocation (which defines the limits for Location values)...
Track 1, Section 1: Starts at Location -100, Ends at Location 100
Track 1, Section 2: Starts at Location 50, Ends at Location 20
Track 2, Section 3: Starts at Location 0, Ends at Location 400
I want to prevent users from entering illegal locations in another Table with Track, Section, Location, like:Track 1, Section 1, Location 101 (this location is beyond the end of Track 1 Section 1)I could code this in VBA, but the downside is it won't be enforced if the user has Macros turned off by default.
I’ll apologize up front; I’m not quite sure where to put this. It is an issue I’m having with a form, but it involves SQL and VBA…
I need some help with an access 2007 project I’ve been working on. I’m new to access, SQL and VBA so I’m not really sure how to make it work correctly. Here is the issue:
The form “frmAddCitationsToDissertations” does not work the way it needs to. It currently allows identical records to be made in the tblCitations. This is not correct, what I need it to do is before creating a new record look at the values from the 3 drop down records and see if that value set already exist, if it does then it should enter the existing CitationID and DissertationID in the join table. If it does not exist, then it should create a record in the tblCitations and insert the new CitationID along with the dissertationID into the join table.
The logic goes as follows:
A dissertation can have N citations.
A citation may be sited N times by any single dissertation.
Two citations may have any two of the 3 attributes in common and not be the same record, but if it has all 3 attributes in common then it is the same citation and a new record should not be created in the tblCitations, it should only be created in the join table. Two of the 3 attributes are able to be blank or null, so if a citation is simply “The bible” and the other 2 attributes are the same then I need to be able to compare it as well, and ascertain that it is the same citation.
I’m hoping for a programmatic solution, I don’t want some long involved process with forms because this database will end up having more than 50,000 citations. A long process will increase the expense of data entry exponentially.
Any advice that someone can offer is greatly appreciated. If you’re interested in helping an old dog learn new tricks you can download the database I’m working on here: http://c-helm.com/access/
I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...
I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.
I've uploaded my first draft of tables and relationships here: http://img514.imageshack.us/img514/3937/snapjl9.jpg (sorry for the blurred bits)
Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.
They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).
As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.
My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?
I have written an extensive Access DB Application for my company and have placed it on a cd for distribution. When we try to copy it from the CD to a folder on another machine the above mentioned error comes up. Any Suggestions. Jeff
I have a text box that the user enters a number into between 1 and 5. I can use the validation rule in the table and that works nicely, the trouble is that I don't want the standard Access message box.how to apply the validation rules.
I am (kind of) new to Microsoft Access and have a question about making validation rules more complex.
I am making a quality control database with 3 tables. One containing the product and their unique codes, the 2nd with their unique lot numbers for each different production run, and the 3rd with all the required quality control data for each product.
Each different product has different required specifications. For example, Product AB-544 needs a value between 11-20 while product RY-233 needs a value between 21-30.
My question is: Is there a method of defining different validation rules for different product codes? I want the QC tech entering the data to only be able to enter in quality data that is within the required specification defined by the product code. If the current product is AB-544 he should only be able to enter in 11-20 but if he is entering in data for RY-233 he should only be able to enter 21-30.
I've been trying to get a field to behave but for some reason I must have done something wrong.
For this specific field people should only use numbers between 10,000 and 19,999 or between 80,000 and 89,999.
In the validation rule option I input:
Or Between 10000 And 19999 Or Between 80000 And 89999
I've also tried
>=10000 And <= 19999 Or >= 80000 And <=89999.
But for some reason MS Access accepts the first section of the validation but not the second part.
Also: I've read that the validation rule is better managed at the Form level on the "Before Update" field, I'm currently managing it directly on the table in the field property. What should I use?
I am trying to add extra validation in a sample db (done in A97 (converted to A2003)see attached.
I am attempting the following: Please not that the db has existing validation which I would like to keep (or change if advised) ...
Conditions: 1. Input into field "ID" must start with the letter P or p or R or r 2. If field "ID" starts with the letter P or p and field "Code" starts with the letter R or r, then a message stating that this combination is invalid should appear 3. If field "ID" starts with the letter R or r and field "Code" starts with the letter P or p, then a message stating that this combination is invalid should appear.
Is there a way to have multiple validation rules in one column in a table. In one column, I need the values to be in all lower case and the first three characters need to be three letters.
Everytime I close, open, modify, etc, you get my drift,either a linked table or an in database table, Access validation takes minutes before I can continue.
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
How to add the IM Gtalk to my database.I have daily queries running and want to send ta IM to some employees when it is done for them to proceed with normal work. Yes you can do it by email, but normally there are so many emails they will miss it.
I have Access 2013, part of office pro, and for some reason it now cannot create a new database, ie nothing is created where i told it to It then comes up with "cannot open a database created with a previous version etc".This is NOT a previous version, it physically does not exist.This did work, well last week.Tried doing an office repair, no differance, tried delting system.mdw.If i click OK on previous message, i do get "Close ole server and restart it outside of access"
I have been asked to develop a new application (not using Access).I have the original Access Database and while I can get the data out I was wondering how I can get the images out of the database.
I want my access database to store information about "tests" and "products" that are in each "test". So "tests" is the "one" and "products" is the "many" (each test has many products).My client needs to routinely send me lists of tests and the products that are in each test. I then need to put this information in to my database
I don't want to type out the information they send me, instead I want to devise a simple form for them to complete and email to me (perhaps in excel) that they populate, and I then import/paste in to my database.How should the excel form be structured so that it is both easy for my client to populate and easy for me to import/paste in to my database. e.g. importing lots of test and product information, rather than type it in. Bearing in mind the 1:M structure.
my database lag whenever i start searching, not really bad but i could notice that it blinks whenever i press a key on my keyboard. computer has a big ram (4gb), i5, with 500gb HDD... lots of free space. the database file is only 6.9MB..maybe there is a code that disable or makes the blinking unnoticed?
I have a database (already splitted in FE/BE) which Clients use on site.Part of the service we offer is Outsourcing: We do all the capturing and when they login on their side, it downloads the latest BE file for them to use "Read-Only".
However we added a new module in which half of the data on a particular table needs to be completed by the Client.So we need for the table to be splitted in such a way that they can capture their info on the form and when we log in on our side, it has to download the data they entered in the same way as when they login and download the data we captured.
Was thinking about adding a 3rd database file to the mix with just one table and in some way link that table to the existing table for the new module. But the intermediate link I created is not updateble.
