Creating A 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.
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
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 1 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
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
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
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
Feb 8, 2006
Hi,
I have a Edit Form, where users edit the customer information. I want to add a Button which creates another copy of the same record in the table.
How Can I achieve that? any suggestions?
Regards
-KJ
View 4 Replies
View Related
Mar 28, 2006
Hi there,
I have a form with information on it relating to several linked tables. I would like this information to be duplicated in the tables and a new autonumber assigned.
Is there a way that I can do this by clicking one button and the autonumber will automatically generate a new number keeping the rest of the information in the form and updating the tables with a new record?.
Thanks for you help
Belinda
View 2 Replies
View Related
May 4, 2007
hello,
I have form "QUOTE" (which has a check box "GenerateOrder") and form "WORK_ORDER". Once I click on "GenerateOrder", I want form "WORK_ORDER" to open, and I want to assign a value from a txtBox in form "QUOTE" to another txtBox in form "WORK_ORDER" creating in this way a new record on form "WORK_ORDER". I want to make clear that as soon as the value is passed or assigned to the txtBox in form "WORK_ORDER", a new record for this form should be created.
I tried this code:
Private Sub GenerateOrder_Click()
If (Me.GenerateOrder.Value = True) Then
DoCmd.OpenForm (WORK_ORDER)
[Forms]![WORK_ORDER]![QID] = [Forms]![QUOTE]![QID] 'assignment
End If
End Sub
but it doesn't work
Thank you very much
View 2 Replies
View Related
Feb 13, 2008
Hi
I am trying to create an alert function so that when a user open up a form and displays a record, it will also display any alerts that have been created for that record. e.g. missed payments etc.
I have created a table to store the alert data along with the job number to which the alert relates so in essence I can have more that one alert per job record. I amtrying get one of the alerts to display ok using the following code
alerts = DLookup("[Job _Number]", "Alerts", "[Job _Number]='" & Me.Job_Number & "'" And "[Start_Date]" >= Date)
You will see that I am trying to only display alerts that are within the valid date range (I have not yet included [End_Date] as I cannot get the code above to work.
Can anyone help me correct this code please?
The other thing is that because I am using a dlookup, I am assuming it will only display the first alert it finds in the table and that's it? If so, how do it get it to display all 'active' alerts with a matching job number. By active I mean where the Start date is >= Date() and End date is <= Date()
I would really appreciate any help on this..
Thank you all for reading.
View 4 Replies
View Related
Dec 16, 2004
I am trying to put a spreadsheet on a form that will allow me to enter data on the spreadsheet and store the information for each record.
Example: I have patients that I am using a combo box to bring up their name and address information. I want a spreadsheet tied to each patient that I can keep a running total of their payment history. Is this possible and How? Thanks for your help!
View 1 Replies
View Related
Sep 16, 2014
But I use one for creating new records in a table.
And one to View/Edit exiting records.
When I open an existing records, and tab through the fields. Once I pass the last field it creates a new record.
View 2 Replies
View Related
Oct 12, 2012
I have an access database that I use to manage my car dealership, part of it holds data of a stock vehicles, another aspect is invoicing.As part of the invoicing form, I have a part exchange vehicle section which creates a new record in the part exchange table.
What I would like to be able to do is when a new vehicle is added to the part exchange vehicle table it automatically adds it to the main vehicle table so it shows as a stock vehicle.
View 1 Replies
View Related
Dec 14, 2007
Hello all. I've tried searching the forums as well Google for an answer for my problem, but perhaps I'm not searching on the correct key words. Would someone please point me to a post that addresses the below issue.
My company has a customer service (CS) application where our CS agents document CS requests. For reporting purposes, they drop a text file containing open service requests that I then import in Access 2003 so I can analyze the data. I've had the vendor use È (ALT+0200) as the delimiter and double-quotes to enclose text.
This has been working well until the application vendor upgraded the app last week. Now, whenever a user hits the [Enter] key to create a new line in the comments section of the application, a new line is created during the import process into Access.
When I view the text file I can see the Enter Character because it is displayed as (except it looks more like a rectangle standing in its short side.)
I thought if creating a macro to run on the text file to delete the enter characters, but I can't find a way to systemically find the character. So far, the only solution on my end is to manually delete the characters, but that's unrealistic because there are thousands of records.
Thank you in advance for pointing me to a helpful post or commenting here.
Stephen
View 5 Replies
View Related
Apr 10, 2005
I have a form. In that form I want to display a series of dates from a database table..ex. 12/15/2004, 12/16/2005, etc... Next to those dates I want to have a checkbox. The user should be able to click on that checkbox if they want to sign up for that date. So there could be more then 1 checkbox checked, but there will always be atleast 1. So my first question is, how do I make this work. I've tried several things and everytime I check on the checkbox it checks all of the checkboxes.
PLEASE HELP!
Subject 2: Regarding the above question, how do i reference those dates that I've checked the checkbox next to, to put into another table?
PLEASE HELP!
View 1 Replies
View Related
Aug 4, 2005
Hi,
I want a user to be able to create a new record in the main form, after they have finished filling in the boxes in a subform by pressing the Enter key, rather than having to click the 'new record' button on the main form.
I reckon I need a 'default button' on the subform but I have no idea what this should do when clicked to create the new record...
View 1 Replies
View Related
Apr 26, 2006
Please forgive me if I don't explain everything properly, I am relatively new to Access.
I have two tables with the following information:
VOLUNTEER
PK: volunteer_ID (autonumber)
name, address, school, etc
SCHOLARSHIP
PK: scholarship_ID (autonumber)
description (text)
amount (currency)
FK: volunteer_ID (number)
One scholarship can be awarded to only one volunteer.
I have a form to enter all the volunteer information. At the bottom of the form, I would like to place a button to open a new form to award a scholarship to this volunteer. I believe I need to link the forms in the following manner:
VOLUNTEER.volunteer_ID = SCHOLARSHIP.volunteer_ID
The problem is this: The forms are not linked properly because in order to assign a volunteer to a scholarship, the scholarship must already exist. So when I click the button to open the scholarship form, the scholarship form is empty and the volunteer_ID defaults to "0".
I think this could be fixed by somehow making my button create a new scholarship_ID in the scholarship form and THEN linking the volunteer_ID fields.
Does this seem like a solution? If so, how would I implement it? I have a screenshot to help. Thank you in advance for your help.
View 6 Replies
View Related
Jul 27, 2004
I'm having difficulty controlling data entry to (disabling) a subform when a new record is being added to the parent form. I can set the subform to be disabled if there is no index value in the parent form (this allows the subform to function for existing records) but then the user has to create the parent record and move off of that record and then return to it in order to add child records to the subform. This is basically a create new records/data entry problem and I'm feeling pretty stupid. There must be a way to handle this...
View 3 Replies
View Related
Dec 17, 2013
how to use the Like statement when creating a record-set of data through VBA. Before I was always able to find work-arounds but now is the time to slay this issue once-and-for-all.A person can build several sales quotes for a specific company and I am trying to find the last sales quote that was built. The function is passed a variable length string and I am trying to build a recordset of all quotes based on the variable. Here is the offending line of code:
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & "*" & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an empty recordset)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like ""*" & strProposalBase & "*"" ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is an invalid column name)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is incorrect syntax near the word ORDER)
I have tried different variations above and beyond these strings and get one of the three listed errors.
View 6 Replies
View Related
