Before you start yelling, use the search. I did.;)
But the solution mentioned on this forum (place me.lastupdated = Date() after BeforeUpdate event) doesnt work for me. My situation: I have two forms. On the first you are asked for an registrationnumber. This is used to open the second form with that registration number so you can fill in data. I want the date on which things are changed on the second form to be saved. If I use the BeforeUpdate event, I get an error when I try to open the second form. When I use the AfterUpdate event, the date is changed but not stored. Any solutions? :confused:
G'day, I'm trying to replace a date on a table using an update query. I have the criteria set to [enter date] but it won't put it in the update to block. I know this has to be one of those easy one but after a search and using help I can't nail it down. Much appriciated, Tim
Guess I should add that I'm going to use a command button on a form to run the query and I want it to prompt me for the date.
So i have a field called [LastUpdate] that i want to have change to the current date to show when the form was last updated. which event do i use to make this happen and should this be a form event or this field event? I tried using the On Data Change even for the form and then just assigning [LastUpdate] = Date, but that didn't do anything. any ideas?
Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!
My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].
Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)
The db has been so successful :confused: that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).
tblIdeasBank: .IdeaID (Primary Key) .Originator (stored as PersonID)
With a One-Many relationship from PersonID to Originator
Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?
I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!
I have a list of codes that need to be changed if it is part of the a list that need to be updated after being entered into the database. I created a function that holds the old values and what they need to be updated to. To get this accomplished on a form do I just need to write a Update query and then reference the query to the appropriate field I am trying to update with new codes for the 11 codes needing to be changed and leaving the other codes the same.
I have a database that I imported as a comma deliminated csv file. To keep my original text comas intact I changed all the commas to semicolons before I created the csv file and imported the data. I now have a data base with semicolons in place of the original commas.
I am trying to use the update query function to change all the semicolons back to commas in one of the database fields called File_name
I have a data entry where a record in the File_name field looks like this for example
Chevrolet cavalier; red
and I want to change it so that it looks like
Chevrolet cavalier, red
I run an update query with the criteria set as Like "*;*"
and this finds all the records which have a semicolon in them
I have set the update field to "*,*"
but all this does is to change the record from
Chevrolet cavalier; red
to
*,*
Is the query update the way to go and what is the syntax that I should be using in the Update field just to change the semicolon to a comma and leave the other text intact?
I am trying to update values in an unbound text box based on a selection in the combo box on the same form; however whilst I am easily able to display the information for the first selection the values do not update when I change the value in the drop down.
I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.
My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.
I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.
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'm a Access novice. I have tables with existing data in numerical form, and would like to know how I can use Queries or VBA code to update these values into a new format in a new table. for example the original data might be of race type:
1 - caucasian 2 - african american 3 - hispanic 4 - other
how can i achieve this in Access? i know how i can do this conceptually with "if" and "case" statements, but I have no idea how i can do this in Access. I don't want to mess with the original data, so please help with CODE or QUERY examples.
I have Access 2000. I want to update all my records in one table so that they only have 2 decimal places instead of the 10 they have now. What is the expression used to do this?
I have a form with a Combo box, populated from table A. From this list, the user can select a item for data entry into table B. If the desired item is not found in the combo box, the user can launch another form that will allow the item to be added to table A. However, when the user returns to the data entry form for table B, the new item doesn't appear in the combo box.
I saw this problem addressed in another posting, but the solution (which required code behind an Exit button) will only work if the data entry form for table B is open :eek: . So, if the user wants to only do data entry into table A, an error will be raised when they try to exit.
Other than creating a seperate data entry form strictly for table A, is there an easier way to update the combo box on the entry form for table B?
I have a form with a listbox in it. I want the listbox to update as the record changes.Let me get a little more in-depth...The form will be used to identify merchandise that exists in the company to fulfill current POs.
What I want is for the listbox (which displays Store Locations) to update when the PO# updates on the form (so as the record changes).Currently, the listbox only pulls all the store locations no matter if you hit next record to get a new PO.The two main fields I am working with are PO# (text box) and Store Locations (the listbox).How can I get the listbox to update as the record changes on the form?
As the unbound parent list box record selection is changed, I want the sub form to refresh. Do I put code in the Subform Current Record event?
Master Form Name: frmsr_NewWellEntry Unbound Listbox - when record selected the primary key populates txtNavWellID (unbound) on parent form
subform Container fsubsrNavSHLBHL Link Master Fields =Forms![frmsr_NewWellEntry].[txtNavWellID]
The read-only form in the subform Record Source is something like: select * from vsrNavigatorSHLBHL where Well_ID =90243..Key The Form used as the subform above will be re-used in multiple parent forms. The parent form data is form SQL Server, the subform from Oracle.
I have an inventory of equipment that is assigned to different locations. When repair is needed, the equipment is brought in and swapped with a loaner. How can I update on a form the location change from "A" to "Shop" that will show up on the related equipment table? I have "Location" in the table, but don't know if I need something to the effect of "NewLocation" to assist in showing the equipment swap.
Given a table field that is a hyperlink type. I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value. Any ideas how? Thanks.
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
I need to update a table at a client's site to change a couple of fields, and would like to be able to send them something that they can run on their end to perform an insert query.
I cant update the tables via RDP etc. because the database needs to be in Access '97 format, and they all run access 2003.
I also cannot get them to send me the file for updates since it needs to be online basically 24/7.
I have a query that allows the user to see how many hours we've flown, sorted by month for the current year.
Problem is that in the query I have to manually put in the criteria "january 2007" or "february 2007" or etc....
What I would like it to be able to do is based on the current year (2007 for instance) set the criteria by itself. Someday I might not be around to reset the year in the criteria so therefore it should be automated.
Anybody have an idea what I'm talking about and a simple solution a hack of a programmer could figure out?
I have just upgraded my large client database from Access 97 to Access 03. Unfortuntely all date of birth prior to 1930 are being displayed as 20??. IE date of birth 1928 displays 2028. Is there any way to automatically change this without altering the input mask and changeing each record individually. I have approx 15000 on the database, so not looking forward to doing it manually. Any suggestions appreciated.
I have a db that is a checkbook register. I use a field in each transaction record to tell whether the transaction is current or is a future transaction (if it is in the future then it doesn't yet affect my balance). For instance, if the item is not scheduled to be paid for two weeks (I schedule payments with online banking), I check the "future item" box, then that record for that item isn't included in the report that tells me my current balance. How can I automatically take away that check mark on records when the date in that record becomes todays date (or before today)? Because at that point it is no longer a "future" transaction but is current. I could do this manually but it seems like there should be a way to do it automatically.
After having to take on the work of someone else and finding that the dates were stored as text in the format yy.mm.dd I would like to change it so it is dd/mm/yyyy and stored as a Date. I was thinking about using an update query rather than go through 10000 odd records!
