Is Dlookup Sooo Bad?

[Dal]

I know it gets a bad rep but I'm new to queries & what I don't like about them is it seems I could end up with amass of queries where dlookup would avoid these. I know they are faster but I am concerned I will end up with so many; which I'll never be able to keep track of. No doubt I'm missing something essential but simplifying the below as it is greatly more complex but I'm wondering if my Schema is correct in trying to simplify the process as rates in resources can change out of scope from when the Activities are created; resulting in incorrect rates used:

• tblResources[Rate] has various resources with their rates & feeds below:
• tblActivites[Rate] . Contains the total of it's subTable below
• tblActivityComposition contains different combinations of tblResources[Rate] which produce the total to be used; governing everything (I'm not sure whether to derive the total from vba code looping recordsets/ query...) or change the schema...?

My main concern is the tblResources[Rate] can change when the tblActivityComposition changes. New items can be added/ removed/ updated. I can think of ways to do it with VBA updating the total of the tblActivity[Rate] but my design cannot be that great if I'm having to do this?

Appreciate info is ltd but it borders on the subject of historical price tracking where the rare exception of redundant data is permitted (technically not redundant data). I understand the best way around this is to use queries, so they refresh... that has it's own downsides as they only produce values when run. Not to mention different events firing all over the place opening different forms and recordsets all over the place that would be susceptible to clash. That's the best example I can give as the main topic is more complex. Hopefully someone gets my gist?

[Edgar]

Hello.

Your table names are a concern if they have square brackets, get rid of the square brackets.
DLookups are not bad, over-optimizing early in the development is. If you see performance issues, then consider the queries.
You seem to be doing construction stuff, there are so many ways to approach this situation that it's hard to tell whether it is a bad schema or not by just looking at three table names.

[ArviLaanemets]

I can't see any way DLOOKUP() would be helpful for you (it will return the value from single record from target table)! Maybe DSUM() or DCOUNT() will be usable, but it will slow down your app even more!

And what is your problem with queries?
NB! you create saved queries only when you really need them. Mostly you use queries as sources for reports (defining them directly, when creating report), sources for forms (again defining them directly, when creating form), or in events (where you define and run them directly in event script). And in case you need to use a saved queries (e.g. when yo can use the same query as source of several reports), try to create them as general as possible, and when calling them, use WHERE clause to limit the scope of returned records to needed ones only.

And allow direct access to tables and queries only when designing your app! Forms and reports is all, everyday users must have access to!

[CJ_London]

you should also not be storing totals (tblActivities[Rate]) - calculate as and when required.

that has it's own downsides as they only produce values when run

agreed, they need to 'run' but you can still return a single record/value using parameters and they don't need to be 'run' in the sense of opening a query, you can just reference it- and think of the time you save not having to write routines (and execute them) to update the total every time there is a change - plus perhaps verifying the total is correct and all updates have been applied.

Can't comment on the schema - as Edgar says, would need to see what you actually have and the purpose of the app - what it is intended to do.

[xps35]

There is little meaningful to say about your design if we don't know it at all.

[Dal]

Thanks for your replies, tblName[FieldName].

agreed, they need to 'run' but you can still return a single record/value using parameters and they don't need to be 'run' in the sense of opening a query, you can just reference it

Yes but correct me if I'm wrong; each definition of the query would need to be defined; resulting in lots of queries. The parameters of the query are in the definition of the query itself? Pretty sure I'm running them already by passing comboxes as parameters... You can create subqueries off the main query to avoid reinventing the wheel, but the query must be defined. Comboboxes seem to be a nice way to avoid cluttering the Object Browser; shame you cannot create a query directly relating to a text box... on a form.

Populate A Record's TextBox On A Form From A Query
How to populate the textbox of a record with the result of a subquery? There seems no way to populate a text box on a form; where the form's recourdsource is not the query. Referencing the field of the query in the control source, does not work... nor should it I imagine as this is the property to where data is written; not retrieved. It seems a text box is missing a recordsource property. Is Dlookup my only option when trying to match a record on a form? I've explained the relationship of the tables which should suffice for the below:

• In the frmActivityComposition I have several nested comboboxes. A subquery returns a Rate for that particular resource which is displayed in an unbound textbox; for purposes to see what the intermediary totals are. But I cannot figure out how to show that value from the query in that textbox for that particular record. A Dlookup works perfectly in obtaining that record's rate with a match to the ID on the form but I cannot understand how to reference a query's result on the form.
• I'm going to end up with several queries alone for this single form which just seems unmanageable. Unless it is possible to reference different fields of that query matching to a record? If so - how? This must be possible.
• I am trying to avoid setting the recordsource of the entire form for the minority of controls not referencing the query - a continuous form btw; making things harder. Which will later be a subform itself but this is another topic; forewarning in case of further restrictions.

Appreciate insufficient info for schema so forget that subject.

[CJ_London]

shame you cannot create a query directly relating to a text box... on a form

.

Would need to see an example of what you mean - you could use a listbox (or combo) instead of a textbox to return a single record.

I suspect you are making life difficult for yourself with a poorly designed schema and/or form(s)

if you have a combo that has a rowsource which contains (or could contain) the value(s) you want, bring it through on the combo, hide the column and in your textbox use =mycomboname.column(X) where X is the column number (which starts from 0)

I use this method for example for the user to select a customer to be invoiced and the controls that display things like address, phone, contact etc are populated by referencing the relevant column in the customer selection combo.

[Micron]

I knew a guy who made tons of queries, one for each time he wanted to know something, and usually 99% of it was the same. They were a huge mix of date spans, departments and the like. That's what he did instead of building one or more forms and feeding a single query with all those parameters. I suspect that's the road you're on. If you want an analysis of your db tables and relationships consider posting a compacted and zipped copy, but do explain the process it is supposed to support. Note: you cannot copy/paste files here. See How to Attach Files at the top of this page.

[Dal]

.Would need to see an example of what you mean - you could use a listbox (or combo) instead of a textbox to return a single record.

Thanks but this was not the question asked:

Thanks for your replies, tblName[FieldName]
Populate A Record's TextBox On A Form From A Query
How to populate the textbox of a record with the result of a subquery? There seems no way to populate a text box on a form; where the form's recourdsource is not the query.

I knew a guy who made tons of queries, one for each time he wanted to know something, and usually 99% of it was the same. They were a huge mix of date spans, departments and the like. That's what he did instead of building one or more forms and feeding a single query with all those parameters. I suspect that's the road you're on.

Thanks, if we just concentrate on the ActvComposition atm so i can grasp. The Rate needs to be from a query so it is always the most recent rate used. It is the result of the 3 nested comboboxes.
The total (not a stored result but a query) will be dependant on whether the CoEfficient or Output is populated & this is a good tut on that.
I'm struggling with how to get the query result into the form. It seems I'm populating from the form, then running a query & need to return that back to the form. Unsure on the best process here, would really appreciate some guidance? If it was not for the comboboxes on the form in my mind I would set the recordsource of the form to the query.

[CJ_London]

I cannot make sense of what the OP is asking. Looks like it should be a fairly standard 'recipe' type structure, but isn't. Can see why a listbox is not a solution since the form is a datasheet. Don't think it is worth suggesting using a continuous form designed to look like a datasheet.

The rate required would appear to be a labour rate, but the only rate that appears is for material. Also no indication whether the datasheet form needs to be updateable.

Without seeing the actual structure and relationships and a clear description of how the rate is intended to be calculated, I don't believe I can contribute anything worthwhile.

So good luck with your project, but I'm out.

[Dal]

No probs, appreciate info is ltd, cannot post the blueprints as itself is quite valuable, thank you for your input thus far; much appreciated.

[Micron]

Maybe a pic of your table relationships (if you created them) would shed some light.

[NoellaG]

To return to the original question: IMO Dlookup is not bad if used to populate a text box on a form or report. (of course 20 lookups on a form with 10000 recs would be pushing it ) Domain functions are bad in queries because they have to be executed for each row in the query. So if you have a query retrieving 100 records with a dlookup that searches in 100 recs you would have to scan 10000 recs each time.

[June7]

I have experienced noticeable delay in form refresh when navigating records with DLookup in textbox and it didn't take 20 lookups to get that behavior.

I have a good size database with dozens of DLookup in VBA and I don't notice performance issue there.

By "nested" comboboxes do you mean cascading?

Would Rate be from the same table as ResLiblDDescription?

Generally, it is best to avoid duplicating data between tables. However, duplication can be justified in cases where a value can change over time, such as product prices and service rates. The options are to either not save and create a new record in lookup table every time value changes and flagging old records as "inactive" OR save value into data so when lookup table record changes historical data does not.

CJ, I see two forms in image, one for Labour and one for Material, each has Rate.

[CJ_London]

CJ, I see two forms in image, one for Labour and one for Material, each has Rate

yes, but one has a #name value - I assume that is what the OP is trying to fix. That rate is against a labour type cost whereas the rate in the other form is a material rate.