Updating 1 Table That Updates Multiple Fields
Sep 20, 2006
I've been working on this problme for several weeks, thinking about it for the last month or so, and am at a loss of what to try next. I'm working on a database for my office that tracks all of the permits we have issued. Every year our permit fees increase by the CPI (consumer price index) and the permit fee entry in the table needs to be updated (75 to 80 entries).
Rather than typing them in every year (too many chances for errors) I'd like to be able to update our fee schedule table (8 entries, look up table) and have that table update all of our fee entries in the permit table.
Any ideas of how to go about this? Thank you for your help.
Scott
View Replies
ADVERTISEMENT
Dec 16, 2014
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
View 1 Replies
View Related
Jun 4, 2015
I have a training database with multiple training codes. I would like to be able to update the training date for multiple codes. These trainings are normally completed at the same time and I want to be able to reduce data entry by auto updating the date of training for the employees for multiple codes automatically. How can I link certain codes together?
View 1 Replies
View Related
Nov 12, 2012
I have a Database that has been split into two.
Back-end for all the data.
Front end for all the forms and queries.
The front end has been saved in a accde format and distributed to 20+ users. They have all saved this onto there computers and this is used as an application.The users complete a form on a weekly basis on the application and then save. This then saves into the back end.For some reason I have noticed that a few users submission randomly do not save. For the life of me I cannot understand why.All users have saved them into the back end in the past but every now and then submission for users do not save.
View 5 Replies
View Related
Nov 1, 2012
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
View 3 Replies
View Related
Jul 6, 2012
I have 2 tables.
Table 1: Master table
Table 2: Temporary table
This is my current process:
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code
Table: Table 1
Update To: [Table 2].[Access Bar Code]
Field: Access Box Number
Table: Table 1
Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
View 1 Replies
View Related
Nov 8, 2004
I have two tables, one is a list of customer details including email addresses and the other a list of customers and email addresses that have unsubscribed from my mailing list.
How can I use the unsubcribe table to update a field in the details table so I know who's unsubscribed?
View 11 Replies
View Related
Nov 8, 2006
I have a database with nine tables, and forms for data entry, no subforms. It is created for students to track all work required to complete their degree. When student information in added for a new student I need to update other tables with their student id.
The student information table has student ID defined as autonumber. How do I update the other tables with the value generated here, and should all the tables with student ID be defined as autonumber, or some other data definition?
Thank you.
View 1 Replies
View Related
Jun 17, 2007
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
View 4 Replies
View Related
Dec 9, 2005
Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.
The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).
TotalCopies is the total no. of copies there are of the book
CopiesBought is the number of copies that have been purchased
RentalDate is the date on which book is being loaned
ReturnDate is the last date on which the book has to be returned
Returned is a Yes/No field to indicate if the book has been returned
In the library a member can take only one book at a time.
Problem 1:
The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.
BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.
I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:
(i)If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form.
(ii)The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?
Problem 2:
When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?
Thanking you in advance for your help.
View 2 Replies
View Related
Nov 14, 2007
Hi everyone,
I was wondering if this is possible to do:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Bear
View 1 Replies
View Related
Nov 26, 2005
I have a data entry form where country(ies) (there can be more than one for
each project) is/are entered in a sub-form on a tab control. Countries not
listed are entered by the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
This works fine. However, if I restrict the country names by a region
selection on the main form (for example if region Africa is selected on the
the African countries are preselected) the NotIn List does not work. Any way to work around this?
Another question, I want the user to select a region (a combo box ) on the
main form and when a new country (using the NotInList event) is entered in the subform both values go into
the same record in tblCountry (which has two fields CountryName and Region).
For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to
add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) "
strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
But it does not work (Region is the control on the main form named frmAddPro).
Any suggestions would be welcome. Thanks.
Niels
View 4 Replies
View Related
Dec 6, 2005
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks
View 1 Replies
View Related
Apr 25, 2012
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
View 7 Replies
View Related
Nov 30, 2005
How do I get access to accept changes made when there are four individuals working in the same continuous form at the same time? In addition, I noticed when a record has been deleted, the view for those viewing the database, the screen displays the word DELETE. How do I get Access to delete the record and not show the word delete?
FYI…I already split the DB and placed the front end on each desktop.
View 1 Replies
View Related
Jun 8, 2006
Hi,
I am new to access and DB in general.
Is it possilbe to create a form that updates mutiple records at once with the click of a button? If each record has 4 fields and for each group I want to update at once there are 2 fields with the same value and 2 fields with different values?
Any help on this would be appriciated..
Thanks.
View 1 Replies
View Related
Apr 14, 2005
I currently have a form [order] that houses a subform [orderdetails]. is there a way for me to set up a print button on form [order] that will also update the [ysnPrinted] and [dtmDatePrinted] fields in subform [orderdetails]?
View 2 Replies
View Related
Sep 23, 2013
I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.
The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.
View 1 Replies
View Related
May 17, 2005
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
thanks.
View 1 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Nov 16, 2006
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Aug 14, 2013
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
View 2 Replies
View Related
Apr 7, 2006
I'm new at access and have added some expressions to a couple of my forms but the table is not being updated with the results. help!
View 5 Replies
View Related
Nov 21, 2014
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
View 2 Replies
View Related
Nov 21, 2004
How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?
View 2 Replies
View Related
