Delete Entries From Combo Box Without Deleting Historic Data
Aug 3, 2013
I'm using Access 2010. I have a form that has a combo box. The combo box lists all employee names and one has to be selected. I've been adding employees over time as new employees come. This DB is 3 years old so there are many employees showing in the combo box selection that are no longer here. I'd like to only have the current employees visible but I do not want to just delete them from the Table because then the historic data wouldn't be complete. How can I only show the current employees in the combo box and maintain the data in the table?
I am trying to design a database for an HR management system. I am not very clear on how to handle historic employee data in this database and will need some help and advice please. Any comment you can provide, i'd really appreciate it.
We will like to have record of historic data and the date of change: Employee address change Employee name change Salary change Position/Title change Office location change Number of dependents change Bonus received year-to-year
E.g. if an employee name changes, we should still have to have a record of what the old name was. Scenario: Jacqueline Peters got married 2years after her employment date and now goes by Jackie Harrison (the changed was made on the system), she leaves the firm 10years later. Many years later, someone calls about a Jacqueline Peters that worked here, we have no way of knowing who that is. Now, we would like to the salary she was earning 5years after she started here though her endsalary has changed 10years later she got a big promotion (salary change) and changed department, we have no way of knowing what her salary at a particular time was and what her old title was.
What's the best way to design this database to handle historic data? Have you worked on something similar? How did you go about it? Any ideas and comments will be greatly appreciated.
i'm building a MS access DataBase for my customer. in between i'm always testing if it works fine (VB code, DB connections, etc.). But at the end, i would like to have DB empty of those test entries.
what is the best and savest way to delete these entries (so non of the traces are left behind)?
When i enter entries into Access, and then they are deleted, for some reason the size of the Access Database doesn't reduce in size. I've only just noticed this as i accidently entered over 65000 entries in and then had to delete them now the database is twice it's usual size.
Can someone please tell me how to reduce the size of the database and explain why when you select delete record it doesn't actually delete the record.
I have a columnar form with a sub-form based on an updatable query. I've created a delete button on the master form with the intention that if the user hits the delete button, it will delete the current record (like its supposed to, right?).
But what is happening is when the delete button is hit, it is asking twice for deletion confirmation and deleting the current record AND the next record. Even if I hit "Cancel" to stop the deletion, it still deletes the current and next records.
Each record has a primary unique ID that is included on the form (but not tab stopped - if that makes any difference), but it doesn't seem to be using that as the basis for deleting.
I've tried two different codes for this:
Private Sub Command110_Click() On Error GoTo Err_Command110_Click DoCmd.RunCommand acCmdDeleteRecord Exit_Command110_Click:
I made a delete query. In the query the from table is linked to it's child table. I dragged down a field from the child table and specify Is Null as crieria
If I view the query it shows all the entries with no children, which are exactly what I want to delete. However when I run the query it gives this error:
What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!
I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).
Code: Private Sub Form_AfterDelConfirm(Status As Integer) Dim KillFile As String KillFile = me!MailLocation Kill KillFile End Sub
Any idea how i can create historic view of the table/record so i can view any changes that have been made to a specific record?
For example...
I create a record. Later somebody comes and changes some of the data on this record. I would like to be able to view all the previous data before it was modified for this one record.
I want to know if possible how to define the structure or function that when you have a table "customers" and yoy (f.instance) change the address, you can save, modify the actual value but conserve the historical value.
Previously I am reading on how to create a Form that permit to obtain all the data required (not to complete) but to consult and if u want to modify.
I think this form could be easy but I want to create the register of the modification so that if any problem I can check the person who changed the data when and what items.
What code do I need to delete a record using a form and a combo box. I need to be able to link the selection in the combo box to a button that, when pressed, will delete the selected record.
Information:
Account Executive Combo Box - select an account executive based on a table (that contains only account executives and an autonumber)
I'm trying to populate a combo box with the last few entries of a record. I've been looking on SQL Queries and can see a way of using SELECT TOP on the recordset but querying/sorting the whole record every time seems a very clumsey way of doing it (especialy if the recordset gets large). So is there a more elegant way of getting lastrecord,lastrecord-1,lastrecord-2, etc into my combobox ?.
Note as a suplemental question is there a way of a subform showing records from last to first instead of what seems to be normaly first to last?
Hi, I am trying to choose an entry from a COmbo Box which ahas a Lookuop underlying in it. I then want to display the additonal four entries in the form it self. The data is already created and displayed in a query. The primary key gets displayed but thte other data cannot be displayed. Is there any way I can display the other details eg: Firstname, Surname Age in the same form but under different fieldnames. I am not sure how to do this. I could retype it all in but that is uneconomical. Any ideas? Please help. Thanks.
sorry to bother everyone again, this is such a great forum, and ive gotten soo much help here.
i have a customer table, containing custRef, custName, drawerRef, Address, Phone, Fax
now it contains site addresses so the custName can be the same for quite a number of entries. no the combo box in my form obviously shows all entries for custName which means the same name repeated sometimes 6 or 7 times, as i only need to select one, is there a way without looking to different tables etc to just show unique entries. i dont want to list them all in another table because i dont know them all yet, so i want it to lookup the list as i enter them.
I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.
The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.
My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.
I'm having trouble making a query that will give him the balance for each day in the past.
The important tables/fields here are
TBL_MAIN - dateReceived (date) and amountRefunded (currency)
TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day
So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.
I am trying to get a 2nd combo working from the first .
I have a table called Keystage (tblKeystage) and I have also named my 1st combo cboKeystage. then by following the vba below I created another 6 tables with corresponding names and combo's.
Private Sub cboKeystage_AfterUpdate()
On Error Resume Next
Select Case cboKeystage.Value Case "Foundation" cboYear.RowSource = "tblFoundation" Case "KS1" cboYear.RowSource = "tblKSOne" Case "KS2" cboYear.RowSource = "tblKSTwo" Case "KS3" cboYear.RowSource = "tblKSThree" Case "KS4" cboYear.RowSource = "tblKSFour" Case "Post16" cboYear.RowSource = "tblPost"
End Select
End Sub
The combo boxes both work, brilliant. My problem is that when I enter another record and make a selection from the 1st combo box, it changes all the records.
I seem to remember that there is a way, in a combo box, of entering an entry that isn't displayed in the combo box, so that you can then process that entry into a form's underlying table?
I think the example of doing this was in the MS Samples database for either Access 97 or Access 2000.
Does anyone recall what I'm talking about? Second question, does anyone have a link to those sample databases? Our technicians didn't bother loading the samples for us, so I'm kinda stuck.
Thank you tremendously to any and all who reply..... I'm looking forward to your responses!
I've got a combo box that lists duplicates. I want the dupes removed.
I went to the Query Builder for my Combo Box and set Unique Values from "No" to "Yes" - Saved, then re-opened the form. It still lists dupes.
I went back into Query Builder and unchecked the box for the Patient_ID (Autonumber) field. I saved it, then viewed the Datasheet for the Query, and it correctly lists the data I want WITHOUT dupes.
However, when I go to Form View, the combo box lists several blank entries..
The entries come back when I re-enable the Autonumber field in the Query.
This is what I currently have...
SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;
I have two tables and their associated forms. Agent Firm and Agent Person. I have created a one (Agent Firm) to many (Agent Person) relationship because a Firm can have many Persons (not vice versa).
I have also created a combo box autofill so that some of the information from the Agent Firm can be posted on the Agent Person.
Query one: I tried using text for the AutoFirm ID because each firm's name is unique. However, when I tried to see what happened if the firm changed its name, still having a unique value, I was not allowed to. Why? and can I over-rule this? For the time being I have used an autonumber as an ID, but I feel that is inefficient.
Query two: I have set up a combo-box, which works, using the following:
Private Sub cboAgentFirmID_Change() Me.txtAddress1.Value = Me.cboAgentFirmID.Column(2) Me.txtAddress2.Value = Me.cboAgentFirmID.Column(3) Me.txtAddress3.Value = Me.cboAgentFirmID.Column(4) Me.txtTown.Value = Me.cboAgentFirmID.Column(5) Me.txtPostcode.Value = Me.cboAgentFirmID.Column(6) End Sub
I originally had a first line Me.txtAgentFirm.Value = Me.cboAgentFirmID.Column(1) but deleted it because the AgentFirm is being entered in the AgentFirmID box (why??)
AgentFirmID, which is the autonumber primary key (see query 1) in Agent Firm is the link between the two tables in the relationship.
Query three: A major feature should be that if an AgentFirm changes its name, or address, phone number, etc this is reflected immediately across all related entries. And it does not happen: each one has be updated manually.
(Imagine if a bank changed its name and had to alter each account manually!) How do I make it work as I am sure it is meant to?
I'm quite new to creating a database. On my navigation form, I have a dropdown list/combo box that contains up to 16 possible selections. The user should be able to select more than one item, but can't, when using the navigation form. In the man form that this 'writes to' the selections can be made, with checkboxes in front of each item in the list; but not in the navigations sub-form. the Navigation sub-form only shows the list in a column. I don't see any properties that explicitly allow more than one suggestion or restrict to only one, so am I using the correct control?
I am currently looking for a way to add data in a form with picture and then be able to view all entries with data.
Basically what I want to see is "product image > item number > client > description > material" with for example 500 entries, i want to be able to see a whole list of all entries. I would like to be able to make every entry as described above.
I realize that some data entries are no longer needed. I want to delete them. However, I have this data within a relationship dependent upon autonumbers. For instance, table 1 is linked to table 2 is linked to table 3. As of now, only data in table 1 exists. In the future, corresponding data in 2 and 3 will need to be entered. How can I make sure that by deleting some entries within table 1, that i will not jeopardize the functioning of tables 2 and 3? What does one do in a situation like this?
I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.
The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.
I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.
Firstly I rather unsucessfly tried the search function,
I want to automatically delete any enteries older than 6 months, I do NOT want to archieve them, simply delete them. I only have three entries; Date Address Line 1 Post Code
I was unsure how i would go about this or even where to begin so some help would be much appreciated.
I have a project that was given to me because I have previous experience with MS Office, but my level of Access experience is somewhat below par. Here is my problem: We are using one form (Enter Required Training) to create a row of data that goes to a report to let us know when employees need to be trained by.
This report is called 'Past Due - Required Trng'. When employees have completed a training course we go into a form called 'Enter Training Attendance' to update another report to show that they have completed training. However, once employees have completed a course, we have to manually go to the database where the required training courses are stored to manually delete them so that they do not show up on the report.
My boss would like me to find a way to automatically delete the required training information without having to manually delete them each time. Is it possible to have the 'Enter Training Attendance' form delete the record created by the 'Enter Required Training' form so that it does not show-up on the report once completed? I have been messing around with the datasheets, events and even formatting but I can't seem to get anything to work.
