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 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

[DH22]

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

 set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile(strFilePath)
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

[Stewart Ross]

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

[DH22]

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?

[Stewart Ross]

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

[DH22]

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

[Stewart Ross]

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

[DH22]

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

[DH22]

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

[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