Recently I've been charged with the task of setting up a database for the company I work for. Not a big problem I thought, I sat down and started playing. I've figured out most all of what I wanted to do with one glaring exception.
I have 3 fields of data that I want added together and automatically spit out into a 4th field. If the 4th field was also unable to be altered by the user, that would be all the better.
For exampled I'm looking to add [Field1] [Field2] and [Field3] and have them spit out into the field [Output].
My coding knowledge is limited, and my access experience is almost as poor.
Surely Access is capable of doing this, without having to build some elaborate script or macro or whatever it's called.
I'm a newbie to access and I need to build a database with the following specs:
Be able to click on a specific "skillset" button - 13 Skillsets in total.
This would take you to a list of people who are in that skillset
Then you would need to be able to click on a person and it would take you to the current day, with a list of about 5 stats. Eg. No. of calls taken, Duration of calls. (Data can then be entered / viewed)
How difficult would this be to do if it is even possible? Would anybody be kind enough to point me in the direction of a tutorial or give some advice.
I have a form for employees that contains their salaries. How would I go about creating a macro that would cause a warning box to display any time a salary greater than $100,000 is entered?
On the same employee form I have a textbox to input the email address. After you input the email I want a lostfocus event to check if it follows the same format as "username@email.com" If it wouldn't match that format an error message will be shown.
I'm trying to teach myself access. I'm having some difficulty, so let me explain my requirements.
I need a database that allows entry of reservations from multiple locations, and that can track how many of a particular item is being used.
Example: I have 32 devices, but only 7 paths that I can book to these devices. How can I set it up so that when someone attempts to book an 8th device, the system will alert them that there are no available slots for them to use. I would like to make the database available for editing for future expansion also.
I hope someone here won't tell me to take a hike, I know I'm a newbie, but once I dissect an idea, I catch on real quick.
hi everyone, as a network engineer, it administrator, etc etc people expect to know absolutely everything about pc's. so here i am.....
a company whose network i look after are using excel spreadsheets on a weekly basis to take orders, wages, running costs etc from their remote sites (currently about 20 sites) the spreadsheets are accessed via terminal services and are moved via a script at the end of the week so the remote site doesn't have access to the old records and replaced by a new sheet. so lots of spreadsheets floating about. you can imagine how hard it is to pull past records & compare etc.
now, i'm new to databases and have order a book to gen up on the subject. the sites should only be able to edit the last 7 days of records but be able to see the last years. i can see how a query and subforms can achieve this etc.
each site shouldn't be able to see the other site's records and the head office should be able to run reports etc on each site or as a whole.
i can narrow the time down that everybody is accessing the data. the sites may grow to 50+ but the head office is unlikely to have more than 5 people access the dabase at the same time.
my question is..... (access 2007.... can't see any user security)..... am i better to do an individual database (all exactly the same apart from name, titles etc) for each site that sits in their individual folders via terminal services? then let the head office access each seperate database via linked tables? my thinking for the above way was security (they can't see each other) and resilience (1 database gets corrupted, the others are still workable) and speed (only a few users accessing each database) the downsides are obviously the amount of files although this is nothing compared to the excel files at the moment. any ideas or help would be appreciated. thanks louis
First, if I have a table, and I want to combine two records together, how would I accomplish this? For example, say I have the following table.
NAME SALES Bill 2000 Jim 500 Ted 1000
And by some miracle of science, Bill and Jim are able to fuse together to become Jill, combining their sales together. So the new table would look ilke this.
NAME SALES Jill 2500 Ted 1000
My second question is, say I have an extended list of the same table with 30 names and corresponding sales. However, I want to find out Ted's percentage of sales of the TOP 15, not all 30. How can I accomplish this?
Thanks so much in advance for helping out a complete newbie. :)
I was wondering if a kind sole out there can help me or give me some advice.
I'm creating a DB (I have attached a simple copy) but I'm having real problems with it. On the Roleprofileform I want to give the users the ability to choose what applications they need. I've done this by double clicking the text box will produce a list box from which they choose their apps. On closure this will then update the text box with the respective ID's. This solution was supplied through help received on this forum.
The trouble is I still don't under stand how I can ever run a report with this information without have mountains of code.
What I would like to happen and I'm all ears to any suggestions is to somehow have the ability for users to choose say their drives and it be reflected in the table tblRoleApps as I can then run reports easily. My main aim of engaging you help is to help me with A. How to allow users to choose their apps B. How this is displayed on the form (would prefer the names of the apps but it is not that important if the reports reflect this) C. How to reflect what apps match up to what roles for the purpose of reporting.
Hello, after browsing many, many forums on the net for access help, this one seems to be the most active, so im really hoping someone can provide some quick assistance.
Quick run down of the project: Im working on an access database that has a simple login form with usernames & passwords set into a table. Once the user logs in, a global variable (declaired within a module) is set within to the username for reference throughout the life of the project.
The whole reason a user is required to log into the database is so that every new transaction entered into specific tables will have a username attached to the transaction (basically, so I know who to talk to when data has been entered incorrectly, or there is a questionable entry in a given table).
My problem here is that most of you know that you can not assign a variable to a default value of a field in a table. Since my users will only be working within forms and not the tables, the only logical place to force this value would be within the form code.
Now the problem: Through debugging, I know that my global variable strAccountName$ maintains the proper value as required, but I can't for the life of me get the results I require.
When a new row is inserted, I need the value of strAccountName$ to be automatically filled into the AccountID text box, but having terrible results:
Assign the default value for AccountID control when the form loads results in empty value upon new record.
Assign the default value for AccountID control when the forms BeforeInsert function is called also results in empty value upon new record.
Assign the text value for AccountID control when the forms BeforeInsert function is called works, but im forced to change focus from the initial control in which the user was originally working with, making the user return where he/she was and force re-selection of data within a combo box (which is was initiated the BeforeInsert call in the first place).
I can't believe that such a simple thing would turn out to be such a frustrating experience!
Is there any alternative that you guys can suggest? It's vital that I have the ability to track entries made by my users.
Table 2's Customer Name field is a lookup field back to Table 1 (so the name is always spelled the same)
Query 1 contains the fields of the Job Record table
Form 1 contains fields from Query 1, and is used to update/edit Table 2
Query 2 contains the fields of Table 2, plus a calculated field that will assign an invoice amount based on who the customer is. I'm using an IIF statement to make this determination. The statement reads IIf ( [Customer Name]="ABC", 120, 180)
The above scenario results in Error# returned as value.
Our Contacts database holds records relating to individual clients. As these clients are visited in their homes, I have put a "Lone Worker Caution" yes/no field on the form to alert staff of potential risks prior to visiting.
To have a more visual signal to staff, I have placed a label (label202) on the form with it's visibility properties set to NO. With a lot of help from other forum users, I have put the following code behind the yes/no check-box, to activate the label and cause it to "flash" in red & black colours:
Private Sub LoneWorkerCaution__AfterUpdate() If Me.LoneWorkerCaution = True Then Me.Label202.Visible = True End If If Me.LoneWorkerCaution = False Then Me.Label202.Visible = False
End If
End Sub
Private Sub Form_Contacts()
If Me.LoneWorkerCaution = True Then Me.Label202.Visible = True End If If Me.LoneWorkerCaution = False Then Me.Label202.Visible = False End If Me.TimerInterval = 300 Me.Label202.ForeColor = vbBlack
End Sub
Private Sub Form_Timer() With Me.Label202 .ForeColor = (IIf(.ForeColor = vbRed, vbBlack, vbRed)) End With
End Sub
Everytime I click the yes/no check box to activate the warning message, VBA opens up with the message "Compile Error: Method or Data Member Not Found".
What does this message mean? What do I have to do (in Plain English please, I'm new at this!) to fix it?
I've been working with this access database for a professor for about a month. Each time I try to do totals, I always get a larger value than it is suppose to be.
Say, I'm suming three fields that have 3, 4, 5 in them, instead of getting 12, I get 50. This happens all the time and I've had to redo the output data several times because the result I got was always outrageously wrong.
I am trying to figure out how to have a field work like the auto number (automatically fill in the next number) but I need it use a prefix that doesn't change. IE: IN82806*** with the *** being the auto fill numbers. I have read that I can make a macro to do this, but have been unable to figure out how. Any assistance would be greatly appreciated.
1) There are some databases here at work that I can open in BOTH A97 and A2K. I thought A2K won't open in A97, and before you can open an A97 in A2K, you have to convert? At least that's been my experience thus far. Any idea what is happening and how I can determine which version these databases are currently in?
2) With those same databases...assuming some were A97, the option to Convert them under Database Utilities is greyed out. Is this most likely a rights issue? Could it be anything else?
Recently I created a database using MS Access for the company and I am the Admin for it. The database is in the share folder and many employees are using it but if I want to take backup or modify any thing a message is appearing saying that one user open the database.
Is there any method to know which user is opening the database and in which PC (workstation) it is open. --------------------------------------
Is there any way of adding export fuction to the toolbar using modules or macro
Is it possible to have a table of 10 random questions and an inital form created with those questions in the form of checkboxes. When you chose random questions from that pool of questions a new brand form and table have the option to be created.
Basically, I have many external databases to research and some of the questions are the same in each database. Each database must be saved as individual tables and forms.
I have few questions so please bare with me 1- i want the text box to change automatically according to date so i tried this: if date>text14.text then text30.text=sea else text30.text=port but nothing happens 2-i made a form but not all the data rows in the table are in the form(there`s still some rows) so if i delete a data that are in the form how i can automatically delete the data that are in the other row ?
3-this may sound stupid..but is there a way to stop the access logo from apearing when loading the database?and how to make access directly open a specified form?
I am designing a database and have a couple of questions.
I have designed a form that I have opening at startup. How do I force this to maximize on opening?
I have set up a report based on a query. It works fine. The query requires a parameter to be entered when it is run. The parameter comes from a list (of teachers in my school). How do I set up the query so that the 'Enter Parameter' dialog box provides a drop down list of teachers from which the user selects their name?
I am new to Access and have just designed a new database and was wondering if anyone could help with a few questions.
1/ I am setting up a Macro to send an e-mail on the change of a form. When the e-mail is generated by Access I get a message from Outlook stating that "A program is trying to automatically send e-mail..." and asks you to confirm that it is ok to go. Is there any way to bypass this check or alternatively send the e-mail from a specific e-mail address rather than the database user.
2/ I have changed the background colours on a number of my forms but the Record Selector's and Scroll Bar stay the standard Access Grey. Is there anyway to change these?
3/ I have used the User Security Wizard to setup users, groups and access but I can't seem to see a way to limit the number of simulateous logins a person can have i.e. I can log in more than once with the same user when I only want to be able to login once. Any ideas?
Hey guys, I'm just doing this database and have suddenly realised I dont know as much about MS Access as I thought!
Q.1 - I need to run a query that displays the top 5 records in a table, how would I go about doing this?
Q.2 - In my tables, I have of numerical data like the following... http://img276.imageshack.us/img276/2695/11ch1.png is it possible to make a Totals column which automatcily adds up the data in the Fraud, Competition, Loss of data, etc rows? (similar to MS Excel if possible)
Say your got a database that has many users & in which quite alot of information is added to it on a regular basis. Basically its getting slower, what could be the problems & how could you fix it???
Hi all, I built a system in access for my a level IT course a few years back but haven't had need to use access since. just been asked to build simple system at work and i'm forgetting some really simple things. trouble is i think i'm overcomplicating the terms and can't find anything in the help file!
basically the system is to keep track of how many hours various people have worked on various jobs.
I've got 3 tables:
Jobs - job id, location etc etc Staff - staff id, name etc link - job id, staff id and hours worked.
the job and staff tables have the id as the primary key, and i've set the relationship to link them to the corresponding field in the link table. which is all fine if i remember rightly?!
now what i'm trying to do is set up a form for entring the hours worked etc into the link table. we use job id numbers all the time so i have that as a drop down box. what i want is to have a drop down for employee names, which will insert the employee id into the link table- basically so we can work with names rather than numbers. for the life of me i can't remember how to do it. i know is really simple but i can't find a way to express what i want to do to search for it in the help or online!
if anybody can refresh my memory it would be much appreciated!
First...I have a table called company rates which will be used to calculate time and materials. In the material field, I need to enter .10 for 10 percent. When I enter .10 the field changes back to zero. I have it set to long interger. Is this wrong?
Also, I am working on the form that will calculate all of this. I have a field that needs to look in a flex price field. If there is a number in the flex price than that is the labor, if not, it needs to multiply the estimated time (Text43) times the quantity, times the CurrentLaborRate contained in the CompanyRates subform. I can't seem to get it right...
Allrighty, some questions about replication. I have gotten the Replication Manager from the helpful IT folks here, have made the database, and it works well to synchronise between different files locally on this computer. However, ideally the other people in the department would be able to carry replicas around on their laptops, while ideally the design master and overall database tidying should sit on mine. The setup is typical intranet here - computers are invisible to one another directly, and so are their virtual My Documents drives, while shared network folders are visible to all.
Questions;
1. Since I can't see any of the other laptop drives, and the database would have to be accessible offline, I assume indirect synchronisation is the only choice, correct?
2. Does the Replication Manager need to be installed on each of the laptops that will use the database offline?
3. If yes, what's the process? Do I install RM on each laptop, then use the Create Replica function to create one locally on each? No hidden pitfalls here?
4. If yes, is it sufficient to keep RM shut all the time unless specifically wanting to synchronise? Would happen at least once a week.
5. If Replication Manager doesn't need to be installed on other laptops, how do I move replicas there? I tried moving one through copy/paste, but that broke the link to the Replication Manager.
6. (Optionally - I've seen some references to 'replica farms' - what is that?)
Very much appreciated if anyone is able to answer..
I am trying to build a db for the office I work at. And I have a few questions about how I should design it.
1. My vendor number should be an autonumber, but it has a relationship to the vendor name on a report. I cant create a relationship btwn text and autonumber.
2. Right now I have 3 main tables. Vendor, builder, and job. All of this is for the marketing aspect of the business. But I want to include other aspects, like collections and payroll. Should I add columns to the jobs table, or create a new table? For example, on the jobs table, I already have job info like details on the job and who referred it to us. Should I add invoiced amount, amount collected, etc., or create a table called collections to store all of this info? Im really confused about this.
Hi I'm new to the forum, and i have a question (don't we all)
I need the following, i tried some turorials but couldn't find it.. I'm creating a form, that has 2 fields that I want to relate,
let me explain a little: the database if for a Church I work for. The church has 3 sites (3 different buildings in 3 different parts of the city) Each site has it's own schedule of day and time. I need to create a field that asks for the site, and depending on the site that is selected, it should display in another drop down menu the possible days and time of only that site. I have no idea how to do this. I have created a table with the sites, days and time How do i make one field depend on the other.
The other question is regarding a field that I want to add for the Cities of States. I want the field to remember the new ones, and add them to a drop down menu, so that if it is there it can be selected without not having to write it again, and if it isn't there yet, it can be written and then it would be added to the list....
thanks I want to thank you in advance. hope i can be of help soon enough:cool:
I've run into a problem with my db :( When I'm trying to create users for it, apparently Access won't accept them when they try to log in from different computers.
Is it a general problem with Access, or is it just me who's a noob with this?
