Update And Delete Query Of A Record By Date At The Same Time..
Oct 9, 2006
I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..
Hi, on a database I created with probably over one hundred fields in it I have one text box called update. What I want is if ANY of the fields are altered it then updates the "update" field with date and time. At the moment I have started altering every field with creating an event proceedure in the "on change" entry which reads as below for a change to text5 box. (text141 is the update field)
Private Sub Text5_Change() [Text141] = Now() End Sub
Now, although this works; as you can imagine it's very time consuming going through every single field I have puting this proceedure in place. My question.....Is there a much simpler way of making this "update" field change if any part of the record gets altered ?????
Hope this isnt confusing.
I should add that each record has about 4 tabs(pages) too, so the overall record is spread over all this too. I need something that knows that anything is altered on the record and so update the "update" field
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
I don't know if this is the right place for my post. Since is in regard to queries...
I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
I have a database of personnel. People come and go all the time; however I can't have a personnel record deleted as soon as they leave. They remain on our books until a specified date for each individual. The date is different for each individual. I was hoping to have a field in their record with the date, and have a query or some code delete that record on the date in the field. Is this possible?
I am trying to have the code perform these things but unable to do so. While the code would still work, it will not move to the next user with either no date or the earliest date.
Goal: -Go through the query record by record according to the date/time field -Find the record with no date or earliest date (in that order) -change its date to today's date
Code:
Code: Private Sub Command56_Click() Dim i As Long With Screen.ActiveForm.[AMID] Dim LResponse As Integer LResponse = MsgBox("Do you wish to auto-assign to an Accreditation Manager?", vbYesNo, "Continue")
I want to make a delete query, which delete record between certain dates, I want access to ask user to specify range of date before executing the query.
This stuff can sure be stressful. :) Anyway! This is all linked to a form, but it's queries I'm sure I'm having the problem with. I'm hoping someone can help me out here.
I have fields in a subform which I've got an update query running after the focus is lost on a field, and it puts these values in a table I created. I'll call this table A. This is so that I may run another update query on the form when this is done, and have it take the values from this table created and add them to a total in another. I'll call this second table B.
I know this design behind this is flawed because in theory I don't need the 'temporary store' (A), but it's in the design of the thing to show that the values are infact being stored. As I'm sure you can understand, in order to keep doing this each time I open the form, I need to clear A. Preferably on a button press on the same form so that I may have the totals all calculated on B and then clear A right after.
The problem I'm having is that I'm using a delete query on a button press, and instead of just clearing the fields I need it to, it's deleting everything in A. I should mention that in A I have an ID field which should be telling the update/delete query which records to update/delete from in the criteria. This is a problem because it's deleting all the records on the form as I have them linked to A.
Hi. I have two forms that both look at the same table. One form is for inputting data and the second for is for closing the job. I want to create a button that when clicked automatically changes a field on the input form and also fills in the current date on a field displayed on the closeout form. I then want it exit my two forms and take me back to my switchboard. Can anyone out there help?..Thanks.
What event would you use to update a label caption relative to a number field in the same form, so that each time a new record is displayed, it goes through my complicated "if" bva code. I want the letter "th", "st", "rd" to display beside a text box relative to the value displayed. I suppose I could do this in the query. :confused:
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I am trying to automatically update one table to another each time a new record is added to my form, I have tried using the Update and the Append Query is there another way to do this without using code?
why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
i have a table1 in my msaccess and i linked another table in mysql to ms access, (table2). when table1 matches the record in table2, it will delete the record in table1.
my query is delete * FROM Table1 where table1.artid = table2.artid;
but it always ask me the value of tbl2.artid, the value of tbl2.artid is in the database of mysql and it has already records...
how can i compare the records of table1 (msaccess) and table2 (mysql) and delete the duplicate records? :D
Hello All... I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid". The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
