473,721 Members | 2,256 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DLookup in TabControl in Form

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
Nov 13 '05 #1
6 3468
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

Nov 13 '05 #2
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

Nov 13 '05 #3
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

Nov 13 '05 #4
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

Nov 13 '05 #5
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])
Nov 13 '05 #6
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])


Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2332
by: JLM | last post by:
What am I missing here? I have a form where I enter a "Class Code". This value corresponds to what sits in table "class code descriptions" along with the "title" of each "class code." Key field is "class code." I want a text box to be populated with the "title" which corresponds to the "class code" which I enter in the form. The control source for the text box is:
6
508
by: Don Sealer | last post by:
I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as I've said. It doesn't populate until I go to a new record. Then if I go back to the record the number is in the field. Instead of populating immediately it waits until I've created a new record. Am I making sense with this description. Here's...
2
2345
by: Francois Vanderseypen | last post by:
Can someone tell me why you cannot drag something onto a tabcontrol with zero tabpages? I have a kind of 'docking' interface and relies heavily on GDI+, a screenshot is available here: http://netron.sourceforge.net/Biotron3Shot.jpg You can move tab pages in the different panes, unless it's totally empty (TabCount=0). Is there an alternative? Thanks a lot,
8
15668
by: Aaron Ackerman | last post by:
If I am expanding (maximizing a form how do I maximize the tab control along with it?
9
3456
by: Michael Turner | last post by:
Hi Guys Having problem with the tab control, I need to set the background color to something different than the standard, I have found code on the web and now can redraw the tabpage buttons so the are set to the right color, I can obviously change the tabpage colour but I am left with a grey top edge(Where there are no buttons) when the background of the form is changed any ideas on what I can do? Mike.
2
3352
by: Simon Verona | last post by:
I have a few hundred forms in my application. All are based on a custom base form class. I decided that I wanted to globally change the look and feel of many of the controls in my application - including the tab control. I did this by writing a component that I attached to the form. The component has a "Start" function which takes the form as a paramater. This cycles through all the controls on the form, changes some of the properties...
5
4817
by: hrreece | last post by:
I have a form, Master List Temp, in Access 2003 that uses the Dlookup command to pull values from another table, Address Master. The user types in a person's home phone number in the HomePhone field on the form and the command looks up information in the Address Master table pertaining to the First Name, Last Name, etc. that match the HomePhone number criteria This allows the user to see if information for the account already exists. The...
0
1418
by: bertie78 | last post by:
Hi all, New poster here! I'm having some difficulty updating a tabcontrol cross-threads. The general idea is : the main thread is the owner of the tab control and the form controls. However, due to the vast amount of data to populate listviews within the tabs, I have created a worker thread (an implementation of a BackgroundWorker) to form the data and populate a tabcontrol 'offline', so that it doesn't interfer with (i.e freeze) the UI. ...
2
2115
by: Jamey | last post by:
Ran into an inconvenience with TabControls yesterday. I found a partial explanation from Allen Browne on the MS message boards explaining when it happens, but not why. I thought Stephen Lebans (who's done a little work with this for his multi-colored listbox class), or someone else might've figured this out. Here's the scenario: DBs created on Windows XP using A2K7 are apparently incapable of displaying TabControl transparency....
0
8858
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9376
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9230
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9148
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5994
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4499
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.