Inactivating records

I wanted to run this by you all and see if I'm approaching this the "best" 
way, and I know "best" can be subjective.  :-)

I have look-up tables which serve to populate comboboxes in data entry forms 
and standardize data entry...  A user can also add more values to the list in 
the look up table (via another form).  For example, a lookup table of 
WaterColor will have fields of WaterColorID (primary key set to autonumber) 
and WaterColor (green, brown, blue).  If someone wanted to refine the green 
color further to more specific green colors, say "aqua green" and "neon 
green", changing the "green" record would mean that all previously entered 
green records would be changed because of the ID.  So, instead, I wish to 
have it so that the user would enter new records of aqua green and neon green 
and inactivate the green record.  Thus, any new data entry would reflect this 
new color refinement but previous records would remain intact.

The way I've figured this out is to add a 3rd field, WaterColorActive, to 
the lookup table and set this to be a Yes/No type.  Then I would create a 
query and use the criteria of Like "1" to pull out those active records.  The 
combobox on the data entry form would then be set to this query.

Is that right?

Your guidance is much appreciated,

Jill
0
Utf
12/8/2009 7:35:01 PM
access 16762 articles. 2 followers. Follow

3 Replies
485 Views

Similar Articles

[PageSpeed] 55

"Jill" <Jill@discussions.microsoft.com> wrote in message 
news:3445BB31-64BB-4620-A2E1-B62521C824E2@microsoft.com...

> The way I've figured this out is to add a 3rd field, WaterColorActive, to
> the lookup table and set this to be a Yes/No type.  Then I would create a
> query and use the criteria of Like "1" to pull out those active records. 
> The
> combobox on the data entry form would then be set to this query.

That would be perfect. Your criteria would be -1 though.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com 


0
Arvin
12/8/2009 8:08:48 PM
Hi Jill,
Perhaps you need another table. Lets say RefinedColors.

This table's fields would be:
RefinedColorID
WaterColorID
RefinedColorName

Table RefinedColors would be like a subset of your Watercolor table. Lets 
say green has a WaterColoriID of 1, aqua green would still have WaterColorId 
of 1 but with RefinedColorId of say 1; neon green WatercolorID of 1 and 
RefinedColorId of 2 etc
On your data entry form you would need to add another combo box to display 
the refined colors for selection.
The user would select, say "green", from the watercolor combo. Your 
RefinedColors combo would then display all your greens ie "aqua green", "neon 
green","snot green" etc. There is lots of helpful code available on the web 
to help with "displaying results in one combo based on selection in another 
combo"

I hope I have understood your question and the info is helpful.


"Jill" wrote:

> I wanted to run this by you all and see if I'm approaching this the "best" 
> way, and I know "best" can be subjective.  :-)
> 
> I have look-up tables which serve to populate comboboxes in data entry forms 
> and standardize data entry...  A user can also add more values to the list in 
> the look up table (via another form).  For example, a lookup table of 
> WaterColor will have fields of WaterColorID (primary key set to autonumber) 
> and WaterColor (green, brown, blue).  If someone wanted to refine the green 
> color further to more specific green colors, say "aqua green" and "neon 
> green", changing the "green" record would mean that all previously entered 
> green records would be changed because of the ID.  So, instead, I wish to 
> have it so that the user would enter new records of aqua green and neon green 
> and inactivate the green record.  Thus, any new data entry would reflect this 
> new color refinement but previous records would remain intact.
> 
> The way I've figured this out is to add a 3rd field, WaterColorActive, to 
> the lookup table and set this to be a Yes/No type.  Then I would create a 
> query and use the criteria of Like "1" to pull out those active records.  The 
> combobox on the data entry form would then be set to this query.
> 
> Is that right?
> 
> Your guidance is much appreciated,
> 
> Jill
0
Utf
12/8/2009 8:50:02 PM

"Proko" wrote:

> Hi Jill,
> Perhaps you need another table. Lets say RefinedColors.
> 
> This table's fields would be:
> RefinedColorID
> WaterColorID
> RefinedColorName
> 
> Table RefinedColors would be like a subset of your Watercolor table. Lets 
> say green has a WaterColoriID of 1, aqua green would still have WaterColorId 
> of 1 but with RefinedColorId of say 1; neon green WatercolorID of 1 and 
> RefinedColorId of 2 etc
> On your data entry form you would need to add another combo box to display 
> the refined colors for selection.
> The user would select, say "green", from the watercolor combo. Your 
> RefinedColors combo would then display all your greens ie "aqua green", "neon 
> green","snot green" etc. There is lots of helpful code available on the web 
> to help with "displaying results in one combo based on selection in another 
> combo"
> 
> I hope I have understood your question and the info is helpful.
> Proko
> 
> "Jill" wrote:
> 
> > I wanted to run this by you all and see if I'm approaching this the "best" 
> > way, and I know "best" can be subjective.  :-)
> > 
> > I have look-up tables which serve to populate comboboxes in data entry forms 
> > and standardize data entry...  A user can also add more values to the list in 
> > the look up table (via another form).  For example, a lookup table of 
> > WaterColor will have fields of WaterColorID (primary key set to autonumber) 
> > and WaterColor (green, brown, blue).  If someone wanted to refine the green 
> > color further to more specific green colors, say "aqua green" and "neon 
> > green", changing the "green" record would mean that all previously entered 
> > green records would be changed because of the ID.  So, instead, I wish to 
> > have it so that the user would enter new records of aqua green and neon green 
> > and inactivate the green record.  Thus, any new data entry would reflect this 
> > new color refinement but previous records would remain intact.
> > 
> > The way I've figured this out is to add a 3rd field, WaterColorActive, to 
> > the lookup table and set this to be a Yes/No type.  Then I would create a 
> > query and use the criteria of Like "1" to pull out those active records.  The 
> > combobox on the data entry form would then be set to this query.
> > 
> > Is that right?
> > 
> > Your guidance is much appreciated,
> > 
> > Jill
0
Utf
12/8/2009 9:06:08 PM
Reply:

Similar Artilces:

Macro recorder #2
How can I use relative references in my macro when I record a macro with the "record"-tool? >-----Original Message----- >How can I use relative references in my macro when I >record a macro with the "record"-tool? >. > Use the arrowkeys in a formula this will give you something like this ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]" Regards Peter Click the "Relative Reference" toolbutton (the second toolbutton) on the Stop Recording toolbar, which should appear when you start recording. If it doesn't appear that would be beca...

Shipping record not created
Sometimes I have to ship items to customers with $0 charged for shipping. I would still like to add a tracking number to these sales. When I set the shipping cost to $0, it does not create a pending shipping record. Is this supposed to work like this or not? How can I fix it? Is there a setting somewhere I am missing? Thanks, Nick ...

Inactivating Vendors
Trying to inactivate a vendor, get the message: This vendor has been assigned to a purchase order or an unposted receipt, the status cant be changed to inactive. I have checked and the 2 PO's are closed, and there are no open receivings. What do I have to do to inactivate this vendor? thanks! -- Doug You have to run the Remove Completed Purchase Orders process (Tools-Routines-Purchasing) in order to move closed or cancelled purchase orders to history as long as you are keeping PO history. "Doug" wrote: > Trying to inactivate a vendor, get the message: > &g...

find a record
Hi, I have an unbound form with a number of combo's. I'm wanting to select from the combo and populate a number of text boxes. I know I can do it just by putting a control source in the text box of =cboDem.column(2) but am trying to work out how to do it with code. I've tried many different ways and just can't do it. I think I probably need 'find' in there somewhere... I'm completely lost now. can anyone assist? Private Sub cboDemo_AfterUpdate() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT tblDemo.DemoIDS, tblClient.Client, ...

Excel 2007 Macro Recorder not recording shapes
Hello, I am trying to figure out why my Excel macro recorder is not recording creation of any shapes or changes to existing shapes. I started the macro, and drew a rectangle and stopped it. This is all the code I get. I like to see the syntax for shapes and I am unable to see maybe there is some problem with macro recorder. Please help! Thanks! Sub Macro1() ' ' Macro3 Macro ' ' ActiveWindow.SmallScroll Down:=25 End Sub Regards, Jaan On Feb 14, 2:35=A0pm, jaanbazz <jaanb...@domain-xyz.in> wrote: > Hello, > I am trying to figure out why...

Inactivating a vendor
I would like to change the vendor status to inactive, but get the message: This vendor has been assigned to a purchase order or an unposted receipt, The status cant be changed to inactive. i have checked and all PO's are closed or cancelled, and there are no open receipts. Havent used this vendor for over a year. Do I have to do something else to change a vendor to inactive? -- Doug You need to run the PO Removal Routine (Tools-Routines-Purchasing-Remove Completed Purchase Orders) for the Vendor ID you are having the issue with. This will move the PO info to history and allow y...

Record Count
When viewing accounts and contacts or in Advanced Find results, it would be helpful to be able to see how many records/results there are. With the 250 records per page limit, you have to page through to count up the records. Other than through a report, has anyone created a record count function that could be launched through a custom toolbar button? Go to a grid that has the proper view you are looking for (or advanced find preview). Click on XL button. Click on "Dynamic PivotTable". Drag the "(...id)" item to the "Data Items" are. There you go, count ...

Delete records when certain records have duplicate column data
Hi, I'm new to excel. I want to delete (sort of) duplicate records. My spreadsheet has many columns. My spreadsheet has many records I want to delete records where the data in just a few columns is the same in multiple records. (e.g. if the values in columns "A" "B" "D" "F" in any record is duplicated in multiple rows ..delete all matching records/rows. A= house number B= street name D=apt number F=city Bonus points: Can a macro/button be created that will allow me to load a spreadsheet and then somehow run the above filter/function on the ...

Return to original record
I have 2 subforms on a main form. I leave subform_01 and go to subform_02, enter information there. Then, at the click of a button on subform_02, I want to go back to the exact record I was on in subform_01 before I left subform_01. Any ideas? Thank you, -- RJF Are the two subforms both open at the same time, or does subform 1 close when you to to subform 2? If the form stays open then all you should have to do is set the focus back to a control on subform1. The code for that would look something like the following (note that subform1ControlName is the name of the subform CONT...

Pension Policies
Does anybody know a tidy way to record revaluations of pension policies in MS Money? For instance, a policy can increase in value from £10,000 to £12,000 due to investment gains of the funds in that policy. How would you go about recording this? Would you record this as an Investment Transaction? If so, what Activity would you allocate to this transaction? Thanks in advance for any useful pointers. "Peter Barrett" <PeterBarrett@discussions.microsoft.com> wrote in message news:987F89B8-7A89-400D-815A-22FA47FE6DA2@microsoft.com... > Does anybody know a tidy w...

Auto time recording in another cell for data entered in one
I need to record the time any data is entered in a cell of a particula coloumn. Example: In coloumn A if data is entered in cell A10 the automatically current time is recorded in cell N10. If there is any macro for this then pls let me know -- Message posted from http://www.ExcelForum.com Hi you may also have a look at http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany > I need to record the time any data is entered in a cell of a > particular coloumn. Example: In coloumn A if data is entered in cell > A10 then automatically current time i...

How to Inactivate a Kit
Hello, I'm looking for recommendations on what is the best process to inactivate a kit so it is no longer available to sell? Thanks! Jeff Couple options: 1) GP does not keep or track history of kits in any way shape or form, so you can delete the kit. or 2) Change the kit components to discontinued and you can either disable or password protect the "Allow Sales of Discontinued Items" in the Sales Order Processing Setup Options window. There is no way to inactivate a kit directly. If deleting the kit or changing the kit components to discontinued i...

Email Current Record from Form
I would like to email only the record currently being viewed within the form. The ideal way for this to work is to click a button on the form to email the current record only to the specified address within the form. Can anyone help with this? SendObject can email a report. Unlike OpenReport, it does not have a WhereCondition, so the problem becomes, "How do I limit SendObject to just the current record?" One approach is to use a string variable to act as the filter for the report. You set this to the filter you want before you open the report (with SendObject), and in the r...

How to make a commad button show a new record
I know the code for a command button to show a new record is: DoCmd.GoToRecord , , acNewRec But the question I have is, how do I stop the mouse scrolltab action from moving to a previous or new reocord. I only want the command button of new record and command button of previous buttons to move the records back and forth. Can I get help on this matter. If you really want to disable the mouse wheel, see: http://www.lebans.com/mousewheelonoff.htm But *why* you wish to put the user into a straight jacket and prevent them using the familiar ways to functionality to move records? To do that...

Finding non-existing records, or Nulls in XML
Hi guys! I am getting a bit flustered with XML. Using CR, I am trying to create a report that shows all salesorders that has no associated invoices. Using regular SQL I could do a: Select * from Salesorders where salesorderid NOT in ( select salesorderid from invoices ) Using the CR report wizard I don't know how to achieve this. And looking at the underlying SQL query only shows me XML. :-( Maybe there is a completely different way to do this? Anyone out there with any tips on how I can go about this? Gratefully !Robert Robert, The underlying query is presented in FetchXML fo...

nested subform will not stay on new record :( !
Oh, the joys of programming... I have a check in form I am developing, and i am having an issue with a nested subform not behaving properly. The setup: Main form is bound to table DateInfo, which has datetime column for each day of year. In this form are two subforms, sfmATTHDR and sfmDailySched. sfmDailySched is a continuous form linked to DateInfo by date, and shows all of the classes scheduled for the current date. sfmATTHDR is a single form also linked to DatInfo by date, and displays the current class available for check in. sfmATTHDR is filtered by time in order to show only the current ...

Conditional Subreport Record Source
I am using Access 2003. I have a main report page that has a couple of Subreports on it. I want one of the subreports to open up using a different Record Source depending on Who opened it up. I am using an If...Then statement to determine who is opening the report, and using different SQL strings in code to provide the different Record Sources. I have put the If...Then statement in the OnOpen Event of the subreport. When I open the subreoprt all by itself, everything works. When I open the Main Report with the subreport in it, it gives me an error and the Debug command t...

Viewing All Records Offline
I would like to view all accounts and contacts offline regardless of who owns them. What is the best way to handle this. Scottie; Assuming you mean you'd like to take the contacts offline using the CRM for Outlook Laptop client, it can be accomplished through CRM role security. make yourself a member of the root level business unit and give yourself rights to see all child business units. it might take some playing around to get things the way you want it, depending on the complexity of your orgainzation. If you're a disgruntled employee and are going to take the data and ru...

Sharing a record
I'm new to CRM and I have a question about sharing a record. We have record owners who have assistants. The assistants will have a user account and their managers will be the owners. How can we give each owner's assistant the abitility to ONLY update the owners accounts, contacts, etc. and not be able to delete the record. I know the owner and give this ability to each assistant record by record, but we'll like to automatically this this right to the assistant we the record is created. So, something in the backend would have to happened for this to occur. Any thoughts?...

Purging Duplicat Records
I have imported a list that has multiple contacts for each location (e.g. three teachers at one school). For a mailing all I need is one mailer to go to each location and I don't want to print labels for every contact. How can I find/delete the duplicates automatically without scrolling through the document manually to find the duplicates and then delete them? HELP!!!! Use a help column next to the names, in the first adjacent cell put assume you inserted a new column A to the left of the old, the old would now be B =COUNTIF($B$2:B2,B2) copy down as long as needed, select the ...

go to next record issues
I have an (inherited) form that I've been adding a lot of code it is essentially a one record at a time data entry form suddenly I notice that "GoTo First / Last" (command buttons)... does not take me to the first or last record (moving to the top of the table, stops at record 2; down- a few before the last (although I have not checked if this "last" position is dynamic to the number of records in the table...) ...."GoTo Next / Previous" also will cycle thru the records up to the same records that I can access thru "GoTo First / Last"... the r...

Customer-Inactivate but do not remove history
Does anyone have any suggestions on how to handle a customer that we no longer want to use, but i dont want to remove history. (Looks like I cant inactivate a customer if there are posted or unposted transactions) thanks -- Doug Doug, If all transactions for this customer are fully applied, you can move them all to history (Tools > Routines > Sales > Paid Transaction Removal) and then Inactivate the customer. (Note: make sure History tracking is turned on before you run the Paid Transaction Removal.) If you still have open transactions or don't want to move them to...

last entry per record query..
Hi and thanks for your time. My database is tracking the service histroy of vehicles. The main table has VIN as the PK which links to another table containing details about each time the vehicle is serviced. I need to make a query to then generate a report of when each vehicle was LAST serviced. So my query would need to search each VIN and then only show the most recent dated entry. The main table is named "_VehicleDetails" and the related table is named "ht_ServiceHistory" I am fairly new to access so please try to keep it simple. If I need to use co...

Copying record value from column in multiple sheets into single sh
Hi, I have some employee Records in sheets "Nov07","Dec07" and "Jan08". the first two columns(A and B) are employee number and name in each sheet, and column C is their overtime. I want to create a consolidated sheet "YTD" so that I can see all their YTD overtime in one sheet and do some analysis. Hence the new sheet will have first two columns A and B same as above howver column C, D and E will be the overtime from each sheet. It would have been a simple copy paste if the number of employee had been static, but it changes every months as new em...

Account Record Changing?
We have a Customer Maintenance question that we cannot figure out, hoping for insight from more experienced users. Sometimes the Bill To and Ship To defaults in the Customer Record change to the last one used on an order, and we can't figure out how. The only party that really uses that record is Accounting, and they claim ignorance. Does anyone know how those might change from another entry or posting transaction? ...