lookup button

[Elaine]

I have a field that is linked via a list box to a table, I have a button
that should open another form when the appropriate name has been selected,
there is something missing out of my code, as I do a value list and it works
but as soon as i do a lookup to a table it doesn look at it, totally
baffled...

Private Sub List26sagain_Click()
On Error GoTo Err_open_Click

Dim stDocName As String
Dim stLinkCriteria As String
'This code is for the List26 and the forms for April 2008 to March 2009
'1 - Doctors

If (List26) = "AHMAD A DR (GENERAL MEDICINE/ DIABETES)" Then

stDocName = "Dr Ahmad A (General Medicine/Diabetes)"
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf (List26) = "ALAWATTEGAMA A DR [GENITOURINARY MEDICINE]" Then

stDocName = "Dr Alawattegama A (Genitourinary Medicine)"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Please help

Elaine

 

[Ken Sheridan]

Elaine:

The value of the list box is almost certainly a hidden numeric ID column
which references the primary key column of the physicians 'lookup' table, not
the name you see. My guess is that the table was set up using the dreaded
'lookup wizard' as the column's data type. See the following for the reasons
why this should be avoided:


http://www.mvps.org/access/lookupfields.htm


Normally when doing this sort of thing you'd open the same form filtered to
the physician in question, rather than having separate forms per physician.
You can then filter the form on the hidden ID column not the name, so you
never need to know the value of the hidden ID column. Is there any reason
why you can't use the same form for all physicians and filter it in this way
rather than having one form per physician, and filtering it to the selected
physician (and to a particular year to judge by the comment line in your
code)?

Ken Sheridan
Stafford, England