DLookup from a query criteria trouble

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

[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