Autopopulate based on a prior field in a form

[lvandyke]

Hi all,

I have a form to enter participant data for a study. There is a field "participantID" which is entered manually as numeric 1-100. I want to be able to do a couple of things.

1) The next field "groupType" should autopopulate as "I" if "participantID" is 1-50 and "C" if "participantID" is 51-100.

2) The field "classAttended" should only display a specific list based on whether "groupType" is I or C. I want a list like "Class1, Class2, Class3" to display for when "groupType" = I, but I only want "Class1, Class3" to display if "groupType" = C.

Thanks for any help in advance!

 

[hmlhml]

I'm only a newbie on this, but:
(1) I'd deal with I/C by putting an AfterUpdate event in the control where people enter their participantID, which would populate any other fields as need be.
(2) The control (combo-box) for classAttended needs a row-source taken from a table of entries such as "Class1, Class2" etc., with a where clause according to I/C. I would build the row-source using the query design tool, and I think you'll need to add a requery for this control in your AfterUpdate event so that it takes account of the changed I/C value and updates its rowsource accordingly. There are several ways to arrange the table of options for your control. You could have separate boolean columns for IfI and IfC, but it's tidier to have just two columns in your table: the text to show in the combo-box, and the text to match against (I or C), and for any Class1 that should show up in both lists, include two rows. Incidentallyu, I would keep this control locked until a valid participantID has been entered.
OK, tell me this is all hopelessly bad design and tell me a simpler way, someone. I'm only learning!

 

[lvandyke]

I did try your suggestion for #1, and put "=IIf([ParticipantID] Between 1 And 50,[groupType]="I",[groupType]="C")" on the "AfterUpdate" which didn't work and the "OnClick" which also didn't work. So I'm definitely missing something!

We'll learn together. It's a long and painful process.

 

[TheAceMan1]

How are ya lvandyke . . .

Remove your IIf statement. Then in the [blue]After Update[/blue] event of [blue]ParticipantID[/blue] copy/paste the following. Note: your pasting into the event [red]not the event line.[/red] To get the event put cursor on the event line and click the three elipses [blue]...[/blue] on the right. If a [blue]Choose Builder[/blue] windo pops up select [blue]Code Builder[/blue]. When the VBA code window opens you should see:

[blue]Private Sub ParticipantID_AfterUpdate()

End Sub[/blue]

... and the code to paste

[blue]   If Me.ParticipantID > 0 And Me.ParticipantID < 51 Then
      Me.GroupType = "I"
      Me.ClassAttended = "Class1, Class2, Class3"
   ElseIf Me.ParticipantID > 50 And Me.ParticipantID < 101 Then
      Me.GroupType = "C"
      Me.ClassAttended = "Class1, Class3"
   Else
      Me.GroupType = ""
      Me.ClassAttended = ""
   End If[/blue]

When your done should look like:

[blue]Private Sub ParticipantID_AfterUpdate()

   If Me.ParticipantID > 0 And Me.ParticipantID < 51 Then
      Me.GroupType = "I"
      Me.ClassAttended = "Class1, Class2, Class3"
   ElseIf Me.ParticipantID > 50 And Me.ParticipantID < 101 Then
      Me.GroupType = "C"
      Me.ClassAttended = "Class1, Class3"
   Else
      Me.GroupType = ""
      Me.ClassAttended = ""
   End If

End Sub[/blue]

Hit [blue]Alt + Q[/blue] to return to design view and perform your testing.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]