Saving choices from combo box

[Ishim684]

Dear Access experts, I am a student of linguistics trying to build my first database with Access, and I'm looking for help, although I realize my problem is something very basic.

I have two tables, tblDictionary and tblCorpus which are connected through their IDs. And I have a form, frmFORM where the combo box is populated from one of the fields in tblDicionary (I have it as the RowSource for the combo box).

I want the choices made by the user of frmFORM in the combo boxes to be saved in these combo boxes and passed to a field in tblCorpus. But I can't figure out how to do this.

Any advice will be very much appreciated. Unfortunately, I could not attach my database to this post (maybe that's because it's my first message on the forum?)

 

[pbaldy]

The normal setup would be for the form to be bound to tblCorpus, and the combo's control source to be the field in that table. In other words, the combo gets its selections from tblDictionary but saves to a field in tblCorpus.

 

[Ishim684]

Dear Paul, thank you SO much for your generous help!!! Your advice has been SO helpful to me!!! It took me a while to get it all work as I wanted, but now it's perfect -- exactly what I needed!

 

[pbaldy]

Happy to help!

 

[Ishim684]

Dear Paul, may I ask one more question? This time I’m trying to design a form (FORM1) which is bound to a table (TABLE). The text field (Field1) in FORM1 is populated from the field Def of TABLE. But I want the values that enter FORM1!Field1 to be filtered by the value in the text field in another form (FORM2!Field2). The values in Field1 and Field2 are compared via another field in TABLE (TABLE!Lemma). I am running the following code on the Enter event of Field1:

Private Sub Field1_Enter()
Dim strField1 As String
strField1 = "SELECT TABLE.Def FROM TABLE " & _
"WHERE TABLE.Lemma Like '" & [Forms]![FORM]![Field2] & "'"
Me.Field1.ControlSource = strField1
Me.Field1.Requery
End Sub

But the filter doesn’t seem to apply. In my Field1 I keep getting all the values from TABLE.Def, unfiltered.
I have also tried the After_Update event, but with the same result.
I am doing something totally wrong, or may there be minor bug in this?

 

[Ishim684]

Oh, Paul, I've figured it out myself, after many trials and ordeals! I first built a query where all these fields were present, and bound the form to this query. It got to work! Sorry I bothered you. Thank you.

 

[pbaldy]

Glad you got it sorted. You can't set a control source to SQL anyway.

 

[Ishim684]

Oh, that's great to know! Thank you, Paul, you've saved me any possible fruitless attempts!