I currently have a database that I had to copy over each field from another databse to create the one i have now. When i copy the records from one field over it appears they all move up three spaces for some reason is there a equation i can enter to make them all move down three records in the table?
View RepliesI would like a button on my form that will move a record from one table to another. Can anyone tell me how to do this
View 2 Replies View Relatedhiya!
i have a database that holds details of calls that have been answered on the phone concerning problems with computers.
details that are recorded are caller name, and date of call etc...
however, i want to know if there is a way that i can move all calls recorded BEFORE the present date to another table ( say a "past calls")..?
is there a button that i can put on my database that will run a function and move all calls recorded before the present date to another seperate table..?
thanks everyone!
johnny
:)
Could someone assist me in getting code that allows me to move a record from one table to another when the user clicks a button.
Thanks
Hi again
I have a table that’s full of records, I want to split it into 2 separate tables (identical structure but they have input masks), The input mask is set to LLL0000 all I want to do is send valid records to one table and invalid ones to the other.
I think the best way would be an update query but it doesn’t check the input mask only the validation (and trying to break the string apart using an expression is impractical and lengthy)
Can anyone think of any way of splitting this table – I’m getting rather desperate now and have been trying for over a week with no success
Any help is greatly appreciated
Thanks
Matt
I have an Employee Database with a form that displays the employee record. When an employee leaves they want to be able to click on a button on the form and have the record moved to a different table. My thought was to do a append query to move the record from my active employee table to the termed employee table, but that would still leave a copy in my active table. I'm not real up on coding that much so Im not sure if there's a way to have code move a record from 1 table to another table or copy the data from 1 table to another table and delete it from the first table. Any help would be greatly appreciated.
View 12 Replies View RelatedHello there,
Is it possible somehow to move a record in the same table, to different row (in the form)? Or in other words, is it possible manualy adjust the position of records (rows) in a report?
I have a continuous form, where rows are filled with data (e.g. 10 rows ).Then i have report , which looks the same as form (rows, and their order). But sometimes user wants to move certain position (row) up or down, to be it on top or bottom or two places above, or below etc, of row list....is there any easy way to do it? Like some button press, where you could swap position with next record (next row) .....
Or just delete (everything) and fill again from beginning the way he wants...
Thanks!
I was wondering if anyone knew how to check when the user has clicked to move on to the the next record on a form?
Ed
Hi, found this forum looking for an answer to this question. Hope someone can help.
I'm trying to build a database that tracks electronic components. Mostly the database tracks location movements, but also repair issues and other possible changes. I'm thinking the best way is a database that has a record for each move, and any other changes are included in each record.
My question is, is there a way I can easily have the current information be propogated over to a new record, where it could then be modified? Example: I'm looking at a record of an item that was just returned to me. I would like to click a button on the form which would create a new record, then move all currently displayed information into that record.
Thanks for any help.
OK guys, ive done some searching and saw some stuff on this, but everytime I try something I get a INVALID SQL error message. So Im hoping you guys can help out cause Im lost right now.
I have a 2 tables, Training and Schedule. Training contains all the employees training records. Schedule is meant to be a temp table where the supervisors can enter the training and then once completed, can check the COMPLETED checkbox and hit the LOAD TO TRAINING RECORD command button and it moves ONLY the files for that trainee (a combo box) with a completed checkmark to the training table.
Ive tried this code sooooooooo many ways and its giving me a headache. My original way didnt work at all (had a lot of Do..Loop and With Statements, and then someone posted something about Archiving which is the code Ive gone off of now). Any help you guys can off would be awesome!
Heres what I got:
Private Sub cmdLoad_Click()
On Error GoTo Err_Load_Record_Click
UploadHistory
Exit_Load_Record_Click:
Exit Sub
Err_Load_Record_Click:
MsgBox Err.Description
Resume Exit_Load_Record_Click
End Sub
Sub UploadHistory()
Dim DB As Database
Dim WS As Workspace 'Current workspace (for transaction).
Dim strSql As String 'Sql Code.
Dim strMsg As String 'MsgBox message.
Dim bInTrans As Boolean 'Flag that transaction is active.
Set DB = CurrentDb()
Set WS = DBEngine(0)
WS.BeginTrans
bInTrans = True
Set DB = WS(0)
On Error GoTo Err_UploadHistory
'Execute the add.
strSql = "INSERT INTO [Training] " _
& "(TaskNumber, Date, Hours, TrainerLast, TraineeLast, Qualified) " _
& "SELECT " & "Schedule.Task, Schedule.Date, Schedule.Hours, Schedule.Trainer, " _
& "Schedule.Trainee, Schedule.Qualified FROM [Schedule] " _
& "WHERE (((Schedule.Trainee) = " & Me.TraineeCombo & " AND (Schedule.Completed)= 1));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
'Execute the delete.
strSql = "DELETE FROM [Schedule] WHERE Trainee = " & Me.TraineeCombo & " AND Completed = 1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
'Get user confirmation to commit the change.
strMsg = "Upload " & DB.RecordsAffected & " record(s) from " & Me.TraineeCombo & "?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
WS.CommitTrans
bInTrans = False
End If
Exit_UploadHistory:
'Clean up
On Error Resume Next
Set DB = Nothing
If bInTrans Then 'Rollback if the transaction is active.
WS.Rollback
End If
Set WS = Nothing
Exit Sub
Err_UploadHistory:
MsgBox Err.Description, vbExclamation, "Upload failed: Error " & Err.Number
Resume Exit_UploadHistory
End Sub
hi all,
I have this database I'm working on and have a couple of issues I can't work out.
The first is this, I want to have my sub forms go to the last record when moving through the main form, currently I can only get this to work when I open the form?
If I move through the records in my main form (forward and backwards), the two sub forms go to the first record only, so how can I get them to go to the last recor?
I've attached the DB to show you what I mean.
Thanks in advance
Tez
I have to move records from one table to another.
When we lose a client I need to take them out of my current table and put them in my dropped table.
I don't want to lose my summary info and I want the dropped client to keep it's ID Number which is an identifier.
Seems I can't just copy it from one table to another and then go back and delete it from the Current.
Seems I can't cut from Current and paste into Dropped.
In a perfect world it would be great if my boss could just check a box in Current Table and the record moved to Dropped all by itself!
I am designing a database to keep track of a state's surplus fire property to be doled out to volunteer departments as I see fit.
I have a table for surplus property that includes what department currently has the surplus property, what type of equipment it is and additional equipment information. I also have a table for "pending transfer property" to track what pieces of property I have decided to potentially assign to a fire department from the state's surplus inventory.
My problem comes when I need to transfer a piece of equipment from one department to another. I would like the surplus property table to automatically remove the property from the surplus property table to the pending transfer table so that it will not be listed on a pending transfer and in the states surplus at the same time. On the flip side, if a transfer falls through I'd like that record to repopulate in the surplus inventory again.
I have been tasked with creating a simple database for work.
Basically the table tracks the progress of various employment checks. So example headings are Name, CRB, Occupational Health etc. Now basically, once all the checks are completed, I need the record to be moved to another table named 'Live'.
Is there any easy way to achieve this, or will i have to manually move each record once the checks have been completed.
1st - Table one, holds around 10,000 recordes, names and phone numbers and basic address information
2nd - Table two which holds all out customer details eg full name and case details and is used by an admin team
What I was hoping to do was have one form designed from table one and when the agent changes a status it moves the record from table one to table two.
I want to filter my subform data, to only show records where field A is a higher value than field B.
Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True
This way it doesn't find field B.
Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True
This way it seems to filter all record to the field B value of the first record.
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I am trying to get the records on start and end date, still showing error no records found.
My code is like this:
Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"
[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 that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
[Code] ......
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
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
[Code] ....