Query functions

[Banelight]

Hey all,

I recently got roped into designing a database for my company, after some long hours work have first working copy up and running. Now I need to tweak it to lock down errors etc. So with that in mind....

• I have a query running on the main table, that uses criteria field to bring up little box where user inputs Data to specify what data is shown....

• this is turn opens a form that displays the requested data.
• However, if there is no matching data, it opens said form, blank with no info.
• This form is used to update 2 fields in the main table, and because the results are coming up blank, they are saving the info on its own in a blank line on the database

So now I need to make it that if the query returns zero results, an info box must appear telling them this, then close the form so they cant update lost info.


I have searched the forums, and seen similar discussions, however they seem to be setup different to me, and was not able to successfully implement any of those solutions.


PS: I have a main "Home" form which has all the buttons that launch these queries...so the button directly runs query, and query results opens form. I need to do this for all the queries, SO...do I need to make changes to all queries, or is there a way to run same conditions across all queries on database!

 

[pr2-eugin]

You can use a Date Control Text box on the "Home" form, and base all your Queries criteria to pick that up, then on click of the buttons, use DCount to determine if it will return 0 or more records, if 0 display the message to the user and Exit the Sub.

 

[Banelight]

Ok,

When I said Home form, I mean its the equivalent of the switchboard function in access 2007..like landing page when access database opens, it only has buttons it.

I have read about this Dcount before when researching this problem....im not sure where to try implement this? On the button that launches the query, or on the actual query itself?

 

[pr2-eugin]

When I said Home form, I mean its the equivalent of the switchboard function in access 2007..like landing page when access database opens, it only has buttons it.

I understood what you meant, okay lets do this, a bit unconventional.

STEP 1 : Open the "Home" Form in design view, then add a Text Box. Name it - criteriaDate. Then set its visibility to No.
STEP 2 : Then open one Query (for the moment), change the Criteria to Forms!yourHomeFormName!criteriaDate. Save and Exit.
STEP 3 : Now go to the design view of the Home Form, and then select the button whose Query you just changed in STEP 2, and go to the code section and make it look something like..

Private Sub theButtonName_Click()
    Me.criteriaDate = InputBox("Enter the criteria Date")
    If Dcount("*", "theQueryName") = 0 Then
        MsgBox "Sorry no details to process/see. Good Day !"
        Exit Sub
    Else    
        DoCmd.OpenQuery "theQueryName"
    End If
End Sub

Do the same through all the Queries you wish to do this..

 

[Banelight]

Whew...took me a while to get that right, and had to modify code somewhat, but I understood you were getting at! My thanks sir!

Its now doing what I was looking for, and you have shown me a whole other world of options for designing queries!

I a code newebie when it comes to writing from scratch, however can understand (mostly!) what going on in written code, so once I copied all the code to a notepad and put correct info, I saw what you did! So was able to midify it slightly to do what I wanted!

 

[pr2-eugin]

LOL ! Sorry if it was confusing. Did not mean it to be that way.

Well am glad you have it all working now Good luck !

 

[Banelight]

hehe, no was not confusing so much as knowing where to start! I dont like being spoon fed, and like to be able to figure stuff out...you showed me the way and I learnt a new trick!

What you posted didn't make a lot of sense at start, until I broke it down...as I have very limited coding knowledge....once I broke down the steps, the light came on!