I have a table where records are updated regularly with new information [date & engineer] & the old information over written.
What i want to be able to do is every time a field is updated with new data, write the new data to a second table as a new entry so that i have an archive of all old records held in a second table.
Thanks for your help in advance.
Larry.
Hi there. My mother (who hasn't programmed in say 25 years) has been driving herself absolutely up the wall trying to figure something out. So I'm asking here since my programming knowledge does NOT include Access. We have a form for keeping track of previous appointments and scheduling the next appointment. It's a simple form consisting of a "Date Last Visit" text box, an Interval integer text box, and a Date Next Due text box.
What we're looking for, is to have it so that when the user clicks on or tabs to the Date next Due text box, it reads what's in the Date Last Visit variable and changes Date Next Due to Date last Visit + 3 months. So... here's what we have.
Code:Private Sub Date_Next__Due_GotFocus() 'MsgBox (Interval) If IsNull(Date_Last_Visit) = False Then Date_Next_Due = DateSerial(Year([Date_Last_Visit]), Month([Date_Last_Visit]) + Interval, Day([Date_Last_Visit])) End If 'MsgBox (Date_Next_Due) End Sub
Now if I uncomment that bottom msgbox, it shows the correct Date_Next_Due... But it's not displaying it on the Form, nor is it updating it in the table! We've had this working before, but due to an accidental deletion and some other messing around, I CAN"T FIGURE IT OUT! So why isn't it updating?
If there's anything else you need to know, please ask. We really need this to work. Thanks so much in advance!
Is there a way to have an update query update values in a table based on the user's input?For example, I have categories of tests and educational assignments indigenous to a course I manage."How many students failed the Science Exam?" The user would then put the manual calculation in since it varies from semester to semester.
View 6 Replies View RelatedI can't work out how to update the selections available in a combo box as follows: I use a combo box in a main form to select a record (yacht) If it's a new yacht then I use an event linked to a button to call another form to add the new Yacht On exiting the Yacht Maintenance form I return to the main form The combo box in the main form doesn't list the yacht i just added unless I exit and re-load the form
I'm not sure of the best way to refresh the combo box selections or main form without exiting.
I have a date field with dates stored as MM/DD/YY, ie.10/13/09 and I want to use an UPDATE statement to change the year from 09 to 2009 but keep the 10/13/. The issue I'm encountering is when I use a wildcard in the SET portion it takes that as literal and updates with the wildcard. ie. Set Date = '*2009' WHERE Date LIKE '*09' updates the column with *2009 as the entry and not 10/13/2009. How can I update my field so I end up with 10/13/2009, not *2009?
View 4 Replies View RelatedI have made a database for data entry, currently i have a challenge of getting it update and navigate.
On the form if the staff name is xyz it should only shows the records filled by xyz in form and navigate that records only. I am attaching the data base also....
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
I have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.
My problem is:
(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and
(2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
I am working on creating an access database for tracking physical assets linked to locations. I need to make a combo box list to show items other than the current location of the asset. Basically I need it to refer to last enery of the user and define the new possible entries. so we have a unique relationship between location and asset. The assets and location will always remain fixed and there is never going to be any addition. I am creating a web form so that it can be uploaded into sharepoint.
View 3 Replies View RelatedMy 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.
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies View RelatedI 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
I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.
Any help would be appreciated.
Regards
Mark
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
View 1 Replies View RelatedHi there, I'm fairly new to access. I hope you can help me.
I have an update query that creates an entry for my stock and the date. This runs every time I run access. I only want one entry per day though :confused:
At the moment, every time I run access another entry gets appended.
Thanks for your time,
Robin
How do I make data entry required, I've sent it to be in the table but it doesnt seem to work in the formI've tried the below with no luck Private Sub Command14_Click() If tbName = Null Or tbTelephone = Null Or tbAddress = Null Then MsgBox "Please complete all details.", vbExclamation Else DoCmd.Close DoCmd.OpenForm "frmInvoice" DoCmd.MoveSize , , 5100End IfEnd SubCheers
View 1 Replies View RelatedHello, I'm trying to search an entry of a recordset with VBA
The entry can only be found by searching 3 field values. In my case the correct entry can only be found if the data in the field Date, No_Employe and No_Project all match the data of the entry I want.
Findnext doesn't work, it only access one field and I need three.
Seek doesn't work since it need to search a key, and neither Date, No_Employe or No_Project are keys.
How can I proceed ?
Should I use multiples Findnext ( how do I do that ? ) ?
EDIT :
Also please note that I'm searching for 400-500 entries in a row, so speed is an issue.
Hello,
I'm looking for opinions on how people like to enter information into a many to many relationship.I understand how a many to many works as well as a Main Form/Sub Form.
Subforms is what I currently use. Would you do anything different? Below is a sample of fields I currently have. Any advice? I keep wondering if there is a better way. I'm not a huge fan of sub forms. I also build lookups from the Junction. Is this correct?
tblProjects
ProjectID PK
Project
Date
Budget
tblProjectsandManagers
ProjectID PK FK
ManagerID PK FK
tblManagers
ManagerID PK
FirstName
LastName
Phone#
Thanks
I have an input box that gets a string from the user. I want to put that string in a table. How would I do that?
View 4 Replies View RelatedI have a table called Tests that contains 80+ tests that need to be performed on our system. A second table is Test_Data. It contains the test results. I need a query that will pull the most recent test data for each test in the Tests table. So supposing there are 83 tests, I should only get 83 results from my query. I am will need to join the tables as I need to get some of the descriptive information from the Tests table. Another problem I have is some tests have not been performed even once, so not every item in the Tests table has a corresponding entry in the Test_Data table.
Any thoughts on how to do this?
Hi,
Im having a little problem with the following code in one of my queries
Expr1: IIf(IsNull([Forms]![editLookForm]![Company_Name]),True,[Company Name]=[Forms]![editLookForm]![Company_Name])
What this does is, it take an typed entry from the form (editlookform) and seaches for that entry in the query
i want to add wildcard entrys to this so that the person operating doesnt have to know the complete company name - i just dont know where to put them
Help please
i am editing the user interface for operators.
here is the issue:
operators are entering data but sometimes forget to put the part serial number. they then go to the entry.
this is what i want to do:
the operator will not be able to go the next screen (complete the entry) unless they enter the part serial number. where and what should i enter that will prevent the issue?
much thanks.