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