User to input data into one box or another not both

[samjh]

Hi,

I have a form, and on the form there is a Provider Rate which is a combo box, if the user select a zero rate, then it has the description please enter manual rate in box below. There is another box which the user can enter a manual rate.

Can someone suggest how I can limit entry into these boxes, as currently a user can select a rate in the provider rate box and still enter something into the manual rate box. I want it so that if a rate other than zero has been selected in the provider rate box, then they can't enter anything into the manual rate cell.

Thanks.

 

[CJ_London]

you'll need to correct for your actual box names etc. Have also assumed values are not text

In the form oncurrent event - :

Set 'otherbox' enabled = false
set 'providerratebox' enabled =true
Set Rateselected=false '- [U]Rateselected is a boolean global variable set after the Option Compare Database and before the first procedure or function[/U]

Assuming your providerrate box is in the detail section of the form put the following into the detail mouse move event (if in the header or footer, place in the appropriate mousemove event

Private Sub [B]Detail[/B]_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
if Rateselected=true and providerratebox=0 then
    'otherbox' enabled = true
    'providerratebox' enabled =false
end if
End Sub

In the providerratebox' afterupdate event put

rateselected=true
otherbox=0

Note: You might want to have a separate button to 'reset' if someone has selected 0 by mistake (simply call the oncurrent event)

 

[samjh]

Hi, thanks for this, and I'm about to ask a really daft question, so appologies for this. I already have something in my On Current event , so how do I got about adding something else, and do I literaly just type Set 'ManualRate' enabled = False

As I have tried typing this into my On Current event after the stuff that is already there but before the final End Sub and it doesn't work.

I'm learning as I'm going along!!

 

[CJ_London]

just add another line!

Don't forget to declare the variable just below

Option Compare Database
Option Explicit
[COLOR=red]Dim RateSelected as Boolean[/COLOR]
 
Private Sub Form_Current()
'... whatever code you already have
[COLOR=red]RateSelected=False[/COLOR]
 
End Sub

 

[samjh]

Hi,

Sorry I can't get this to work properly, currently what I have is the manual rate disabled, but when I select a Provider Rate of 0, the manual rate box does not change to be enabled.

I think it might be because my Provider Rate is a combo box, which is bound to the ProviderRateID, therefore the value would really never be 0 as the 0 would be in the second column of the row source which is what I see in the box.



Thank you

 

[pr2-eugin]

Hello samjh, I am not sure if I am understanding this.. So let me summarise, what I have understood correct me if I am wrong..

You have a ComboBox, say rateComboBox which is two column first columm is ID and it is bound to the field something and the second column has Zero, Ten or something like that.. So the box to enter Manual amount should be enabled only if the ComboBox is selected to zero..

If the above description is right.. Then go to design view of the Form and select the boxes and set its Property for Enabled = No and Locked = No this will grey out the box.. thus nothing can be entered.. unless the zero is selected.. i.e in the AfterUpdate event of the combo box..

Private Sub rateComboBox_AfterUpdate()
    If rateComboBox = 1    Then    [COLOR=Green]'Change this 1 to whatever ID that Zero represents[/COLOR]
        Me.manualAmount.Enabled = True
    End If
End Sub

 

[samjh]

Hi, thanks for your response, it's not as simple as that, as I have a rates table which has rates linked to Providers, so for example, I have a
ProviderRateID = 986 ProviderRate = 0 Provider = Homecare (in this scenario I want to enter a Manual Rate)
ProviderRateID = 987 ProviderRate = 14.69 Provider = Homecare (in this scenario I don't want them to be able to enter anything in the Manual Rate box)
The issue is that I have many ProviderRate that = 0 and they all have different ID as they are all linked to different providers, therefore the code below needs to accomodate the fact that the 0 would be in column 2 as the bound column is the ID.
I hope that makes sense!


Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If RateSelected = True And [cmbProviderRate] = 0 Then
[ManualRate].Enabled = True
[cmbProviderRate].Enabled = False
End If
End Sub

 

[CJ_London]

You still need to disable the combo box so you will still need this

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
if Rateselected=true and providerratebox[COLOR=red].column(1)=[/COLOR]0 then
    'manualAmount.enabled = true [COLOR=seagreen]now done in combo afterupdate event
[/COLOR]    rateComboBox.enabled =false
end if
End Sub

If you don't disable the combo box, the user can select 0% then add in a manual amount and then change the combo box to another percentage which is the same problem you have at the moment

 

[samjh]

I'm sorry it's been a long day and now I;m really confused.

I have set the Manual Rate.Enables = False in the after update event of cmbProviderRate, and it works, so the Manual Rate box is only enables if the user selects a 0 rate.

Do I still need this:
Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
if RateSelected=true and cmbProviderRate.column(1)=0 then
'ManualRate.enabled = true
cmbProviderRate.enabled =false
end if
End Sub

If I do, it is coming up with an error or the cmbProviderRate.Enables=False line.

I don't think I need to make the cmbProviderRate box disabled, as if the user is to enter a manual rate they will only ever be given a 0 rate option. The issue I do have is if they have selected the wrong provider and they go and change it I already have a requery event for the ProviderRate but I need something to requery the Manual Rate box to disable it again, unless the new provider also has the option of a 0 rate.

I do hope this makes sense.

And thank you very much for taking the time to read and answer my queries.

 

[samjh]

btw enables = enabled, I clearly have typing issues!