I have an orders form and subform, ie one order can have several product records with the following firlds
Product
Ordered
Received
At the bottom I have a command button 'ReceiveOrder'
What I want is on click of this button that the received field (that is 0 by default) will equal Ordered.
ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well.
This much I have done with the following code
Private Sub ReceiveButton_Click()
Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered
End Sub
This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.
I've searched the forum for the answers but can't find the answer, can anyone help,
Thanks
I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.
At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.
SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit FROM Budget WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));
My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.
I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.
Hello, I would like to update multiple records in one query. The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds. Can anybody help me please......????
I have a form that contains a subform. I want to make a button that on click updates all the records listed in the subform. This is the best I came up with.
Dim rs As DAO.Recordset Set rs = Me.SubList_for_Billing_Center_Form.Form.RecordsetC lone With rs .MoveFirst Do While Not .EOF .Edit UPDATE Billing SET Billing.Billing_Declined = True, Billing.Billing_Declined_Date = Date()
I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.
Code: UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A'; UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';
Instead of using an update query, so my Audit Trail will continue to update correctly, I'd like to use an input box and run a function that performs much like an update query.
My problem is, only one out of 4 records updates correctly.
I've got a table which contains about 4000 workers, all of whom are issued with an ID card using a 3rd party software which looks at the data in this table. Every 3 years the card expires and needs to be re-issued.
When this happens for a batch of workers, the Print Date needs to be updated to reflect the new date - is there any way in which I could select a number of different workers and update the Print Date field for them all at once?
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control Dim varItem As Variant
I am currently building a database with the purpose of capturing data about various employees' skills. The idea is that a supervisor can fill out a questionnaire of sorts, with this employee, and give them a rating out of 5 for each skill. I have been working on this for a few days, and I am most certainly not an Access genius, so I have come to a bit of a wall. I know where I want to get, but I don't know how or if it is even possible, to get there.
I want to have a form that has all the questions on it, so each time the supervisor pulls it up they can just tab through and fill out the form. I have a main form with a subform, although this isn't ideal, it is the only way I can think of to even get close to what I am looking for. The main form portion, after a bit of testing, seems to be producing the desired results I am looking for in populating the specified table, but my subform (questionnaire) is just not doing what I had hoped. I know some of my formatting will change, but I just need a way to allow the supervisors to fill out the questionnaire, hit a button that adds all of the information in both the mainform and subform to their respective table(s) (which I know will require multiple records-1 for each question), and then be able to produce reports by employee.
I want various text boxes to autofill with the questionnaire info, and then have a box for the supervisor to input the ratings. I have used a Dlookup for the default property in each of the question and description text boxes in the subform, so they will reference info from a questionnaire table.
I've only started using Access 2010 since the beginning of January and have googled almost every problem to date.
My db is used to show the status of material through a manufacturing process. We start with one slab of material which gets cut up in to many parts.
The db works so far however I've come to the conclusion that the date was not normalized correctly. So I've created the following tables to fix this.
I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.
Table 1 has the following fields
Cast Number P/O UST Status UST Comment
Table 2 has the following fields
Cast Number Blade ID (which will be 1 to 32) Status Comment
I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!
I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.
I did have 32 status fields for every part but realized that meany were left null. I'm using, Access 2010 with Win 7...
I created a form based on a table I have. Table structure is:
ID, Rt, contractors, Crew, activity, condition. A crew can have 1 to many contractors. Each “contractors” can have one activity and one condition.
The form has a combo – dropdown list with all the crews. Also combo - dropdown list with activity , condition and a text field update_date. And 3 buttons: Reset condition, Refresh screen, Save reords.
Every time when user pick a crew number from the list and set new values for activity and condition, all the records having crew = crew_number picked on the screen has to be updated in the table.
I tried to create a DAO recordset based on user selection of the crew, and to update those fields in a do while loop. Always does updated the first record and only one.
My question is: Can I do what the user wants, to update multiple records, based on a screen selection? What code should I use? If you can give me a hint it would be very much appreciated.
I am building a stakeholder database in Microsoft access 2010 and I want to be able to say that a stakeholder attended an event. I have managed to do this but I can only say that one stakeholder attended an event at one time. This is quite a problem as there can be up to 800 or even more stakeholders attending an event so to go through and click each one would be very time consuming.
My current set up is I have a stakeholder table an events table and an event attendance table. It all works fine apart from only being to edit one record at a time. I have tried update queries with no success, I can attach the database but would need to remove the data for data protection reasons.
I have a Sales Order form that is used to input sales orders. the main form is comprised of customer and shipping information that is created from a query based on a customerOrder table while the subform is comprised of product information that is created from a query based mainly on the CustomerOrderDetail table.
I need to be able to put multiple products in the subform as a customer can order many items, when I save and close the form I look in the customer order table and see only one record for that sales order while seeing all the records in the detail table for all the products ordered for that sales order as I should but the problem I run into is the query that I have for the sales order shows multiple records for every product that was ordered on that sales order and shows up multiple times in my sales order maintenance form when I only want to see the one. if I change the query to a total query that fixes that problem and only see one copy of the sales order but makes the forms un updateable which is no good.
I cant figure out what I did to make this happen as I have a purchase order form that is setup the same but doesn't have this problem and works great, no matter how many items I have in the subform I only see one purchase order in the query and in the maintenance form.
I currently have a report which contains a subform. The master/child fields used to link them are called 'StudentID'.
In the table on which the subform is based, it is possible for multiple records to be associated with a single StudentID: in this case, the subform shows the locations where a student is studying, therefore if a student is based in three different locations the subform will display all three.
This seems to be resulting, when I run the master report, in three identical iterations of the same report being displayed (at least this is the only reason i can see why these duplicates would be displayed; it doesn't happen if a student only has one record in the subform).
Clearly, as a single instance of the report shows all three records in the subform anyway, I don't want to be seeing these duplicates.
Off of the same dB I would like to create a subform which will allow me to enter multiple records to the main table, the one attached to the main form. How to go about creating one?
I am trying to design a subform to allow multiple records to be entered and then uploaded to a table. I've designed the subform but it is currently pulling all of the records from the table through and all I want is a blank subform for data entry.
I have tried putting a macro in the On Load and On Open events to get it to go to a new record but I keep getting error number 2046.
I have a table with many records, using a form with an update button click event, I would like to update ALL records where the Item in the table = the Item in the form.
There are 6 checkboxes and 6 text boxes that will need to be updated, but right now I am just trying to test if i can even change one text box to keep it simple. I have tried to start by using this:
Code: Dim mySQL As String Dim ItemNo As String Dim SO As String
mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = " & ItemNo DoCmd.RunSQL mySQL
So every time i run this command button, it gives me a prompt "You are about to update 0 row(s)". Nothing gets updated at all, I am clearly missing something vital here.
I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
i have a main form with three sub forms on it. when i update subform 1 i want subform 2 and 3 to update..currently i have an after update event in a combo box subform 1
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.
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
