HOW CAN I UPDATING TWO PULLDOWNS 1

[chubby]

I have a problem: I have two pulldowns, one is the [Rate] and the other is the [Rank]. Examples:

[Rate][Rank]
ETSR= E1
ETSA= E2
ETSN= E3
ET3= E4
ET2= E5
ET1= E6
ETC= E7
ETCS= E8
ETCM= E9

(There are over 120 rating in my database:BM,BT,AO,IC,etc)
When a person makes Rank both pulldowns must be updated. The problem is, somtimes my end-users update one and not the other. (and that screws up things like the Total Rank and Rate breakdown weekly reports) Is there away when little Johnny goes form ETSN to ET3 in the Rate Pulldown the Rank pulldown goes from E3 to E4 at the same time????

 

[Aivars]

Hi!

Try to include into Form BeforeUpdate procedure for updating of missing data.

Private Sub Form_BeforeUpdate(Cancel As Integer)
dim rst as recordset

if isnull(me.Rate) and isnull(me.Rank) then
msgbox "You may update..."
'or do.....
'or ignore it... and exit sub
'otherwise
cancel=true
exit sub
end if
if isnull(me.Rate) or isnull(me.Rank) then
set rst=currentdb.openrecordset("Select Rate, Rank from MyTable;"
if isnull(me.Rate) then
rst.findfirst "Rank='" & me.Rank
rst.edit
me.Rate=rst!Rate
rst.update
elseif isnull(me.Rank) then
rst.findfirst "Rate='" & me.Rate
rst.edit
me.Rate=rst!Rate
rst.update
end if
rst.close
set rst=nothing
End Sub

But better solution is that you created combobox based on this table. Then you would be update always both fields.

Aivars

 

[chubby]

Thanks!!!! I think I'm going to try some else or try your second idea. Thanks again...