Create a text box that looks up a value in a table
in [General]
|
Prev: report error message
Next: Mensaje
From: KenSheridan via AccessMonster.com on 23 May 2010 11:57 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/Forums.aspx/access/201005/1
From: john on 26 May 2010 04:30
hi! nice to meet you. "Steve" <notmyemail(a)address.com> ���g��l��s�D:OzHVSio%23KHA.5044(a)TK2MSFTNGP04.phx.gbl... > That's just what I said four and a half hours ago! > > Steve > > > "Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message > news:mjbhv5dv873p2irnvls7sgog72tkgptnd5(a)4ax.com... >> On Sat, 22 May 2010 16:42:01 -0700, vanmen >> <vanmen(a)discussions.microsoft.com> 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. > > |