Use Function to find data from a Query

[SaskiFX]

I need some assistance with code for a function. I'm new to VBA (but not coding) so I'm not 100% sure what I'm looking for.

I'm wanting to use a function to fill a field on a query. I'm using this to have a form with lots of filters and a search box to narrow down a large set of results in a listbox. Everything is working great except the next part I'm trying to expand it to handle. I have a list of different Stresses each item in the listbox may or may not have associated with it. So I have some check boxes that I am going to use with the original code to show if the results have or do not have the stresses data available. However, since each result could have lots of stresses, this breaks things. So I'm going to use a calculated field to see if these stresses exist.

So, each entry on the listbox has a Division. Each Division can have lots of stresses associated with it, or none. I have a query setup to show each Division and the stresses it has.

qryStresses looks like:



All I have of my function so far is the declaration:

Public Function HasStress(StressType As String, Division As Integer) As Boolean

I want to be able to use the HasStress function with a calculated field, so each record will have some new hidden fields I can filter with. I don't care how many different types of each Stress there are, just if there is any of each type present.

So for example HasStress(Bending, 4) should return True, and I don't care that there are two bending types. and HasStress(Shear, 3) should return False, cause there is no Shear listed for Division 3.

Can anyone point me in the right direction with the best way to tackle this?

Thanks!

[ItsMe]

My approach to these types of issues is to first determine where and when, within the application, it matters how many stress issues are remaining. With that in hand I can determine the best way to get the info from the table(s). I would probably use a totals query to display the quantity of stress issues and display the value in a subform. A calculated field may be beneficial when the user is querying data over a WAN. If you are not on an extranet then I would probably not use calculated fields.

As for a user defined function. You would probably get better performance using the built in function Dcount().

It just depends on when, how, and where. A totals query would probably offer the most versatility. Using a function would require a trip to the data for each record/inquiry made vs. storing an entire recordset in memory.

[ssanfu]

I have to agree with ItsMe, a function call can be slow (and inefficient).

I have a query that takes about 2 hours to run - approx 5,000 records, with 4 calls to the function per record. Just a simple look up function using a record set.

BTW, "Type" is a reserved word in Access, so in my example, I changed it to "StressType".

Here are two functions to return a Boolean result. You didn't specify the table name and I changed a field name, soooooo:

Public Function HasStress(pStressType As String, pDivision As Integer) As Boolean
   Dim r As DAO.Recordset
   Dim sSQL As String

   HasStress = False

   sSQL = "SELECT tblStresses.StressType, tblStresses.Division"
   sSQL = sSQL & " FROM tblStresses"
   sSQL = sSQL & " WHERE tblStresses.StressType = '" & pStressType & "' AND tblStresses.Division = " & pDivision & ";"

   Set r = CurrentDb.OpenRecordset(sSQL)
   If Not (r.BOF And r.EOF) Then
      HasStress = True
   End If

   r.Close
   Set r = Nothing

End Function

Public Function HasStress2(pStressType As String, pDivision As Integer) As Boolean
   Dim sSQL As String
   
   HasStress2 = False

   sSQL = "StressType = '" & pStressType & "' AND Division = " & pDivision
   HasStress2 = DCount("*", "tblStresses", sSQL)

End Function

Allen Browne also has a DCount() replacement function, named ECount(), at http://allenbrowne.com/ser-66.html

[rpeare]

if you have that query, why would you need to do a lookup at all, can't you just link the qryStresses back to your main query and just adjust your citeria for an isnull or not isnull?

[SaskiFX]

ssnafu, that first block of code worked great! After changing a couple variables things worked perfectly. Thanks!

I'm 100% positive that I'm not getting this database put together in the best way seeing as I'm kind of stumbling around to get things working the way we want. I have tried to organize and create the tables logically (anytime I have duplication of data I try and split things off) but there are lots of little bits and pieces that don't quite match up sometimes.

rpeare, its hard to explain. Basically when I tried to link the various stresses to the search form, I had lots of duplicate results in my search query. Each entry on my search can have multiple different stresses, even of the same type. So if I had my search window showing every item, I had 35 entries. Once I tied in the stresses, each item had 4 more entries. One for each stress type. So I suddenly had 150 results.

[rpeare]

So right now, you have two queries, each showing you part of what you want and you are trying to create a function that will basically retrieve a value from one query and put it into another.

Here's what I propose. Give an example of your data right now in each of the two separate queries, enough records to give a good idea of what we can expect.

Then tell us what you WANT your data to look like when the 'final' query is done.

just as you are 100% sure your database isn't pefect, I'm 100% sure a function is not the way you want to go and equally sure it can be done through queries.

[ssanfu]

Glad I could help. I provided the code function because (1) you asked and (2) it would be (hopefully) a learning opportunity.
But I agree with rpeare; a function is not the best way to go if the dB table structure can be optimized.

If you want someone to look at the table structure/queries, post the dB. No sensitive data (doesn't sound like there would be) and have some records to see how things work. And, like rpeare said, an example of what you want the output to look like.

[SaskiFX]

I'm working on getting another piece of the dB setup at the moment, but I'll come back and post up a stripped copy of the dB. Thanks again all.