A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create a text box that looks up a value in a table



 
 
Thread Tools Display Modes
  #1  
Old May 23rd, 2010, 12:42 AM posted to microsoft.public.access
vanmen
external usenet poster
 
Posts: 1
Default Create a text box that looks up a value in a table

I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the number
of the class.

I tried =DLookup("[Title]","[ Course Listing]",”[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.
  #2  
Old May 23rd, 2010, 12:52 AM posted to microsoft.public.access
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Create a text box that looks up a value in a table

Assuming the text box is named EnterCourseNum and the field in the table is
named CourseNum, you need:

=DLookup("[Title]","[ Course Listing]","[CourseNum] = " & Me!EnterCourseNum)

Steve


"vanmen" wrote in message
...
I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the
number
of the class.

I tried =DLookup("[Title]","[ Course Listing]","[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.



  #3  
Old May 23rd, 2010, 05:28 AM posted to microsoft.public.access
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Create a text box that looks up a value in a table

On Sat, 22 May 2010 16:42:01 -0700, vanmen
wrote:

DLookup takes two or three strings. Your last one is not terminated.
It should be [Course #]"
Once you do that, it will still not work. That's because the third
string is supposed to be a where-clause, typically:
fieldname = value
For example:
"[Course #] = " & Me.myCourseNoControl

-Tom.
Microsoft Access MVP


I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the number
of the class.

I tried =DLookup("[Title]","[ Course Listing]",[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.

  #4  
Old May 23rd, 2010, 03:59 PM posted to microsoft.public.access
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Create a text box that looks up a value in a table

That's just what I said four and a half hours ago!

Steve


"Tom van Stiphout" wrote in message
...
On Sat, 22 May 2010 16:42:01 -0700, vanmen
wrote:

DLookup takes two or three strings. Your last one is not terminated.
It should be "[Course #]"
Once you do that, it will still not work. That's because the third
string is supposed to be a where-clause, typically:
fieldname = value
For example:
"[Course #] = " & Me.myCourseNoControl

-Tom.
Microsoft Access MVP


I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the
number
of the class.

I tried =DLookup("[Title]","[ Course Listing]","[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.



  #5  
Old May 23rd, 2010, 04:39 PM posted to microsoft.public.access
John... Visio MVP
external usenet poster
 
Posts: 900
Default Create a text box that looks up a value in a table

You are showing your lack of awareness to the details. You did not submit a
post at 5:30am as you indicate, your post was at 7:52 last night.

Of course, details and facts are not your strong point.

John..
"Steve" wrote in message
...
That's just what I said four and a half hours ago!

Steve


"Tom van Stiphout" wrote in message
...
On Sat, 22 May 2010 16:42:01 -0700, vanmen
wrote:

DLookup takes two or three strings. Your last one is not terminated.
It should be "[Course #]"
Once you do that, it will still not work. That's because the third
string is supposed to be a where-clause, typically:
fieldname = value
For example:
"[Course #] = " & Me.myCourseNoControl

-Tom.
Microsoft Access MVP


I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the
number
of the class.

I tried =DLookup("[Title]","[ Course Listing]","[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.





  #6  
Old May 23rd, 2010, 04:57 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Create a text box that looks up a value in a table

The other factor to be taken into account is the data type of the Course #
column in the Course Listing table, If it's a number data type do as the
others have stated, e.g.

=DLookup("[Title]","[ Course Listing]",”[Course #] = " & Me.[txtCourse #])

If it's a text data type however (the college where my wife teaches use
alphanumeric course codes) the value needs to be wrapped in quotes character
like so:

=DLookup("[Title]","[ Course Listing]",”[Course #] = """ & Me.[txtCourse #] &
"""")

A contiguous pair of quotes characters within a literal string is interpreted
as a literal quotes character.

On the other hand there might be a better way of doing this which avoids the
user having to remember the course numbers at all. By using a combo box
rather than a text box the user can select from a drop down list. So a
[cboCourse #] combo box would be set up like this:

Name: cboCourse #

RowSource: SELECT [Course #], [Title] FROM [Course Listing] ORDER BY
[Course #];

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 1cm;7cm

ListWidth: 8cm

AutoExpand: True (Yes)

If your units of measurement are imperial rather than metric Access will
automatically convert them. Experiment with the ColumnWidths dimensions to
get the best fit. The ListWidth is the sum of the column widths.

A user can then select a course by scrolling down the list or can type in the
number and automatically go to the first match.

When a course is selected the number will show in the control. To show the
course title in a separate text box set its ControlSource property to:

=[cboCourse #].Column(1)

The Column property is zero-based, so Column(1) is the second column, Title.

Taking this a step further, does the user really need to see the course
number at all, or can they simply select from a list of titles? In this case
you'd again use a combo box, but set up as follows:

Name: cboCourse #

RowSource: SELECT [Course #], [Title] FROM [Course Listing] ORDER BY [Title]
;

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0cm;8cm

ListWidth: Auto

AutoExpand: True (Yes)

The first column is now hidden by having a width of zero, so the user sees
only the titles listed alphabetically. When a course is chosen however, the
underlying value of the control is the hidden Course #. Again the user can
scroll down to select a course, or can type in the course title, in which
case the control will progressively go to the first match as each character
is entered. In this scenario you can if you wish also include a text box on
the form with a ControlSource property of:

=[cboCourse #]

to show the course number for the selected title.

Ken Sheridan
Stafford, England

vanmen wrote:
I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the number
of the class.

I tried =DLookup("[Title]","[ Course Listing]",”[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

  #7  
Old May 26th, 2010, 09:30 AM posted to microsoft.public.access
john
external usenet poster
 
Posts: 2
Default Create a text box that looks up a value in a table

hi! nice to meet you.
"Steve" .gbl...
That's just what I said four and a half hours ago!

Steve


"Tom van Stiphout" wrote in message
...
On Sat, 22 May 2010 16:42:01 -0700, vanmen
wrote:

DLookup takes two or three strings. Your last one is not terminated.
It should be "[Course #]"
Once you do that, it will still not work. That's because the third
string is supposed to be a where-clause, typically:
fieldname = value
For example:
"[Course #] = " & Me.myCourseNoControl

-Tom.
Microsoft Access MVP


I've been trying to get dlookup to work without success.

I'm trying to get a title of a class to be inserted depending on the
number
of the class.

I tried =DLookup("[Title]","[ Course Listing]","[Course #])

and I get "invalid syntax" message. Can anyone help? Thanks.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:19 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.