How can I set the default values for fields on different rows in a continuous form?

Question

I have a Group combo field, and a person combo field on a continuous form.

I would like to say if group = "Group1" then Default Person = "Bob"

Perhaps ideally in a case statement for each person field to be set to a different person for each group.

When the user clicks on the person combo field the other people for that group will be shown but the default person highlighted.

I have only managed to use conditional formatting successfully on a continuous form in the past. Is the solution to use that somehow?

Thank you

---

Nath

Answer 1

Hi Nath, Have you tried using a Cascading Combobox?

Answer 2

Hi NaPazz,

its looks like you want to change the combobox value based on other combobox selection.

please take a look at thread below may help you to solve your issue.

Change a ComboBox value based on a selection of another ComboBox

if that not helps you to solve your issue then let us inform about that and if I misunderstand your requirement then please correct me.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

The problem you're likely seeing with continuous forms view is that property settings of your combo box affect all records.  The reason for this is that even though you see data for all records, you only have one set of controls across all records. 

If your combo box has the limit to list property set to 'yes', you may have to use a combination of the cascading combo technique mentioned in previous comments and a textbox bound to the same field. By carefully adjusting the width properties and the relative placement of the combo and textbox, you can make the two controls look like a single combo box.  The text box will display the underlying data, even if the combo appears blank for records where the underlying data is not included in the rowsource.

---

Miriam Bizup Access MVP

Answer 4

Create a separate table like this:

GroupDefaultPeople
….Group  (PK)
….DefaultPerson

It would have values like this:

Group        DefaultPerson
Group1       Bob
Group2       Betty
Group 3      Bill
Group 4      Beatrice
....and so on

In the AfterUpdate event procedure of the Group combo box put code like this:

Dim strCriteria As String

strCriteria = "Group = """ & Me.cboGroup & """"
Me.cboPerson = DLookup("DefaultPerson","GroupDefaultPeople", strCriteria)
Me.cboPerson.SetFocus
Me.cboPerson.Dropdown

In reality you should use a numeric PersonID column in the above table and in the table to which your form is bound, in each case referencing the primary key of a People table, rather than a personal name, as the personal names can legitimately be duplicated.  The value of the cboPerson combo box would be the hidden PersonID column, but the control would show the name.

---

Ken Sheridan, Stafford, England