I'm having a problem with how my forms add data to my tables. I've been using the wizard to create 3 different tables and a form to update them. One of two things happens to me each time I finish the form, either it won't allow me to input any data or instead of updating a field each time I put something new it will write over the existing data for that field. I've tried using sub-forms too with the same problems. Is there something I can do to fix this or is there a past post that I can look at that may help me out.
Sorry if this isn't too clear like I said I'm new to Access and this is really only my second time using it.
I have constructed a DB for work to record accidents, however I am running into problems. On each form I have a button that Saves the data closes the current form and opens the next form, depending on what data was entered. I have this ruuning fine using this code,
Private Sub SaveRecord_Click()
Dim NextForm As String
If Me.Status = "Pupil / Child" Then NextForm = "StudentDetail" Else NextForm = "EmployerDetails" End If
DoCmd.save DoCmd.Close DoCmd.OpenForm NextForm End Sub
Private Sub SaveRecord_DblClick(Cancel As Integer)
Dim NextForm As String
If Me.Status = "Pupil / Child" Then NextForm = "StudentDetail" Else NextForm = "EmployerDetails" End If
DoCmd.save DoCmd.Close DoCmd.OpenForm NextForm
End Sub
The primary key of this form is "PersonKey" and the two forms taht could open, each have"PErsonKey" as a foriegn key. However the key does not get brought across into the next form and the save function does not appear to work on all forms... :confused:
I put another button on this form using the control button wizard, and it gave me this code,
Private Sub Command23_Click() On Error GoTo Err_Command23_Click
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster INNER JOIN on JoinedDupList ON TestMaster.ID=JoinedDupList.ID WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
I'm building a simple db to track people with access to systems and buildings (Access 2000). I have three tables:
1. People (names, ID, etc.) with a primary key of an autonumber 2. Bldgs - y/n fields of bldg #'s with a primary key of a number (linked one to many from the People table) 3. Systems - essentially the same set up as Bldgs table (though they're not related)
People may have access to either bldgs, systems, both or none.
So I build a query to combine all three tables. If I add a new record (to the People table and only one of the other two tables) I can't go back later and add data to the third linked table seemingly because the autonumber exists in the other two tables but not that one. Well, sure.
So the question is, I'm obviously missing something insanely obvious, or if not, how do I get around this? It's driving me nuts.
Hello hello. I'm new here, and new to Access... I've just started taking Microsoft's Office Online training tutorials and I was wonder about planning tables and repetitive data.
I had transferred some stuff from Excel to Access but now I'm looking at redesigning my whole database using relationships, etc.
I'm mostly working with a mailing list, so my fields are:
Mr./Ms. FirstName LastName Title Company Address City State Zip
Okay. There is also a second list of contacts that will link to the Company field. But my question is, most of the contacts in this list have the same title (be it President or CEO or whatever...) -- there's only like four variations.
Would I make a separate table with just those four titles, so I don't have to repeatedly type "President" in the title field?
And then if so, what about things like Mr./Ms.? It's only two variations and every record needs one. And what about State? Again, only two states ever. These don't get their own little "related" table, do they? And if not, how do I avoid typing it every time??
Thank you so much for reading this, and thanks in advance for your help.
~Jas/LadyGrey
P.S. I have another major question too, actually. As I said, I'll have two different lists hinging on the Company name. Except I have some companies with multiple contacts... so I don't think I can make the Company the primary key, since it may be repeated for a few records. Help, please! Thanks!
I have a form with a search text field, inwhich i want the user to type in a value to the search text and then I want a query which would search two tables and return which table the results in. i.e
Form: Search Text Field ----> ABC12345
Table 1 Date Container Number -----> No Record of that number
Table 2 Date Container Number ------> Number Found
Hi how are you glad i found a forum especially for access i am about to top myself trying to sort this really stupid problem out! So i am trying to knock up a database for my mother to use at the school where she works. I have created the tables and even managed to create some drop down boxes to save her typing to much in (she is a teacher remember). However when i then go to form wizard, an select the table i have created there is absolutely nothing in the 'available fields' box to transfer over. This is the case for any of the tables and any database it would seem! The thing that really stumps me though is if i put it onto a different computer everything works how it should! I have reinstalled Office to still no avail! I am sure its just a simple setting or it could even be a security issue! Please help me, any would be much appreciated!
I’m completely stumped … could be because its so late or my brain has just decided to give up on me!
Short of not going into too much detail, could someone advise … does one HAVE to create a query in order to create a report based on a form OR can one create a report based solely on a form?
Here's the situation. I have a Database in Excel (~3000 rows & 7 Columns)
The first Column has numbers like this (3-10-34 or 223-7-45) the other columns have names, addresses and other stuff.
What I'm trying to do is to eliminate the use of huge binders. I want to have a Blank screen with only one input box where I can type a number and retrieve a specific row from my Database. Time saving.
Now, when I'm searching, I usually only have the first numbers before the dash (such as the 3 or the 223) not the other numbers. So I cannot input 3-10-34 for instance. I can only put 3...:confused:
I've already imported it to Access and it works fine but have no idea how to do what I just described!
sorry if this question is answered somewhere else i did check through the forum a bit first
anyway to the question
is there a way in access 2003 so when i change a value in one table it creates a new record in another table
example
i have a table called tickets and one called fixes, when i change a value in a record in the tickets table i want a new record in the fixes table to be created for me
I am not sure if i am supposed to post in this section......
--------------------------------
Using Access 2003.
I am very new to Access and have started off very well building my first project..
My help is that i would like a macro button to delete a record once you have put in to the database if that is possible also i would like a save button if that is possible...
I have put a Tab Contol on and want to change the backgound but i can't see on the properties box to change this. Is there a piece of code that allow me to do so...
I am still very new to Access 2003 and have started off very well..
Just a few help please:
Help 1. I need a macro button that will allow me to delete a record from the database via a form.
Help 2. I need a macro button to save the database if that is possible via a form.
Help 3. I have a Tab Control on my form and i want to change the background of the Tab Control but can't find it on the properties is there a piece of code i can put in to VB which will do this for me or is there another way.
hello guys i m very new to access and i need some help! if i had a form that contains a field like salary and i need to add all the salaries from all the records into one total salary how can i do it? plz take into consideration that i m totally new and dumb:p in access thank you
Alright here is what I am trying to accomplish. I have A, and B. Then I have 6 different tables that relate to the six different things B could be and contain all sorts of other information.
What I want is to make a form that lets you type in A, and B. Then the correct table/form for the respective B will pop in to the subform area. Then as soon as you select or type in a new A and B, the subform will change, or stay the same if the B has not changed, but a new record will have started.
"A" is just an ID... something like AA001, AA002, etc. "B" is the stage each ID is at... like beginning, middle, end, etc.
Sorry if it is a bit confusing, I am willing to explain any details that could help. I consider myself a somewhat advanced Access user, but this just stumps me for some reason.
Im looking to create a frontend and backend for a Dojo (Martial arts center). Basically, it will log in people(record their training time, by day not hour) either by Number or name. Secondly, I want to be able to have all these fields seen by the admin: Dojo Number Date Joined Last Name First Name Male/Female Address City Zip Home Phone Work Phone E-mail Birthday Occupation Adult/teen/child Rank Active/ Inactive status Martial Art Exp. Staff Y/N ASNJ Dues Info USAF Dues Info USAF Membership Number Misc. Notes
So the backend/ admin would be able to update this field and keep records up to date.
There would be around 800 entries/people, and only one admin accessing the db at any time. The frontend would be used to log in..
Can some kind soul help with pointing me in the right direction to a problem below?
In my database (property database), I have some old properties which are now sold. There are relationships between property, rent received, billing, lessees, etc. I want to be able to keep the details of the 'sold' properties.(i.e., who owned it, what they paid in the past, when they paid etc. in case I need it, but don't want it shown any longer on the main working database). I want to be able to delete the properties that are sold from the main table. Any ideas how I go about this? Hope I've explained myself ok.
I have recently created a database which exports a daily report in Excel. The problem I am having is that the reports are sent out in Excel 95 which cannot be opened by Blackberry. We used to create this report from an excel database and send it out but it went out in excel 97 format which was readable. As a complete "Access" novice I am at a loss on how to cure this problem short of copying the excel 95 report which we create automatically and cutting and pasting into excel 97. Over to the experts.
Very much a novice here What im trying to accomplish is so simple its silly, but i cant figure it out for the life of me I have a field id like to restrict entries to the words Buyer and Seller, and i'd like it so that in the form, if the user hits the letter B, Buyer fills in and if S seller fills in ... What can i do in the table to accomplish this? Thanks so much
Hi, I have used access in the past but very little. I need help setting up a table that I can make relationships to another table. Here's what I need.
I need a table(s) that I can log samples of medications into. It needs to have a drug name, dose, lot #, expiration date, and #of samples. As we get samples, these will be logged into here via a form made from the table (I've already done this exactly as stated). I will need to be able to get a total from the number of a certain dose of medication i.e. amoxicillin 500mg (all of the lot numbers added together).
Now, another table(s) needs to have samples signed out. It needs to include pt. name (first and last), date, drug, dose, lot #, exp. date, #of samples, ordering practitioner, and clinic. What I want to happen is when I sign out the Amox 500mg #30 with a specific lot #, it will remove the number from that specific lot # in the inventory table and reduce the number of Amox 500 total (all lot #s).
Does this sound too complicated. I tried to set up one table for the sign out process, but suspect it's too many variables. The primary key I was thinking would be the lot # but i need to be able to use it more than once if I get 100 samples and only use 30 at a time.
Would someone be willing to offer some advice!!!?? Thanks, Robin.
I made a simple Table with a couple of fields.Each record shows what a job needs to be done ...Now I'd like to add simple checkbox and each time I would check this box (that would mean that job has been done) Access would automatically delete or hide that record.
Thank you
Also could you point me to good tutorials?Thanks a lot
I've created a simple access database for my deparment, that I want other departments to be able to query data from. I don't want them to have to open the entire database each time they pull data, because I'm afraid they'd become confused by the program.
The company seems much more comfortable with excel files, and I've been trying to use an Excel-based query to display selected information. The problem is this: Nobody can update their excel queries, because only my department has full access to the .mdb file that the data is stored in. Everyone else has read-only access to the file and the folder it's located in, so they are unable to run an update.
Is there a good workaround for this? I feel like I'm overlooking something incredibly simple.
Ideally, I'd like to create an .xls file with the necessary queries in it, and then just email that file around, allowing other users to just save the file to their desktop and open it/update it when they need to check on something. Other methods of accomplishing the same thing are welcome too, but that's what I'm going for right now.
I need help. Now I've written a post so that some one could help retrieve current information and they told me to use Dmax. Great but I don't know how to use Dmax. Basically I need step by step instructions.
This is an example of what my table looks like:
Date of Order --Product--- UNIT COST 20-May-05----- XYZM---- $8.87 20-Jun-05------ XYZM ----$9.39 21-Jul-05 ------XYZM -----$9.24 19-Mar-05----- ACE ------$7.85 20-May-05 ----ACE------- $7.89 20-Jun-05 ----ACE-------- $8.01 21-Jul-05 -----ACE------- $8.13 22-Jul-05------ACE ------$8.05
What I need is a report that will show me the last ordered price of the product.
Date of Order --Product-- UNIT COST 21-Jul-05------ XYZM ---$9.24 22-Jul-05 ------ACE ----$8.05
How do I get a query that will allow me to retrieve this information? I'm still in training and need step by step intructions. Please Help!!!!
I am new to microsoft access. I am trying to develop a data base that will store data as well as return means and SEMs. I have multiple parameters entered into an access table. The fields are Group Number, MAP, AP, and KV. I have a query to return the averages for each group, but i am having trouble making a query to return the SEM. the formula for SEM is
SEM = Standard Deviation / (Count ^ (1/2))
This should be easy because access will return Stdev and Count, but when I try and enter the formula into expression builder, all hell breaks loose. Any advice would be greatly appreciated.
I am having a little trouble creating a database. I am using Access 97 if that helps.
I have created two tables the first table is a list of brokers (Brokers) with the following columns: - User Name, Contact Name and Broker Name. The second table (Main) is where I would like to store all of my records for each call recieved.
What I would like to be able to do is on the main form is to have a combo box for the user Name and then to have two other text boxes to auto populate the Contact name and the Broker Name once the User Name has been entered. Then all 3 fields will store the information in the Main table. Hopefully this all makes sense. I have been trying to use the help within Access but to no avail!.
ok first i am new to access and databases so bare with me. i need to create a table that holds 6 doctors and 4 nurses appointments that start at 9 0clock last 20 mins finish at 5.oclock. (this bit i can do) i need to use this over and over again so each day there is a new appointment table how do i date the table and get it to change everyday and clear ready for the new day can i do this??? or do i need to create a new table every day??
