Solved

Help using query as record source for form

Posted on 2011-09-04
30
434 Views
Last Modified: 2013-11-29
Using Access 2007, the following query is the record source for a form

SELECT tblSelectionsCountertop.SelectionsMasterID, tblSelectionsCountertop.RoomID, tblSelectionsCountertop.CountertopID, tblCountertop.CountertopName, tblCountertopCat.CountertopCatName
FROM (tblSelectionsCountertop LEFT JOIN tblCountertop ON tblSelectionsCountertop.CountertopID = tblCountertop.CountertopID) LEFT JOIN tblCountertopCat ON tblCountertop.CountertopCatID = tblCountertopCat.CountertopCatID
WHERE (((tblSelectionsCountertop.SelectionsMasterID)=GetvSelectionMasterID()));

This works fine, except that my form has a txtbox for Countertop price.  This field is located in tblDevelopmentCountertop which has the following fields:

DevelopmentID
CountertopID
CountertopPrice
RoomID

Every time I try to modify the query to include this table, or a subquery based on this table, I lose the ability to add new records.

Can anyone enlighten me as to how to include tblDevelopmentCountertop.CountertopPrice in the query and retain the ability to add records?

BTW, I have a public function defined to retrieve the DevelopmentID, GetvDevelopmentID()
0
Comment
Question by:bwander
  • 12
  • 11
  • 5
  • +1
30 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36482027
The problem is that a third join to tblDevelopmentCountertop will make the query "non-updatable".  You can see this by looking at the query in datasheet view - the "new record" button will be disabled.

Take a look at this for a reference:
http://rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html

(Item 10 under the "A recordset is never updateable when..." heading)

I haven't tried this, but Roger's note says that if you change the form's RecordSetType property to "Dynaset (Inconsistent Updates)" this will work.

Try adding in the third table using a Left Join, set the form's RecordSetType property to "Dynaset (Inconsistent Updates)"  and see if that does the trick.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36483695
One more thought... with all those left joins (one to many relationships?) have you ruled out the possibility of using subforms rather than a single form with a complex recordsource query?

I'm not sure of your experience level, but this is a great beginners' tutorial on subforms:
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx
0
 

Author Comment

by:bwander
ID: 36484296
Thank you for responding.  I am definitely a beginner but I don't think a subform would be appropriate.  The form in question is actually a subform itself.

I think the Dynaset (Inconsistent Updates) is working, but now I'm having some refresh issues on two textbox controls on the form.  I'll try to work those out and report back if I get everything working.

The inconsistent updates part sounds really ominous.  Can you shed some light on what that could mean for the tables in question and the database in general?
0
 
LVL 75
ID: 36485108
""Dynaset (Inconsistent Updates)" this will work."
Sometimes it works ... *depending* ...on the various joins, relationships, etc.

bwander:
I would seriously rethink your recordsource.  Multiple tables is almost always ... problematic, as you are already seeing. Your goal s/b a single table or query based on a single table.  This is 'best practice'. Even if 'inconsistent updates' works, it's dangerous.  I can't think of any case wherein you cannot use a single table/query for the Recordsource.

mx
0
 

Author Comment

by:bwander
ID: 36485538
DatabaseMX:
Can you explain what you mean by dangerous?

Perhaps I could upload an example of what I'm doing and you could point me in a "better" direction?  Let me know if you would be willing to take a look and I"ll extract this form and the related tables from my DB.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36491490
<Can you explain what you mean by dangerous?>
1. Well, in your case, it just flat-out doesn't work...
;-)
2. As mbizup states, unless you are fairly good with multitable queries, you should try to avoid them.
3. If you set "Inconsistent Updates" and really don't know the consequences, you can inadvertently create *Inconsistent* (Key word here is *Inconsistent*) data.

4. I would rather troubleshot something like this:

SELECT CounterTopID, CoiunterTopName, CounterTopType, CounterTopPrice
FROM tblCountertops
..rather than this:
SELECT tblSelectionsCountertop.SelectionsMasterID, tblSelectionsCountertop.RoomID, tblSelectionsCountertop.CountertopID, tblCountertop.CountertopName, tblCountertopCat.CountertopCatName
FROM (tblSelectionsCountertop LEFT JOIN tblCountertop ON tblSelectionsCountertop.CountertopID = tblCountertop.CountertopID) LEFT JOIN tblCountertopCat ON tblCountertop.CountertopCatID = tblCountertopCat.CountertopCatID
WHERE (((tblSelectionsCountertop.SelectionsMasterID)=GetvSelectionMasterID()));
;-)

5. But for me, I just sometimes get twisted into knots trying to figure out the correct SQL to update one table, while simply "Looking Up" fields from other tables.

JeffCoachman

0
 
LVL 75
ID: 36491586
"Can you explain what you mean by dangerous?"

Sure. Say you have two tables, a main table and a 'lookup' table, joined as 1 to Many. Further, you have (inadvertently) a field on the Form bound to the primary key field in the lookup table, and this PK field is a Foreign Key in the main table - a typical scenario.  

IF ... on the form, you update this PK field, and assuming you have a Relationship between the main table and lookup table, with cascade updates set to yes ... *every* record in the main table with that PK value is going to get update, which is no doubt not what you would want.  There are other similar scenarios, especially with more than two tables.

So, the point is ... you have to be VERY careful on the form with what you are doing with different fields from different tables in the query.

Again, I highly recommend you rethink your design such that you have only one table/query for the recordsource.  And that's not really something I can do for you, not at all being familiar with the overall design.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36491671
<No Points wanted, as mbizup and MX have covered most of the main topics here...>

Just a not e here, from my own experience.
You may hear from "Elitist" or "Purest", that a query is the way to go.
...and if you "Know what you are doing" this is the best approach...

And that might very well be the case, ...If you were exceptionally good at SQL...
:-O

I'll to this perhaps with one table, but not more than this...
(and as MX states, even that can give you headaches if you don't get the keys just right.)

Mbizup has posted many solutions that simply use Dlookup to pull in related data, so she can probably explain this better than I can.

Again, you can exclude my posts from consideration for points.

Jeff
0
 

Author Comment

by:bwander
ID: 36491818
I appreciate all the comments and understand completely how using a table as a recordsource is much simpler and easier to understand what is happening.  I have also gathered from the reading I have done on this subject that there are those that think a query is the *always* the way to go.

Where I'm coming from, I have a 95% complete database with a main form and 10 subforms all using a query as the datasource.  The others are not as complex as this one, but of course all reference multiple tables and they work as they should.  I even understand what is happening ;)

What I hoped to get from posting this question was the one piece of brilliant understanding, that as a begineer I don't posses, but as ranked Geniuses (and I mean that respectfully) each of you do.

I'll take a chance and post the subform in question.  If anyone has the time to look at what I have and suggest a way to accomplish what I'm after I would appreciate it, even you end up telling me I can't do what  I want to do the way I'm trying to do it.
 countertopform.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36492867
Then it seems to me that you could use a Dlookup() as the controlsource for the Price textbox.

However, the Price depends of 3 factors (Development ID, CountertopID and RoomID)
Of the three, only two:  CountertopID and RoomID ...are present on the form.

Perhaps another Expert can take this further...?

BTW, the error when adding the Development table is "Ambiguous outer joins".
So you might want to take a closer look at this query that is the Form's recordsource...

Finally, note that you have no relationships defined between your tables
(Database Tools-->Relationships)
...so it is hard to tell if your design is properly "Normalized"

JeffCoachman
0
 

Author Comment

by:bwander
ID: 36493089
Is the issue of no defined relationships really an issue if I'm not concerned about cascading updates?  It seems I can define the relationships in the queries as needed.  Am I missing something?

I created the qryGetCountertopDetail to handle DevelopmentID which is found on tblSelectionsMaster, but in trying to add that as a subquery to the recordsource query is when I lost the ability to add new records to tblSelectionsCountertop.  I left it out to show you far I got (without populating the price txtbox) before I broke it.

Would you say there is no way to write the query as I'm envisioning it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36493362
<Is the issue of no defined relationships really an issue if I'm not concerned about cascading updates?>
Well there is more to that screen than just Cascade Updates...
Referential Integrity is the most important.

Creating the relationships there is also what is called an "Entity Relationship Diagram".

Not having this is like building a House or Car with no Blueprints,...you can do it, but if anything goes wrong, your screwed by not having anything documented.

This is also something that is crucial to the developer to not only see if the tables are properly normalized, but also to see the "Data/Program Flow"

As it stands now I don't know what each table represents or how/if it should be related to the other tables.
For example, I only really feel comfortable about the way tblCountertop and tblCountertopCat are defined.
I cant really be sure of any of the other tables, because it is not clear to me what they represent.

You also have SelectionMasterID defined as the Primary key in three tables...?
While tblDevelopmentCountertop has no primary key...
They may be other inconsistencies as well, but again, with no real understanding of waht the tables represent, it is hard to make any definitive statements here...


As far as creating a query that does what you want...
I'm sure it can be done at some level, so let's see what another Experts post.
As for me, if I can't "see" past the relationship/normalization issue, I would feel very uncomfortable about creating queries without having a solid idea of if the relationships are correct.
This may lead to the query working, but causing other issues in the DB somewhere else.

But again, perhaps I am over-thinking this, so let's see what some other Experts post.


JeffCoachman
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36494886
I looked at the database last night, and got a bit confused about your recordsource and reasoning behind it.  I had a few thoughts about it...

1.  Does the form, as-is (without the price field added in) do what you want it to do with out any unnecessary or undesired side effects?

When you add or change a record, you are editing data in multiple tables.  Is this necessary?  my understanding of that form is that it is intended to keep records of a user's counter top choice for any given room.  What is the minimal amount of data that you need to define these records?  Is it RoomID and CounterTopID?

2.  Do all of the feilds on that form really need to be editable?  Your combobox lists price and countertop type.  Selections then populate textboxes on your form (which duplicate the data in the combo box for a given selection).  Do these textboxes really need to be editable fields, or are they simply there to display to the user the other columns in the combo selection?  If the latter, then these textboxes can be filled (read-only) by setting their control sources to the relevant columns in the combo selection. ie:

= MyCombo.column(2) <--- this will display price info.

This data can be looked up at any point, and does not need to be stored.

If this describes what you are trying to do, you should be able to reduce your form's recordsource to a single table (the table that stores the customer's countertop selections), and lookup the other fields as needed, or use the combo box columns  to display data to the user through textboxes as described.        

In other words, data that simply needs to be displayed can be looked up at any time; only data that really needs to be edited should be bound to controls or present in your recordsource query.
0
 

Author Comment

by:bwander
ID: 36497853
1.  Does the form, as-is (without the price field added in) do what you want it to do with out any unnecessary or undesired side effects?

Yes

1a. When you add or change a record, you are editing data in multiple tables.  Is this necessary?

I don't see where I'm editing multiple tables?  tblSelectionsCountertop should be the only table edited by this form.

1b.  What is the minimal amount of data that you need to define these records?  Is it RoomID and CounterTopID?

Your understanding of the form is correct.  tblSelectionCountertop represents the minimal amount of data needed to define these records.

2.  Do all of the fields on that form really need to be editable?

No, only the combos.  Referencing the cbo.column(2) value will not work due to the use of "continuous forms"

<This data can be looked up at any point, and does not need to be stored.>

I don't see where I'm doing that!

0
 

Author Comment

by:bwander
ID: 36497968
boag2000:

These are related:
tblCountertop is a list of countertops
tblCountertopCat is a list of types of countertops
tblDevelopmentCountertop is a list of each countertop price depending on the room and development
{I did not include tblDevelopment, as I didn't think it was necessary}

tblRoom is a list of rooms, one field of which determines whether a countertop selection needs to be made

tblSelectionsMaster is the "Master" table whereby all other "tblSelections*" relate, via SelectionsMasterID
tblSelectionsCountertop - all countertop selections by room, for each SelectionsMasterID
tblSelectionsRoom - all Rooms related to a particular SelectionsMasterID

<While tblDevelopmentCountertop has no primary key...>
I went without a primary key on this table because the alternative is all fields other than price should be primary.  When I did that I got access errors relating to null value in primary key when I did a saverecord in the afterupdate event of my room combo

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36498024
mbizup, seems to have dug a bit further into this issue to address the issue at hand more directly.
So I will let you continue on with her, to avoid confusion.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36498064
1. <tblSelectionsCountertop should be the only table edited by this form.>
Ok.  Thats what I thought... however, the only fields that are in this table are SelectionsMasterID, RoomID and CountertopID.  Your form, because of the recordsource query has the text versions of these IDs pulled from other tables.  These fields are editable, which MAY lead to problems when records are added or edited. (These fields should not be editable and I don't think you need them in your query).

2. <Referencing the cbo.column(2) value will not work due to the use of "continuous forms">
The effect you are describing with all the values appearing the same is actually due to the use of unbound controls, not continuous forms.  That behavior is normal for unbound controls... it is just blatantly visible in continuous forms.

To get around this (while still using unbound controls), you can set the control source property directly on the property sheet rather than setting the value through VBA. If you reference the column on the property sheet, you will see distinct values for each record.


I'm going to take another look at it and get back to you a bit later.

0
 

Author Comment

by:bwander
ID: 36498182
I think we've actually arrived at the meat of the problem....that is how to display the correct CountertopCatName and CountertopPrice for each record.

I don't have a preference between query or lookup/reference, just want it to work :)
I was just lead to believe using a query was the best way to accomplish this, and I must say it works great on my other subforms.  Unfortunately, none of the other forms reference a table like tblDevelopmentCountertop, where there are three "primary" keys, they all just have two.

I did set the control source property on the property sheet, not through VBA and it did not work like you suggested, so maybe I'm still doing something wrong.

I appreciate you looking at this again.

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36498399
<that is how to display the correct CountertopCatName and CountertopPrice for each record.>

And the challenge in your particular form is that your combo box rowsources are dynamic... which rules out my earlier idea of referring to the column numbers.  However, I think that Dlookups would do the trick.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36528406
Sorry it's taken me this long to get back to you.  I haven't forgotten about this question; it's just that this turned out to be more complicated than I had initially thought.

While my initial thought of using lookups for this has worked for me in many applications it is not working properly in yours.  The complication is the dynamic rowsources of your comboboxes (which you obviously need for your application).  While looked up values work, each time the rowsource of your combos gets changes, there is a flash/blink in the lookup -- which makes for a really poor user interface.

Other options I have tried  all involve some trade off or the other, which is not good either.

I can dig into some other ideas if time is not of a concern for you - or if you want to get others involved, you can use the "Request Attention" button to ask the Mods to try to direct more traffic here.
0
 

Author Comment

by:bwander
ID: 36529373
I appreciate the effort.  I don't need it tomorrow but I also can't wait another month either.  Would the use of a temp table be appropriate?  What about having the developmentID in a hidden field on the form, like I have the SelectionMasterID?  Would either of these help create a workable query?



0
 
LVL 61

Expert Comment

by:mbizup
ID: 36552879
bwander,

Possibly...

Before going there, however, is the price of the countertop 'historically important' ?  By that I mean if the price of a Baltic Brown countertop changes, is it important for records of existing selections to retain the 'price paid' for the countertop (this is along the lines of keeping receipts to maintain a record of costs at the time of purchase)?  
0
 

Author Comment

by:bwander
ID: 36553011
no price history is needed.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36571227
Okay - I've looked into several different options, and don't think it is possible for you to get everything that you want out of that form the way you currently have it laid out:

- All three joins
- Records in continuous forms
- Dynamically changing rowsources in your combos ***  <-- this is what is really throwing the monkey wrench in the works
- All textboxes updating in a reasonably timely fashion
- No flickering or other unpleasant UI quirks

However, if you take a different design approach I think you can get the net results you need in a much simpler way.

This is a workaround I have used in similar situations at work:

- Limit the recordsource of the form to just the table whose fields need edited (tblSelectionsCountertop)
- Display the table data in the detail section of your continuous forms, and lock/disable control editing and form record additions
- Set the combo rowsources in the detail section to have no criteria.  This ensures that the text is seen for all records at all times.  (With dynamically updating criteria, the text sometimes 'goes missing' if the stored record is not included in a combo's criteria).  This approach eliminates the need for the layered textboxes you currently have to display the text.
- Do all data entry through unbound controls in the form's header section, using a "Save" button to run an insert query.  The unbound combo boxes should have your dynamic rowsources, so that the user is restricted to certain countertops for certain rooms.

That's basically it... try out the uploaded sample.

I realize that this form is actually a subform in your real database, so this may complicate how it gets integrated into your main form a little bit.  However, it is doable, and I think for handling your data in this particular form that this is the simplest and best approach.
countertopform.accdb
0
 

Author Comment

by:bwander
ID: 36571234
Thanks for taking the time to develop this.  I'll take a look and let you know.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36571250
Oh... I forgot the most important point (the topic of this question)!

Those textboxes that display the counter type and price are tied to columns 2 and 3 of your combobox through their control source properties.  That is a very quick and simple no-code approach to getting distinct values in unbound controls on continuous forms -- and it is what allows you to reduce the recordsource of that form to a single table.
0
 

Author Comment

by:bwander
ID: 36571313
When I start with no selections (delete any previous), the Type and Price fields do not update after making a room and counter selection.  I tried adding Me.text19.Requery and Me.text20.Requery but that did not work.  On subsequent entries those text fields populate.

In the detail section of the form, the price is always the same regardless of the actual selection price.

I'll play with it and try to figure out why that is, unless it's working for you for some reason, but the design looks promising.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36572819
<In the detail section of the form, the price is always the same regardless of the actual selection price.>

I hadn't noticed that with the examples I quickly tested, but am seeing that now.  The reason for that is that your CounterTopID in tblDevelopmentCountertop is not unique.  A DLookup will work with your existing table structiure:

=CDbl(nz(DLookUp("CountertopPrice","tblDevelopmentCountertop","CountertopID = " & [CountertopID] & " AND RoomID =" & [RoomID]),0))

Open in new window


Use that as the control source for the Price textbox in the detail section (leave the control source of the header section textbox as-is).

That said, if you plan to develop this database much more, you should consider taking a step back and restructuring this and other tables to include unique primary keys.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36572844
<< When I start with no selections (delete any previous), the Type and Price fields do not update after making a room and counter selection. >>

Those lookups seem to be expecting your form to be populated.  I'm not sure why off-hand, or what to do about it.

I'll put some more thought into that one...

A clunky workaround is to simply hide/get rid of the textboxes in the header section, since the user sees those values when making a selection in the combo and then immediately upon saving the record, when those details appear in the form's detail section.
0
 

Author Closing Comment

by:bwander
ID: 36577986
Thanks to everyone for very informative input.  Mbizup obviously earned the points on this after taking the example and reworking it.

I have done some initial work in getting this new form integrated into the main DB and I know it will work as intended with some tweaking.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now