Check for similar phone types.

[lmcc007]

First of all, I am not sure how to ask this question. But, this is what I am
trying to do: I do not want the same phone type per company. If I choose,
for example, Fax as the phone type twice, I want a message to display saying
to choose another phone type. I entered the code below, but keep getting an
error:

Private Sub PhoneTypeID_BeforeUpdate(Cancel As Integer)

If Me.CompanyID And Me.PhoneTypeID <> Me.PhoneTypeID Then

Me.PhoneTypeID

Else

MsgBox "Choose another phone type.", vbCritical, "Phone Type"

End If

End Sub


The error highlights Me.PhoneTypeID then give this error: Compile Error
Invalid use of property.

Then I tried putting the validation rule on a control in form design view.
I tried this:

=DLookUp("PhoneTypeID","Telephone","PhoneTypeID =
Forms!frmTelephone!PhoneTypeID") Is Null

The problem is it is not requiring it a unique PhoneTypeID per company. It
is doing it for the entire table.


I know I am missing something, but haven’t been able to figure it out.



Thanks!

 

[Allen Browne]

Presumably you have a separate table for companies (with a CompanyID primary
key), and then this table with fields:
CompanyID relates to the key of the Companies table.
PhoneTypeID the field you are talking about.

If so, you can make the combination unique at the table level. The simplest
thing might be to make the 2 fields together the primary key.

1. Open the table in design view.

2. Select the 2 fields (by clicking on the "record selector" to the left of
the first field, and dragging down.)

3. Click the Key icon (on the menu/toolbar.)

Now you won't be able to enter 2 records of the same phone type for the same
company.

 

[Tokyo Alex]

Easiest way to achieve this would be to put a multi-field, unique index on
the CompanyID and PhoneTypeID fields in the table.

To do this, open the table in design view and bring up the 'Indexes' window.
(A2007: Design -> Indexes on the ribbon, earlier versions: don't currently
have any, so not sure)

In the first blank row of the window, enter a name for your index (e.g.
CompIDnPhoneType). In the 'Field Name' column, select CompanyID. In the
next row, 'Field Name' column, select PhoneTypeID; DO NOT enter anything in
the 'Index Name' column. Then click on the Index Name you created and in the
properties section set 'Unique' to 'Yes'. Save changes to the table design.

This index requires a unique *pair* of values, so you will not be able to
enter the same PhoneType twice for the same Company. Either of the fields
separately can still contain duplicates. And because this is managed by the
DB engine at the table level you don't need any code.

Hope this helps, and please post back if you have any further problems.

Cheers,
Alex.

 

[lmcc007]

Okay, I know how to do that in table design. I am trying to form validation.

Thanks.

 

[lmcc007]

I have that already. I am trying to do form validation.

Thanks.

 

[lmcc007]

I am trying to use the code below:

Private Sub PhoneTypeID_BeforeUpdate(Cancel As Integer)
If DCount("[PhoneTypeID]", _
"tblPhones", _
"[CompanyID] = " & Me.COmpanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID) > 0 Then
MsgBox "Choose another phone type.", vbCritical, "Phone Type"
Cancel = True
Exit Sub
End If
End Sub

The code works, but I keep getting this error message:

! No current record

OK HELP

When I hit the HELP button it doesn't tell me what's wrong.

Any suggestion on what's wrong?

Thanks!

 

[Allen Browne]

Something like this (untested aircode):

Private Sub PhoneTypeID_BeforeUpdate(Cancel As Integer)
Dim varResult As Variant
Dim strWhere As String

If IsNull(Me.CompanyID) OR IsNull(Me.PhoneTypeID) OR _
(Me.CompanyID = Me.CompanyID.OldValue AND _
Me.PhoneTypeID = Me.PhoneTypeID.OldValue) Then
'do nothing
Else
strWhere = "(CompanyID = " & Me.CompanyID & _
") AND (PhoneTypeID = """ & Me.PhoneTypeID & """)"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Oops! Same type as in ID " & varResult
End If
End If
End Sub

I've assumed CompanyID is Number, and PhoneTypeID is text. Remove the extra
quotes if both are numbers.