Newb Question Perhaps - Make Form Recordsource field editable

[DB via AccessMonster.com]

Using VBA I have set the Recordsource of the form to pull someinfo from two
different tables. The thing I cannot figure out has to be a small thing but
I have changed up all of the form and field properties that I can and it
won't let me type in the field to update the tables that the info comes from.
Any help is appreciated. Thank you.

 

[Douglas J. Steele]

It's likely your query isn't updatable.

Check Allen Browne's checklist at http://www.allenbrowne.com/ser-61.html

 

[dustinbrearton via AccessMonster.com]

Thank You for your help.

So my query is read-only for a couple of those reasons. This is the only way
I can find to get the correct results though. What would the best way to
overcome this problem? Would having the query create a temporary table and
use that temp table as a recordsource and at the end write the three fields
that I am trying to update back to the main table and delete the temp table?

Group By clause
Joins are not indexed correctly; there is no primary key on the joined fields.



SELECT tblHouseFinalScriptInfo.FileNumber, tblHouseFinalScriptInfo.
ConClientNo, tblHouse.[Consignee Client Name]" & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number]" & _
"GROUP BY tblHouseFinalScriptInfo.FileNumber, tblHouse.[Consignee Client Name]
, tblHouseFinalScriptInfo.RevClientNo, tblHouseFinalScriptInfo.BillClientNo,
tblHouseFinalScriptInfo.ConClientNo " & _
"HAVING (((tblHouse.[Consignee Client Name]) Like '*TO*' And (tblHouse.
[Consignee Client Name]) Like '*ORDER*') AND ((tblHouseFinalScriptInfo.
RevClientNo) Is Null) AND ((tblHouseFinalScriptInfo.BillClientNo) Is Null)
AND ((tblHouseFinalScriptInfo.ConClientNo) Is Null));"

It's likely your query isn't updatable.

Check Allen Browne's checklist at http://www.allenbrowne.com/ser-61.html

Using VBA I have set the Recordsource of the form to pull someinfo from
two

[quoted text clipped - 4 lines]

from.
Any help is appreciated. Thank you.

 

[Douglas J. Steele]

There's definitely no way to make a Group By query updatable. How would you
know which of the individual rows should be changed?

Even creating a temporary table and updating that will probably have the
same problem.

Incidentally, your query would be better using a Where clause than a Having
clause:

SELECT tblHouseFinalScriptInfo.FileNumber,
tblHouseFinalScriptInfo.ConClientNo, tblHouse.[Consignee Client Name]" & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number]" & _
"WHERE (((tblHouse.[Consignee Client Name]) Like '*TO*' And
(tblHouse.[Consignee Client Name]) Like '*ORDER*') AND
((tblHouseFinalScriptInfo.RevClientNo) Is Null) AND
((tblHouseFinalScriptInfo.BillClientNo) Is Null) AND
((tblHouseFinalScriptInfo.ConClientNo) Is Null))" & _
"GROUP BY tblHouseFinalScriptInfo.FileNumber, tblHouse.[Consignee Client
Name], tblHouseFinalScriptInfo.RevClientNo,
tblHouseFinalScriptInfo.BillClientNo,tblHouseFinalScriptInfo.ConClientNo " &
_

Where clauses are evaluated before the accumulation takes place, Having
clauses after. If you can eliminate rows before the accumulation, the
accumulation will be faster. The only point for Having clauses is when
you're trying to base selection criteria on an accumulated value. (Yes, I
know the query builder does it your way. That doesn't make it right!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank You for your help.

So my query is read-only for a couple of those reasons. This is the only
way
I can find to get the correct results though. What would the best way to
overcome this problem? Would having the query create a temporary table
and
use that temp table as a recordsource and at the end write the three
fields
that I am trying to update back to the main table and delete the temp
table?

Group By clause
Joins are not indexed correctly; there is no primary key on the joined
fields.



SELECT tblHouseFinalScriptInfo.FileNumber, tblHouseFinalScriptInfo.
ConClientNo, tblHouse.[Consignee Client Name]" & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON
tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number]" & _
"GROUP BY tblHouseFinalScriptInfo.FileNumber, tblHouse.[Consignee Client
Name]
, tblHouseFinalScriptInfo.RevClientNo,
tblHouseFinalScriptInfo.BillClientNo,
tblHouseFinalScriptInfo.ConClientNo " & _
"HAVING (((tblHouse.[Consignee Client Name]) Like '*TO*' And (tblHouse.
[Consignee Client Name]) Like '*ORDER*') AND ((tblHouseFinalScriptInfo.
RevClientNo) Is Null) AND ((tblHouseFinalScriptInfo.BillClientNo) Is Null)
AND ((tblHouseFinalScriptInfo.ConClientNo) Is Null));"

It's likely your query isn't updatable.

Check Allen Browne's checklist at http://www.allenbrowne.com/ser-61.html

Using VBA I have set the Recordsource of the form to pull someinfo from
two

[quoted text clipped - 4 lines]

from.
Any help is appreciated. Thank you.

 

[John W. Vinson]

Thank You for your help.

So my query is read-only for a couple of those reasons. This is the only way
I can find to get the correct results though. What would the best way to
overcome this problem? Would having the query create a temporary table and
use that temp table as a recordsource and at the end write the three fields
that I am trying to update back to the main table and delete the temp table?

Group By clause
Joins are not indexed correctly; there is no primary key on the joined fields.

Why are you using a Group By at all? Totals queries are used when you want to
Sum, or Count, or Average across records, and it does not appear that you are
doing so. Might you be able to use a simple SELECT query such as the
following? I added some blanks to keep strings from runningtogetheratlineends:

"SELECT tblHouseFinalScriptInfo.FileNumber, tblHouseFinalScriptInfo.
ConClientNo, tblHouse.[Consignee Client Name] " & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number ]" & _
"WHERE (((tblHouse.[Consignee Client Name]) Like '*TO*' And (tblHouse.
[Consignee Client Name]) Like '*ORDER*') AND ((tblHouseFinalScriptInfo.
RevClientNo) Is Null) AND ((tblHouseFinalScriptInfo.BillClientNo) Is Null)
AND ((tblHouseFinalScriptInfo.ConClientNo) Is Null));"

Note that if you have a client name such as "Torrent Of Disorder" it will pass
this criterion. If you are looking for isolated *words* "To" and "Order"
you'll need a more complex criterion; searching for text strings embedded in
names is always ambiguous and prone to such problems!

 

[dustinbrearton via AccessMonster.com]

John,
That query does work. To answer your question though the reason I
am using grouping (maybe not be the right reason) is that I have duplicate
file numbers in tblHouse. tblHouseFinalScriptInfo will only have one
occurance of the file numbers. Is there a better way to only show one
instance of each file number than by grouping. I suppose it would be
possible to just use the output from this query but then the user would need
to enter the same info multiple times or know that they only have to fill out
each file number one time.

Thank You for your help.

[quoted text clipped - 6 lines]

Group By clause
Joins are not indexed correctly; there is no primary key on the joined fields.

Why are you using a Group By at all? Totals queries are used when you want to
Sum, or Count, or Average across records, and it does not appear that you are
doing so. Might you be able to use a simple SELECT query such as the
following? I added some blanks to keep strings from runningtogetheratlineends:

"SELECT tblHouseFinalScriptInfo.FileNumber, tblHouseFinalScriptInfo.
ConClientNo, tblHouse.[Consignee Client Name] " & _
"FROM tblHouseFinalScriptInfo LEFT JOIN tblHouse ON tblHouseFinalScriptInfo.
FileNumber = tblHouse.[File Number ]" & _
"WHERE (((tblHouse.[Consignee Client Name]) Like '*TO*' And (tblHouse.
[Consignee Client Name]) Like '*ORDER*') AND ((tblHouseFinalScriptInfo.
RevClientNo) Is Null) AND ((tblHouseFinalScriptInfo.BillClientNo) Is Null)
AND ((tblHouseFinalScriptInfo.ConClientNo) Is Null));"

Note that if you have a client name such as "Torrent Of Disorder" it will pass
this criterion. If you are looking for isolated *words* "To" and "Order"
you'll need a more complex criterion; searching for text strings embedded in
names is always ambiguous and prone to such problems!

 

[John W. Vinson]

John,
That query does work. To answer your question though the reason I
am using grouping (maybe not be the right reason) is that I have duplicate
file numbers in tblHouse. tblHouseFinalScriptInfo will only have one
occurance of the file numbers. Is there a better way to only show one
instance of each file number than by grouping. I suppose it would be
possible to just use the output from this query but then the user would need
to enter the same info multiple times or know that they only have to fill out
each file number one time.

If you have a one (tblHouseFinalScriptInfo record) to many (file number)
relationship, then - rather than trying to construct some monster query to
update records - I'd use a Form for the "one" side of the relationship and a
Subform for the Many. This will let you see the "one" record once, and the
"many" records as many as there need to be, without duplication and without
any barrier to editing either one.

If you're working in table or query datasheets to enter or edit data... well,
don't; the problems you're having are one good reason not to.