Selected chkboxes to Populate a Combo Box

[noboffinme]

Hi

I have a form (frm_main) with an autonumber field called 'ID' & a Combo Box called 'cbo_chosen'.

It (frm_main) has a subform (sfm_others) containing a Checkbox & a text field (txt_others) - 'txt_others' contains various names.

The user will go to the subform & check various checkboxes against the names.

I want a command button on the (frm_main) form to filter the subform & only populate what's been selected into the 'cbo_chosen' Combo Box.

As I scroll through the records on the 'frm_main' the selections need to stay as chosen for each record.

Does anyone have any known examples, have tried to make an example but can't create anything useful to upload, Thanks

 

[vbaInet]

I'm guessing the checkbox is bound?

Loop through your subform's record source using a recordset with a WHERE condition of True, add to your VALUE LIST combo box until EOF.

 

[noboffinme]

Thanks vbaInet

Yes, the combo is bound, I've got to the stage of creating a file & the query works up to entering the results into the combo box, could you suggest what code or examples of where I can find some similar examples to complete this part?

So to repeat what I want to do, the user clicks the file icon to open the form, checks or unchecks the options they want for that record & then clicks the 'Update Combo' command button to have only those 'Honor names' appear in the drop down list.

Thanks

 

[vbaInet]

I don't need to see your db. I will give you the skeleton:

dim rs as dao.recordset, strItems as String

set rs = Currentdb.openrecordset("Select [FieldToAdd] From [TableName] Where [YesNoField] = True;", dbopensnapshot)

' Get the items and save into a string
do while not rs.eof
     strItems = strItems & rs![FieldToAdd] & "; "
loop

' Set the combo's row source if there are items to add
if len(strItems) <> 0 then
     Me.combobox.RowSource = left(strItems, len(strItems) - 2)
end

set rs = nothing

 

[vbaInet]

In actual fact, you don't need code. Just use a query and set the criteria to True. When the button is clicked REQUERY the combo.

 

[smig]

here an example
you should give your subform's CheckBoxes and TextBoxes names like "Chk1, Combo2... Txt1, Txt2....

dim qryClues as String
dim i as Integer
dim ChkName as String
dim TxtName as String
 
' --- This line will make sure nothing is selected before any check is checked ---
' --- you must put this line or you'll have an error if no check is checked
qryClues = " WHERE IsNull(tblKey) = True "
 
for i = 1 to LastCheckNumber
  ChkName = "Combo" & Cstr(i)
  TxtName = "Txt"  & Cstr(i)
  If me.Controls(ChkName) = True then
    qryClues = qryClues & " OR tblTxtField = " & Chr(34) & TxtName & Chr(34) & " "
  End If
next i
 
YourCombo.RowSource = "Select * FROM MyTable " & qryClues

 

[vbaInet]

As mentioned in my last post, code is not needed for this. All that is needed is a SELECT query with a WHERE clause of True and Me.Combobox.Requery when needed.

 

[noboffinme]

Thanks vbaInet

I should have thought of that...

Thanks also smig for the code.

 

[vbaInet]

Glad we could help.

 

[noboffinme]

Hi Again

I noticed that the requery runs the query but I need it to only show the records for frm_mains ID field.

I'm finding that as I move through frm_mains records, the query result is the same whereas I need it to change for each records specific choices.

Is there a need now to use code? perhaps an addition to what you've written already?

 

[vbaInet]

Ok, but that wasn't part of your original request.

Under the criteria row of the id field put this:

Forms!FormName!IdField

Substitute FormName and IdField with the correct names.

 

[noboffinme]

Thanks vbaInet - once again

 

[vbaInet]

No problemo!