General :: Creating New Record After Update
Mar 13, 2014
I have a table called [Tasks] which has the following relevant fields:
[Task ID]
[Location]
I have a form that people use to create new tasks and update current ones. Location on the form is a combo box where people can pick from 5-6 different locations.
What I want:
After someone selects a new location from the combo box and changes the task location, I want to create a new record in a table called [Task Progress] with the [Task ID] of the task whose location was changed, the new [Location], and the [Date] and [Time] it was changed. It would look like
Code:
[Progress ID] [Task ID] [Location] [Date] [Time]
1 5 Station 1 1/1/2011 12:13:01
2 8 Station 2 1/3/2011 01:53:29
3 5 Station 2 1/5/2011 11:13:05
4 5 Station 3 1/6/2011 12:35:22
What should I put in the AfterUpdate event for the [Location] combobox to make the above happen?
Note: All my tables are ODBC linked to SQL Server (can't use data macros).
View Replies
ADVERTISEMENT
Aug 22, 2013
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
View 6 Replies
View Related
Mar 13, 2014
I have a table called [Tasks] which has the following relevant fields:
[Task ID]
[Location]
I have a form that people use to create new tasks and update current ones. Location on the form is a combo box where people can pick from 5-6 different locations.
After someone selects a new location from the combo box and changes the task location, I want to create a new record in a table called [Task Progress] with the [Task ID] of the task whose location was changed, the new [Location], and the [Date] and [Time] it was changed. It would look like
Code:
[Progress ID] [Task ID] [Location] [Date] [Time]
1 5 Station 1 1/1/2011 12:13:01
2 8 Station 2 1/3/2011 01:53:29
3 5 Station 2 1/5/2011 11:13:05
4 5 Station 3 1/6/2011 12:35:22
What should I put in the AfterUpdate event for the [Location] combobox to make the above happen?
Note: All my tables are ODBC linked to SQL Server (can't use data macros).
View 3 Replies
View Related
Jan 5, 2005
i have this problem that is bugging the crud out of me:
sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"
conn.Open connStr
conn.Execute(sql)
conn.close()
Set conn = nothing
when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...
View 1 Replies
View Related
Jul 16, 2014
Not sure if it's possible but I'm trying to create individual calendar's for each staff member and client to put availability and bookings on to them. I've tried using the active x control but it's not really what I want, not sure how else to approach this as my access skills aren't that great.
View 3 Replies
View Related
Jan 20, 2014
I have a table that deals with current data (as in member rentals of items). The normalized tables that we have been given include a rental history table. The idea is that, when the member has returned the product a history entry is made in the completed rental table.
I wish to have a button which is clicked to triggers this event. Would I program a macro to do this? In other words, would I create an event that passes those values to the relevant fields in the other table (Name, date issued, return date etc)?
View 9 Replies
View Related
Mar 22, 2013
I've recently come across a problem whilst saving an edited record..I get the following message but only sometimes..could not update currently locked by another session on this machine..Sometimes the record saves without a problem (mainly on the first edit). However, any further edits of the same record mainly but not always triggers the above message. I can't work out why it's happening, It's a split database but I'm the only user on a standalone PC (so no other users editing the same record). Sometimes I get lucky and the record save even after the first edit but not always. There doesn't seem to be any pattern.
Record locks for the form are set to NO LOCKS
Recordset type is set to DYNASET
In settings -
Default open mode it set to SHARED
Default record locking is set to NO LOCKS
Open database by using record-level locking IS CHECKED
This has been working fine for years so I'm a little stumped as to why it should happen now. The only thing I've changed recently is a memo field from Plain text to Rich text.Whilst writing this I just went back and double checked and found that if I change any other field (not the memo field) the record saved just fine. So the problem is to do with the Rich Text memo field only.
View 4 Replies
View Related
Apr 3, 2014
I have a create new record from with Employees information taken from tblEmployees. On that form I have a dropdown box of company names taken from tblCompanies. How do I get that to input a ClientID into tblEmployees when I add the new employee? At the moment it just adds a new employee but leaves the ClientID box blank.
View 3 Replies
View Related
Jul 25, 2014
I am having a problem with retrieving the information I am looking for. Basically I am checking to see if a user has selected a TRNumber from a drop down menu and if that TRNumber matches any other records in the table in that field. If it does, is that Number already associated with the user selecting it. If it is I want to get the ID number so I can simply update the record with new information. Here is what I have so far.
Code:
Dim Rs As DAO.Recordset
Dim Tech As String
Dim MsgStr As String
Dim IdMod As String
Set Rs = CurrentDb.OpenRecordset("PrepStartDateBlankQuery")
[Code] .....
View 14 Replies
View Related
May 20, 2015
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
View 3 Replies
View Related
Apr 13, 2013
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
View 9 Replies
View Related
May 18, 2005
Hi All,
I have a form that when loads, runs some VBA on the "On Current" event.
Some of that code, fills in text boxes, with concatenated (?) strings.
So every time I use the record selector and get to the end of all the records, the "On Current" fires adds the concatenated string (even if it's empty), and that then becomes a new record! Is there some way i can prevent this, but still keep the "On Current" event, some type of logic I could run before the concatenations etc.
Any Help would be appreciated.
Cheers
BAzZ
View 2 Replies
View Related
Feb 1, 2006
Well from reading the forums i have learned alot about access but now i am stuck and can't find what i am looking for.
I am building a database basically to track the Software bought for our company.
I have my Application table and my purschased table. The relationship between the two is ApplicationID.
What i need is to be able to do is look up the ApplicationID and then have that vaule be added to the new purschase record. My method of doing this was to created a form with 3 list boxes and 1 text box (not viewable). The list boxes are Company, Application, Version. The text box is the returned ApplicationID.
The list boxes dynamically adjust based on your selecetion to find the vaule of the 1 text box "ApplicationID".
Once you have selected the application you want to add as a purschase i have a button to open the Purschase form. I have everything that needs to be filled out in the form. My problem is getting the vaule of ApplicationID in the purschase form for the new record.
My problem;
Right now when the Purschase form opens it as all the data and the ApplicationID is shown as being correct but not selected. Right now the user would have to manualy select the vaule for the data to be commited to the record.
How do i selected a record so the data is submittied and a new record is started, on open, then set the focus onto a new field. This way the user does not have to do it manually.
I hope that makes sense to everyone.
View 2 Replies
View Related
Oct 29, 2007
My form contains the field Bill_Date, which may or may not be input during data entry. What I need is to run a query (I think) that allows me to input a specific date that will automatically populate all Bill_Date fields that are currently null with the date specified. Eventually this will end up on the front end where a biller will click on a link when they open the db, input the date into the field, and then process another report.
I have absolutely no idea how to even begin and was not able to locate any specific information in Help or here on the forum.
Many thanks.
View 7 Replies
View Related
Apr 23, 2015
I've got two tables - one that works like a cypher and one with all my records.
I have 2 queries. One that is a simple query that extracts data that is not so user-friendly and puts it into a user-friendly format. The Other query is for any records that the first query excludes because the new combination of data (lets just call it a code) is not in the cypher table, but needs to be added.
How do a create a form that will show the records in the 'excluded records' query and allow me to select from a drop-down list a specific set of categories to update the cypher table ?
Example :
Record Table: Cypher Table:
Ford Focus = Sedan
Ford F-150 = Truck
Ford Freestyle = Wagon
Ford Escape = SUV
Now a new model comes in, a Ford Edge which is an SUV but is not in my cypher table.
I have the query to pull in the excluded Ford Edge, but I want a form to show "Ford Edge" in the first column and be able to select from a drop-down list "SUV", and either automatically update my cypher table or require me to press a button to update the table (whichever is easier).
View 6 Replies
View Related
Feb 5, 2008
hi i want to create a loyalty points scheme though a database,
general rulse apply 1 point per £1,
when the customer spens some money the staff then go to the machine and input the relevent data.
then a statment will be printed out for that customer with the points to date, and thier purchase the amount of point collect with the new total.
To search the full name and postcode would be adiquit as the printed statment could be lost and so could the customer id get confused by the customer.
for the report the layout would look like this:
eg
customer ID
cathy smith
e-mail,
address,
postal code,
02/12/2007 points to date 200
05/01/2008 puchase made £30.00
05/01/2008 new points total 230
if they use points it would look like
02/12/2007 points to date 200
05/01/2008 puchase made £30.00
05/01/2008 points use 125
05/01/2008new points total 100
point can be enterd manually and removed manually as customers want to save like a saving scheme
please help me with is as i have no idea where to start and if it posible, and limited knoledge of access
thanks in advance
View 4 Replies
View Related
Nov 3, 2013
I get an error "update or cancel update without add new or edit" which seems to point to this code.I am using MS Access 2010.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store when record was last modified and by who.
'Initially stores when the record was entered.
If Me.NewRecord = True Then Exit Sub 'Exit if new record
Me.DateLastModified.Value = Now()
Me.LastModifiedBy.Value = getUser()
End Sub
View 2 Replies
View Related
Feb 20, 2015
I'm working on a project that has project start / end dates which can change. A report is created based on this data. The customer wants me to save all changes as a new record and disregard the older record when the report is created. I was thinking that I would add a (yes/no) column that marks the new record as the most recent (yes) and marks older records as (no). Then I would modify the query for the report to only include records with 'yes' set to true. I was also thinking that I should create a second table that has a foreign key for the original record from the main table. The main table would have the most recently edited record while the second table would have the previous records. Which method should I use?
View 3 Replies
View Related
May 10, 2013
I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.
Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?
View 4 Replies
View Related
Jan 26, 2015
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
View 4 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Nov 4, 2014
is there a way to create a profile on each person on my database, to show their contact details and whether they've made a purchase from me before, or must I literally just make a table entering each individual person and purchase?
My boss (in another job) showed me very quickly an extremely old database one of his old employees had made for him years ago containing all of his clients, each shown individually, and he was able to click through each one, and each window showed the client's name, contact details, personal details, etc. and he just had hundreds of entries for all of his clients and was able to scan through each individual one. I am 99% sure he said it was on Microsoft Access that he'd done it.I just want to monitor how many of mailing list subscribers have also made purchases and whatnot, as I do rewards for them, and it'll be much easier to view each person's profile and the orders they've made than have to scan through a table to find each separate order?
View 6 Replies
View Related
Jul 4, 2012
I'm trying to create a button who is able to send a email with an attachment.The attachment will be all the time the same file.The email address to sent to is one of the field of the database.I need a copy sent in my email address as well.
View 9 Replies
View Related
Jun 24, 2013
I need to create distinct transactions for each invoice, so that if the static data changes, such as rates of charge, it will not automatically update any history, where a historic rate has been charged.My current thinking is that I create a table and effectively archive all invoices that have been created, as they are produced.
View 4 Replies
View Related
Jul 23, 2012
is there a way to create an email through word and say your current balance is £1000.
i thought that as this is an office app then it must be possible. could it be something like
=DLookUp("c:UsersChrysalisDesktopDatabase.accd b[LoyaltyPoints]","tblClientDetails","EmailAddress= " & Mail Recipient)
View 1 Replies
View Related
Nov 9, 2014
I am quite new to MS access and started to develop a small database for my practice. I have made four tables that are for Patient details, their doctor detail, their problem and their treatment. I have successfully created form to enter the data.However I an unable to proceed, now I want to create a discharge letter which will be addressed to the doctor (based on the tables described above)
I want to make this in a way that if I enter the patient details i-e date of birth, ID and / or name a letter is created based on the tables above. I also want this letter to be saved for future use. how to design that letter and should it be a query or report.
View 6 Replies
View Related
