Capture field of certain records to display in code as comma separated text
in [Help]
|
From: Kev on 8 Aug 2010 23:36 Hello, I was wondering what the most efficient way of capturing check box values from those records in a form which are checked so that I can use the values in code. I have a form with 5 controls, 1 is a checkbox. The user will check the records he wants chosen and then click a CommandButton. In the commandButton code I want to be able to display the selected control values like this: Dim Control4 as String Control4 = "SelectedRec1Value,SelectedRec2Value,SelectedRec3Value,SelectedRec4Value" etc Any assistance Much appreciated Tahnk you Regards Kevin
From: Salad on 8 Aug 2010 23:59 Kev wrote: > Hello, > I was wondering what the most efficient way of capturing check box > values from those records in a form which are checked so that I can > use the values in code. > > I have a form with 5 controls, 1 is a checkbox. The user will check > the records he wants chosen and then click a CommandButton. In the > commandButton code I want to be able to display the selected control > values like this: > > Dim Control4 as String > Control4 = > "SelectedRec1Value,SelectedRec2Value,SelectedRec3Value,SelectedRec4Value" > etc > > Any assistance Much appreciated > Tahnk you > Regards > Kevin Public Function ListCheckBoxes(frm As Form) As String 'get the names of checkboxes on a form and the value Dim ctl As Control Dim s As String For Each ctl In frm.Controls If ctl.ControlType = acCheckBox Then s = s & ctl.Name & " " & NZ(ctl.Value, 0) & "," End If Next ListCheckBoxes = s End Function I called it from the form. Ex: Private Sub Command`_Click() MsgBox ListCheckBoxes(Me) End Sub It simply lists the checkbox name and their values. Modify to suit.
From: Kev on 9 Aug 2010 01:04 Thanks for this Salad, I'm not sure if I have applied your code correctly or explained my issue sufficiently. It only seems to be listing one checkbox. I have seen where I need to expand my explaination: I want to capture the value of another control (say Cont4) when Cont1 = Yes, Cont1 being the checkbox: Cont1 Cont2 Cont3 Cont4 Cont5 Record1 Yes abc 1623 fred 5456 Record2 No xabc 1823 bill 4526 Record3 Yes aybc 7123 mike 4h56 Record4 No abzc 9123 peter 4h56 Record5 Yes abcw 5123 jerry 4g56 Result of variable would give me: variable1 = "fred, mike, jerry" I wish to then use this variable in future code. Apologies re confusion of explaination Regards Kevin On Aug 9, 1:59 pm, Salad <sa...(a)oilandvinegar.com> wrote: > Kev wrote: > > Hello, > > I was wondering what the most efficient way of capturing check box > > values from those records in a form which are checked so that I can > > use the values in code. > > > I have a form with 5 controls, 1 is a checkbox. The user will check > > the records he wants chosen and then click a CommandButton. In the > > commandButton code I want to be able to display the selected control > > values like this: > > > Dim Control4 as String > > Control4 = > > "SelectedRec1Value,SelectedRec2Value,SelectedRec3Value,SelectedRec4Value" > > etc > > > Any assistance Much appreciated > > Tahnk you > > Regards > > Kevin > > Public Function ListCheckBoxes(frm As Form) As String > 'get the names of checkboxes on a form and the value > Dim ctl As Control > Dim s As String > > For Each ctl In frm.Controls > If ctl.ControlType = acCheckBox Then > s = s & ctl.Name & " " & NZ(ctl.Value, 0) & "," > End If > Next > ListCheckBoxes = s > > End Function > > I called it from the form. Ex: > > Private Sub Command`_Click() > MsgBox ListCheckBoxes(Me) > End Sub > > It simply lists the checkbox name and their values. Modify to suit.
From: Salad on 9 Aug 2010 01:29 Kev wrote: > Thanks for this Salad, > I'm not sure if I have applied your code correctly or explained my > issue sufficiently. It only seems to be listing one checkbox. > I have seen where I need to expand my explaination: > > I want to capture the value of another control (say Cont4) when Cont1 > = Yes, Cont1 being the checkbox: > > Cont1 Cont2 Cont3 Cont4 Cont5 > > Record1 Yes abc 1623 fred 5456 > > Record2 No xabc 1823 bill 4526 > > Record3 Yes aybc 7123 mike 4h56 > > Record4 No abzc 9123 peter 4h56 > > Record5 Yes abcw 5123 jerry 4g56 > > Result of variable would give me: variable1 = "fred, mike, jerry" > > I wish to then use this variable in future code. > Apologies re confusion of explaination > Regards > Kevin > > > On Aug 9, 1:59 pm, Salad <sa...(a)oilandvinegar.com> wrote: > >>Kev wrote: >> >>>Hello, >>>I was wondering what the most efficient way of capturing check box >>>values from those records in a form which are checked so that I can >>>use the values in code. >> >>>I have a form with 5 controls, 1 is a checkbox. The user will check >>>the records he wants chosen and then click a CommandButton. In the >>>commandButton code I want to be able to display the selected control >>>values like this: >> >>>Dim Control4 as String >>>Control4 = >>>"SelectedRec1Value,SelectedRec2Value,SelectedRec3Value,SelectedRec4Value" >>>etc >> >>>Any assistance Much appreciated >>>Tahnk you >>>Regards >>>Kevin >> >>Public Function ListCheckBoxes(frm As Form) As String >> 'get the names of checkboxes on a form and the value >> Dim ctl As Control >> Dim s As String >> >> For Each ctl In frm.Controls >> If ctl.ControlType = acCheckBox Then >> s = s & ctl.Name & " " & NZ(ctl.Value, 0) & "," >> End If >> Next >> ListCheckBoxes = s >> >>End Function >> >>I called it from the form. Ex: >> >> Private Sub Command`_Click() >> MsgBox ListCheckBoxes(Me) >> End Sub >> >>It simply lists the checkbox name and their values. Modify to suit. > It's late. Have you F1'd ControlType? I'm only looking at checkboxes in the above code. You can check for various types; textbox, combo, list, command, etc. Did you also F1 Value as well?
From: John Spencer on 9 Aug 2010 09:26 You can try the following SAMPLE UNTESTED VBA code to get the list. You will have to modify it for your particular situation. Public Function fGetValues() Dim rst as DAO.Recordset Dim strSQL as String Dim strReturn as String strSQL ="SELECT Cont4 FROM [SomeTable] WHERE Cont1=True" Set rst = Currentdb().OpenRecordset (strSQL) If rst.RecordCount > 0 then While Not rst.EOF strReturn = ", " & rst!Cont4 & strReturn rst.NextRecord Wend strReturn = Mid(strReturn,3) fGetValues = strReturn Else fGetValues = Null End If End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Kev wrote: > Thanks for this Salad, > I'm not sure if I have applied your code correctly or explained my > issue sufficiently. It only seems to be listing one checkbox. > I have seen where I need to expand my explaination: > > I want to capture the value of another control (say Cont4) when Cont1 > = Yes, Cont1 being the checkbox: > > Cont1 Cont2 Cont3 Cont4 Cont5 > > Record1 Yes abc 1623 fred 5456 > > Record2 No xabc 1823 bill 4526 > > Record3 Yes aybc 7123 mike 4h56 > > Record4 No abzc 9123 peter 4h56 > > Record5 Yes abcw 5123 jerry 4g56 > > Result of variable would give me: variable1 = "fred, mike, jerry" > > I wish to then use this variable in future code. > Apologies re confusion of explaination > Regards > Kevin > > > On Aug 9, 1:59 pm, Salad <sa...(a)oilandvinegar.com> wrote: >> Kev wrote: >>> Hello, >>> I was wondering what the most efficient way of capturing check box >>> values from those records in a form which are checked so that I can >>> use the values in code. >>> I have a form with 5 controls, 1 is a checkbox. The user will check >>> the records he wants chosen and then click a CommandButton. In the >>> commandButton code I want to be able to display the selected control >>> values like this: >>> Dim Control4 as String >>> Control4 = >>> "SelectedRec1Value,SelectedRec2Value,SelectedRec3Value,SelectedRec4Value" >>> etc >>> Any assistance Much appreciated >>> Tahnk you >>> Regards >>> Kevin >> Public Function ListCheckBoxes(frm As Form) As String >> 'get the names of checkboxes on a form and the value >> Dim ctl As Control >> Dim s As String >> >> For Each ctl In frm.Controls >> If ctl.ControlType = acCheckBox Then >> s = s & ctl.Name & " " & NZ(ctl.Value, 0) & "," >> End If >> Next >> ListCheckBoxes = s >> >> End Function >> >> I called it from the form. Ex: >> >> Private Sub Command`_Click() >> MsgBox ListCheckBoxes(Me) >> End Sub >> >> It simply lists the checkbox name and their values. Modify to suit. >
|
Pages: 1 Prev: Only show first X hits for a column in query? Next: Multiple Counts In Select Query |