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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"lmcc007" <(E-Mail Removed)> wrote in message
news:14AF29F3-F362-46D5-A8E8-(E-Mail Removed)...
> 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!

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" wrote:

> 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!

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

Thanks.


"Allen Browne" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "lmcc007" <(E-Mail Removed)> wrote in message
> news:14AF29F3-F362-46D5-A8E8-(E-Mail Removed)...
> > 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!
>
> .
>

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

Thanks.

"Tokyo Alex" wrote:

> 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" wrote:
>
> > 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!

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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"lmcc007" <(E-Mail Removed)> wrote in message
news:C2D47A05-D4B6-49AD-88A9-(E-Mail Removed)...
> Okay, I know how to do that in table design. I am trying to form
> validation.
>
> Thanks.
>
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "lmcc007" <(E-Mail Removed)> wrote in message
>> news:14AF29F3-F362-46D5-A8E8-(E-Mail Removed)...
>> > 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!
>>
>> .
>>