dlookup error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

dlookup error

Author

Topic

enrico
Starting Member

5 Posts

Posted - 2009-03-26 : 22:11:50

i created a form(frmInterviewer) with:

1 combobox - row source(SELECT Lastname, InterviewerID FROM tblInterviewer)
1 textbox(txtID) - bound column(1) = returns the InterviewerID of the name selected on the combobox
3 textboxes, namely:
txtLastname = control source (=DLookup("Query1","Lastname")
txtFirstname = control source (=DLookup("Query1","Firstname")
txtMI= control source (=DLookup("Query1","MI")
1 button - me.refresh

i created a query(Query1):

SELECT Lastname, Firstname, MI
FROM tblInterviewer
WHERE (((tblInterviewer.Lastname)=[Forms].[frmInterviewer].[txtID]));

the problem is every time i open my form the three textboxes already has an "error" word and it doesn't return anything. doesn't access recognize dlookup if the source is from a bound textbox? what's the problem to my form?

wakkowarner
Starting Member

4 Posts

Posted - 2009-04-01 : 11:23:07

Not sure what exactly the source of you problem is, but I do see several issues.

First, your DLookup syntax is incorrect, here is the syntax:
DLookup ( expression, domain, [criteria] )

So, for your uses, you might have
=DLookup("Lastname", "Query1")

Since there is no criteria, your Query1 needs to return only 1 value. Looking at your Query1, it's criteria doesn't make much sense. How can Lastname match on InterviewerID (which is what you stated was the bound column in txtID)? Does your query return a single row when you open it? Since the form is separate from the query I think you need to use bangs (!) instead of dots (.). It makes more sense to change your Query1 to this:

SELECT Lastname, Firstname, MI
FROM tblInterviewer
WHERE (((tblInterviewer.InterviewerID)=[Forms]![frmInterviewer]![txtID]));

Though I'm not sure that you really need that txtID. If the InterviewerID is something the user needs to see, then it is fine to show it. If you simply need it as a filter for the query, just use the combobox (make sure that InterviewerID is the bound column). If you do this, just substitute the combobox in the SQL I provided above.

Lastly, I'm not sure if Me.Refresh will do what you need it to do. You may need Me.Requery.

All of this is moot though depending on what you are trying to accomplish. With what it seems like you are doing it seems far easier to use a bound form. Is there a reason you can't tie frmInterviewer to tblInterviewer? You can lock the fields if you don't want the user to be able to edit the entries. Honestly, there are a few dozen different ways to do what it looks like you are trying to do. Only you know your specific situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources