DLookup from a query criteria trouble

[DH22]

[I'm new here]


Access 03

I'm currently having an issue using Dlookup when trying to reference a query (criteria syntax)

Currently I have 1 table, which is L_Emps (which contains Employee_ID as my key (numeric), First_Name, and Last_Name (as text).

My query is Query1 which is the following:

SELECT L_Emps.Employee_ID, L_Emps.First_Name, L_Emps.Last_Name
FROM L_Emps
WHERE (((L_Emps.Employee_ID)=[ID:]));

I have one form that is named L_EmpsForm. It is basically three bound fields that references Query1.

What I'm trying to do is have an onclick button return the ID that was returned from the query.

Private Sub Command6_Click()
 
ID = DLookup("[Employee_ID]", "[Query1]", "[Employee_ID]=" & [Forms]![L_EmpsForm]![Employee_ID])
 
MsgBox ID
 
End Sub 

This is just a test database to try to teach myself how to use DLookup, but it isn't going so well. I can get DLookup to work fine if I use a static query, but whenever I prompt for the input, I usually get the "You canceled the previous operation" error. I've read up that some of the field references could be off, but I'm unsure as to what exactly.

I know it is a tad redudant to have the Onclick button return the value that is obviously right in front of me when I run the form, but I actually plan on passing the table information into different directions once I actually figure out Dlookup.



Advice?


-DH22

[Stewart Ross]

Hi again. No harm in experimenting - it all adds to your Access experience!

If you could post a small sample of your data from the table holding the directory and suffix fields - real if possible, disguised a bit if the data is sensitive - this will help us to understand how your data is really structured (it's difficult to get a handle on it at this remove).

There are plenty of ways to pick files from Windows API file dialogues that may well be the way to go to resolve your issues, but until we see your data it is really difficult to advise you further.

Thanks

-Stewart

[DH22]

Hi again. No harm in experimenting - it all adds to your Access experience!

If you could post a small sample of your data from the table holding the directory and suffix fields - real if possible, disguised a bit if the data is sensitive - this will help us to understand how your data is really structured (it's difficult to get a handle on it at this remove).

There are plenty of ways to pick files from Windows API file dialogues that may well be the way to go to resolve your issues, but until we see your data it is really difficult to advise you further.

Thanks

-Stewart

Alrighty, here's a very small example, only three records but that's basically it.

[Stewart Ross]

No sign of the three records so far... -S

[DH22]

No sign of the three records so far... -S

Yeah, sorry, Stewart. As I was adding the zip as an attachment, I got called away to a very long meeting. Ooi.


I'll be adding the zip to this post.

This is just mock information for security reasons, but the database reflects the real one closely.

[DH22]

Hopefully the attached dbase is what you were asking for. :)

[DH22]

What about a filter?

I really don't know much about it, but could that help out? It would make the combo box less busy, too.

[Stewart Ross]

I will review the DB, DH22, as time allows; I'll post back when I have done.

-Stewart

[DH22]

Take your time, I'm stumbling through with ideas, myself. Nothing ventured, nothing gained!



-DH22

[DH22]

Perhaps using the Query as a Make Table query and then running the form on that result? That would narrow it down.

[Stewart Ross]

Hi DH22. Sorry for the delay in responding; I can only look at the sample DB in my own time, and now that I've done so I find I have no new ideas to offer I'm afraid. I think the simplest solution is to use a series of cascading combo boxes to filter your file lists - selecting by account, folder and so on. Generating another intermediate table is unlikely to help.

The Windows file dialogues don't really help either, because unless these could be filtered in some way users will still be presented with lists of files in the normal tree-like view - and with the number of files involved in your application this would be an overwhelming list for individuals to scroll.

Cascading combos seems like the only viable answer to me. We have a HowTo article on Cascading Combo/List Boxes linked here which provides further information.

-Stewart