Continuous Form Yes/No

[brownti via AccessMonster.com]

I have several vendors that supply the same product. In order to always be
using the best price i have a database that controls which price/vendor i am
currently using. My table has a vendor name, vendor number, product style,
my company number and a yes/no field. So for one item my table looks like
this:

Vendor Name Vendor # Style Comp# Current
Company A 123 A 1 yes
Company B 3-40 A 1 no
Company C 33040 A 1 no

So i am currently purchasing my product style A number 1 from Company A. I
have a continuous form that this information is shown on. I would like to
set it up so that when i change from Company A to Company B for this product
it will automatically set Company A to no and Company B to yes. It needs to
verify both the style and Comp# as i have multiple styles for one Comp#. Any
thoughts on accomplishing this? Thanks in advance!

 

[brownti via AccessMonster.com]

got it figured out

I have several vendors that supply the same product. In order to always be
using the best price i have a database that controls which price/vendor i am
currently using. My table has a vendor name, vendor number, product style,
my company number and a yes/no field. So for one item my table looks like
this:

Vendor Name Vendor # Style Comp# Current
Company A 123 A 1 yes
Company B 3-40 A 1 no
Company C 33040 A 1 no

So i am currently purchasing my product style A number 1 from Company A. I
have a continuous form that this information is shown on. I would like to
set it up so that when i change from Company A to Company B for this product
it will automatically set Company A to no and Company B to yes. It needs to
verify both the style and Comp# as i have multiple styles for one Comp#. Any
thoughts on accomplishing this? Thanks in advance!

 

[Roger Carlson]

This is just off the top of my head, but it's something to try:

In the AfterUpdate event procedure of the Current control (a checkbox?) add
the following logic (pseudocode):

If CurrentControl = true then
strSQL = "UPDATE Table SET Current = False " & _
"where Style = " & StyleControl & "AND Comp# = " & CompControl
docmd.RunSQL strSQL
End If

The idea here is if you check a box to TRUE, then the Update Statement will
check every other record that has the same Style and Comp# to FALSE.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L