dlookup in subform help

[Mesa]

First i would like to say thank you to this community group for
helping me through all my learnings of access. Also please forgive
any lateness in my response but i am not easily available to the
internet here.

Ok, i created a database in which i put a person in the main form and
on a subform i can select in a listbox a group and then click a button
to put them in that group. The subform uses a one to many table that
is linked to the main form. That field is an autonumber and named
"Record". On the subform table, there are two fields, "Network", and
"Record". What I am trying to do is limit a network in a record only
once. Each record can be in multiple networks but I dont want a
person to show that he is on the same network twice. In other words,
I want to restrict a network from being entered twice when i click on
the "Add Button". Here is my code so far

Private Sub Add_Button_Click()
On Error GoTo Err_Add_Button_Click

Me!Network.SetFocus
DoCmd.GoToRecord , , acNewRec
Me!Network.Value = Me![List Total].Value

If (Not IsNull(DLookup("[Network]", "(Networks)", "[Record] =" &
Forms![Main]!Record))) Then
MsgBox "This person is already in this network."
Me.Form.Undo
Else
If Len(Me![Network].Text & "") = 0 Then
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.Form.Refresh
Else
Me.Form.Refresh
End If
End If

Exit_Add_Button_Click:
Exit Sub

Err_Add_Button_Click:
MsgBox Err.Description
Resume Exit_Add_Button_Click

End Sub

the problem I am having is that it will let me enter only one network
in the subform. I want to have the dlookup check the "Network" table
on the "Network" field per record linked by the main form if the
network I selected is already linked to the person. If the dlookup
finds that there is a match in the "Network" field with the same
"Record" field value, then the msgbox will apear and undo will
continue.

 

[Mesa]

ok, after long hours on this i modified my dlookup and if anyone wants
to know how to get this to work, here is the new code below


Option Compare Database

Private Sub Add_Button_Click()
On Error GoTo Err_Add_Button_Click

Me!Network.SetFocus
DoCmd.GoToRecord , , acNewRec
Me!Network.Value = Me![List Total].Value

If (Not IsNull(DLookup("[Network]", "(Networks)", "[Network] ='" &
Me!Network & "'" & "AND [Record] =" & Forms![Main]!Record))) Then
MsgBox "This person is already in this network."
Me.Form.Undo
Else
If Len(Me![Network].Text & "") = 0 Then
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.Form.Refresh
Else
Me.Form.Refresh
End If
End If

Exit_Add_Button_Click:
Exit Sub

Err_Add_Button_Click:
MsgBox Err.Description
Resume Exit_Add_Button_Click

End Sub

 

[BruceM]

If I understand correctly that each person may be a member of many networks,
and each network may contain many people, then there is a many-to-many
relationship between persons (tblPeople) and networks (tblNetwork). That
would mean a junction table (tblPeopleNetworks) is needed to resolve the
relationship. The junction table has as foreign keys that correspond to the
primary keys of the other tables.

tblPeople
PeopleID (primary key, or PK)
FirstName, etc.

tblNetwork
NetworkID (PK)
NetworkDescription, etc.

tblPeopleNetwork
PeopleNetworkID (PK)
PeopleID_FK (foreign key, or FK)
NetworkID_FK (FK)

A typical setup is to have the main form based on tblPeople, and the subform
based on tblPeopleNetwork. A combo box bound to NetworkID_FK gets its row
source from tblNetwork. Its bound column is NetworkID. Its visible column
is NetworkDescription or something more intuitive than the ID number. The
combo box After Update event could use DCount to check for duplicate values
in the PeopleID_FK field, maybe something like:

If DCount("PeopleID_FK","tblPeopleNetwork","NetworkID_FK = " &
Me.NetworkID_FK) > 1 Then
MsgBox "Already in that network"
Me.Undo
End If