UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query/form/macro To Act As Find/replace Function, Access 2010    
 
   
accessblade
post Jan 25 2018, 03:31 PM
Post#1



Posts: 73
Joined: 24-July 14



I have a tblINV that has a field labeled SOA. It is a short date field. It designates start of an overhaul date for a number of received items in an inventory database.

At times the SOA date will need to be altered. This SOA date could be embedded in one data record, or hundreds, depending on the number of assets received and tied to that distribution date.

I want a FORM an everyday user can pull up, First field will mimic the FIND, user puts in say 01/01/2017 to search, and another field to put in data that will REPLACE it, say 01/05/2017. Then they hit a button, and it does the replace function for as many affected data records there are.

I do not want users pulling up the raw data tables and doing the FIND/REPLACE functions.

Every page I look at has a different solution, and none of them seem to work great.

Any advice?

Thanks
Mike
Go to the top of the page
 
doctor9
post Jan 25 2018, 04:02 PM
Post#2


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Mike,

Just to be clear, I'm going to repeat back to you what I think you're saying.

You want to find EVERY record in a table where SOA is equal to a selected date (regardless of any other field values in the record) and change that date to a new date. So if screws and nails and chainsaws all have an SOA of 1/1/17 you want those three items to now have an SOA of 1/5/17.

If this is correct, here's the basic strategy you can use:

You need to run this update query:
UPDATE tblINV
SET SOA = #1/5/2017#
WHERE SOA=#1/1/2017#;

Since you want the user to do this, you can have a combobox named "cmbSelectedOldSOA" that lists the unique SOA values currently in tblINV ("SELECT DISTINCT SOA FROM tblINV ORDER BY SOA DESC;"). That way they can easily find the date they want, and no matter which one they choose, it's a valid one that exists in the table. Then, you have a textbox named "txtNewSOA" where they enter the new SOA date. The command button would do something like this:

CODE
    Dim strSQL As String
    
    strSQL = "UPDATE tblINV " & _
             "Set SOA = #" & Me.txtNewSOA & "# " & _
             "WHERE SOA=#" & Me.cmbSelectedOldSOA & "#;"
    CurrentDb.Execute strSQL, dbFailOnError


That's about it. Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
accessblade
post Jan 25 2018, 04:25 PM
Post#3



Posts: 73
Joined: 24-July 14



Yes, perfect!! Thank you very much.
Go to the top of the page
 
accessblade
post Jan 25 2018, 04:58 PM
Post#4



Posts: 73
Joined: 24-July 14



Ooops, ok, one more question. These dates are tied to ships overhaul periods. Say I have two different ships, field [SHIP] on tblINV, which have the same SOA date.

As it stands, the process changes all SOA dates, which means it changes it for both ships. Say raw data is SHIP=A, SOA= 1/1/2018 and SHIP=B, SOA= 1/1/2018....now I only want to change the SOA on SHIP A but not SHIP B.
Go to the top of the page
 
doctor9
post Jan 25 2018, 05:52 PM
Post#5


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


accessblade,

Okay, so you just need to figure out which Ship as well as which original date. So, create a control on your form so the user can select the ship - same strategy as with the combobox listing dates. Then, your code will need to make the WHERE clause include that ship value.

For example (I'm guessing that SHIP is a text field here):
UPDATE tblINV
SET SOA = #1/5/2017#
WHERE SOA=#1/1/2017# And SHIP='A';

If the actual list of possible ships is really long, you may want to use a Cascading Combobox technique to filter the list of ships to only the ships that appear in records where SOA is equal to the dae in the first combobox.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th June 2018 - 06:05 PM