Dlookup - find an easier way

[mort]

Hi all,
My last obstacle before finishing a big database is getting the summary form correct. This is going to be a long post, but i need help to make my VBA code simpler (if possible). Right now it takes about 15 seconds to do all the DLookup`s.

I have an unbound form (frmSummary). There are no bound controls on this form.

I have 2 comboboxes (cboPerson and cboPlace) and 45 unbound textboxes. The textboxes are ordered like this on the form:

TOTAL ------ SENT ------ OUT ------ IN STORAGE
Cellphone: [txtbox1] --- [txtbox2] ---- [txtbox3]
Computer: [txtbox 4] --- txtbox5] ----- [txtbox6]
And so on.

The textboxes are set to show the total number of that specific item. If the user wants he can filter the results from one of the two comboboxes, showing for examples "Computers" belonging to personA.

With 45 different textboxes that can be filtered in 3 different ways (person, place and no-filter) I end up with about 150 lines of code in 3 different events: formLoad, cboBox AfterUpdate (2 cboboxes) and no-filter_Click. Totalling to about 600 lines of code.

An example of the un-filtered code for cellphone:
Me.txtbox1= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone'")

Me.txtbox2= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone' And [SentOut]=false")

Me.txtbox3= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone' And [SentOut]=true")

If filtered by one of the comboboxes the code simply adds this line in the criteria: And [Place] = '" & Me.txtPlace & "'"). This happens in the afterUpdate event of the combobox.

What I want is to make this with much less code so that the calculations goes much faster.

Is it possible to make several strings, use Recordset or something like that (I got not experience with neither). Or maybe it is better to make the textboxes bound to a query, adding criteria with the comboboxes?

Any help is appreciated. After solving this last problem I will be finished

 

[NauticalGent]

Well let me be the first to shoot my face off. I have a similar set up with not so many summaries so DCount works for me although there are some who avoid domain functions like the plaque.

The only alternative that comes to mind would be to bind the form to a summary table that gets updated on Form Load by a series of Update Queries.

There are probably some better ways to skin this cat. Hopefully others will chime in and educate both of us...

 

[Galaxiom]

Each DLookup is a separate query which is why it takes so long.

Multiple DLookups from the same table may be able to be replaced by a single aggregate query as the RecordSoource of a subform. Perhaps you will need multiple subforms.

Why not bind them instead of using code? The LinkFields of the subformcontrol can be used to make them respond to the changes in the filter textboxes on the main form.

Note that a MasterLinkField can be a control.

 

[mort]

Why not bind them instead of using code? The LinkFields of the subformcontrol can be used to make them respond to the changes in the filter textboxes on the main form.

Note that a MasterLinkField can be a control.

How would you go about doing this?

 

[Galaxiom]

For example the textboxes with SentOut counts can be based on an aggregate query grouped on SentOut. This query will return two records with counts for True and False.

Base another quuery on this to get the two counts in separate fields as a single record. Derive a third field as the sum of the two counts.

Use thes three fields as as the ControlSources for the textboxes in your examples.

 

[Mark_]

Step 1) Look at how you name your controls. "Me.Txtbox1" tells you and others NOTHING about what should be shown in this field.
Step 2) Create a query that uses the values from your combo boxes to limit the data.
Step 3) Create ONE sub that does the calculations.
Step 4) Call your new sub when you need to reset the form

I'd also use Galaxiom's advice on using multiple queries to do the work for you rather than DLookup. This will take some time to set up and test though, to get each working right. His advice will probably give you the greatest speed increase, I'm looking at making your code more "Programmer" friendly.

Also look at places where you already have the base data and won't need a lookup.

Me.txtbox1= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone'")

Me.txtbox2= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone' And [SentOut]=false")

Me.txtbox3= DCount("[Beslagtype]", "tblbeslagsoversikt", "[Beslagtype]='Cellphone' And [SentOut]=true")

Is doing work you don't need to do. Me.Txtbox2 and Me.Txtbox3 ALREADY can tell you how many cellphones you have. As such,you can remove the lookup for Me.Txtbox1 and replace it with

Me.Txtbox1 = Me.txtbox2 + Me.txtbox3

If you have several other DLookups for values you can already calculate you can quickly remove them for an immediate improvement in speed.

 

[Pat Hartman]

Since all the data is in the same table, you can make a bound subform rather than 45 individual textboxes. Use a totals query.


Select Beslagtype, Sum(IIf(SentOut = False, 1, 0)) as Sent, Sum(IIf(SentOut = True, 1, 0)) as Out
From tblbeslagsoversikt
Group By Beslagtype
Where ....
Order by Beslagtype

Add the filters to the main form and build the where clause.

 

[mort]

Since all the data is in the same table, you can make a bound subform rather than 45 individual textboxes. Use a totals query.


Select Beslagtype, Sum(IIf(SentOut = False, 1, 0)) as Sent, Sum(IIf(SentOut = True, 1, 0)) as Out
From tblbeslagsoversikt
Group By Beslagtype
Where ....
Order by Beslagtype

Add the filters to the main form and build the where clause.

Thank you Pat. I will try your solution first. I have made a query using the SQL code you gave. The query has 4 columns; Type, Sent, Out and Total. It looks good, but i dont understand how to get that information to show in different textboxes on a form. And further down the road, how to filter the data using 2 comboboxes.

I need all the information to show up on 1 page. 10 different subforms for each type dont seem to fit my needs. If I understood you correctly?

 

[Pat Hartman]

The criteria will look like:

Where somefield = Forms!yourform!somefield AND somefield2 = Forms!yourform!somefield2 AND .....

Forms do not paginate. If you want page breaks, you MUST use a report. A report can give you a separate page for each Beslagtype but with the query above, you will only get a single line for each Beslagtype so I'm not sure what you need.

 

[mort]

For example the textboxes with SentOut counts can be based on an aggregate query grouped on SentOut. This query will return two records with counts for True and False.

Base another quuery on this to get the two counts in separate fields as a single record. Derive a third field as the sum of the two counts.

Use thes three fields as as the ControlSources for the textboxes in your examples.

I will try this out, thanks.

I have now made 13 different queries for each type. The queries all have a single record each showing "Type", "In", Out". The last two being a totals count using this expression: In: Sum(IIf(Utkvittert=False;1;0))

I have also made a totals query based on all the 13 queries (not sure if i need this). The totals query is a Union Query (UNION SELECT ALL...) showing all 13 queries in one.

I have tried making a form based on the UNION query. The form works fine, but I get the In\Out totals on 13 different pages (1 for each type). I need to have all the textboxes on 1 page (I dont mind placing them there and setting the control source manually).

However, I have no idea how to get this information to show on my textboxes (all on 1 page) without using DCount. I have googled like crazy to see if its possible to write code populating the textboxes with a where clause, for example me.textbox1.controlsource = In\Out totals WHERE type = xxx and so on. No luck.

Am I on the right path, or have I totally misunderstood your advice?
I would prefer not having to make 13 different subforms connected to the same mainform.

 

[The_Doc_Man]

As to the discussions on using names such as "Textbox1", "Textbox2", etc. - even if these boxes are NOT bound, you might consider changing names to prevent you from making a bleary-eyed mistaken assignment during a late-in-the-day coding session where you forget for a moment which box was In and which box was Out. The names used internally in the code aren't for the users. They are for the maintainers - which at the moment is YOU.

If you have this down to 13 queries from 45 but they are still all from the same table, then you could grab the data via a recordset loop. Read up on recordset operations. If the difference between the queries is because you have 13 different types of device, you can do this with ONE query and just use your filtration once. Step through the records of the query to pull the data you need, one group at a time.

Look carefully at Pat's suggested query. If you opened that query in datasheet mode, you would see rows of data grouped by device type and with sums of in and out, and of course the total would be In + Out for each row. The field names would be Beslagtype, Sent, and Out. Think about something like this if you named the query "BeslagTotals":

Dim rsSums as DAO.Recordset

...

   Set rsSums = CurrentDB.Openrecordset("BeslagTotals", dbOpenDynaset)
   rsSums.MoveFirst
   Do While Not rsSums.EOF
      Select Case rsSums![Beslagtype]
         Case "Cellphone"
            Me.CellpSent = rsSums![Sent]
            Me.CellpOut = rsSums![Out]
            Me.CellpTotal = rsSums![Sent] + rsSums![Out]
         Case "Computer"
            Me.CompuSent = rsSums![Sent]
            Me.CompuOut = rsSums![Out]
            Me.CompuTotal = rsSums![Sent] + rsSums![Out]
         Case ..... as many of these as you have
         Case Else
            MsgBox "Unexpected data type " & rsSums![BeslagType], vbOKOnly, "Unexpected data type"
      End Select
      rsSums.MoveNext
  Loop
  rsSums.Close
  Set rsSums = Nothing

Look this over. The loop will be short to run because you tell us you have it down to 13 queries, I assume for 13 different types. A 13-step loop behind the scenes is a trivial operation. If this looks like it might help you, all you need to do is read up on recordset operations. With Pat's suggested query as the basis, you would be able to knock this out in a very short time.

 

[Pat Hartman]

I have now made 13 different queries for each type.

ONE query is sufficient. Review my example. It returns one row per Beslagtype

 

[Mark_]

I have tried making a form based on the UNION query. The form works fine, but I get the In\Out totals on 13 different pages (1 for each type). I need to have all the textboxes on 1 page (I dont mind placing them there and setting the control source manually).

Try opening it as a continuous form.

If you are using this as a report you should be making a report.

Until you make a report, you could make an unbound "Parent" form that has all of your static text, then put your form in continuous mode on as a child record.

 

[mort]

First off I really want to thank everyone for taking your time to help me, I really appreciate it. I am what you would call an amateur, and therefore some of my questions will probably be basic and stupid

1. Pat and Doc_Man: Is it possible to use your suggestion and show the data on 1 page of a single-form?

2. 39 of the textboxes gets the information from the same table, but I also have 15 textboxes getting their information from 2 different tables. Is this a problem, or can I continue to use DLookup on these?

3. I have one query with 3 columns (type, In, Out) and 13 rows, one row for each type.

4. Say I make a bound subform from the query, and end up with an unbound parent (which now contains all my 50 or so unbound textboxes). Do I enter the code in the subform, or do i just reference all the 50 textboxes in the mainform to the query, and make the subform invisible?

What I dont understand is how to get all 13 rows in the query to show in different textboxes on my singleform (all on one page). I also dont understand where to write the Recordset-code you gave Doc_Man.
- Do I write it in the load-event of the form? If so, where do i reference the textboxes?
- What determines which Case is true

 

[The_Doc_Man]

1. Pat and Doc_Man: Is it possible to use your suggestion and show the data on 1 page of a single-form?

I would think so.

2. 39 of the textboxes gets the information from the same table, but I also have 15 textboxes getting their information from 2 different tables. Is this a problem, or can I continue to use DLookup on these?

Yes, but the same idea (using a recordset for each table) might be possible depending on what level of filtering you must apply.

3. I have one query with 3 columns (type, In, Out) and 13 rows, one row for each type.

About what I figured you had. No surprises there.

4. Say I make a bound subform from the query, and end up with an unbound parent (which now contains all my 50 or so unbound textboxes). Do I enter the code in the subform, or do i just reference all the 50 textboxes in the mainform to the query, and make the subform invisible?

If you have 39 unbound text boxes, one for each of the 39 potential values returned from Pat's query, you don't NEED the sub-form. At all.

What I dont understand is how to get all 13 rows in the query to show in different textboxes on my singleform (all on one page). I also dont understand where to write the Recordset-code you gave Doc_Man.
- Do I write it in the load-event of the form? If so, where do i reference the textboxes?

If your form with all the DLookups worked OK (other than being slow), then this code snippet could REPLACE 39 of your DLookups. Wherever THEY were, the code could go.

As to where your textboxes were referenced? Remember that I told you it would be good for maintenance purposes to rename the text boxes to match the data they would eventually hold, because the name [Textbox1] tells you NOTHING of importance. So my code snippet assumed you would do that. Look at the Me.xxxx = rsSums![yyyy] lines. Each one of those fills in one (renamed) text box based on the type code that is the first column of Pat's query.

- What determines which Case is true

Sorry, you lost me on that question. Can you ask that another way?

 

[Pat Hartman]

1. The query I suggested returns multiple rows so it would not be appropriate to use a single record form. However, you might use an unbound main form and several subforms if you want to show different, unrelated things.
2. Using multiple subforms will solve this problem. DLookup()'s are very inefficient. Why run 39 queries when you can run ONE? Or 15 when you can run ONE?
3.
4. You are fixated on these being independent, one off values. They are not. They are part of a set. A query can efficiently retrieve a set of records. You then use the query are the RecordSource for a subform or subreport to display the set as a LIST. You can do a lot of unnormal formatting to make the subform look like 39 separate controls but that only makes your work harder because you would have to reformat the form if you added another item bringing you up to "42" controls or 14 rows in a subform. The subform solution is no code at all. The solution you keep trying to find is at a minimum one line of code per control. It isn't necessaryly a lot of code but as you have already seen, doing this with domain functions is the slowest possible solution.

Please step back and try to look at this from a database point of view instead of an Excel point of view. In Excel, every cell has a separate calculation and that is the solution you keep trying to implement. You can do it. You have done it but it is not a pretty sight. The query I suggested has two calculated fields and gives you all 39 values!!! How can that be a bad thing?

 

[mort]

As to where your textboxes were referenced? Remember that I told you it would be good for maintenance purposes to rename the text boxes to match the data they would eventually hold, because the name [Textbox1] tells you NOTHING of importance. So my code snippet assumed you would do that. Look at the Me.xxxx = rsSums![yyyy] lines. Each one of those fills in one (renamed) text box based on the type code that is the first column of Pat's query.

I guess my big problem is that I dont understand the code you supplied good enogh. For example, do I put it in the forms load event?

And how do i rewrite the code when i want the textboxes filtered on a combobox selection? For example if cbo1 is filtered on place, then i want all the textboxes to show the items from that selected place, and no more.

And my question about what determines which Case is true I think i figured it out. The Case is the text value of the field "Beslagtype"?


I will spend the rest of my day at work trying both your solution and the one Pat just came up with. Hopefully I will get them both to work without having to nag too much on these forums


As for Pat I can say i agree with you. The reason why I am so obsessed with getting all this information on 1 page with so many textboxes is because its more user friendly. I work in the police, and my database are meant for registering and working with items seized in different investigations. The computer skills of an ordinary police detective are about zero, and I am afraid that if its not easy and intuitive enough they wont use it.

But i will try out your method as well, and work my ass of to try and get it user-friendly enough.

Thanks again to both of you for helping me out!

 

[mort]

Dim rsSums as DAO.Recordset

...

   Set rsSums = CurrentDB.Openrecordset("BeslagTotals", dbOpenDynaset)
   rsSums.MoveFirst
   Do While Not rsSums.EOF
      Select Case rsSums![Beslagtype]
         Case "Cellphone"
            Me.CellpSent = rsSums![Sent]
            Me.CellpOut = rsSums![Out]
            Me.CellpTotal = rsSums![Sent] + rsSums![Out]
         Case "Computer"
            Me.CompuSent = rsSums![Sent]
            Me.CompuOut = rsSums![Out]
            Me.CompuTotal = rsSums![Sent] + rsSums![Out]
         Case ..... as many of these as you have
         Case Else
            MsgBox "Unexpected data type " & rsSums![BeslagType], vbOKOnly, "Unexpected data type"
      End Select
      rsSums.MoveNext
  Loop
  rsSums.Close
  Set rsSums = Nothing

I have succesfully written this code in the load event of my unbound main form. It works perfectly, and everyone of my textboxes shows the number from the query BeslagTotals.

However, as I wrote in the previous post, I also have to be able to filter the results based on a selection in 2 different comboboxes:
1. cboCaseNumber
2. cboPerson

(CaseNumber is a unique identification given to every criminal case. An investigation can have several different casenumbers, each containing a different amount of seized items).

I need help writing this in code.

I have made 2 new queries called BeslagTotalsPerson and BeslagTotalCaseNumber. As opposed to the first one, these two queries have 4 columns:
1. CaseNumber \ Person
2. Beslagtype (type)
3. Sent
4. Out

Is it possible to write the same code adding a Where condition or something like that? Any idea how to go about this?

 

[The_Doc_Man]

The WHERE conditions go into the query that Pat wrote. Since you are filtering on the new field, but then want to show the same totals, it doesn't affect my code that much. When you open the recordset, you want to then distribute the results whether or not it is a filtered recordset.

The only thing that filtration MIGHT do is make the query return fewer records, in the case where your case number or person happened to not have ANY records for a particular value of Beslagtype. I.e., for a particular case it might have been that NO computer was involved at all. So you would have no records contributing to those sums. Which means you might have to pre-load a 0 into every one of the text boxes associated with the query in case it returned fewer than 13 types. THEN run that code to pull data from the query.

The issue of filtering on case number OR person means you could be facing a different issue, though. AND would have been easier but I understand that you need to filter what the job requires you to filter. I cannot tell you what you want to do and it seems like you are not yet comfortable with VBA.

The way that I have done this in the past is to manipulate some strings to make it into two or three parts. I have the "SELECT x, y, z FROM tablename GROUP BY fieldname" part as essentially a constant. Then I have a WHERE clause that starts with something I always use as a filter, like in my most recent case, "[Active]=True" because in my case we kept inactive things in the table until we had an archiving session and remove the inactive things in a single operation. (Don't know if you have something like that.)

Then when it came time to run the query, I created extra elements of the WHERE clause based on the selections in some combo boxes. So my code would concatenate an extra element to the already-formed "WHERE ( [Active] = TRUE) ", like

SELECT CASE Maker
    CASE "Dell"
        sqlWhere = sqlWHERE & " AND ( [Maker] = 'Dell' )"
    CASE "Acer"
        sqlWhere = sqlWHERE & " AND ( [Maker] = 'Acer' )"
    ... (other cases)
    CASE ELSE        'do not add anything if the selection is blank or null
END SELECT

... (other SELECT CASE clauses) ...

sqlQuery = sqlFirstPart & sqlWHERE
SET rsSQL = CurrentDB.Openrecordst( sqlQuery, dbOpenDynaset )

So in this case my code was adding criteria to the WHERE clause. I had six combo boxes that were involved in this particular query so I had six different SELECT CASE statements, each potentially adding one element to define the contribution to the WHERE clause.

You CAN open a named query in a recordset, but you could also build a dynamically defined SQL string, which is what I had to do for my situation. Works just as well.

And my question about what determines which Case is true I think i figured it out. The Case is the text value of the field "Beslagtype"?

Yes - the SELECT CASE <variable> statement is REALLY just a shorter way of writing an extended IF ... THEN ... ELSEIF ... ELSEIF ... ELSE ... END IF ladder. When you have only a couple of cases, no biggie, but the typing required for 13 ELSEIFs becomes tedious. The CASE statement replaces the ELSEIF <variable> = <value> THEN syntax. Otherwise it works the same. In fact, if you debug one of those and single-step through it, it steps through the cases until it finds a match, just as it would for an ELSEIF ladder.

 

[Pat Hartman]

The reason why I am so obsessed with getting all this information on 1 page with so many textboxes is because its more user friendly.

Getting to one page is reasonable and I've provided solutions that will do that. Insisting on doing it with individual text boxes is not reasonable. I'm done. Write all the unnecessary code you want to write. Make a maintenance nightmare for yourself with all that hard-coding others are doing for you. Hard-coding dynamic values is like jumping in one of those gerbil wheels. Run as fast as you want, thee are always new changes to be made. That is how spreadsheets work. It is NOT how relational databases work.