Selasa, 27 September 2011

RE: [MS_AccessPros] Re: Change color of record based on event???

 

Tiffany-

If "none of the data in the sold table would match up," how in the world were you planning to "move" a current asset to sold?? Is there ANY data that matches between sold and current asset? If we're talking about vehicles, surely there's at least a VIN.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, September 27, 2011 11:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Change color of record based on event???

John,
The spreadsheets were inherited by my boss and the previous owner was lax in detailing information on the sold vehicles. Once my boss took it over and added all the current fields to the assets then she just started cutting and pasting the data from one spreadsheet to the other, not caring if the columns lined up so you cannot actually make sense of the sold spreadsheet from looking at the columns. The bad thing is none of the data in the sold table would match up to the assets table so i cannot append the records.

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, September 27, 2011 2:59 PM
Subject: RE: [MS_AccessPros] Re: Change color of record based on event???

Tiffany-

One wonders why the "sold" table is missing so much info. Are there any columns in "sold" that aren't in "assets"? If not, you basically need to add all the rows in "sold" to the "assets" table but with the Sold field set to True. Then create two forms - one to show all the unsold Assets using a query WHERE Sold = False. Another form shows the sold data using another query that uses Sold = True.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, September 27, 2011 10:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Change color of record based on event???

So here is the problem, my boss had two spreadsheets (not identical or even close in format) one "sold" and the other "assets". When I created this database I imported both spreadsheets creating two tables, I only have one form created from "assets". Please guide me from here....

Under the current form "assets" I would like to add a column called sold with a checkbox, when I check the box I would like the sold vehicle to disappear in the form (datasheet view) but show as sold in the "Assets" table.

I am not sure what to do about all the sold vehicles that were imported as "sold" table, as I said the current format of that table is missing alot of information and does not evenly closely resemble the current "Assets" table.

Thanks for your help,

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, September 27, 2011 2:28 PM
Subject: RE: [MS_AccessPros] Re: Change color of record based on event???

Tiffany-

Remember there is no "sold" table anymore. You might have a "sold" query that shows only the rows where the Sold field = True, and you can base a "sold" form on that.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, September 27, 2011 10:16 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Change color of record based on event???

okay so, I need to create a column called sold in my form with a check box? When I check the box then it will make the record invisible in forms but visible in the sold table? Is this correct?

________________________________
From: Bill Mosca <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, September 27, 2011 11:35 AM
Subject: [MS_AccessPros] Re: Change color of record based on event???

Tiffany - Yes. There is no need to move records from one table to another in a relational database. Using the Sold field in query criteria will allow you to either show it or not.

In fact, if you put the criteria to show only unsold records on your form's recordsource you can mark them as sold so the next time they won't show.

Then if you ever *do* need them to show you set the report/form's criteria to include those records.

Bill

--- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@...> wrote:
>
> Okay, so I should add this field to the form and incluse all the sold in the table. My boss had this data originally set up in excel spreadsheets and she would move the line item when she sold the vehicle.Â
>
>
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Tuesday, September 27, 2011 11:08 AM
> Subject: [MS_AccessPros] Re: Change color of record based on event???
>
>
> Â
> Tiffany
>
> If you are moving a record to a different table just because it is sold, you are not designing your database correctly. Just add a Yes/No field named Sold to your table. When a record is sold update that field to Yes (-1). If you want to omit it from displays set up your queries to show only if Sold = No (0).
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@> wrote:
> >
> >
> >
> > Â Hi Duane,
> >
> > I am working in 2010. Can anyone recommend a great Access book that a dummy can understand? I tend to over think a problem and it is usually the simplest answer.
> >
> > I have one more question/problem: I have two tables current and sold. I want to create a control that moves the record from current to sold when needed.
> >
> > Can someone help me with this?
> >
> >
> > Thank you very much,
> >
> > Tiffany
> >
> >
> >
> >
> >
> > ________________________________
> > From: Duane Hookom <duanehookom@>
> > To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> > Sent: Monday, September 26, 2011 6:37 PM
> > Subject: RE: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> > Tiffany,I'm not sure you have ever told us what version of Access you are using or replied with the data type of "202". There are reasons why we ask questions and expect answers. It saves lots of time and bandwidth if you read replies and either respond to questions or ask for clarification. In design view, select all of the text boxes in the form. Then find the Conditional Formatting dialog and select "Expression Is" from the drop down.Then enter either:[Acc Code]=202or[Acc Code]="202" Then make the formatting selections for fore and/or back colors. Save your work. Duane HookomMS Access MVP
> > To: MS_Access_Professionals@yahoogroups.com
> > From: cc0623@
> > Date: Mon, 26 Sep 2011 16:07:14 -0700
> > Subject: Re: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Â
> >
> >
> > Â Â
> > Â Â Â
> > Â Â Â
> > Â Â Â Please write the formula out for me.
> >
> >
> >
> > I want it to say: Expression is, if Acc Code = "202" then back color purple. this works on acc code but it keeps telling me syntax is wrong for other fields. Please remember I have never done this before so I am out of my depth. I appreciate you trying to teach me this.
> >
> >
> >
> >
> >
> >
> >
> > From: Duane Hookom <duanehookom@>
> >
> > To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> >
> > Sent: Monday, September 26, 2011 4:49 PM
> >
> > Subject: RE: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> >
> > Tiffany,
> >
> > I just created a simple datasheet form and set the conditional formatting of every text box based on "Expression Is" as suggested by John Viescas. My table has a field named TotalFreight and I set the background color based on this expression:
> >
> >
> >
> > [TotalFreight]>500
> >
> >
> >
> > Set the default view of the form to datasheet and your done. It took me less than 5 minutes (less time then it took to create and send this email). The datasheet view of a form is very similar to that of a table but you have much more flexibility.
> >
> >
> >
> > If you have any questions regarding the steps to reproduce this, please let us help you.
> >
> >
> >
> > Duane Hookom
> >
> > MS Access MVP
> >
> >
> >
> > To: MS_Access_Professionals@yahoogroups.com
> >
> > From: cc0623@
> >
> > Date: Mon, 26 Sep 2011 14:57:24 -0700
> >
> > Subject: Re: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> >
> > Â
> >
> >
> >
> > Well, I created the form and indicated 202 and it did change all the fields with 202 purple, but I needed it to change the entire record purple not just one field. And, I wanted to see this change in the datasheet view under the table not just the form, but it doesn't show the effect under datasheet view. Very frustrating... my boss did not want a form she is stuck on excel and with all the data we have to manage I am trying desperately to get her to use access. Any suggestions?
> >
> >
> >
> > From: Duane Hookom <duanehookom@>
> >
> > To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> >
> > Sent: Monday, September 26, 2011 3:51 PM
> >
> > Subject: RE: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> >
> > Greetings Tiffany,
> >
> >
> >
> > Table and query datasheets don't support conditional formatting. Creating a datasheet form takes less time than creating and sending an email.
> >
> >
> >
> > Duane Hookom
> >
> > MS Access MVP
> >
> >
> >
> > To: MS_Access_Professionals@yahoogroups.com
> >
> > From: cc0623@
> >
> > Date: Mon, 26 Sep 2011 20:52:50 +0000
> >
> > Subject: [MS_AccessPros] Re: Change color of record based on event???
> >
> >
> >
> > Â
> >
> >
> >
> > Hi John,
> >
> >
> >
> > My name is Tiffany. Thank you for responding to my question. I am assuming this is to be done under forms? Since only two people are using this database the primary party does not wish to add a form to it. Can this be done under datasheet view or design view?
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > >
> >
> > > Dear cc0623, (name?)
> >
> > >
> >
> > > Use Conditional Formatting. Select all the controls that you want to be purple,
> >
> > > then click on Conditional Formatting. In 2003 and earlier, it's on the Format
> >
> > > menu. In 2007 and later, it's on the Design tab under Font. Choose Expression
> >
> > > Is, then enter your field name in brackets, an equals sign, and "202", then
> >
> > > select either the Fill/Back Color or Font/Fore Color to set the color you want.
> >
> > >
> >
> > > John Viescas, author
> >
> > > Microsoft Office Access 2010 Inside Out
> >
> > > Microsoft Office Access 2007 Inside Out
> >
> > > Building Microsoft Access Applications
> >
> > > Microsoft Office Access 2003 Inside Out
> >
> > > SQL Queries for Mere Mortals
> >
> > > http://www.viescas.com/
> >
> > > (Paris, France)
> >
> > >
> >
> > >
> >
> > >
> >
> > > -----Original Message-----
> >
> > > From: MS_Access_Professionals@yahoogroups.com
> >
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
> >
> > > Sent: Monday, September 26, 2011 8:30 PM
> >
> > > To: MS_Access_Professionals@yahoogroups.com
> >
> > > Subject: [MS_AccessPros] Change color of record based on event???
> >
> > >
> >
> > > I would like to change the color of the record based on data.
> >
> > >
> >
> > > I want to change the color of the record to purple if the data in a specific
> >
> > > field says "202". So in datasheet view I will see specific records are purple
> >
> > > and someone else can followup with that record.
> >
> > >
> >
> > > I am a newbies to access and I am having difficulty with this. Can anyone
> >
> > > adivise me please?
> >
> > >
> >
> > >
> >
> > >
> >
> > > ------------------------------------
> >
> > >
> >
> > > Yahoo! Groups Links
> >
> > >
> >
> >
> >
> > Â Â Â Â Â Â Â Â Â Â Â Â
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > Â Â Â Â Â Â Â Â Â Â Â Â
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> > Â Â
> > Â Â
> >
> > Â Â
> > Â Â
> >
> >
> >
> >
> >
> >
> > Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar