A
Amit
Hi,
I'll appreciate any help with this. I have one piece of
the puzzle for a list in place, but am stuck here.
I have 3 tables:
- tblPerson (ID [PK], PersonName)
- tblColor (ColorID [PK], ColorName)
- tblPersonColor (PersonID, ColorID) [both together are PK]
tblPerson.ID = tblPersonColor.PersonID
I'm trying to have 2 forms : one to display the selected
colors for a person, and the second form to edit the
colors.
I'm all set with the first form (thanks Gary, Graham). I'm
having trouble with the second form.
I have PersonName, ID, unbound list (List2) and a button
on frmPerson. The source for the list is a query that gets
ColorID and ColorName from tblColor and sorts it by
ColorName. ColorID is not visible in the list.
In the list, I'd like to show all the possible colors, and
highlighting the colors that are selected for that person.
I have the following code in the Activate event of the
frmPerson:
========
Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer
strSQL = "Select * from tblPersonColor "
'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
For i = 0 To List2.ListCount - 1
Criteria = "[PersonID] = '" & Forms.frmPerson.ID & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
If rs![ColorID] = List2.Column(0, i) Then
List2.Selected(i) = True
Else
List2.Selected(i) = False
End If
End If
End If
Next i
========
Logic is to select '*' from tblPersonColor, then select
the colors where PersonID matches the ID on the form, and
then go through the list and highlight the colors where
the condition is met (PersonID = ID).
I'm getting a data-type mismatch error in the Criteria
line, and I'm unable to figure out why (because of
quotes?). I'll appreciate any help with this code...
TIA.
-Amit
I'll appreciate any help with this. I have one piece of
the puzzle for a list in place, but am stuck here.
I have 3 tables:
- tblPerson (ID [PK], PersonName)
- tblColor (ColorID [PK], ColorName)
- tblPersonColor (PersonID, ColorID) [both together are PK]
tblPerson.ID = tblPersonColor.PersonID
I'm trying to have 2 forms : one to display the selected
colors for a person, and the second form to edit the
colors.
I'm all set with the first form (thanks Gary, Graham). I'm
having trouble with the second form.
I have PersonName, ID, unbound list (List2) and a button
on frmPerson. The source for the list is a query that gets
ColorID and ColorName from tblColor and sorts it by
ColorName. ColorID is not visible in the list.
In the list, I'd like to show all the possible colors, and
highlighting the colors that are selected for that person.
I have the following code in the Activate event of the
frmPerson:
========
Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer
strSQL = "Select * from tblPersonColor "
'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
For i = 0 To List2.ListCount - 1
Criteria = "[PersonID] = '" & Forms.frmPerson.ID & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
If rs![ColorID] = List2.Column(0, i) Then
List2.Selected(i) = True
Else
List2.Selected(i) = False
End If
End If
End If
Next i
========
Logic is to select '*' from tblPersonColor, then select
the colors where PersonID matches the ID on the form, and
then go through the list and highlight the colors where
the condition is met (PersonID = ID).
I'm getting a data-type mismatch error in the Criteria
line, and I'm unable to figure out why (because of
quotes?). I'll appreciate any help with this code...
TIA.
-Amit