Forms :: Update Values In A Table At Once From A Form
Feb 28, 2015
I'm making a very basic sales system for my school project and I'm currently working on the stock management part, as I said it's very basic.
My task -When the user receives a stock delivery they need to update the stock numbers. the value they need to update is the StockNumber value in the Products table. to so this I want to make a form that displays all the product names in a table with a textbox or cell next to it in which the user can enter the amount of that item that has been delivered. And at the bottom of the form there is a button which will add all these new values to the existing values.
1. There is no set number of products, the user is able to add new ones, this is preventing me from just making a separate textbox for each existing product
2. I have attached a jpeg I made in paint to illustrate how I want to the form to work as well as my system as it currently stands, there are a few unused forms and queries.
View Replies
ADVERTISEMENT
Jul 16, 2015
I have an unbound form in which a user enters data relating to a credit dispute, and when they hit the Save button I have a sub that is intended to insert that data into a table. This is a bit out of my comfort zone, so I found some code from another forum and adjusted it to my needs. However, I'm getting errors.
The table name is tbl_complaints. The fields from the form are:
txtAcctNumber
txtClient
txtEoscarType
txtEoscarControl
cboMethodReceipt
....
My code is below.
Private Sub cmdSave_Click()
Dim StrSql As String
On Error GoTo ErrorHandler
StrSql = "INSERT INTO tbl_complaints " _
& "(ACCT_NUMBER, CLIENT_NAME, EOSCAR_TYPE, EOSCAR_CONTROL_NUMBER, METHOD_OF_RECEIPT,
[Code] ....
The error I get is error number 3075, and it says I'm missing an operator in the section that references cboResponseType, but I can't seem to figure out what I'm missing. My head is spinning from staring at these parentheses and apostrophes for so long.
View 14 Replies
View Related
Feb 27, 2015
I'm running into an issue with my forms. I have a form that contains a subform, that contains a list of projects. This list is read-only. To update a record, you can double click on it, which opens another form filtered on this record, and which contains the fields in the first form as well as some others.
I have a command button on that last form to "Save and close", which fires up a macro that saves the record and closes the window.
Now, the issue that I'm having is that, when the window closes and I'm once again in front of the first form with the list of projects, the values of the record I changed are not updated and when I click on another record, Access tells me there's a conflict in the values and asks me if I want to keep the changes, discard them or copy to the clipboard.
It seems to consider the old values from the first form as a change, and thus asks me which to keep between these, and the actual (proper) changes I made in the dedicated form.
For the proper changes to be applied, I need to select "Drop changes".
I tried to requery the first form, also undo the changes to it after closing the window, but none of that worked.
I'm actually basing this on one of the templates, which does exactly what I want and which obviously works...
View 3 Replies
View Related
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Mar 14, 2013
I have a data input form with multiple combo boxes. The combo boxes are related to their own separate tables. The Row Source on some of the combo boxes are from queries with concatenated fields.
Not sure the best way to be able to quickly/simply input new values into the appropriate tables from here.
I've attached a screenshot of my form.
View 2 Replies
View Related
Dec 16, 2013
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
View 1 Replies
View Related
Sep 8, 2014
I have a table with the fields Thermometer_ID (primary key) and CalibrationType (combo box list with the options of InHouse or SendOut)
I want the user to click on a button and have an input box to input the thermometer_id. Then if the CalibrationType is InHouse open form Verification and if it's SendOut open form Thermometers.
View 2 Replies
View Related
Jun 17, 2013
I'm a relative beginner at VBA and I'm trying to use a form to update a table. The form is used to get an agent name, start and end date. When they hit the "update" button, I want to go to the table, select all records where the agent name (called Fullname in the table) matches and the field called "event_date" is between the start and end dates. I have the below code, which isn't working.
Set rst = CurrentDb.OpenRecordset(Table)
rst.MoveFirst
Do While rst.EOF = False
If rst.Fields("Fullname").Value = Me.Agent And Me.StartDate <= rst.Fields("event_date") And rst.Fields("event_date") <= Me.EndDate Then
rst.Edit
rst.Fields("CompleteDate").Value = DATE
rst.Fields("Comments").Value = "Autofilled"
rst.Update
End If
rst.MoveNext
Loop
rst.Close
View 3 Replies
View Related
Mar 22, 2013
If I have a text box (Text1) on my form with comma separated values entered i.e. 100,120,250,300 what would be the easiest way to enter these into a table column with a button click event. I would like to enter these values into my table tb_test under column Values.
View 7 Replies
View Related
Jul 15, 2013
I have a form, a subform and a master unique table. I need the unique table to updated in the following manner: Form has certain fields that need to be assigned to every record created in the subform[id] and [info1] Subform has a unique [caseid], and contains further [info2] but needs to be bound into [id] in the main form. Example of the master unique table:
id info1 caseid info2
1 aaa 1001 asd
1 aaa 1002 dfg
2 bbb 1003 fff
3 ccc 1004 ffg
3 ccc 1005 ggh
I've managed to accomplish this with a linked table&subform structure, but the master unique table looks like crap because it collapses the subform values into sub-records in the master table when the [id] is similar. The data is in the rigth place, i just want it not to be collapsed/expanded, but every unique recors shows in the table in the regular format
View 8 Replies
View Related
Apr 23, 2015
I've got two tables - one that works like a cypher and one with all my records.
I have 2 queries. One that is a simple query that extracts data that is not so user-friendly and puts it into a user-friendly format. The Other query is for any records that the first query excludes because the new combination of data (lets just call it a code) is not in the cypher table, but needs to be added.
How do a create a form that will show the records in the 'excluded records' query and allow me to select from a drop-down list a specific set of categories to update the cypher table ?
Example :
Record Table: Cypher Table:
Ford Focus = Sedan
Ford F-150 = Truck
Ford Freestyle = Wagon
Ford Escape = SUV
Now a new model comes in, a Ford Edge which is an SUV but is not in my cypher table.
I have the query to pull in the excluded Ford Edge, but I want a form to show "Ford Edge" in the first column and be able to select from a drop-down list "SUV", and either automatically update my cypher table or require me to press a button to update the table (whichever is easier).
View 6 Replies
View Related
Dec 3, 2014
I am looking for updating one record in a table from data on a form.
I have a table called ctntbl with fields: Item, Credit_Amt, and Incd_Num.
I also have a non linked form from which I want to make changes to one specific item in the ctntbl table. The user will enter a number in field FRM_CR_INC which links one specific record in table ctntbl matching field Incd_Num. Then they will enter data in FRM_CR_AMT which I am needing to update just that one record in the table field Credit_Amt with the data in FRM_CR_AMT.
Here is my code:
DoCmd.RunSQL "Update ctntbl set credit_Amt = " & Me!FRM_CR_AMT & " Where Incd_Num = " & Form![FRM_CR_INC]
I am getting a data type mis match error. Cant understand where error is coming from. All fields in table and form are text fields.
View 3 Replies
View Related
Sep 23, 2014
I have a form, which has a search box. This allows you to search through the id numbers of each record. Once an ID has been selected a bound textbox displays the id number. Other unbound textboxes then display other information from the table with the relevant record.
What I want to do is then allow users to change information in textboxes and update the record.
View 7 Replies
View Related
Dec 2, 2014
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?
View 1 Replies
View Related
Feb 24, 2008
The attached Access XP file demonstrates my problem. I've included a form to make testing easier.
Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.
A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.
I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.
In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.
I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.
View 5 Replies
View Related
Feb 4, 2015
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
=[frmNewPurchaseOrderDetails subform2].[Form]![txtsubfrmSupplierID]
did i mess up in the code or did i do something to the relationship between the form and the table?
View 3 Replies
View Related
Apr 14, 2014
So I have a combobox in a form that lists the values of a field in a table. The rowsource is this:
Code:
SELECT [Estes Training Modules].[Training Module], [Estes Training Modules].[Period] FROM [Estes Training Modules] ORDER BY [Period];
The problem is that ALL values on that field show up, including repeated values. I only need each different value to show up once. How can I do that?
For example, what I get in the combobox is: Annual, 2-Year, Annual, Annual, 2-Year, 3-Year,...
What I need is: Annual, 2-Year, 3-Year,...
View 10 Replies
View Related
May 2, 2015
I'll simplify this form to a
- textbox
- command button
- subform (showing a table with 4 fields, including an "EntryID" field but just one record)
What I'm trying to accomplish is to enter a value in the textbox (this value will be one of the EntryIDs in the EntryID field), click the command button, and the subform should refresh itself, showing the record from the table (the 4 fields) which has the same EntryID.
View 2 Replies
View Related
May 19, 2013
I have 6 text box on the form which are unbound. Now we enter the entry in these boxes then I have update button on the form to update the record in table. all fields should be clear after update the records in table so I can enter the new entry.
I am using ms access 2007. Is there any liberary to add?
Form fields
text1
text2
text3
text4
text5
text6
Table fields
emp_id
batchid
training_name
training_hour
start_date
end_date
View 13 Replies
View Related
Jul 14, 2014
I have a combo box linked to table shipstatus. This table has 2 columns. ShipCode and ShipName. These values are delivered, on hand, received, returned, warehouse.
I have another table named manifestdetails and a column labeled shipstatus
I would like to create a form where the user can use the combobox selections from the table shipstatus and update the column shipstatus from the manifestdetails table.
View 3 Replies
View Related
Sep 19, 2014
What i need:I have the attached database which includes 2 tables (Table1 and Table2) which are related One-2-Many (Table1 is the One and Table2 the Many)There is one form also with the name "Form1" which includes 11 unbound (ctr1,2,3...11) and are calculated with the values of fields [MainDate] , [Days] and [EndDays]
The Issue: As you can see in the attached sample, on Table1 i can assign the "MainDate" value , the "Days" and on field "EndDays" i can have the value of the [MainDate]+[Days].
I need to update the field "RefNo" on Table1 on depending the date range of the field [EndDays] and the controls [ctr1-11]. Specifically i need : If the Date() is in the range of eg. [EndDate] and [ctr1] i need to have the value of the first record of the Table2 (RefNo) on field "RefNo" into Table1. If the Date() is the range of [EndDate] and [ctr2] i need the second record values of the field [RefNo] into Table2 and so over..
View 1 Replies
View Related
Mar 12, 2013
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
View 6 Replies
View Related
May 5, 2006
I have a textbox on my main form that calculates the total of a field on the sub-form. It works well and display the correct ammount on the textbox when I load up the form, but if I do changes the values of the sub-form it doesn't update at all ! I need to close and open the form again to see the changes.
How can I update the textbox so it always reflect the values on the sub-form?
View 1 Replies
View Related
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Jul 23, 2015
Situation: 3 tables. Manufacturers, Countries & Provinces/States. 1 Form for data entry in Manufacturers.
Countries table contains ID, Country and CountryCode fields. i.e. 47, Great Britain & GB
Provinces/States table contains ID, Province/State and CountryCode. as in 1, Alaska and US.
In the form the country is easily selected from a list refering directly to the Countries table.
Problem: How to make a list in the form from which the user can simply select the province for the country that has previously been selected. And not a list with all teh provinces and states from every country in the world. (This would be a really really long list...)
View 3 Replies
View Related
Mar 29, 2013
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
View 2 Replies
View Related
