Need combo to update records after selection

[ppoindexter]

i have a form(frm_Alpha)
record source is tbl_one
and the fields are fld_one_id (auto number_primary key),
fldA, fldB, fldC, fldD, and fld_two_id (foreign key from tbl_two)

i select a record from comb_fld_two_id
(bound to tbl_one.fld_two_id),
populate fldA, fldB, fldC, fldD then click a command button "add new record"
and all is good

now what i am having difficulty with is when i go to
combo_fld_two_id for the next selection
i still have the first record that i just populated in the drop down box ....ie..if there were 5 records the first time i chose a record from combo_fld_two_id, i want to only see the 4 that havent been selected as of yet....i have tried several methods and searched this forum but no luck.....

thanks in advance

 

[WayneRyan]

ppoindexter,

What you need to do is set the rowsource for the combo to:

Me.Combo.Rowsource = "Select SomeField " & _
                     "From   SomeTable " & _
                     "Where  SomeField Not In (" & _
                         "Select SomeField " & _
                         "From   SomeOtherTable)"

Then just do a Me.Combo.Requery after
you insert a new record. Use the AfterInsert
event of your form.

Wayne

 

[ppoindexter]

i have the following code as the rowsource that provides the selection for the combo in question...i am not sure how to change the existing code to incorporate what you suggested....do i just add another line??? if so, could you please explain..thanks



Private Sub Combo_brief_GotFocus()
If IsNull(Me.Combo_standard) = True Then
Me.Combo_brief.RowSource = "select fldtplanid,fldtplan_brief_description,fldtplan_label from tblmimtplan where fld_resource_added=0 and fldmim_core_id = " & Me.Combo_Component & " And fldlevel2id = " & Me.Combo_Grade & " order by fldtplan_brief_description"
Else
Me.Combo_brief.RowSource = "select fldtplanid,fldtplan_brief_description,fldtplan_label from tblmimtplan where fld_resource_added=0 and fldmim_core_id= " & Me.Combo_Component & " and fldlevel1id= " & Me.Combo_standard & " And fldlevel2id = " & Me.Combo_Grade & " order by fldtplan_brief_description"

End If
End Sub

 

[WayneRyan]

ppoindexter,

Something like this:

Me.Combo_brief.RowSource = "select fldtplanid, fldtplan_brief_description, fldtplan_label " & _
                           "from tblmimtplan " & _
                           "where fld_resource_added = 0 and " & _
                           "      fldmim_core_id = " & Me.Combo_Component & " And " & _
                           "      fldlevel2id = " & Me.Combo_Grade & " And " & _
   Omits These Records --> "      fldlevel2id Not In (" & _
                           "      Select fldlevl2id " & _
                           "      From   ThatOtherTable) " & _
                           "Order by fldtplan_brief_description"

Wayne

 

[ppoindexter]

Wayne
Works great! Thank you so much. My final code as follows:

Private Sub Combo_brief_GotFocus()
If IsNull(Me.Combo_standard) = True Then
Me.Combo_brief.RowSource = "select fldtplanid,fldtplan_brief_description,fldtplan_label from tblmimtplan where fldmim_core_id = " & Me.Combo_Component & " And fldlevel2id = " & Me.Combo_Grade & " and fldtplanid Not In (select fldtplanid from tblmimreference) order by fldtplan_brief_description"
Else
Me.Combo_brief.RowSource = "select fldtplanid,fldtplan_brief_description,fldtplan_label from tblmimtplan where fldmim_core_id= " & Me.Combo_Component & " and fldlevel1id= " & Me.Combo_standard & " And fldlevel2id = " & Me.Combo_Grade & " and fldtplanid Not In (select fldtplanid from tblmimreference)order by fldtplan_brief_description"

End If
End Sub

Happy Holidays