CW
On the newsgroup we can't see how your forms and database are setup, so this
advice is still a bit general.
When the combo is showing the name Joe Bloggs, you need to show the checkbox
for commission, when the combo is showing any other salesman, you need to
hide the checkbox.
This code gives the idea, it is just a starting point. You will need to
change it to suit your field and control names at least.
I am assuming that the name of the Salesman is in the second column of your
combo, if this is not the case, the code would need to be changed to suit
your form.
Private Sub Form_Current()
If Me.cboSalesmanID.Column(1) = """Joe Bloggs""" Then
Me.[NameOfCheckbox].Visible = True
Else
Me.[NameOfCheckbox].Visible = False
End If
End Sub
A further refinement would be to have a Yes/No field in your tblEmployees
called Commission.
Next to any salesman who receives commission the field would show Yes. It
would show No for all other salesmen.
We could then change the code above to show the checkbox whenever a salesman
who receives commission is selected in the combo.
We could make it so that the name of the salesman didn't matter. The form
would just check tblEmployees to see if this salesman is entitled to
commissions.
If none of the above makes any sense, try a book or tutorial on VBA code for
Access.
Jeanette Cunningham
CW said:
Jeanette -
Thanks for your reply but I have two points to raise:
(1) I'm not sure if this affects your suggestion but none of the forms
would
actually be "for Joe Bloggs". They are customer order records - the
salesman
is just one field out of many that are completed for each order. So it
won't
ever be a case of "editing or adding data for Joe Bloggs".
(2) Even if we pass over that point, I don't know what code to put behind
the On Current event. I have done a reasonable amount in VB but I just
can't
work out what I would need in order to make this work. Could you please
spell
it out?
Thanks again
CW
Jeanette Cunningham said:
Hi CW,
Remove the validation from the table
One way to hide and show the checkbox:
On your form set the visible property of the checkbox to No.
When the form is editing or adding data for Joe Bloggs, to make the
checkbox
visible,
set its visible property to Yes like this
Me.[YourCheckboxName].Visible = True
When the form is editing or adding data for any other salesman, not Joe
Bloggs, make the checkbox invisible
Me.[YourCheckboxName].Visible = False
The code to hide and show the checkbox goes in the OnCurrent Event for
your
form.
Jeanette Cunningham
We pay commission to only one of our salesmen.
In my tblInquiries I have a Salesman field and a Commission Yes/No
field.
On my frmInquiries I have a combo for the Salesman to be selected (from
tblEmployees) and a checkbox for when commission has been paid out.
I would like to restrict use of the checkbox to where the Salesman is
just
that one eligible person.
I tried entering a validation rule firstly on the Commission field in
tblInquiries, then on the Commission control on frmInquiries, like
this:
[Salesman] = "Joe Bloggs".
On trying to save this I got the following error message:
"Invalid SQL syntax - cannot use multiple columns in a column-level
CHECK
constraint".
Now what's all that about??!!
All explanations and solutions gratefully received, thanks
CW
