By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 2,003 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

DLookup from a query criteria trouble

P: 21
[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:

Expand|Select|Wrap|Line Numbers
  1. SELECT L_Emps.Employee_ID, L_Emps.First_Name, L_Emps.Last_Name
  2. FROM L_Emps
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  
  3. ID = DLookup("[Employee_ID]", "[Query1]", "[Employee_ID]=" & [Forms]![L_EmpsForm]![Employee_ID])
  4.  
  5. MsgBox ID
  6.  
  7. 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
Aug 29 '08 #1
Share this Question
Share on Google+
30 Replies


Expert Mod 2.5K+
P: 2,545
Hi DH22, and Welcome to Bytes!

There is nothing wrong with your syntax for the Dlookup. I am sure the problem is that you are trying to lookup a field value in a query, Query1 in this case, which has a parameter asking for data entry from the user ([ID:]). Not only is this unnecessary in the circumstances, it will cause a Dlookup failure, because within DLookup the JET database engine will not be able to interpret the parameter as a valid field.

Remove the parameter (the whole WHERE clause) from Query1 and see how you get on.

-Stewart
Aug 29 '08 #2

P: 21
Hi DH22, and Welcome to Bytes!

There is nothing wrong with your syntax for the Dlookup. I am sure the problem is that you are trying to lookup a field value in a query, Query1 in this case, which has a parameter asking for data entry from the user ([ID:]). Not only is this unnecessary in the circumstances, it will cause a Dlookup failure, because within DLookup the JET database engine will not be able to interpret the parameter as a valid field.

Remove the parameter (the whole WHERE clause) from Query1 and see how you get on.

-Stewart

Hey, that did work, Stewart. Thank you! I'm still very fuzzy on the criteria, but I'm practicing.

However, the reason why I need the query is I will be setting up a Dlookup to use in conjunction with a delete function.

I'm trying to think of the best way to phrase this..

I have a database with records that point to files. Field 1 points to the directory, field 2 points to the sub directory, field 3 points to the file, field 4 points to the account number and 6 is the suffix.

I need the users to be able to input an account number and the suffix, pull up the matching records (indexes), and upon finding the one they wish to delete be able to press the button and have the file be deleted.

How do I set that up then if I can't use the form to call whatever the query tells it to bring foward?

I have the
Expand|Select|Wrap|Line Numbers
  1.  set objFSO = CreateObject("Scripting.FileSystemObject")
  2. objFSO.DeleteFile(strFilePath)
  3. WScript.Echo "Successfully deleted file" 
I wish to use to delete the file once it is found with the query, I just need to set strFilePath to field1 & field2 & field3 when the account number and suffix returns a hit.

Am I being clear? I hope so.

-DH22
Aug 29 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi DH22. I wouldn't use Dlookup for the purpose you mention; I'd use an unbound combo box instead (an unbound control is one that is not bound to an underlying field in a table or query). The rowsource of the combo would be a query based on the table you mention. You can use a calculated field in the query to combine the separate field values you mention into a single filepath you can pass to the delete code.

To run the delete after making a selection from the combo use the After Update event of the combo to run the delete file code based on the value of the combo.

Combo boxes in unbound form make very flexible lookup controls, as the multiple columns they can display can all be accessed in VBA code for whatever purposes you might need. In this case, you only need to list the filepaths, so one column should suffice.

-Stewart
Aug 29 '08 #4

P: 21
Hi DH22. I wouldn't use Dlookup for the purpose you mention; I'd use an unbound combo box instead (an unbound control is one that is not bound to an underlying field in a table or query). The rowsource of the combo would be a query based on the table you mention. You can use a calculated field in the query to combine the separate field values you mention into a single filepath you can pass to the delete code.

To run the delete after making a selection from the combo use the After Update event of the combo to run the delete file code based on the value of the combo.

Combo boxes in unbound form make very flexible lookup controls, as the multiple columns they can display can all be accessed in VBA code for whatever purposes you might need. In this case, you only need to list the filepaths, so one column should suffice.

-Stewart

Stewart, I will search around to see if I can find some more details for that, but what I am after is very possible, right?
Sep 2 '08 #5

Expert Mod 2.5K+
P: 2,545
Indeed so, DH22. Experiment a bit with combos and particularly the After Update event, which is fired whenever a user makes (or changes) a selection in the combo.

-Stewart
Sep 2 '08 #6

P: 21
I firmly admit that at this second I'm clueless on how to use the combo box, but I'll figure it out.

Would it be possible for you to point to some reference material? I will be hitting google quite hard here, in a bit, as well.


-DH22
Sep 2 '08 #7

Expert Mod 2.5K+
P: 2,545
First of all, create an Access query which combines your separate fields into one overall filename for each file you want to delete. The calculated field is nothing more than a field in the Access query editor, something like this:

Filename: [Directory] & "\" & [subdir] & "\" and [filename] & "." & [suffix]

You may or may not need the slash marks and the dot - all depends on how your paths etc are stored.

Then, on a blank form use the Access combo box wizard and place a combo box on a form, basing it on the filename field of the query you just created.

Once this is done you can then explore how to use After Update to perform the actual delete if the combo box is not null - in other words if the user has not cleared the contents of the combo.

Search this site for examples of the use of After Update in Access - there are plenty of examples here.

Beyond that there is no real reference material I can give you - it is just a case of knowing what combos do, and combining this with code fired on the After Update event of the combo itself.

-Stewart
Sep 2 '08 #8

P: 21
Stewart,

I will be doing just that. Thank you very much for your time and patience in this matter. Hopefully I'll figure it out. I've been using Access and VBA for a week and a half now, so I'm gaining steam.




-DH22
Sep 2 '08 #9

P: 21
Stewart,


I'm not having much luck so far with the combo box (and frankly I'm not liking it very much, either. That's probably due to my lack of experience with it, though.)

While I work on learning to like the combo box, are there any other options you can recommend?



-DH22
Sep 3 '08 #10

Expert Mod 2.5K+
P: 2,545
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
Attached Files
File Type: zip Test Combo.zip (12.7 KB, 113 views)
Sep 4 '08 #11

P: 21
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT dbase.Directory, dbase.SubDirectory, dbase.Filename, dbase.Account_Number, dbase.Suffix, [Directory] & "\" & [SubDirectory] & "\" & [Filename] & ".txt" AS File
  2. FROM dbase
  3. WHERE (((dbase.Account_Number)=[Account:]) AND ((dbase.Suffix)=[Suffix:]));
So I still need the WHERE clause.

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

-DH22
Sep 4 '08 #12

Expert Mod 2.5K+
P: 2,545
...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
Sep 4 '08 #13

P: 21
...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
Sep 4 '08 #14

Expert Mod 2.5K+
P: 2,545
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
Attached Files
File Type: zip Test Combo.zip (18.0 KB, 103 views)
Sep 4 '08 #15

Expert Mod 2.5K+
P: 2,545
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
Sep 4 '08 #16

P: 21
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
Sep 4 '08 #17

Expert Mod 2.5K+
P: 2,545
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.
Sep 4 '08 #18

P: 21
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.
Sep 4 '08 #19

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


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.  
  3. Mystr = InputBox("Enter Account Number")
  4.  
  5. MyAccount = DLookup("[Account_Number]", "[dbase]", "[Account_Number]=" & Mystr)
  6.  
  7. MsgBox MyAccount
  8.  
  9. 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]
Sep 4 '08 #20

Expert Mod 2.5K+
P: 2,545
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
Sep 4 '08 #21

P: 21
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.
Sep 5 '08 #22

Expert Mod 2.5K+
P: 2,545
No sign of the three records so far... -S
Sep 5 '08 #23

P: 21
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.
Attached Files
File Type: zip db2.zip (12.7 KB, 92 views)
Sep 5 '08 #24

P: 21
Hopefully the attached dbase is what you were asking for. :)
Sep 9 '08 #25

P: 21
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.
Sep 10 '08 #26

Expert Mod 2.5K+
P: 2,545
I will review the DB, DH22, as time allows; I'll post back when I have done.

-Stewart
Sep 10 '08 #27

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



-DH22
Sep 11 '08 #28

P: 21
Perhaps using the Query as a Make Table query and then running the form on that result? That would narrow it down.
Sep 17 '08 #29

Expert Mod 2.5K+
P: 2,545
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
Sep 19 '08 #30

P: 21
Stewart,

I thank you for your help in this, again! I'll definitely keep the recommendation in mind and will probably go ahead with it.

If I do find out any other solution, I'll post it here just for future searches.

Once again, thanks!!


-DH22
Sep 19 '08 #31

Post your reply

Sign in to post your reply or Sign up for a free account.