New help setting up a tracking database Results

I have a tab control set up with check boxes on it. It works except every time I click a box on one record/person's info. it carries over to all the other records. How do I get it to stay with just the one record? I have about 2000 names to go into this database, with several different tab controls containing check boxes in each.

My bark is always worse than my bite, except during a full moon!
Okay, more info! I have a database which I use for keeping track of members/people. In order to keep up with which internal groups these people are members of I have set up tab controls on the main form. The first tab is where I put the check boxes to note what profession members are in. But when I put a check in the box for doctors on one person's record, it shows up on everyone's records. I'm new at some of this so I apologize if I don't always explain this really well. But if you ask the questions I'll do my best to answer them. Thanks!

[This message has been edited by thechangling (edited 10-31-2001).]

I took all the Access classes back in 2002. Apparently it has just been WAY too long because I'm trying to build a very simple database and cannot seem to remember key things. I'm using 2003. It isn't the later edition that is the problem. What I want this to do is super simple. I just cannot remember how to do it.

I need to keep track of 4 different expiration dates. I want to do data entry using a form. I also want to be able to edit those dates without creating a whole new record. So I built my tables (CLIENT, SERVICE ONE and SERVICE TWO) and my form. CLIENT only has client name and client id as the primary key. SERVICE ONE has service id (as the primary key), client id, exp date 1 and exp date 2. SERVICE TWO is set up the same as service one, but it will be different information.

The data I have entered into the tables does show on the form, but it will not let me add a new record. Can someone please tell me what I'm doing wrong? Its making me crazy trying to figure this out.

In the world of databases, what i need to do is super simple!!!! I just need to be able to

1. enter clients and the 4 exp dates
2. i need to be able to overwrite exp dates for current clients
3. i need to be able to search by client to view these dates
4. i need to run a report on the first of the month for anyone with a date expiring the following month.

This is supposed to be so easy that it is embarrassing that I can't handle setting it up. Please help me.

I am basically a beginner when it comes to designing forms in Access, but have recently developed a patient tracking database. It all works well, except for one key piece.

I have a main form, where new patients are entered and get an autoID key that follows them throughout all visits. Then, each patient also gets a visit ID, which applies to a series of visits with different dates. Therefore, when filling out forms on a patient, I need the auto ID, visit ID and date fields to all be primary keys. I have set this part up correctly.

My problem is that I have a subform in the main form that allows the user to click on a buttom that opens up a form that they have to fill out periodically for each patient. I want the autoID, visit ID and date fields to automatically be entered into the form that the user clicks on when it opens. I have yet to be able to do this. This is the key piece of the database. Any help would be greatly appreciated!

Thanks, Melissa

The place my sister works for needs to track companies to which their people are "cleared" to go and how long they are "cleared" for that location. I've developed a simple database that allows her to either input or view all "clearances" for each of their people. However, if a new location comes along, she needs to be able to add it.

I have 3 main tables: Personnel, Location, Clearance

Personnel contains 2 fields: NameID, LastName (that's all she wants - hopefully, there are no duplicate last names! Can always add in 1st name later very easily, though)

Location contains 2 fields: LocID, Location

Clearance contains fields: ClearID, DateGen, DateExp, NameID, LocID

The main form (based on a query from the 3 tables) shows the name and a subform showing all clearances for that person. Example:

ClearID DateGen DateExp Location
1 1/25/2002 2/25/2002 Company X (SomeCity, State)
2 1/25/2002 2/25/2002 Company Y (AnyCity, State)

and so forth. Location is a drop-down box where she can either click on the arrow and scroll to find her location or start typing and hope she gets a match. However, if there is no match and she wants to add a new location what is the best way to do that? Here's what I was thinking about:

1) Set it up so that she can select or enter a new one. But I don't know how to do that! Keep in mind, I don't write code - I just use wizards and properties!

2) Set it up so that she has to click a button to open another form (call it Location Form) and enter it that way.

BTW, I did it via option 2 but the locations were not showing up unless I first close the Main Form, even though they were present in the location table while the main form was still open.

Any suggestions?


I am new here and new to Access so please don't judge my ignorance to harshly...I am using Access 2010 and have a database that I am trying to set up to track employee inefficiencies in an assembly plant.

I have a table that has each tool that we produce. That table is linked via lookup to another table that lets you know what grouping that tool belongs to. (We make pneumatic nail guns) If it is a framing gun, staple gun, coil tool, etc.

I then have a query that calculates the targeted tools per hour that need to be assembled to make money.

What I would like to do on a form is: 1st-Select the grouping - Then when the grouping is selected I only want the tools that fall under that grouping to be allowed to be selected, and then when the tool is selected from that list I want the targeted tools per hour to be displayed for the group/tool selected.

I am guessing this is not a big task, but I am not sure the correct way of doing this...any help would be greatly appreciated

HELP! I set out over 7 days ago to accomplish what I thought was a fairly simple task; to add another search tool to our database. And here I am in the Access help forums!

First off I am using Access 2010. It is also necessary to note that I am very new to Access altogether.

My project is as follows:I work at the Catholic University of America in Washington, DC. Strangely enough I am a mechanic here. We have about 80 vehicles that belong to the University itself, and are used by University staff members on a daily basis to perform different types of jobs. Our job is to keep all of them running.

The University has about 6 different tools to track different aspects of the vehicles, but none are synched together in any way. That is why I decided to "try" and design a database using Access 2010; to combine all of these details in one location.

So far everything has gone quite well... that is until this task came along.

Important information: While all of the vehicles are issued a number (CUA#) from our department when we get them, some of the departments re-number them with their own numbering system. Therefore ALL vehicles have a "CUA#", while some ALSO have an "Alternate ID" (ALT ID).

This task is to allow the team members to look up the vehicles information (which is located on the main form, frmVEHICLE_DETAILS_All) using either the vehicles CUA# or the Alt ID.
What have I done thus far:
Created the main form, frmVEHICLE_DETAILS_All, which contains the CUA# as the primary key, and the Alt ID as it's own column. T(Note: the CUA# field in a number field, while the Alt ID is a text field) Created a form named frmALTID_LIST, which is a modal popup formit consists of one Combobox, which is is CAPTIONED: "Select an Alternate ID#:", and is NAMED: Alt ID. (Right now it is not connected to anything, so it doesn't do anything.) it also has a "CANCEL" button on it that is not set up yet. (But that part I can figure out.) The help that I need is to be directed on how to make it so when a team member selects an Alternate ID from the drop-down list on the popup form (frmALTID_LIST), it opens the main form (frmVEHICLE_DETAILS_All) to the vehicles record that contains the same Alternate ID.

Also, I have spent way to long trying to do this task, so please don't be shy with information. While I have stumbled along thus far without a ton of help, I am not trying to learn the process for this task. I really don't see me needing it again any time soon. I tried it with a parameter query, a form filter, VBA (several different ways), etc., and nothing to this point has worked.

Thanks for the help guys!


Hi, I may have posted a question sort of like this before and I don't think I asked it right because I never got an answer.

I have a database that is used for a service company. There is a schedule to display active orders, a specifications form to print out the specs for each part and various reports.

The probelm I have, which may be due to structure, is that I have to type in the part information (ALL OF IT) in both the workorder form as well as the parts specifications form. Once I put the part in the specifications once I can pull up the info whenever but i am trying to find a way to choose a part on the workorder form and all of that parts information automatically fills in. The part info will never change or be altered.

I have tried doing this by creating a workorder form from the parts information table but whenever I put in a new order and autofill it changes the first orginal entry. I need to be able to keep track of the orders as well and this just changes the last entry. I have also tried creating a form from a workorder table that has the part info fields from the parts info table, it does not let me autofill at all this way. I tried creating a macro that pulled the fields I wanted but from the form it does not fill them all in.

Can someone give me an example as to how to do this and maybe how my tables should be set up for it all to work?

Any response is greatly appreciated

I have what seems like a very basic database begun for keeping track of clients for a social service non profit. I have set up a client form and set the client number to be unique and automatically assigned. One of the fields is for a social security number which of course is unique. What I would like to be able to do is when I go to a new record and enter an social security number that already exists in the database to be taken to that existing client's record rather than continuing in the new record. There must be a way for me to do this without doing a query to find this out. I hope I am making some sense. Can anyone help?

Have a long question but hope the answer is not long or difficult.

I wanted to track any changes made to data in a form that I have and and who made the changes, this is how it was done:

First I created a new field (updates) in the design view of the Table where I want to track changes.

Then I added the module to my database - batGetUserName

Then I pulled up the form (APPLICATIONS) in design view and pasted this code under General Declarations :

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
' This code is used to create and audit trail of who made changes to
' the record. The code capture the new value and old value of each
' change along with the date, time and user ID number and enters it
' into the UpDates field in the table. Original code found on
' Microsoft Knowledge Base.
' Added by: Richard Rensel
' Date Added: 27 Mar 2000
' Date Modified: 27 Mar 2000
On Err GoTo TryNextC
Dim MyForm As Form
Dim ctl As Control
Dim strUser As String
Set MyForm = Screen.ActiveForm
strUser = fOSUserName

' Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "Changes made on " & Now & " by " & strUser & ";"

' If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & "New Record """
Exit Sub
End If

' Check each data entry control for change and record old value of Control.

For Each ctl In MyForm.Controls

' Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "Updates" Then GoTo TryNextC ' Skip Updates field.

If ctl.Value ctl.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & " " & ctl.Name & ": Changed from: " & ctl.OldValue & " to: " & ctl.Value
End If
End Select
Next ctl
End Sub

The new Update field from the table was placed on the form. I set the field's Visible property to No, the Enabled property to No and the Locked property to Yes
The update field when diplayed can take up a lot of space, so I created a small popup form that displays the Update field and I wanted it to display when the user click on a command button on the original form. This is where the problem starts. When I click on the button the popup form does not open and I get a message box that says:

The table 'tblAPPLICATIONS' is already opened exclusively by another user,or it already open through the user interface and cannot be manipulated programmatically.

This must have something to do with the field UPDATES being on both forms? I don't know. Does anyone know why it is doing this and is there a way to make the idea work?


There is probably a very easy way to solve this problem, but unfortunately I can't figure it out. Hoping someone can help.........please!

I have a listing database which tracks all of our properties. I need to start tracking what services have been done on these properties, so I added a new vendor table and new vendor form. Then I linked this form as a subform within the main property form based on File #. So I open the main form for 123 South Street and in the subform I can see all of the services that have been performed and by what vendor. What is giving me problems is within the vendor form, I set up a macro to set values for address, city, state, zip, phone # based on the combo box of vendor name. (i.e. the user selects the vendor name and all contact information is automatically filled in). This works great if the vendor form is opened separately, but when I put it in the main form as a subform and select the vendor name I get these errors saying it can't find my vendor form.

Any suggestions would be greatly appreciated on this one!

Thank you!

I have a question on preventing users from adding a new record to a continous subform if a certain condition on the current record they are working on is not met. Let me explain…

I created a database (with the tons of insight from these forums)for tracking incoming phone calls to my department. It basically tracks the information about the call and what the issue is. Once a call is entered, a supervisor assigns the call to an individual. I have a form and a subform to do this, the Inquiry Form tracks the basics of the phone call, and the Assignee Form (subform) tracks all the individuals that the call has been assigned to. Each call gets a unique ID number, and each Assignment record for that call is linked on that number. The subform contains one field called “Completed”, which the user clicks when they have completed that particular step. Once they mark it completed I want them to be able to assign the case to another individual (essentially start a new record), but not until they complete their step. The way my system is setup now people can have multiple steps marked incomplete, which I don’t want to happen. The subform is set up as a continuous form, since the next person working on a step to complete the inquiry needs to be able to see what went on before it was assigned to them.

I hope this makes sense and any help would be appreciated. Attached is a screen shot of the form.


Hello All,

As a virgin member of the Access World Forums board and a new Access user I am trying to get started creating a database to run a stock control system for my recently opened jewellery shop.

I started off by recording all my data in Excel so I have several workbooks containing each item I have bought, the price paid for it, the suggested retail price, supplier code etc. and a unique code I have given to each item.

So far I have set up 3 tables, "Products" is the main table, "Suppliers" holds the wholesalers I buy from details and "Catagory" holds Rings, Bracelet, Necklace etc.
I have managed to import the data from Excel into the Products table so I don't need to enter everything all over again.

What I have not been able to get my head around is how to use a form (presumably) that I can type the unique code number into, enter the number of items sold and the price sold for. This should then update the stock level and also put the data into a fourth table so I can keep track of what is selling well.

Has anyone designed a similar database or knows of such an example so I can see how it is done.
I realise what I want to do is possible in Access - I just need an opening to get me going.

Many thanks for any help, examples or pointers

Hello all. I am re-creating a database and need some guidance. I had received some awesome guidance from another access site, but honestly still feel a bit lost...please look over the attached database and let me know if you think the relationships are correct...

Here's the concept:

TblItem is unchangable. It is actually a table i link to and resides in NaVision, so any changes to that are a no-go.

Ok...what i am doing is tracking the production runs of various products. We have several production lines, and they are listed in tbloperationsarea. We have several vendors, but do not always list them. For those cases where we do, I have them lsited in tblMill. Again...the Mill field in TblProductionRuns will not be a required entry. There are several causes for product to be not-so-good. Those causes are listed in tblDownfall. Tbldownfalltotals ties the not-so-good causes and piece counts to the production run. another tbl i have in there is tblrepairdata which will be used to track the piece count sent to or from repair. In the tblProductionRuns, a new run ID is created for each product, each time it is run, therefore, one day may have as many as 50 RunID's. That is ok. I just don't think i have the relationships right because each time I try to set up a form I hit some snags and deeply suspect it is my relationship set up.

On a last note...a production run will always have a LineID (FK to tblOperationsArea) but not necessarily always downfall entry or repair entry), hence the form I want to create is a main form w/tblproductionruns data and 2 subforms, 1 with tblrepairdata and one with tbldownfalltotals data.

Please help... LOL I feel dumb & lost & really really really humble.

I'm posting this in the Sample Database Section, so that anyone that wants to use/modify this for their own purposes can. Note that on open, the main page has 3 labels that act like web page links. These are labels that I've coded to act like command buttons, and to look like Internet links. I've put together what code is in this with help from this website and a couple of others, and if I did not reference it in the code, I'll state here that I had a LOT of help with it by using these forums.

There is a form for weight lifting workouts. If you're using this for tracking gym clients, you'll need to add to the underlying main query of this form. It will need gym client field(s) or something, so that in the workout form, you can set up a combo box to choose a gym client. I set up 2 sychronized subforms on this main workout form. One keeps the exercises used, using a combobox list to choose exercises. I left my list in this, and it is set up to let you add new exercises to the list through code on the "Not IN List" property of the combobox. The other subform is linked to the exercises subform. In it, you put sets, reps, and weight used. To do this for each workout, click on an exercise in the exercise subform, and then either "Alt-Tab" to go to the sets/reps subform or just click directly on the sets/reps subform. Fill in the data for the first exercise. To record the data for the next exercise, go back to the exercises subform and click on the next esercise you recorded. The sets/reps subform "should go blank, and is ready for you to enter the data on the next exercise. You may not like this format for tracking this stuff, but I really liked doing it this way for tracking progress on weightlifting. It will be fairly easy to make queries for charting reports, if you want to visually track progress.

There is a Cardio Form that can be opened from the main page. It's pretty simple, actually, and I'm going to try to set up something better later on, but this WILL capture the basics for you. Again, you'll need to edit it to let you enter the client identification for each recorded cardio workout, if you're using this for gym clients.

Change the colors up or whatever you want to do. I pulled out all data except for a list of exercises, and pulled out linked images to keep me or this board from getting into trouble over using images taken off a couple of lifting equipment web sites. I hope it's useful to someone. If you can improve it, do me a favor and let me know what you did; I might like it and change my version

Good morning,

I am new to this forum. I guessing someone here can help me with a problem that I'm having. I am designing a access database that will track warranty claims for the company that I work for. I have a form created where I enter in the warranty claims to be filed. The record source for this form is a query. I have a combo box on the form that sets the status of the warranty claim to To Be Filed, Filed, Accepted, Rejected, or Missing Information. I want to be able to set a filter on the form so either it will show all claims waiting to be filed, or it will be able to show all claims regardless of their status. I have a checkbox on the form called chkShowall. If the checkbox is checked, the form should show all the records. If it is unchecked, only the records showing the status "To Be Filed" should show. On the click event of the checkbox I have the following code. I have very simple code so far with no error handling events or anything else.

If chkShowall.value = True Then
txtshowall.vaule = "*"
txtshowall.vlue = "To Be Filed"
End If


The criteria field for the status field in the query is pointed to the text box on the form

When I run the query all by itself and put the * in the criteria field all of the records show up. However when I run it from the form, none of the records show. What is the matter with my code? Can someone tell me.

I'm wondering if it is possible to store data as part of a report, but not part of an underlying table. I have a simple calendar database with a report that prints one page for each day of a project listing all the various events for each day. I would like a version number to appear atop each page so that as events change and pages are reprinted with new information we can keep track of what information is most current.
My first thought was to place a combo box in the group header of the report (I've used the group feature to organize the record set into individual days) with fixed values of the version numbers we use. My hope was that in report view, I could simply select a version for each day and then that information would print with the report. The problem I've run into is that this information doesn't seem to "live" anywhere. Any time I navigate away from report view, the values are all reset to 1. As a possible solution I tried creating a table that would simply store the values v1 through v9 and making that the data source for the combo box. Access doesn't like this, though, because there is no actual relationship between the values in that table, and the value of the table that makes up the record set the report generates.
Is what I'm trying to do even possible, or am I simply approaching this from the wrong angle? Thank you for your help and patience.


Hello, all. I am new to this forum and have a question about a database I created last year for our family business. It is a split database with about 12 user stations and all data is manipulated through forms and VBA code. I am self taught over the years but this database is larger and has more users than any in the past so I am running into "new" issues in regards to muliple user access at the same time. The database is for part tracking, a "move ticket" number is assigned when an item is received or created. The numbering system for both received items (our raw goods) and the created items (our finished goods) is the same. Basically, I have a temp table that stores the last ticket number used (tblTICKETREF). When a user wants a new ticket it looks at that temp table, updates that record by incrementing that number up by one, then prints a ticket using that number. If the user is requesting more than one ticket then it loops for a designated number of times. All works good, except about once a month I find duplicate ticket numbers in the system. It seems to happen when two stations are accessing that temp table (tblTICKETREF) at the same time and both end up being assigned the same number. I am looking for a solution that would pause or lock one of the users until the code has finished for the other, or a better method if this one is subpar. The code loop is below. Any help would be greatly appreciated.


'begin loop for the desired number of tickets
Set mydb = CurrentDb
Do Until i = 0

'find the next move ticket number
'from the tblTICKETREF table
ticktemp = DMax("[TICKETREF]", "tblTICKETREF")
ticketnew = ticktemp + 1

'Update the ticketref field, increment it by one
mydb.Execute "update tblTICKETREF set " _
& "[TICKETREF]= " & ticketnew & ""

'insert new record into tblTicketTempProd, the temporary table for tickets
mydb.Execute "INSERT into tblTickettempProd ([ticketno], [item], [qnty], [batchdate]) values (" _
& """" & ticketnew & """, " _
& """" & Me.ProductID & """, " _
& """" & Me.txtPartQnty & """, " _
& """" & mydate & """) "

i = i - 1

Another newbie question here. In my publication database, I set up a master table (tblProjectOverview) that will be populated with data from other users to capture the basic information for each project: Project Name, if it needs funds, if it will be printed, and the names of people working on it. Many of the projects won't need funds and won't be printed, but for those that will, I need to track other data. I've already gotten help restructuring the funding tracking part of it (thanks ssanfu!). Now I need to tackle the personnel part of it.

I have set up Project Name in tblProjectOverview as a primary key, as there will be no 2 projects the same. I need to be able to link other data to the project's name, including a whole checklist of items that we will fill out if the the project actually needs to be printed.

I've set up a separate Personnel table, with names and contact info. The person's name is set up as [Last, First] so it can serve as the primary key. Then I used tblPersonnel as a lookup table in tblProjectOverview, so users can just pick the team members from a list.

My biggest problem is that I need to be able to capture which projects each person is working on. Each project will have a Lead, and some projects will have up to two more team members. A staff member may be in any of the 3 positions, but will not be in more than 1 position.

How do I view all the projects an individual staff member is working on, regardless of their assigned position within that project? In my overview I have the fields Lead, Member1, and Member 2. Perhaps this is part of the problem.

I've attached a zipped sample file (hope it works ok, haven't zipped anything on my new computer until now!). In the sample the financial structure works, and I have added the overview and other tables I'm using to test the mechanics.

As with my earlier question I'm betting it's something simple . . . I appreciate your patience! Attached Files (25.4 KB, 10 views) Reply With Quote 03-07-2011, 11:44 AM #2 LillMcGill Dagny fan Windows 7 64bit Access 2010 64bit Join Date Oct 2010 Location Southern USA Posts 64 In my overview I have the fields Lead, Member1, and Member 2. Perhaps this is part of the problem. yep, it is. In order for a field to be appropriate for a table, the field must be a fact about the primary key... a fact that no value in the primary key field could ever have more than one of. A project can have more than one person, so you cannot put persons in there. The Projects table is only for facts that a project can only have one of.

Additionally... not only can a project have more than one person, but a person can have more than one project. This is a many-to-many relationship, so a junction table is needed...
If I were you, I would make a new table consisting of the PK's from the Personnel table and the Projects table... the combination of these could serve as PK in this new table. Add a field that either is Yes/No IsTeamLead, or add a Position field. Relate the new table to Personnel and to Projects.


I am new to Access 2010. I am using the contacts template. I have set up a database for contacts. These are companies that we work with at my company. It is there to keep a track of communications between us and them. There a couple of things that I can not work out yet though.

1/ On the address book tab rather than having the attachment box next to the name I would prefer to put the company that that person works for. But when I try and change one it then saves that as the default to all the contacts and I am trying to personalise each one.

2/ There are a couple of contacts for each contractor that we use and it would be nice to have some kind of link up or to put both contacts on the same page so for company A it shows both contacts and their individual details and then the log underdeath.

Thanks for any help you can give to me in these areas.

Hi Everyone,

I have attached a copy of my database to aid in visualization of my brain wall I ran into. I am working on a chemical database to house chemicals throughout our lab. The easiest way to see what I am attempting to do is by opening the database, and clicking on relationships, as I have them laid out according to my thought process. Generalizing, a chemical needs to have multiple ChemicalID's (an autonumber PK). What I have set-up is as such:
tblChemicalInfo---to house a chemical's information from the bottle. PK is Chemical Name
tblIntermediate---Only has two fields: ChemicalID (autonumbered PK) and Chemical Name (FK for tblChemicalInfo), and acting as a "middle man" between tblChemicalInfo and the "subset" tables below.

Then my "subset" tables that house the information broken down into relevant "sections", with ChemicalID as the FK:
tblDateInfo---when a chemical was received, expires, disposed, etc.
tblInventory---to keep track of annual inventories
tblVirologyInfo---info that is specific only to one section of the lab, not necessary for other sections to document.

My idea is that users would log-in a chemical by going to a form that would have the "Chemical Name" field from tblChemicalInfo for them to select a pre-entered chemical (or enter a new one), the chemical information would automatically fill in on the log-in form, and then the user can fill in the relevant "subset" information. When the user is done, this would create a new ChemicalID for a chemical in the tblIntermediate. That way I can search for a Chemical (Acetone) from tblChemicalInfo, and see all the Acetones throughout the building (since each would have its own ChemicalID)

Have I overthought breaking my information down into those "subset" tables? Have I overcomplicated this thing? Any thoughts would be greatly appreciated!!!! Much Thanks!
David Attached Files ChemManagement2013.accdb (428.0 KB, 4 views) Reply With Quote 02-28-2013, 04:05 PM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 I suggest you review this tutorial to see the steps involved in taking some business facts, identifying potential tables, ensuring tables are normalized , then establishing relationships between tables all leading to a well structured data base.
It will also help with identifying Primary and Foreign keys.

Not finding an answer? Try a Google search.