If then Or Case (1 Viewer)

[halem2]

Hi folks:

long time to no post! Using Access 2003.

I have a form with 28 cmd buttons running each a different query. Each qry gives only one result (Top N of 1). I'm thinking of writting an if statement that would pass the qry result to a text box on the form regardless of which cmd button is clicked.

In other words, instead of evry tie a cmd button is clicked it opens its qry showing only on cell, I would like to populate a text box on the form with the result of the qry, regardless of which cmd button I click.

Can anyone give me an idea how to start?

thanks a lot in advance

 

[Alc]

How about creating a function that receives some value when each button is clicked, then performs whatever the query is and populates the field on the form?

 

[halem2]

the problem is each query has different criteria so the function would get really complicated (I think).

 

[pbaldy]

28 buttons sounds ugly, but how about a DLookup behind each one that grabbed the value from its respective query?

 

[Alc]

the problem is each query has different criteria so the function would get really complicated (I think).

Do they have anything in common?
I have a form that uses six buttons in this way.
They all select the same data, but the where clause is different in each an the sort order is different for each of three pairs of buttons.

 

[boblarson]

Why not expand upon Paul's suggestion -

Instead of 28 buttons, use a list box to list all of the queries and set the double-click event to display the results in the text box based on the DLookup.

Or, use a combo box (it would definitely make the form smaller). You can get the queries easily enough by selecting from the system table.

I currently use this query to pull all of the forms available (those with a specific name) and you could modify it to pull the query names instead:

SELECT MsysObjects.Name, Mid$([Name],InStr(1,[Name],"_")+1) AS [Form Name]
FROM MsysObjects
WHERE (((Right$([Name],6))="lookup") AND ((Left$([Name],3))="frm"))
ORDER BY Mid$([Name],InStr(1,[Name],"_")+1);

 

[Alc]

For what it's worth, I also use a similar screen to Bob's suggestion for reports.
The addition I've made is to handle the fact that the users keep changing their mind on what the various reports should be called .

The actual names within the database remain constant, but the name people within the deparment use to refer to them differs from week to week. After having to alter various code a few times, what I now use is a table containing each report's actual name, along with the currently used name.

The user picks a 'current' name, the value is pulled from the actual name. That way, the code can remain constant but I can quickly handle the fact that the report may suddenly need to be listed as something else.

 

[boblarson]

Here's a quick sample for you.

 

[halem2]

thanks Everyone.

I'll give it a whirl and let you know how it goes. Thanks once more Bob.

 

[pbaldy]

In the FWIW department, in a situation where the users can choose different criteria, sort order, etc I do not create multiple queries. Since I never let them see queries directly anyway, I'd open a form or report. That form/report would be based on a query that returned all records. I present them with a form to choose their various criteria options, and build a wherecondition to open the form/report with the selected records. Sort orders, summary vs detail or grouping changes I handle in the report as it opens.

 

[halem2]

users d o not have access to the queries...just to the results

 

[pbaldy]

Then I'd have 1 query, not 28. In fact it sounds like you could just open a recordset and get the value, thus have no queries. The difference is that the users would have to fill something out to determine the criteria, rather than you hard-coding 28 buttons and queries.

 

[halem2]

sounds like I have not explained myself clearly enough to give you folks a good understanding of the question.

We have 4 division offices and they all have computers.

Let's say Miami's naming convention is

CPUM001, CPUM002... for desktops
LAPM001, LAPM002... for laptops
and so forth.

Vero Beach is

CPUV001, CPUV002...
LAPV001, LAPV002...
and so forth.

I also have Two other cities. What I currently have is a form with several cmd buttons, each one giving me the last name used per city per device so we can assign the next in line. Each button runs a TOP 1, desc qry.

I need to have, let's say a sub form with one field that's populated regardless of which cmd button is clicked.

I thought I could do a Select Case or if statement to pass the qry result to the sub form but I'm stuck there.

 

[pbaldy]

Well, in post 4 I suggested a DLookup, which I think would work. You might also be able to set the source of the subform to the appropriate query. However, I still suspect it could be done without queries. Choose a city, choose the equipment type, then the code knows from those what to look up (and that's where an If/Then or Select/Case would be used). Perhaps if you posted a sample db, we can see what you've got and determine if I'm right or full of you-know-what.