Help using query as record source for form

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.

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

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?

""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

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.

<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

"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

<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

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

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

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?

<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

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.

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!

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

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.

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.

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.

<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.

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.

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?

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)?  

no price history is needed.

Thanks for taking the time to develop this.  I'll take a look and let you know.

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.

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.

<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))

Select allOpen 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.

<< 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.

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.