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]

Well, I would not attempt to use Access for file deletion when the Windows environment provides the normal Explorer facilities to do so.

I attach a very simple demo (in Access 2003) based on what you have mentioned in the above posts but without active delete code. It is just a single-form database showing how you would feed a combo from a source query. In design view of the form you should have a look at the combo's After Update event code to see where the delete code would go.

However, if it was me I would not do this in Access anyhow.

-Stewart

[DH22]

I'd prefer not to do it with Access, either.

However, my problem is thus: My directory is huge, over thirty databases, one million+ files, and 500gb+ of information.

I need to be able to look up an image file and delete it. I will not know the directory or subdirectory in which the file will reside, though there are over fifty directories for each database with over thirty subdirectories for each (and climbing).

I'll only know the account number and suffix and have to find the image based on those two criteria.

Searching through such a huge directory takes windows forever. Access returns the value I am looking for quickly.

My options are limited: I cannot download anything and only have Access and Windows at my disposal.

I'm open to other suggestions, though.


[Edit]

Thank you for the sample database, Stewart. I really do appreciate your help. I did manage to get that far in with the query and form. As mentioned though, I have to hunt down which file I need by Account and Suffix, so having a gigantic list in the form wouldn't be feasiable. I have to restrict my form to just the results of the search, which is hopefully only one account number + suffix.

SELECT dbase.Directory, dbase.SubDirectory, dbase.Filename, dbase.Account_Number, dbase.Suffix, [Directory] & "\" & [SubDirectory] & "\" & [Filename] & ".txt" AS File
FROM dbase
WHERE (((dbase.Account_Number)=[Account:]) AND ((dbase.Suffix)=[Suffix:]));

So I still need the WHERE clause.

Am I making any sense? Heh. [/Edit]

-DH22

[Stewart Ross]

...in which case you need a cascaded combo box solution involving two combos, one to select the accounts and the other to select the files pertaining to that account only. I will see if I can modify the sample later to add this for you to see. I can't reproduce the thousands of filenames, though :-)
-Stewart

[DH22]

...in which case you need a cascaded combo box solution involving two combos, one to select the accounts and the other to select the files pertaining to that account only. I will see if I can modify the sample later to add this for you to see. I can't reproduce the thousands of filenames, though :-)
-Stewart

If it works with three files, it should work with a thousand, I'd image.

I was sort of thinking was it possible to have a form to retrieve the information, make it static long enough for a second form to use something (maybe Dlookup at that point, since it works with 'static' information).

But you understand where I am coming from, right, Stewart?

I basically need to restrict the range of data..

-DH22

[Stewart Ross]

Oh ye of little faith... DLookup is, as I have said all along, not the right solution for this kind of application. See the attached sample DB duly updated and attached to this post.

-Stewart

[Stewart Ross]

By the way, this is not a complete solution to everything; if you need to filter the suffixes you can adopt a similar cascading approach to these using combos (or listboxes should you prefer) as necessary. I'll leave you to work this side of things out if it is needed...

-Stewart

[DH22]

Oh ye of little faith... DLookup is, as I have said all along, not the right solution for this kind of application. See the attached sample DB duly updated and attached to this post.

-Stewart

Okay, how do I narrow the account list down to just a specific account? We're working on close to one million account numbers as well. I cannot possibly expect them to go through the entire list looking for one, right?

I may just be confused and being frustrating, which if so, I humbly apologize!

I feel real close to a solution thanks to your assistance, but the long listing just doesn't feel.. "right", ya know?

[EDIT]

Ah, filtering, that could be another way to do what I'm looking for, yes?


Neverless, I am extremely appreciative of your help Stewart and if you're done with this topic, I'll be more than glad to press foward on my end and will not be offended. You've done alot of good, as is. I just need to.. tailor it, I guess.

If you're still curious, I won't be unappreciative for your continued input, either.

Thank you!

[/EDIT]

-DH22

[Stewart Ross]

Hi again. You know that Access combo boxes character match as you type, right? So although a long list is presented to a user, the moment they type the first character the list reduces in size, and thereafter for every character that matches...

There will no doubt be other ways to reduce complexity - for example, selecting an account range to start with - but for me to know what would be best for you I'd need to know your application as well as you do - and I really, really don't know enough about it to come up with ideas like that...

Anyway, I'm more than happy to monitor the thread for further suggestions or inputs.

Cheers

-Stewart

ps combo box character matching can be turned off too, but it is on by default. And it shows the nearest complete match from the first character typed onwards - it is a really helpful facility IMHO.

[DH22]

Hmm, well, I could give more details and see what you may come up with, idea wise. I'm up for anything that keeps it simple on my end users.

What information would you need, Stewart? I typed a few long paragraphs to try and describe the situation, but then I realized trying to wade through that wouldn't be very nice and I won't do that to you.

Here's a summary:

I have tif files linked to accounts, that's all the database is.

Yes the database is used in conjunction with another application (web based), but that app is not designed to handle this situation.

I need to be able to delete these .tif files as appropriate.

I know nothing about the .tif file. The only information I have is the Account and Suffix (sorry for repeating myself). These two pieces of information are linked to the file, so when I search for one I find the other.

The *ideal* solution would be a very simple query for my end users that will let them pull up the information inside of Access and delete it using the VBA. The combo box works, but I think it'll make my end users uncomfortable and very unwilling to use the process.

If they could enter information into a prompted input box, have the values returned for verification that is what they are looking for, and use an Idiot Proof button that deletes the referenced file, that would be best. I.e. my obsession with Dlookup with that thought in mind.

That's pretty much it sums it up, I hope, Stewart. Anything else I can provide? I'd attach a sample database but it doesn't differ that much from the one you provided.

As you stated, I'm just trying to reduce complexity so that only a few records, tops, appear.

We're dealing with a very large amount of records. I don't even know if there would be a performance issue with having a combo box listing that amount of records at once.

[DH22]

Heh, I bet you're going to scold me for this one, but what do you think about this?

Private Sub Command10_Click()
 
Mystr = InputBox("Enter Account Number")
 
MyAccount = DLookup("[Account_Number]", "[dbase]", "[Account_Number]=" & Mystr)
 
MsgBox MyAccount
 
End Sub

I can use the Inputbox to trigger Dlookup into returning the right information?

[EDIT]

Well, okay, I get why it doesn't work. I mean, it does work, but there's no promise the two fields will match, it'll return just the first thing that matches and put them together.. so it's a no go. Chalk it up to newbism. Heh.

[/EDIT]