Check in the BoX

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a big question. I have a database that has all of our Navy Command
names and info. I want to be able to input one topic and then be able to
select who this topic applies too. and then be able to come back later and
input a new topic and still have the last topic stay with the associated
name. any information will help.
 
I have a big question. I have a database that has all of our Navy Command
names and info. I want to be able to input one topic and then be able to
select who this topic applies too. and then be able to come back later and
input a new topic and still have the last topic stay with the associated
name. any information will help.
Click to expand...

YOu need THREE tables: Personnel; Topics; TopicAppliesTo.

The latter table will have the PersonnelID (the person's Service
Number I presume) and the Topic as a joint, two-field Primary Key. The
table would be related to Personnel by the PersonnelID, and to Topics
by the topic (or TopicID if you're using a numeric primary key).
If a given person has six topics, they would have six records in this
table.

You'ld use a Form based on the personnel table, with a Subform based
on TopicAppliesTo; on the subform you'ld have a combo box to select
topics.

John W. Vinson[MVP]
 
Thanks so much for your help. I think i got the database the way you
mentioned. Now here is the detail. Let me know if I can create a form that
would look something like this.

the topic on top of the form with a list of names on the bottom (the service
members names) listed in i.e. alphabetical order and with some check boxes on
them. So when you input a topic you can select what names you want to choose
from and will add that topic to the selected names.

I know I'm getting really technical here but I can almost invision that
being able to happen. Can it? Again thanks for your help.
 
Thanks so much for your help. I think i got the database the way you
mentioned. Now here is the detail. Let me know if I can create a form that
would look something like this.

the topic on top of the form with a list of names on the bottom (the service
members names) listed in i.e. alphabetical order and with some check boxes on
them. So when you input a topic you can select what names you want to choose
from and will add that topic to the selected names.
Click to expand...


If you actually want to SEE the names, one way is to base the Subform
on a Query joining the Personnel table to the junction table. Use a
"Left Outer Join" - select the join line and choose "Show all records
in Personnel and matching records in JunctionTable". You will need to
include the join field from both the junction table and the personnel
table in the query; and you'll need to add a yes/no field to the
junction table. Selecting the checkbox bound to this field on the
subform will "dirty" the record causing a new junction table record to
be created.

Alternatively, you can use a multiselect Listbox showing all the
names; the user can just click the names they want included. You'll
need some VBA code to get the selected records into the table; here's
a sample that you can adapt:


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,
' newly 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 combination is currently
' 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




John W. Vinson[MVP]
 
Back
Top