validate a field based on sum of other fields

I have to assign commission to agents for each "order".  I can have anywhere 
from 1 agent at 100% commission to 5 agents with every possible combination 
of percentages.  Obviously, I need the percentage entries to only add up to 
100 and I'd like to force the user to adjust the figures until they total 100.

Firstly, am I right in assuming it would be better to be assigning the 
agents to the orderIDs in separate records:  OrderID, Agent, Agent Percentage 
(as opposed to having one record contain the OrderID, Agent1, 
Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage, 
etc...)?  The former method seems better for developing reports of the agent 
total commissions, but the latter seems easier for accomplishing this 
particular goal of data input.  

I hope I explained this in as non-stupid a way as possible! Thanks ahead of 
time for your patience!
0
Utf
2/17/2008 9:10:01 PM
access.forms 6864 articles. 2 followers. Follow

6 Replies
1081 Views

Similar Articles

[PageSpeed] 49

lela,
One order can have many agents.
One agent can be on many different orders.
The above is a many to many relationship.

Use 3 tables
a table for Orders
a table for Agents
a Commission table that has the OrderID from the Orders table and the 
AgentID from the agents table

there is a one to many relationship between Orders and Commissions
there is a one to many relationship between Agents and Commissions

Once you have the tables set up like this, it will be easier to see how to 
set up the form to set commission to 100%

Jeanette Cunningham

"lela2a" <lela2a@discussions.microsoft.com> wrote in message 
news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
>I have to assign commission to agents for each "order".  I can have 
>anywhere
> from 1 agent at 100% commission to 5 agents with every possible 
> combination
> of percentages.  Obviously, I need the percentage entries to only add up 
> to
> 100 and I'd like to force the user to adjust the figures until they total 
> 100.
>
> Firstly, am I right in assuming it would be better to be assigning the
> agents to the orderIDs in separate records:  OrderID, Agent, Agent 
> Percentage
> (as opposed to having one record contain the OrderID, Agent1,
> Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
> etc...)?  The former method seems better for developing reports of the 
> agent
> total commissions, but the latter seems easier for accomplishing this
> particular goal of data input.
>
> I hope I explained this in as non-stupid a way as possible! Thanks ahead 
> of
> time for your patience! 


0
Jeanette
2/17/2008 9:24:17 PM
I knew I wouldn't explain it properly. Yes, I have an Order table, an Agent 
table and the table I referred to as "assigning the agents to the orderIDs in 
separate records" - you referred to it as "a Commission table that has the 
OrderID from the Orders table and the AgentID from the agents table."

Sorry, nothing jumped out at me any better now than it did before your hint. 
 Could you be A LOT more specific?  Tx.

"Jeanette Cunningham" wrote:

> lela,
> One order can have many agents.
> One agent can be on many different orders.
> The above is a many to many relationship.
> 
> Use 3 tables
> a table for Orders
> a table for Agents
> a Commission table that has the OrderID from the Orders table and the 
> AgentID from the agents table
> 
> there is a one to many relationship between Orders and Commissions
> there is a one to many relationship between Agents and Commissions
> 
> Once you have the tables set up like this, it will be easier to see how to 
> set up the form to set commission to 100%
> 
> Jeanette Cunningham
> 
> "lela2a" <lela2a@discussions.microsoft.com> wrote in message 
> news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
> >I have to assign commission to agents for each "order".  I can have 
> >anywhere
> > from 1 agent at 100% commission to 5 agents with every possible 
> > combination
> > of percentages.  Obviously, I need the percentage entries to only add up 
> > to
> > 100 and I'd like to force the user to adjust the figures until they total 
> > 100.
> >
> > Firstly, am I right in assuming it would be better to be assigning the
> > agents to the orderIDs in separate records:  OrderID, Agent, Agent 
> > Percentage
> > (as opposed to having one record contain the OrderID, Agent1,
> > Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
> > etc...)?  The former method seems better for developing reports of the 
> > agent
> > total commissions, but the latter seems easier for accomplishing this
> > particular goal of data input.
> >
> > I hope I explained this in as non-stupid a way as possible! Thanks ahead 
> > of
> > time for your patience! 
> 
> 
> 
0
Utf
2/17/2008 9:42:01 PM
A form that shows all the agents for the same order would be easier to make 
the commissions add up to 100%

Here is a way to set up the forms for a many to many relationship - is this 
what you are asking?

Use a main form (single view) for one of the Many side tables.  Within the 
main form, use a continuous subform bound to the junction table with the 
Link Master set to the main form's PK field.  This subform has at least one 
visible control that is a combo box bound to the second table's PK field. 
This form's Current event and the combo's AfterUpdate event sets an 
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many 
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record. 
The continuous subform displays the items assigned to the main form record. 
Clicking on a record in the continuous subform automatically displays the 
related
record in the other subform.

If you already have forms similar to the above, would you mind explaining 
your question in more detail.

Jeanette Cunningham

"lela2a" <lela2a@discussions.microsoft.com> wrote in message 
news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
>I have to assign commission to agents for each "order".  I can have 
>anywhere
> from 1 agent at 100% commission to 5 agents with every possible 
> combination
> of percentages.  Obviously, I need the percentage entries to only add up 
> to
> 100 and I'd like to force the user to adjust the figures until they total 
> 100.
>
> Firstly, am I right in assuming it would be better to be assigning the
> agents to the orderIDs in separate records:  OrderID, Agent, Agent 
> Percentage
> (as opposed to having one record contain the OrderID, Agent1,
> Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
> etc...)?  The former method seems better for developing reports of the 
> agent
> total commissions, but the latter seems easier for accomplishing this
> particular goal of data input.
>
> I hope I explained this in as non-stupid a way as possible! Thanks ahead 
> of
> time for your patience! 


0
Jeanette
2/17/2008 10:07:56 PM
Thanks, Jeanette.  I think this will take some time to digest and apply.  

What I have set up now is one main form which is assigning the agent and 
commission to the orderID.  I put a subform on it which shows all the agents 
assigned to it and I calculated a box to total the percentages.  By your 
explanation...which I'm having trouble with (what's a PK?) I think I need an 
event on ...the percentage assigned field (?) ... but how do you get it to 
wait until the user has entered all the agents they were planning on?  All I 
can envision is having it alert when the totals do not equal 100, but that is 
going to happen on each entry until they have entered all of them - in some 
cases 5 in all.  

I'm not really understanding the hidden boxes or their purpose.

Sorry, I know the solution is in your answer but you're over my head right 
now.  Thanks so much for trying!!

"Jeanette Cunningham" wrote:

> A form that shows all the agents for the same order would be easier to make 
> the commissions add up to 100%
> 
> Here is a way to set up the forms for a many to many relationship - is this 
> what you are asking?
> 
> Use a main form (single view) for one of the Many side tables.  Within the 
> main form, use a continuous subform bound to the junction table with the 
> Link Master set to the main form's PK field.  This subform has at least one 
> visible control that is a combo box bound to the second table's PK field. 
> This form's Current event and the combo's AfterUpdate event sets an 
> invisible text box on the main form to the value of the combo box.
> 
> Along side the continuous subform is another subform bound to the other many 
> side table with its Link Master property set to the invisible text box.
> 
> With this arrangement, you can navigate or search for the main form record. 
> The continuous subform displays the items assigned to the main form record. 
> Clicking on a record in the continuous subform automatically displays the 
> related
> record in the other subform.
> 
> If you already have forms similar to the above, would you mind explaining 
> your question in more detail.
> 
> Jeanette Cunningham
> 
> "lela2a" <lela2a@discussions.microsoft.com> wrote in message 
> news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
> >I have to assign commission to agents for each "order".  I can have 
> >anywhere
> > from 1 agent at 100% commission to 5 agents with every possible 
> > combination
> > of percentages.  Obviously, I need the percentage entries to only add up 
> > to
> > 100 and I'd like to force the user to adjust the figures until they total 
> > 100.
> >
> > Firstly, am I right in assuming it would be better to be assigning the
> > agents to the orderIDs in separate records:  OrderID, Agent, Agent 
> > Percentage
> > (as opposed to having one record contain the OrderID, Agent1,
> > Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
> > etc...)?  The former method seems better for developing reports of the 
> > agent
> > total commissions, but the latter seems easier for accomplishing this
> > particular goal of data input.
> >
> > I hope I explained this in as non-stupid a way as possible! Thanks ahead 
> > of
> > time for your patience! 
> 
> 
> 
0
Utf
2/17/2008 10:51:00 PM
PK is an abbreviation for the primary key field.
The hidden text box is to keep the second subform in sync as you move to 
another rexord on the 1st subform.
If you are only using 1 subform at the moment, just forget about the hidden 
textbox.

The calculated percentage can update each time a percent is assigned to an 
agent.
What you can do is to check the sum of percentages, to see if it is over 100 
when you are saving the record for that OrderID.

On the before update event for the subform put code that checks the sum of 
all the percentages entered.
If the sum is greater than 100 then you set Cancel = True and pop up a 
msgbox telling the user about the problem.

Jeanette Cunningham

"lela2a" <lela2a@discussions.microsoft.com> wrote in message 
news:5F02C843-C4B1-423E-AA3D-F64291DBCB16@microsoft.com...
> Thanks, Jeanette.  I think this will take some time to digest and apply.
>
> What I have set up now is one main form which is assigning the agent and
> commission to the orderID.  I put a subform on it which shows all the 
> agents
> assigned to it and I calculated a box to total the percentages.  By your
> explanation...which I'm having trouble with (what's a PK?) I think I need 
> an
> event on ...the percentage assigned field (?) ... but how do you get it to
> wait until the user has entered all the agents they were planning on?  All 
> I
> can envision is having it alert when the totals do not equal 100, but that 
> is
> going to happen on each entry until they have entered all of them - in 
> some
> cases 5 in all.
>
> I'm not really understanding the hidden boxes or their purpose.
>
> Sorry, I know the solution is in your answer but you're over my head right
> now.  Thanks so much for trying!!
>
> "Jeanette Cunningham" wrote:
>
>> A form that shows all the agents for the same order would be easier to 
>> make
>> the commissions add up to 100%
>>
>> Here is a way to set up the forms for a many to many relationship - is 
>> this
>> what you are asking?
>>
>> Use a main form (single view) for one of the Many side tables.  Within 
>> the
>> main form, use a continuous subform bound to the junction table with the
>> Link Master set to the main form's PK field.  This subform has at least 
>> one
>> visible control that is a combo box bound to the second table's PK field.
>> This form's Current event and the combo's AfterUpdate event sets an
>> invisible text box on the main form to the value of the combo box.
>>
>> Along side the continuous subform is another subform bound to the other 
>> many
>> side table with its Link Master property set to the invisible text box.
>>
>> With this arrangement, you can navigate or search for the main form 
>> record.
>> The continuous subform displays the items assigned to the main form 
>> record.
>> Clicking on a record in the continuous subform automatically displays the
>> related
>> record in the other subform.
>>
>> If you already have forms similar to the above, would you mind explaining
>> your question in more detail.
>>
>> Jeanette Cunningham
>>
>> "lela2a" <lela2a@discussions.microsoft.com> wrote in message
>> news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
>> >I have to assign commission to agents for each "order".  I can have
>> >anywhere
>> > from 1 agent at 100% commission to 5 agents with every possible
>> > combination
>> > of percentages.  Obviously, I need the percentage entries to only add 
>> > up
>> > to
>> > 100 and I'd like to force the user to adjust the figures until they 
>> > total
>> > 100.
>> >
>> > Firstly, am I right in assuming it would be better to be assigning the
>> > agents to the orderIDs in separate records:  OrderID, Agent, Agent
>> > Percentage
>> > (as opposed to having one record contain the OrderID, Agent1,
>> > Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
>> > etc...)?  The former method seems better for developing reports of the
>> > agent
>> > total commissions, but the latter seems easier for accomplishing this
>> > particular goal of data input.
>> >
>> > I hope I explained this in as non-stupid a way as possible! Thanks 
>> > ahead
>> > of
>> > time for your patience!
>>
>>
>> 


0
Jeanette
2/17/2008 11:19:27 PM
Phew! I understand this one!  Thanks, lots, Jeanette. l.

"Jeanette Cunningham" wrote:

> PK is an abbreviation for the primary key field.
> The hidden text box is to keep the second subform in sync as you move to 
> another rexord on the 1st subform.
> If you are only using 1 subform at the moment, just forget about the hidden 
> textbox.
> 
> The calculated percentage can update each time a percent is assigned to an 
> agent.
> What you can do is to check the sum of percentages, to see if it is over 100 
> when you are saving the record for that OrderID.
> 
> On the before update event for the subform put code that checks the sum of 
> all the percentages entered.
> If the sum is greater than 100 then you set Cancel = True and pop up a 
> msgbox telling the user about the problem.
> 
> Jeanette Cunningham
> 
> "lela2a" <lela2a@discussions.microsoft.com> wrote in message 
> news:5F02C843-C4B1-423E-AA3D-F64291DBCB16@microsoft.com...
> > Thanks, Jeanette.  I think this will take some time to digest and apply.
> >
> > What I have set up now is one main form which is assigning the agent and
> > commission to the orderID.  I put a subform on it which shows all the 
> > agents
> > assigned to it and I calculated a box to total the percentages.  By your
> > explanation...which I'm having trouble with (what's a PK?) I think I need 
> > an
> > event on ...the percentage assigned field (?) ... but how do you get it to
> > wait until the user has entered all the agents they were planning on?  All 
> > I
> > can envision is having it alert when the totals do not equal 100, but that 
> > is
> > going to happen on each entry until they have entered all of them - in 
> > some
> > cases 5 in all.
> >
> > I'm not really understanding the hidden boxes or their purpose.
> >
> > Sorry, I know the solution is in your answer but you're over my head right
> > now.  Thanks so much for trying!!
> >
> > "Jeanette Cunningham" wrote:
> >
> >> A form that shows all the agents for the same order would be easier to 
> >> make
> >> the commissions add up to 100%
> >>
> >> Here is a way to set up the forms for a many to many relationship - is 
> >> this
> >> what you are asking?
> >>
> >> Use a main form (single view) for one of the Many side tables.  Within 
> >> the
> >> main form, use a continuous subform bound to the junction table with the
> >> Link Master set to the main form's PK field.  This subform has at least 
> >> one
> >> visible control that is a combo box bound to the second table's PK field.
> >> This form's Current event and the combo's AfterUpdate event sets an
> >> invisible text box on the main form to the value of the combo box.
> >>
> >> Along side the continuous subform is another subform bound to the other 
> >> many
> >> side table with its Link Master property set to the invisible text box.
> >>
> >> With this arrangement, you can navigate or search for the main form 
> >> record.
> >> The continuous subform displays the items assigned to the main form 
> >> record.
> >> Clicking on a record in the continuous subform automatically displays the
> >> related
> >> record in the other subform.
> >>
> >> If you already have forms similar to the above, would you mind explaining
> >> your question in more detail.
> >>
> >> Jeanette Cunningham
> >>
> >> "lela2a" <lela2a@discussions.microsoft.com> wrote in message
> >> news:AB24CB73-E908-4CC7-9335-C6521554724B@microsoft.com...
> >> >I have to assign commission to agents for each "order".  I can have
> >> >anywhere
> >> > from 1 agent at 100% commission to 5 agents with every possible
> >> > combination
> >> > of percentages.  Obviously, I need the percentage entries to only add 
> >> > up
> >> > to
> >> > 100 and I'd like to force the user to adjust the figures until they 
> >> > total
> >> > 100.
> >> >
> >> > Firstly, am I right in assuming it would be better to be assigning the
> >> > agents to the orderIDs in separate records:  OrderID, Agent, Agent
> >> > Percentage
> >> > (as opposed to having one record contain the OrderID, Agent1,
> >> > Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Percentage,
> >> > etc...)?  The former method seems better for developing reports of the
> >> > agent
> >> > total commissions, but the latter seems easier for accomplishing this
> >> > particular goal of data input.
> >> >
> >> > I hope I explained this in as non-stupid a way as possible! Thanks 
> >> > ahead
> >> > of
> >> > time for your patience!
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/17/2008 11:30:00 PM
Reply:

Similar Artilces:

Moving Knowledge Base Articles from one CRM server to another
Hi all, I am trying to copy Knowledge base articles in CRM from a production environment to a Development environment. I can not find a tool that does this any assistance would be appreciated Ranga You could write a fairly simple SDK based utility to do this. Otherwise, you might look to buy something like Scribe migrate. -- Matt Parks MVP - Microsoft CRM "Ranga" <ranga.ngwerume@gmail.com> wrote in message news:1141073508.821512.55840@i40g2000cwc.googlegroups.com... Hi all, I am trying to copy Knowledge base articles in CRM from a production environment to a Develop...

Date in Access field is ########
I recently ran a query based on the date field. Ever since then, some of the dates in the field show up as ###### instead of the date I originally entered. How do I fix this? If you have lived a good, wholesome life, it's probably just that the field holding the data isn't wide enough. Try widening up the column or text box. That isn't it? What DID you do? ;-) I would be worried that your database may be corrupt. Make a copy of the entire database file first. Then do a compact and repair on the database and see if that fixes it. -- Jerry Whittle, Microsoft A...

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

Masking the sent from field with a seperate domain?
We have Exchange 2k3 and AD setup. We use the pop3 connector to download email from various seperately hosted domains and route the email to the correct client. Our users have noticed that any email they send shows the recipient the local domain in the sent from field. We need to mask it to say that it come from a different domain... for example one of the domains that we use to download email from using the pop3 connector. Is this possible? Any help would be greatly appreciated. Jackie Jackie wrote: > We have Exchange 2k3 and AD setup. We use the pop3 connector to > down...

Conditional Formats Date Based
I need to format a row in a workbook so that the cells in the range change color based on a date on another workbook. It needs to be formatted so that when a certain date ie 12/1/09 is reached, the color changes to green, but is yellow or red if it's later than 12/1/09 like 3/1/09 depending on what's entered in the worksheet. It's best to assign a cell outside your print area to equal the date in the other workbook, then give that cell a name (like "EffectiveDate"). Then in your conditional format you can use <EffectiveDate or <=EffectiveDate. -- ...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

Delete rows based on a cell value in the row
I'd like to delete rows from a spreadsheet based on the value of a cell in a row. This is a very large spreadsheet (60,000+ lines). I want to delete rows based on a value (in this case all parts which are purchased- vs mfd) without destroying the spreadsheet structure. In this case I have several bills of material where I want to list all the assemblies, but not the purchased parts. It seems it should be possible, but I'm not having any luck with the filter method. Thanks, Dave sort?? or use a macro to delete, from the bottom up, if the cell contains your text -- Don Gu...

Base class constructor call with derived class
Is there a way, other than an initialization list, to call a base-class copy constructor in a derived-class function in order to copy base class member data? Thanks. Chip Pulitzer wrote: >Is there a way, other than an initialization list, to call a base-class copy >constructor in a derived-class function in order to copy base class member >data? You mean "derived-class ctor", and the answer is no. Once you've entered a ctor body, the base class and member subobjects that weren't mentioned in the member initialization list have already been default-initialized. ...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Field Options
We have an Access database that has a field with 2 choices - "A - Been Recommended" and "B - Has Not Been Recommended". When we choose one of the answers in any record, it changes all the records to that choice. Why would this be? Also, regardless of the choice, A or B, when we print, all the records show as if choice A has been selected. Any help will be greatly appreciated. Thank you very much. "Stacey" <snestlerode@keppel.k12.ca.us> wrote in message news:697047F2-ECD1-4566-B541-EF0F4056F0B2@microsoft.com... > We have an Access d...

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

formating a field within a query
In working at a college we must pull our data from the state data management system. I need to convert the class start time field into a true time stamp in order to sort it correctly. example of how the data comes to us: 0400P = 4:00 pm start time. Is there a way to format this field at a true time within my query? I need to sort in order of earlies time to latest time. The query runs a report that will go to our scheduling assistants to determin if a room is available for a class. Thanks Jacqueline Jacqueline "Formatting" doesn't alter the value, just changes how it i...

Chart Base Line in Pivot table #2
I have a pivot table that shows the time spent on each support ticket. I would like to show a base line at the avarage time spent for all tikets. So if the most time spent on a ticket was 10 and the least was 5 then I would like one streight line to go accross the graph at 7.5. Is this at all posible. Thanks for your help. Steve ...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

How do I add field totals
I have a Form with 3 text boxes. I want to add the figures in Box 1 and Box 2 and make it show up in Box 3. How do I do that? Sorry, I'm pretty new to Access. Thanks -- Bobc SRES, LLC One way to do this would be to do the calculation in a query, then use that query to "feed" the form. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in ...

Hyperlink description field
Visio Professional 2002. I have a Conceptual Web Site diagram on which I use Web Site Map Shapes. Sometimes, when I need to change the hyperlink address, the text that the shape displays doesn't change. In Configure Hyperlinks, the Address and the Description fields show the new address, but the shape continues to display the old. Any idea? ...

copying field properties to multiple field
I have several variables for which I need to convert text to number and field limits. I'm wondering if it is possible to copy field properties from one variable and apply it to multiple other variables without having to paste to each individually. Any help would be greatly appreciated. ...

Data Validation
I have set up a data form and have data validation rules on the various input cells. I see that there is an option called "Ignore blanks" in the data validation settings, which is supposed to restrict someone from entering past an input cell without entering valid data, if the "Ignore blanks" box is unchecked. I have tried to do this, but it does not seem to work. Please could someone tell me what I am doing wrong? Provide information, or better yet, a screen capture of the Data Validation dialog box with the Setting tab selected (User ALT+PrintScreen to copy the imag...

Conditional formatting based on matching record in separate query
I would like to conditionally format a patient's name (e.g., Bold) in a report, based on whether it finds that name in a separate query of New Admissions. How can I make my condition look for matching data in the query? The table name is Today'sList, and the query name is Admissions. The matching field name containing the patient's name is "Field1". Do I need to construct an expression using IIf (I'm assuming)? My question is, somehow I need my conditional expression to say "If Field1 matches any record's Field1 in this other query (Admissions),...

validating macro
Hi I am sending a spread sheet out to branchs I need them to complete a sheet (common info) before they do anything else on the workbook. I am looking for a macro or code for VBA which who check (common info) see if the cells are complete if not bring up a error screen informing the user to complete the form and then take them straight to the (common info) sheet. and if possible let them go to the tab which they want to go to. Use an extended version of something along these lines: (if there are lots of cells use a 'for row numbers x to y' or 'do until' statment, o...

Notes about a table or fields -- possible?
Is there a way in SQL Server to store notes about a table or its fields? Aside from keeping a spreadsheet or some other storage mechanism which has notes about my table's fields, it would be really nice to be able to refer to something closer to the table itself. Access offers this in the design area of a table. When I use Access I can add any notes I need in a column called "Description" which is helpful. For example, let's say that I create a field as DECIMAL(18,6). At the time of creation I might know exactly why I set the precision this way, and in the f...

Reboot Validator
I'm running XP Pro, SP3. Attempting to install a program from Nero. Software keeps failing at startup, despite numerous re-installs. During installs, it keeps attempting to update the 'reboot validator' which it does not seem to be able to do. What is the reboot validator, and how do I update it. Couldn't find anything useful on MS site and Nero Tech support just says its an MS problem. -- Thank you, B. Parker Never heard of it but Nero Support should know about it: Reboot Validator appears to be a Nero component; cf. http://www.brighthub.com/comput...

Same field name in two tables
I am running a query that utilizes two joined table. One of the fields for my query is Left([ACCT_NBR] ,3) AS Prefix. The problem is that both tables have a field called [ACCT_NBR]. How do I designate which table it is coming from. thanks Qualify it with the table name: Left([TheTableNameGoesHere].[ACCT_NBR] ,3) Ken Sheridan Stafford, England cluckers wrote: >I am running a query that utilizes two joined table. One of the fields for my >query is Left([ACCT_NBR] ,3) AS Prefix. The problem is that both tables >have a field called [ACCT_NBR]. How do I desig...