How to repopulate data in form once combobox item picked?

[Charlie Erdman]

Hi. I am very new to SQL. I am working on an Access 2007 database. I want to create a form with a combo box, which I know how to do. However, once I select the appropriate SampleID from the combo box, I would like the subform to repopulate with the correct data corresponding to the selected SampleID. This is where I am having problems. Thanks in advance for any help.

FYI, the main table is called all_net_data. Eventually, I would like to have three subforms repopulate with all the data corresponding to the specific SampleID selected (each subform corresponding to a different table).

Does this make sense? Again, I am new to Access and SQL.

Thanks,

Charlie

[Oralloy]

Charlie,

I think you want to hang your VBA code on the combobox "Change" event, like this...

Private Sub ComboBox1_Change()
  If Me.ComboBox1.value = "Field" Then
    Me.Text2.value = "Test"
  ElseIf Me.ComboBox1.value = "Rip" Then
    Me.Text2.value = "Torn"
  Else
    Err.Raise 1 + VBA.Constants.vbObjectError, _
              "Test Combo Box", _
              "Whoops, unexpected value in Combo Box"
  End If
End Sub

Hopefully this helps a little.

Cheers!
Oralloy

[Charlie Erdman]

Thanks for the reply Oralloy. Being a newbie, I am confused. First I guess I should state that I'd like the subform on the form with the combo box. In the above code, I don't really understand the Text2 part. Would that refer to text box in the subform? Also, I added this code and I received the following message: "Data member not found and it highlights the text2 in the code". So I assume I need to change the text2 in the code to the appropriate text box name. Is this correct? Also, on the subform I have about 20 text boxes refering to fields in a table, do I need to code all these separately? Thanks again,

Charlie

[Oralloy]

Charlie,

In my example, Text2 is just a text box on my form. To make it a text box on the subform, use something like:

Me.mySubform.Te xt2.value = "Test"

Where mySubform is declared as the type of the subform you are using, or simply as an Object.

My preference is for strong typing, but then I come from a C++ background where typing is used to prevent the user from injecting errors into the code by guarding computations and assignments at compile time. VBA does this, but you have to be patient and use the facility; the default is Variant, which accepts anything. (enough, we can discuss typing all day long).

[[Of course, it gets worse, if your sub-form has a grid display, but let's not go there, unless we have to.]]

Since your sub-form has about 20 fields, you're going to have to manage them somehow. Either you'll have to explicitly deal with the values, or you'll have the sub-form bound to a table (or query). In the later case, you'll have to build the filter and appropriate update mechanics. Essentially, you created the data, you have to manage it. There are 20 data fields there, and unless you tell VBA how to deal with them in some way or other, they'll just sit there and provide no use to your application.

Is that helpful, or were you asking for something different?

Good Luck and Cheers!
Oralloy!

[Jerry Maiapu]

You said: I would like to have three subforms repopulate with all the data corresponding to the specific SampleID selected (each subform corresponding to a different table).

This would mean that the 3 subforms recordsource/table are having a one to many relationships. If so then try this on the afterupdate event of the combobox:

 ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[[B][I][U]ID[/U][/I][/B]] = " & str(Nz(Me![[B][I][U]cmbCombo[/U][/I][/B]], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Replace ID with your combo's ID and cmbCombo with you combo's name.

[NeoPa]

Check out Example Filtering on a Form.