Running a query and referencing some of its values in VBA

[mindbenderz]

This is the last challenge I currently have, and its taken me days to get to this point. I have finally figured out how to make a query that will run some calculations I need. The query is called

qry_hardcopy_ca lcs

In a subfrm I have 2 fields which will run this query and some extra VBA on an event. I will try and explain what needs to happen as clearly as I can, and the controls associated.

subform = subfrm_xmit_doc s

trigger controls = subfrm_xmit_doc s.HC_sent , subfrm_xmit_doc s.CD_sent

linked field = subfrm_xmit_doc s.Path
(subfrm_xmit_do cs.Path = tbl_hardcopies. Filename)

target update table = tbl_hardcopies

target update table fields = tbl_hardcopies. HC_curr , target update table = tbl_hardcopies. CD_curr


ok, the purpose of the code: If I enter a number in to HC_sent and try to go to the next field, it should run "qry_hardcopy_c alcs" and look at the new value in the query field "HC_diff". If the value in "HC_diff" < 0, it should cancel the process and not allow the update to subfrm_xmit_doc s.HC_sent. It should also give a popup warning saying something like "There are not enough hardcopies on file. Add some first."

If the value in "HC_diff" => 0, it would copy the value in "HC_diff" and paste it to the tbl_hardcopies. HC_curr field that corresponds to Filename.

Thats probably as clear as I can explain it. It is a process that compares a numerical value associated with a filename to a calculation value in a query, and if the value is valid it allows it and updates the current qty amount with the new amt. If the value is less than 0, it prevents someone from entering that qty and warns them about it.

[NeoPa]

That's clearly a much better attempt to explain the situation than your last thread. Nevertheless, as it is quite a complex situation to explain and such explanations are, unfortunately, not a strength of yours, I find myself largely at a loss as to exactly what you need.

I can maybe help by suggesting the use of DLookup() in your code (after HC_Sent is amended) to determine the value of HC_Diff from qry_Hardcopy_Ca lcs. You will need to indicate which record to select the value from somehow if there is more than one in the query. Only you can determine how this should be done at this time as we don't have such information available to us.

I suspect your lack of appreciation of where tables end and forms begin is at the root of the confusing nature of the rest of your explanation. I find myself trying to guess what you're referring to, to the extent that I doubt I can say anything more helpful at this stage. I can certainly recognise a much better attempt at the explanation though, as well as noting that your requirement is unfortunately more than averagely complicated to express.

In recognitoin of your worthy attempts, I feel I should offer my services to look over your database if you choose to attach it to the thread. I expect it will be much easier to realise where you're coming from with the database in my hand, and I'd hate to think I'd set you an impossible (or unreasonable) task of explaining yourself in what is a fairly complicated situation. See Attach Database (or other work) if you're interested.

[mindbenderz]

I will put together a stripped down copy of the db when I get in to the office tomorrow. viewing it and reading what Im trying to do might make more sense. I do realize that sometimes my explanations can be a little unclear

[NeoPa]

It won't be weekend by then, but I'll have a look at it for you when I can. I expect much of what you say will start to make sense when I have what you're referring to in front of me.

[mindbenderz]

I attached the database with the necessary forms to see what I trying to do and enough information in the tables to work with. Ill answer all questions you have while working on this issue. Thanks so much.

[NeoPa]

NeoPa:
As with any problems posted on this site, you won't make many friends if you post code without compiling and testing it first. This goes for Variable Declaration too. If people offer time to help with your problem only to find that you're asking about something the compiler is perfectly capable of catching, they're likely to feel insulted and ill-used.

Having linked you to a thread which explains everything that is expected of you when attaching a database I'm disappointed to find a database with Option Explicit not set. I've quoted one of the paragraphs in one of the threads linked through to.

As it happens, when I fixed my copy of your database I noticed very few errors. You can fix these yourself by following the linked instructions and compiling (hopefully) again. It doesn't effect this question though so I will continue. I expect the advice never, ever, to work in a database without all modules being Option Explicit and compiled again, is likely to prove of more use to you than any advice I end up giving on this particular problem. Read the articles I linked to (One links to another) and I promise you you won't lose out by it (Unless you stop developing today or fall under a bus or something). These issues are so fundamental you can't help but benefit from understanding them.

As I say, there are not many such fundamental problems with your code so I will continue to look at it and post back when I've got something else helpful to say.

[NeoPa]

It seems like what you're really after is knowing about the DLookup() function. This ensures that a query is executed but, instead of displaying it for the operator, allows individual field values to be returned to the code. See below for an example of it at work :

Private Sub HC_sent_BeforeUpdate(Cancel As Integer)
    Dim lngDiff As Long

    lngDiff = Nz(DLookup("[HC_Diff]", "[qry_hardcopy_calcs]"), "Null")
    Debug.Print lngDiff
End Sub

Your code will still need to process the value, but I expect this is what you were missing.

Let us know how you get on from here.