Insert Into Another Table When A Field In Form Update
Apr 6, 2006
i have a form, which is based on query from multiple tables.. in this form i want to add a unbound textbox in the detail section.. whenever a user leaves this records, the system should insert a record in another table with values from this record including this field... how to do this?
the record is from a query where as the field is unbound, still we need to insert the values from record and this field at the same time into another table..
example: i have item master and item production table.... from joining these table i get a query which will give me item A and prod qty .. when i use this query as form datasource, the detail section has these two fields.. now i want to have an unbound field budget.. so i have the followig in detail sectin
item A production qty budget qty
when users enter values in budgetqty field and moves to next recod,, the system should insert into another table xyz values (itemA, production qty, budget Qty)
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
I thank you in advance for considering this inquiry.
From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.
This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.
What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.
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.
hi there I am having some difficulty with using Access (aren't we all?) ;-( I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.
What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.
It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!
Any tips much appreciated - Thanks Mike
PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)
I am working on a shared database. There are two tables in the database.Table1 have all the employee personal information like employee ID, name, nationality, date of birth etc whereas Table2 have the salary information like Basic, HRA etc. The two tables are joined by Employee_ID field.
I have created two forms, Form1 is only based on Table1 whereas Form2 have fields from both Table1 & Table2. The issue is that if I use to enter the data using Form1, the employee_ID is not automatically updated in table2. On the other side If i use to enter a data by using Form2 then everything is working fine. Is there a way to update a field value which is not in form?
I've read over and over that calculated data is bad, that's fine with me... but here is my dillema...
I have four fields that interact with my calculation in a single form. There is only one table in the database.
AmountF (The stored data in the table) (amount financed) FC (calculated by =[amountf]*.25) (finance charge) Amount Paid (used in calculation of total due) total due (calculated by =[amountf]+[fc]-[amount paid])
The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.
All four of these fields have a text box in the form for themselves.
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
I have a form, which i use to book in products received. What i want to do is if the quantity received is equal to what was ordered then i'd like to update a yes/no field (Order Complete) on a separate table to true.
Hi all, stumped on an issue I thought would be pretty straight forwards (again) so i'm hoping to find the answer here! I have a make table query which produces the table using fields from three other tables and it runs fine however I now want to insert a new field with the value "Current" in it, and, if possible, an Autonumebr ID field as primary key. I'd like this is all done in one query so not sure if it will fly but if anyone can advise me I know someone from here can help.
i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
i wanna to insert a new field in the table ... which fill automatically with the date in which i modified the data in this record ...and then i'll insert this field in a report
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
This is really hard to explain, but basically I'm trying to use a combo box that has a control source different than it's record source to update another field on the record source. For example, the combo box pulls it's options from a table called "NameList" and when you select an option from the combo box it updates a table called "Results". On this form there are form fields that are bound to the "Results" table. What I want to happen is, when I make a certain selection from the combo box, I want one of the fields bound to "Results" to automatically display text from a different table. Is this even possible? If this is a horrible explanation, please let me know. I need serious help on this one, I've been working on this for a day and a half now. If anyone has any ideas on how to do it a different way, please make suggestions. Thanks in advance!
- Josh :confused:
Also, another thing I forgot to mention, this may help anyone who tries to help me. This "Results" form is basically used as a log utilizing a report to display a sequence of events. I've included a screenshot for visual affect. I have censored a few things for privacy reasons.
I have a access table with a OLE Object field that is storing a small signature .bmp image. I have a form that loads that image into a bound object frame. I made a command button that lets you choose a new .bmp image and it then loads the new image into the bound object frame. I am them doing update query on a save button that then save the new image into the table. The form works great. You can pick any .bmp and it shows up good in the bound object frame. When I click the save button and it runs the update query it run it 8 of 10 times fine. Problem is when it does run it the images gets inserted into the table and its destorted. Like its all static or have of the image turns black. Sometime when I run the same update query with out any code changes I get a access error that shuts down access or it inserts the image into the table and when you click on the cell to view it, it says the OLE data is not valid.
So Im not sure if the method Im using is ok or if there is a better way. I do need to store these images in the table tho. Below is the code im using.
code that loads new image picked into bound object frame Code: Dim MyFolder As String Dim MyExt As String Dim MyPath As String Dim MyFile As String Dim strCriteria As String MyPath = vrtSelectedItem'vrtSelectedItem is the path to the image from the file picker Me.OLE_Sig.Enabled = True Me.OLE_Sig.Locked = False OLEPath = MyPath Me.OLE_Sig.OLETypeAllowed = acOLEEmbedded Me.OLE_Sig.SourceDoc = OLEPath Me.OLE_Sig.Action = acOLECreateEmbed Me.button_load_sig.SetFocus Me.OLE_Sig.Enabled = False Me.OLE_Sig.Locked = True
Then I run this for the save command Code:DoCmd.SetWarnings FalseDoCmd.OpenQuery "qry_user_edit_update"DoCmd.SetWarnings True
This is the query to save the image. Code:UPDATE tbl_Users SET tbl_Users.Signer_Sig = [forms]![frm_admin_users_edit]![OLE_Sig]WHERE (((tbl_Users.User_ID)=[forms]![frm_admin_users_edit]![field_userid_info]));
Any help would be great.... Been trying everything and no luck.
Hi I have a form that displays line items and their cost. At the bottom of the form I have a text field called text17 that uses a sum to total the value of all the line item costs with this : =Sum([Line Cost])
How would I pass that value into a database table?
I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date
What is the best way to do this? I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?
I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.
Many thanks, Simon.
PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.
I have around 30 text box on a form and trying to insert the value of those 30 text boxes in a table from vba.
Here is my code
Private Sub Submit_Click() Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, aa, bb, cc, dd As String Dim SQL As String a = Month b = Week c = Type_of_Contact d = Country
[Code] ....
From above code I am not able to get my working done
Can we use a variable in referring a field in insert query?
I am using variable because I am not able to write the whole code in a single line and when i am pressing enter it gives me a error.
