I'm not even sure which forum to put this in, as it involves a form, two tables, some queries, and possibly a macro. I've poked around the message boards here and haven't quite found a method that works for my situation. So here we go!
I have a recruiting database that I've developed for some other users in the office who are not Access builders. They have asked if there is a way to archive old records into another table for safekeeping, which I can do through the database window, but which they probably couldn't. I want to give them the ability to tuck these records away as needed, and without them having to ask me to do so every time.
The main table in the DB is called tblPosition. There is a form called frmPositionManagement which is bound to this table. It's through this form that the recruiters make all their updates and whatnot to records stored in tblPosition. I also have a table called tblArchive which I have created using the exact same fields as tblPosition so I can store the outdated records there.
I'm looking for a programmatic way to do this. I was hoping to put a button on frmPositionManagement that would let the user remove that record from tblPosition and send it to tblArchive. I had figured on creating a macro (mcoArchive) that took the following steps:
* MsgBox "Are you certain you want to archive this record?"
* Run an Append Query to add the selected record to tblArchive
* Run a Delete Query to remove the record from tblPosition
* Close the Append Query
* Close the Delete Query
Then I started building those two queries and the macro, and the wheels fell off my brain.
The difficulty I'm having is getting the system to say to itself, "The record currently displayed on the frmPositionManagement is the one I need to append and delete." I have a specific record selected on the form, so how do I pass that record's unique ID through to the two queries to make this record the one which is appended/deleted?
I had tried setting the criteria for the unique key in the Append/Delete queries to =[frmPositionManagement]![AutoID], but that just led to a pop-up box that asked me to input frmPositionManagement!AutoID, which is not what I wanted at all.
My VB isn't all that great, so I was trying to stick to macros, but if someone has an idea for a programmatic solution for this conundrum, I'd appreciate hearing it!
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
Hi to everyone. I was assigned to collect all data regarding the electronic equipment of the firm that I work for and I just need a push to start with :D
My thought is to create 3 tables (one containing the users, another one containing info about the equipment and a third one to be something like an archive or a log...e.g. this monitor is now currently being used by me but in the past somebody else had it).
I am not quite sure how to do this (the archiving I mean...) Any help is welcome
I need some help working out a way to archive records. All i really want is a button on a form that when clicked deletes the record currently displayed on the form from its table and moves it to another table that i will call 'archives'.
I think it might have something to do with using an append query but i cannot think how to do it.
I have a database for tracking tasks. each task may have associated documents for it which are kept in a separate table.
I have an archiving procedure which takes closed or completed tasks (from the main table) and appends them to an archive table but it leaves the associated documents in the second table, or if I enforce integrity it will delete them.
Anyone have a suggestion on how i can archive records from two tables at the same time I include the archive code for your information....
Many Thanks as always for your help and assistance
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''' ' procedure archives closed or complete records to archive table and then ' ' Deletes original records from source table ' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''
On Error GoTo Err_btnArchiveAdhoc_Click
Dim strMySql As String, strResponse As String, intRecordCount As Integer
Dim cnn As ADODB.Connection Dim rstTemp As ADODB.Recordset
strResponse = MsgBox("By clicking Yes you will Archive any closed or" & _ vbCrLf & "completed tasks and delete the original records from the table", vbExclamation + vbYesNo, gstrAppTitle) If strResponse = vbNo Then Exit Sub Else Set cnn = CurrentProject.Connection Set rstTemp = New ADODB.Recordset
' builds sql string to append closed or complete records from the original table into archive table strMySql = "INSERT INTO tblArchiveAdHocTasks ( Seq_Number, Item_Type, TaskTitle, Task_Description, TaskActions, StartDate, TgtDate, ReviewDate, TaskLead, Status, LastUpdatedBy, estHours, Progress, PersIDAssocPerson1, PersIDAssocPerson2, PersIDAssocPerson3, PersIDAssocPerson4, PersIDAssocPerson5 )" strMySql = strMySql + " SELECT tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5" strMySql = strMySql + " FROM tblAdHocTask " strMySql = strMySql + " WHERE (((tblAdHocTask.Status)=6 Or (tblAdHocTask.Status)=10))" ' status 6 is closed and 10 is complete DoCmd.SetWarnings False
I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.
A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.
But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).
My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query....
I have an application which creates 200 rows a day. As you can imagine the tables get big very quickly. Is there a mechansim that I can use to archive off the tables to a new seperate database rather than to a table in the same database. I would like to perfom this by allowing the user to click on a button and perform the archiving automatically.
my users are non technical and requesting them to perform any tasks behind the scenes would not be an option.
I have no idea how to even begin an archive. Can anyone give some direction? I've spent two days searching the web and trying to understand the Microsoft website directions along with Access for dummies. The only thing I can find is something about using Products_Appends and Products_Delete queries.
Im about to create a button on a form that will archive the current record to an archive table.....
The question is, Before I start to look into this, I would just like to clarify what I do.
I assume firstly I would need to make an exact copy of my current table, them remove all data, then change name to something like "tbl_archive_data" ( this would be to hold the archive info right ? ) Once I have done this, I`m assuming i`ll have to make some sort of query that will copy the data to the new table, then delete the record from my current table ? Then I`ll have to create a button on my form, which can be cliocked on by the user to archive this particualr record...
In a nutshell, would this be sort of the correct way to do this ???
Any recommendations/advice will be much appreciated on this.
I know there is already some archive details in the forum, but none of them really made much sence, as they were answers to problems with this already half done....
i created an audit trail in my DB... i do need to submit reports reflecting the relevant changes made.... however, i notice that the report can be very lengthy if not renewed every month.... can i archive these trails.. on a mthly basis? if yes.. how ? if not... is there anything else i can do?
currently on my db it stores data on various projects, and these projects are sorted by a status of on hold, on going, or finished. What im trying do is move only the projects that are finished but still keeping a record of them so we can view them in the future.
i was thinking maybe i could move the finished projects into another db? but not sure how to do, or is there a better way to achieve this?
Hi, I have a form which is made up for 3 tables and I am trying to create an append query for each one in order to keep records of data before it is updated. The append queries seem to work but they append all data rather than just one selected record. I know I will next need to create a macro which can be used each time a record has been updated and a copy is sent to the archive. Can anyone help me with this, or have any useful suggestions?
At certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table and 4 records in the Classdata table, then it exports out 16 records (4 x 4). I would expect it to export out 8 records.
I'm trying to create an archiving routine as my database is becoming very large. For about 10 tables I want to shift certain records to an external database which would have the required 10 tables with the same table names and structure.
So far so good. I now want to automate everything using vba. I can see how to use the INSERT INTO statement but I don't want to have to name every field as there are hundreds. I just can't see how to do this.
If the table structures are identical how do I neatly shift a bunch of records from one to the other using code.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code: Private Sub Form_Current() If Me.RecordsetClone.RecordCount >= 3 Then Me.AllowAdditions = False End If End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry Allow Additions Allow Deletions Allow Edits Allow Filters
I have a form which needs update ever month. When the form is opened, the end user can see the old records and data, and also a new record is added for any new data. I want to protect the old saved data and the user can only add, edit, or delete the new data in the newly added record. The problem is once a user adds the new data and moves to another record or another form, then he/she cannot edit or change the new data in case if there is any mistake or need to change something after couple of minutes.
I changed the Form Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" many different times and situations to solve this problem but with no success. I tried the following with NO success too: One of the Fields of the Record is (Month). In the Data Properties, I set a Default Value for this field as(December 2014) for instance. I set the Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" to (Yes). Then I put the following code in the Form's Current Event:
Private Sub Form_Current() If Me.month.Value = "December 2014" Then Me.AllowAdditions = True Me.AllowEdits = True Me.AllowDeletions = True
I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
We have a database with one main data entry which stores records in a seperate table. There are several thousand records keyed in using this form. Therefore, alongside the entry we have a search form which allow the user to search for a specific record and display it in the main if he/she needs to edit one.
We have received a request from users for a functionality which allows users to browse all records, using the main form layout, that they have keyed in. I guess what I am asking is: Is there a way to limit the records visible in a form using field on that same form as a limitation (ie. the users name - which is a required key-in)?
The Ignore, OverTime, AnnualLeave fields are bound to a I, O, A checkboxes respectively in this continuous form. The ExtraValue bound to a EV textbox in this form.
What I want is that when the user check O or A checkboxes, the EV textbox is enabled only for this form record NOT all the form records, when the user check I checkbox, the EV textbox is disabled only for this from record, NOT all the form records.
The problem is that whenver the user check, all the form records EV text box is affected by this action.
I am designing a db for a dental clinic. I created 3 tables as follows:
1.T_Patients (P_Code(PK) and other fields) 2.T_Diagnosis (P_Code(Fk), ToothRecId (PK) and 32 text fields for 32 tooth named (UR1 to UR8, UL1- UL8 for Upper left / right Arch and LR1 to LR8, LL1 to LL8 for Lower left / right Arch) 3.T_Pmts (P_Code (FK), PmtRecId(PK) and other fields
I created one main form based on T_Patients tbl and similarly another 2 forms from other 2 tables. In the main form I placed 1 sub-form and 2nd sub-form. Bothe the sub-forms are Master / Child relationship on form over P_Code field in the main form.
Single character length has used for all the 32 text fields in table T_Diagnosis. So only one single B, C, D, F, M could be entered.
Normally we search patient’s profile by patient’s code or by name that displays in the main form. But the client needs to search patient’s record by tooth criteria.
There may many tooth fields in the first sub-form that are null and only some of them with any one of the B, C, D, F, M character. Bcz there is very rare chances of damaging all 32 tooth of the patient.
Now my clients want to search based on the data which are B, C, D, F, M characters. Means once he types C (stands for Cavity) in a search text box then it should bring all the sub-form records that hold value C in any of the 32 fields.
1.How can I do this when the search based on SUB-FORM and not over the main form? 2.The entered criteria value might by in other field also. Like say if entered C then this value may be in any of the 32 fields. So how it would bring all records?
I am confused how to do it. I attached a pic of my form for your ready ref. :confused:
I would really appreciate if somebody could help me.
I am having trouble getting this to work. I need the below code to run when I am moving through records on my form. What is happening though is if there is no date in either "cutinplan" or "cutin_actual" it is just carrying over the amount in "ExWorksYTD" from the previous record instead of leaving it Null. Do I have this in the right Event?
Private Sub Form_Current() If Not IsNull(Me.cutin_actual) Then Me.ExWorksYTD = DSum("[volume]", "qryMonth_Volumes", "[monthid] Between " & Me.cutinact.Column(0) & " And Month(monthid) AND [configid] = " & Forms!projects!.configname & "") * ([ExWorksStd] - [ExWorksAct]) Me.ExWorksBOY = DSum("[volume]", "qryMonth_Volumes", "[monthid] Between " & Month(date) & " And 12 AND [configid] = " & Forms!projects!.configname & "") * ([ExWorksStd] - [ExWorksAct]) Else If Not IsNull(Me.cutin_plan) And IsNull(Me.cutin_actual) (Added this code to see if any change)Then
Me.ExWorksYTD = Null Me.ExWorksBOY = DSum("[volume]", "qryMonth_Volumes", "[monthid] Between " & Me.cutinplan.Column(0) & " And 12 AND [configid] = " & Forms!projects!.configname & "") * ([ExWorksStd] - [ExWorksPlan]) End If End If End Sub
Hi, I have many forms which I have designed to be as idiot proof as possible howver I have one problem which is prooving difficult to solve.
When the forms open, records are attached to bound text boxes, list boxes etc. The problem is that when a form opens it should display one record only but using the scroll wheen on the mouse, the tab key and others, a user can navigate to other records. I have disabled the record selectors on each page along with the navigation buttons.
Is there any way of stopping other records from being displayed?? Any help is much appreciated.
I took over a database from another administrator. I'm not at all a pro at this, but one of my forms will not allow the user to see ALL of the records in the table. It will only show just the records that the user has just put in during that session.
Any thoughts on how to fix this? I know that the other guy had to add something to get it to work like this but he can't remember what he did.
