Forms :: Update Multiple Records With Same Date
Jun 14, 2013
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?
View Replies
ADVERTISEMENT
Apr 11, 2015
Below are what I have created.
Table 1 with 3 fields:
- UniqueID
- field1
- field2
Form 1
- text box to enter UniqueID
- button to save the new UniqueID and date/time stamp
I set the Default Value for UniqueID in Table 1 as Now()
Each time I enter a new UniqueID and click the button, a new record will be created in Table 1 with the entered UniqueID and current date/time in field1
This form seems to work well.
Form 2
- text box to enter UniqueID
- button to update
How I can use the UniqueID entered into the textbox of Form 2 and update the corresponding field3 with a new current date/time stamp?
View 1 Replies
View Related
Sep 25, 2014
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).
View 2 Replies
View Related
Sep 13, 2005
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......????
Waiting of your quick reply.
View 3 Replies
View Related
Aug 11, 2005
Hoping that this is a simple problem :confused:
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
View 6 Replies
View Related
Jan 12, 2013
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.
View 3 Replies
View Related
Jan 16, 2015
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';
View 4 Replies
View Related
Sep 11, 2014
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.
Code:
Me.txtTranTo = InputBox("Enter Location Transferring To:", "Location Transferring To", "")
Me.txtTranFrom.Value = Me.Location 'Old location
Me.Location.Value = Me.txtTranTo 'New location
The txtTranTo and txtTranFrom are unbound text boxes.
EDIT: Forgot to mention that this is in a continuous form.
View 1 Replies
View Related
Dec 20, 2004
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.
Thank you.
View 8 Replies
View Related
Apr 15, 2015
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.
View 3 Replies
View Related
Oct 11, 2006
Hi
I have a form that people have to fill in to report when someone is off sick.
The first notification they have is that the person is off sick - so they can only enter a start date on the form, and have to leave the end date blank
I want the end date to always be "today" - and to automatically update to "today" until an end date is entered by the user. To enter the end date, the user will go back to the original record where they put the start date in, and then enter the end date.
Any ideas... using date () will put today's date in, but then when I go in tomorrow, it will say yesterday's date...
Thanks!!!
View 4 Replies
View Related
Oct 29, 2014
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.
View 7 Replies
View Related
Aug 19, 2014
I am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent.
I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
View 3 Replies
View Related
Jun 28, 2006
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
Thankyou
View 1 Replies
View Related
May 2, 2014
I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]
I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]
Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)
The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.
Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)
I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :
Code:
SELECT [tblRates].[RateDate]
FROM [tblRates]
INNER JOIN [tblCurrencies]
[Code]....
But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.
View 3 Replies
View Related
Sep 1, 2015
I have a record in a table (Table1) with the following:
Item Start End
A 2012 2030
I have another table (Table2) with the following:
Item Start
A 2018
A 2025
What I would like to do is update/append Table1 so that it looks like the following:
Item Start End
A 2012 2017
A 2018 2024
A 2025 2030
View 4 Replies
View Related
Apr 7, 2006
I would like to create a text box where I can input todays date, then I want to be able to select the tables I want to update with that information.
So if I input todays date I can then update my Stock, Sales and Control tables with todays date. Tomorrow I would only like to update the Control and Tape tables with it as its the weekend.
I been playing around with 'new date for next record' scenario but the problem is that on the weekend we would not input any data into some of the tables.
Hope I've explained myself clear enough.
Thanks
Tim
View 3 Replies
View Related
May 19, 2013
how to update multiple row in one command button e.g. cmdsave.Example as below: Table Name = Master
PRNUMBER PONUMBER QTY
123456 RLP120001 12
123457 RLP120001 45
123458 RLP120002 23
How to save both RLP120001 with status "OK" because normal form only can have one form with one data. Then, I come out with filter form which can view PO number RLP120001 but how to update both PO Number using filter form. In my filter form, save button only save above row which pr 123456.
View 1 Replies
View Related
Jun 27, 2013
I am trying to get the records on start and end date, still showing error no records found.
My code is like this:
Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"
[code]...
View 1 Replies
View Related
Sep 6, 2014
I am creating a form that has a bunch of yes/no box for daily task. Is there a way for access to record which user selects yes/no.
For example
yes/no box1 - user 1 completed this task
yes/no box2 - user 2 completed this task
and so on
So when i look back i can see which user complete which task. I did a bit of research and saw that i can save a user that update a record. But since this is a daily task, each new record will be a new day.
View 3 Replies
View Related
Mar 7, 2014
I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"
Following is the multiple line Update Statement
Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql
Following is the one liner Update statement which, works perfectly
strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));
View 4 Replies
View Related
May 30, 2015
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code
Code:
Private Sub SearchBtn_Click()
PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
End Sub
This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code
Code:
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &
I get an Compile error saying "expected expression".
View 3 Replies
View Related
Jan 9, 2015
I'm working to create a staffing database that houses changes to staffing week over week.
I have one primary table, the "empMaster" table, that stores the employee's name, contact information, etc. I have other individual tables for noting which employee reports to which manager, what their business title is, what group they're in, their training history, etc.
Once I've populated the empMaster table with employee information, I want a form that allows me to update each of the other tables IF there as a change. Some weeks will have a manager change, some only a business title change, some a group change, some a training change, some all of the above. The problem I run into is that I will sometimes process hundreds of changes a week, sometimes only 10-20 so I almost have to use datasheet view for mass edits. I'm relatively new to Access and I'm having a hard time getting my form to allow this level of flexibility and to update all fields needed.
When I build a form that includes more than two tables (let's say I want to update a Manager and a Business Title), the form will not work and populates nothing. I believe it's because Access wants there to be an existing record to match to across all three tables and there will not always be.
View 4 Replies
View Related
Jan 31, 2014
How do you create a form that is not tied to one single record source? In other words, I want to be able to select the record source that it updates. I have a bunch of tables that have the same data structure but are separated due to geographical nature among other reasons. Is there a way to do this?
View 10 Replies
View Related
Nov 19, 2014
I've got an Access 2010 database based on the Tasks template. I've added a column to the Tasks table called "CompletedDate" I'd like for the Status combo box on the Task Details form to update the Completed Date for that record.
i.e. so when the Status is set to completed, it auto completes the Completed Date with the current date.
I've tried using an OnChange and an afterUpdate statement on the Status box but I don't seem to be getting anywhere.
View 4 Replies
View Related
Jun 27, 2013
My database allows you to log issues (see attached)
An issue will belong to the project, the project may have multiple test plans.
The issue will also be the primary responsibility of a company, and that company may have multiple contacts (people)
Now, i could create a form over the top of each table but then if someone wanted to add a new project, testplans for that project, suppliers (companies) for that project AND new contacts for those companies, they are going to end up going through loads, and loads of forms.
So my question is, what approach would you recommend to create a control panel where they could add these items with the fewest number of page switches possible? (preferably none.
View 1 Replies
View Related
