DLookup in TabControl in Form

[LSemos]

I am trying to lookup phone numbers in a separate table based on the
name in the current Tab control

tblContacts has the fields: Name, Phone, Fax, and Email

In my Form's Tab control, I have the fields: DVP, Phone, Fax and
Email.
In the On Click properties of the Phone field, I have the following:

=DLookUp("[Phone]","[tblContacts]","[Name]='" & [DVP] & "'")

I am getting no results.

Any help would be greatly appreciated.
Thanks for your time.

Luane

[Darryl Kerkeslager]

First, some bad news: 'Name' is a reserved Word in Access, and using it as
a field in a table is just asking for all sorts of wierd problems that will
cause you hours of frustration tracking down bugs because damn it the code
is right and why doesn't this work and why is my mdb crashing and ... etc.

So rename the field to something that is not in this list:

http://support.microsoft.com/kb/q286335/

Second, what is DVP? You say it is a field on your Tab Control, but you
don't mention it as a field of table tblContacts. Is it a field of that
table (I think not)? If not, then what is it? Cause if I don't know, does
your DCount statement know? Is it a textbox of the current form? If so,
then refer to it as Me.DVP.
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:

I am trying to lookup phone numbers in a separate table based on the
name in the current Tab control

tblContacts has the fields: Name, Phone, Fax, and Email

In my Form's Tab control, I have the fields: DVP, Phone, Fax and
Email.
In the On Click properties of the Phone field, I have the following:

=DLookUp("[Phone]","[tblContacts]","[Name]='" & [DVP] & "'")

I am getting no results.

Any help would be greatly appreciated.
Thanks for your time.

Luane

[LSemos]

Thanks for the info on reserved words.
Have changed NAME to CONTACTNAME

DVP is an acronym for a position. It's value is the name of the DVP.
That name (i.e., Bob Smith) in the table is part of one record along
with ContactPhone, ContactFax and ContactEmail.

I agree that is probably where my problem lies; however, when I use
me.DVP I get the message
The object does not contain the Automation object 'me.' I also tried
[form name]![DVP]. Received the same result.

Occurred to me - does the field name in the table have to be the same
as the field name in the tab control. The Table field I want is
ContactPhone for a certain ContactName. In the form it is DVPPhone
for the person in field DVP.

Can we try this again? Any help?
Thanks
Luane

"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:<uL******* *************@c omcast.com>...

First, some bad news: 'Name' is a reserved Word in Access, and using it as
a field in a table is just asking for all sorts of wierd problems that will
cause you hours of frustration tracking down bugs because damn it the code
is right and why doesn't this work and why is my mdb crashing and ... etc.

So rename the field to something that is not in this list:

http://support.microsoft.com/kb/q286335/

Second, what is DVP? You say it is a field on your Tab Control, but you
don't mention it as a field of table tblContacts. Is it a field of that
table (I think not)? If not, then what is it? Cause if I don't know, does
your DCount statement know? Is it a textbox of the current form? If so,
then refer to it as Me.DVP.
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:

I am trying to lookup phone numbers in a separate table based on the
name in the current Tab control

tblContacts has the fields: Name, Phone, Fax, and Email

In my Form's Tab control, I have the fields: DVP, Phone, Fax and
Email.
In the On Click properties of the Phone field, I have the following:

=DLookUp("[Phone]","[tblContacts]","[Name]='" & [DVP] & "'")

I am getting no results.

Any help would be greatly appreciated.
Thanks for your time.

Luane

[Darryl Kerkeslager]

When you say "field name in the tab control", you are actually referring to
a textBox, I presume. It will make things more clear if you and I both use
the proper names of the controls.

A textBox does not need to have the same name as the field in a table; in
fact, I would advise you to avoid that, becuase of the confusion. There are
'naming conventions' for all objects of any type in Access and VBA, and if
you Google 'naming conventions' I'm sure you'll find the details, but if you
at least stick with these basic ones, life will be a whole lot easier (and
of course, always avoid reserved words):

Controls, like textBoxes, comboBoxes, listBoxes, etc, should be prefixed
with a short (usually three-letter) code. Be as descriptive as needed:

Control - prefix - Example
-------------------------------
textBox - txt - txtEmployerName
combBox - cbo - cboEmployers
listBox - lst - lstEmployers

My preference for table names is to use the Oracle convention, which
basically says to write all table and field names in lower case, all SQL
words (SELECT, INSERT, AS, LIKE, ..) in uppercase, and all variables (which
would include conrtols)(Const ants are all in CAPS) in mixed case like the
above controls. This makes reading SQL statements much easier. I name
tables with one descriptive, singular word:

employer
person
agency
address

Some people append 'tbl' to tables, which of course also works, like
tblContacts.

I then name the primary key as the table name plus the suffix _id:

employer_id

All other fields I prefix a short version of the table name with an
underscore

emp_name
emp_street
emp_city

Foreign keys are like so:

emp_per_id (a field in table employee that is the primary key from table
person)

Your naming scheme may vary, but *I would at least have one*, so that you
can be consistent. The bigger a project grows, the more you will appreciate
it. A good naming policy can also help you avoid overly long SQL, overly
confusing SQL, bad VBA, crashes, hard to track bugs, and premature baldness.

Okay, so now with DVP. You indicate that it is a field in the table, but
NOT a ontrol on the form. The keyword Me refers to the current form, so if
DVP was a textBox name, Me.DVP would be legit. But it's not.

I must admit that I am still somewhat confused by your suse of 'field'. In
a bound control, like a textBox, the data source, in other words, the field
of the table, is referred to as the controlsource (that's the property
name). So, if DVPPhone is a textBox on your form, and your form is in fact
a bound form (the recordsource is bound to a table), and bound to table
tblContacts, the the control source of Me.DVPPhone should be the
corresponding field in the table, perhaps tblContacts.DVP . So, if DVP is a
field in table tblContacts, my original advice to say Me.DVP would be wrong;
just use DVP as you did originally, and I assume that the bad field name,
'Name', was the cause of the original error..

Of course, you still have not explicitly said that DVP is a field of table
tblContacts, so that inference may be wrong.
Darryl Kerkeslager

"LSemos" <ls******@aol.c om> wrote:

Thanks for the info on reserved words.
Have changed NAME to CONTACTNAME

DVP is an acronym for a position. It's value is the name of the DVP.
That name (i.e., Bob Smith) in the table is part of one record along
with ContactPhone, ContactFax and ContactEmail.

I agree that is probably where my problem lies; however, when I use
me.DVP I get the message
The object does not contain the Automation object 'me.' I also tried
[form name]![DVP]. Received the same result.

Occurred to me - does the field name in the table have to be the same
as the field name in the tab control. The Table field I want is
ContactPhone for a certain ContactName. In the form it is DVPPhone
for the person in field DVP.

"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote:

First, some bad news: 'Name' is a reserved Word in Access, and using it as a field in a table is just asking for all sorts of wierd problems that will cause you hours of frustration tracking down bugs because damn it the code is right and why doesn't this work and why is my mdb crashing and ... etc.
So rename the field to something that is not in this list:

http://support.microsoft.com/kb/q286335/

Second, what is DVP? You say it is a field on your Tab Control, but you
don't mention it as a field of table tblContacts. Is it a field of that
table (I think not)? If not, then what is it? Cause if I don't know, does your DCount statement know? Is it a textbox of the current form? If so, then refer to it as Me.DVP.
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:

I am trying to lookup phone numbers in a separate table based on the
name in the current Tab control

tblContacts has the fields: Name, Phone, Fax, and Email

In my Form's Tab control, I have the fields: DVP, Phone, Fax and
Email.
In the On Click properties of the Phone field, I have the following:

=DLookUp("[Phone]","[tblContacts]","[Name]='" & [DVP] & "'")

I am getting no results.

Any help would be greatly appreciated.
Thanks for your time.

Luane

[LSemos]

First, thanks again for all the extra info. I thought I had a naming
convention, but you taught me how little I know. I appreciate your
putting me in the direction of learning more of the basics (i.e., the
language), because I do want a "good" database about which I can
communicate intelligently.

Now, let's see if I can describe this situation correctly:

In the tab control, txtDVP and txtDVPPhone are bound to the
controlsource tblBasicInfo.
In the txtDVPPhone OnClick properties, I want to get [ContactPhone]
from [tblContacts] where txtDVP is the same as
[tblContacts]![ContactName]

=DLookUp([ContactPhone],[tblContacts],[tblBasicInfo]![DVP]=[tblContacts]![ContactName])

Is being bound to a different table the problem?
Hope this is more clear. More of your time, please???
Thanks
Luane

"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:<Ir******* *************@c omcast.com>...

When you say "field name in the tab control", you are actually referring to
a textBox, I presume. It will make things more clear if you and I both use
the proper names of the controls.

A textBox does not need to have the same name as the field in a table; in
fact, I would advise you to avoid that, becuase of the confusion. There are
'naming conventions' for all objects of any type in Access and VBA, and if
you Google 'naming conventions' I'm sure you'll find the details, but if you
at least stick with these basic ones, life will be a whole lot easier (and
of course, always avoid reserved words):

Controls, like textBoxes, comboBoxes, listBoxes, etc, should be prefixed
with a short (usually three-letter) code. Be as descriptive as needed:

Control - prefix - Example
-------------------------------
textBox - txt - txtEmployerName
combBox - cbo - cboEmployers
listBox - lst - lstEmployers

My preference for table names is to use the Oracle convention, which
basically says to write all table and field names in lower case, all SQL
words (SELECT, INSERT, AS, LIKE, ..) in uppercase, and all variables (which
would include conrtols)(Const ants are all in CAPS) in mixed case like the
above controls. This makes reading SQL statements much easier. I name
tables with one descriptive, singular word:

employer
person
agency
address

Some people append 'tbl' to tables, which of course also works, like
tblContacts.

I then name the primary key as the table name plus the suffix _id:

employer_id

All other fields I prefix a short version of the table name with an
underscore

emp_name
emp_street
emp_city

Foreign keys are like so:

emp_per_id (a field in table employee that is the primary key from table
person)

Your naming scheme may vary, but *I would at least have one*, so that you
can be consistent. The bigger a project grows, the more you will appreciate
it. A good naming policy can also help you avoid overly long SQL, overly
confusing SQL, bad VBA, crashes, hard to track bugs, and premature baldness.

Okay, so now with DVP. You indicate that it is a field in the table, but
NOT a ontrol on the form. The keyword Me refers to the current form, so if
DVP was a textBox name, Me.DVP would be legit. But it's not.

I must admit that I am still somewhat confused by your suse of 'field'. In
a bound control, like a textBox, the data source, in other words, the field
of the table, is referred to as the controlsource (that's the property
name). So, if DVPPhone is a textBox on your form, and your form is in fact
a bound form (the recordsource is bound to a table), and bound to table
tblContacts, the the control source of Me.DVPPhone should be the
corresponding field in the table, perhaps tblContacts.DVP . So, if DVP is a
field in table tblContacts, my original advice to say Me.DVP would be wrong;
just use DVP as you did originally, and I assume that the bad field name,
'Name', was the cause of the original error..

Of course, you still have not explicitly said that DVP is a field of table
tblContacts, so that inference may be wrong.
Darryl Kerkeslager

"LSemos" <ls******@aol.c om> wrote:

Thanks for the info on reserved words.
Have changed NAME to CONTACTNAME

DVP is an acronym for a position. It's value is the name of the DVP.
That name (i.e., Bob Smith) in the table is part of one record along
with ContactPhone, ContactFax and ContactEmail.

I agree that is probably where my problem lies; however, when I use
me.DVP I get the message
The object does not contain the Automation object 'me.' I also tried
[form name]![DVP]. Received the same result.

Occurred to me - does the field name in the table have to be the same
as the field name in the tab control. The Table field I want is
ContactPhone for a certain ContactName. In the form it is DVPPhone
for the person in field DVP.

"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote:

First, some bad news: 'Name' is a reserved Word in Access, and using it as a field in a table is just asking for all sorts of wierd problems that will cause you hours of frustration tracking down bugs because damn it the code is right and why doesn't this work and why is my mdb crashing and ... etc.
So rename the field to something that is not in this list:

http://support.microsoft.com/kb/q286335/

Second, what is DVP? You say it is a field on your Tab Control, but you
don't mention it as a field of table tblContacts. Is it a field of that
table (I think not)? If not, then what is it? Cause if I don't know, does your DCount statement know? Is it a textbox of the current form? If so, then refer to it as Me.DVP.
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:
> I am trying to lookup phone numbers in a separate table based on the
> name in the current Tab control
>
> tblContacts has the fields: Name, Phone, Fax, and Email
>
> In my Form's Tab control, I have the fields: DVP, Phone, Fax and
> Email.
> In the On Click properties of the Phone field, I have the following:
>
> =DLookUp("[Phone]","[tblContacts]","[Name]='" & [DVP] & "'")
>
> I am getting no results.
>
> Any help would be greatly appreciated.
> Thanks for your time.
>
> Luane

[Darryl Kerkeslager]

What you have here should work, except you need quotes around the
parameters, to indicate that you are using Strings (not variable names). I
have reversed the order of the final parameter, but I don't think that
should matter
=DLookUp("[ContactPhone]","[tblContacts]","[tblContacts]![ContactName]=[tblB
asicInfo]![DVP]")
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:

In the tab control, txtDVP and txtDVPPhone are bound to the
controlsource tblBasicInfo.
In the txtDVPPhone OnClick properties, I want to get [ContactPhone]
from [tblContacts] where txtDVP is the same as
[tblContacts]![ContactName]

=DLookUp([ContactPhone],[tblContacts],[tblBasicInfo]![DVP]=[tblContacts]![Co
ntactName])

[lsemos58]

Well, Darryl, thanks for all of your help. I give up. I just added a
subform to my tabcontrol. It isn't pretty, but at least I can move on.

Thanks again.
Luane

Darryl Kerkeslager wrote:

What you have here should work, except you need quotes around the
parameters, to indicate that you are using Strings (not variable names). I have reversed the order of the final parameter, but I don't think that should matter
=DLookUp("[ContactPhone]","[tblContacts]","[tblContacts]![ContactName]=[tblB asicInfo]![DVP]")
Darryl Kerkeslager
"LSemos" <ls******@aol.c om> wrote:

In the tab control, txtDVP and txtDVPPhone are bound to the
controlsource tblBasicInfo.
In the txtDVPPhone OnClick properties, I want to get [ContactPhone]
from [tblContacts] where txtDVP is the same as
[tblContacts]![ContactName]

=DLookUp([ContactPhone],[tblContacts],[tblBasicInfo]![DVP]=[tblContacts]![Co ntactName])