Tick items in the subform instead of selection one by one by combo

[Masoud]

Hello
In my subform (continuous form) , i have to select some records, these
records are very limited for example 3 until 6 records each time. For this
case i have mentioned combo box, but it takes time selecting one by one. Now
i want all the the records in my subform and just tick in the check box in
front of them for selecting each record that i need (like selecting Cc in
yahoo mail from the list of contacts).
Thanks a lot.

 

[Arvin Meyer MVP]

The link field in the subform is probably the Primary Key of the main form
and Foreign Key in the subform (the ID), so I'll just call it txtLKey for
this code, but you need to substitute the name of the textbox that carries
that value.

So, this code will check them all if one of the check boxes are clicked
True. Keep in mind that this code is untested.

Sub chkSelectRecord_AfterUpdate()
If Me.chkSelectRecord = True Then
Dim strSQL As String
strSQL = "UPDATE MyTable SET SelectRecord = True WHERE ID =" &
Me.txtLKey
CurrentDb.Execute strSQL
End If
End Sub

To use this code you'll need a reference to DAO.

 

[John W. Vinson]

Hello
In my subform (continuous form) , i have to select some records, these
records are very limited for example 3 until 6 records each time. For this
case i have mentioned combo box, but it takes time selecting one by one. Now
i want all the the records in my subform and just tick in the check box in
front of them for selecting each record that i need (like selecting Cc in
yahoo mail from the list of contacts).
Thanks a lot.

You can add a Yes/No field (call it Selected maybe) to the table underlying
the subform, and display this field on the subform using a checkbox control.

An alternative is to use a multiselect listbox with some code to allow the
user to select from a list. Post back if you'ld like the code.

 

[Masoud]

thanks,
Yes, I like to have the code and use some thing like list box and multi
select from that, but I do not know how I have to define relation between
main form and list box.
Just for more information my main table is

Transmittal No (primary key) Date attention
T-CT-AT-0001 01-APR-09 Smith

Now I want to send some Cc to some others that are limited to 6 items.

Cc
Jack
John
..
..
Now I want select from this list each item that I need (prefer by tick). I
like action of this list box should be like what subform does, it means It
fill up another table with these fields after selection.

Transmittal No Cc
T-CT-AT-0001 Jack
T-CT-AT-0001 John

Please help.

 

[John W. Vinson]

thanks,
Yes, I like to have the code and use some thing like list box and multi
select from that, but I do not know how I have to define relation between
main form and list box.

It's not trivial, but here's the code I use in my animal shelter database.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub

 

[Masoud]

Actually there is no link between my main form and subform. if I make the
link , there is no provided records for selection in the subform.

for more information Record source of subform is one table that I called
tblcc that contain always 6 records.like

cc (primary key) check(yes/no field)
john yes
smith no
martin yes
,
..
..

Record source of main table is one table that I called tbltransmittalNo

Transittalno (primary key)
t-ct-at-0001



now I want when select item in subform it add records to another table that
I called tblccTransmittalNo like below

transmittalNo(primary key) cc (primary key)
t-ct-at-0001 john
t-ct-at-0001 martin

and when I clear check box in the subform it deletes that from
tblcctransmittalNo.