cant update recordset thru form when form is built on SQL statemen

[Guest]

Hello,

i have built a form which was based on a table. when i did this, i was able
to write data directly into the table. i needed a look-up description added
to the form so i used the query builder to add another table with the
look-up. now the form will not allow any records to be updated. i read that
forms built off of queries will not allow edits or updates in most cases,
however i have done this before on a similar database and that one works
fine. i know that i'm probably missing something small. i have pasted my SQL
statement below. i would appreciate any input.

SELECT [MAIN TABLE].[BID NO], [MAIN TABLE].YEAR, [MAIN TABLE].[TTL BID$],
[MAIN TABLE].[TTL WON$], [MAIN TABLE].[MARGIN$], [MAIN TABLE].[CONTINGENCY$],
[MAIN TABLE].[REVENUE$], [MAIN TABLE].[TTL MHR], [MAIN TABLE].[MHR RATE$],
[MAIN TABLE].[TTL DIR LBR$], [MAIN TABLE].[EST MHR], [MAIN TABLE].GROUP,
[MAIN TABLE].[W L], [MAIN TABLE].[LOW BIDDER], [MAIN TABLE].RANK, RANK.[Rank
Description], [MAIN TABLE].OWNER, [MAIN TABLE].DESC, [MAIN TABLE].LOC, [MAIN
TABLE].TYPE, [MAIN TABLE].[PROJ COMPL], [MAIN TABLE].[CONTR TYPE], [MAIN
TABLE].NOTES FROM [MAIN TABLE] LEFT JOIN RANK ON [MAIN TABLE].RANK=RANK.Rank
ORDER BY [MAIN TABLE].[BID NO];

i would appreciate any input.
thanks

 

[John Vinson]

Hello,

i have built a form which was based on a table. when i did this, i was able
to write data directly into the table. i needed a look-up description added
to the form

A Lookup field is not necessary. If you're just looking up a single
value, one possibility is to use a Combo Box on the form. Set its
bound column to your table's field and its first visible column to the
field value you want looked up - the user will see the meaningful
value (Rank Description), the computer will see the cryptic ID [RANK],
and both will be happy.

so i used the query builder to add another table with the
look-up. now the form will not allow any records to be updated. i read that
forms built off of queries will not allow edits or updates in most cases,

That is INCORRECT. Simple two-table joins usually *are* updateable.

however i have done this before on a similar database and that one works
fine. i know that i'm probably missing something small. i have pasted my SQL
statement below. i would appreciate any input.

SELECT [MAIN TABLE].[BID NO], [MAIN TABLE].YEAR, [MAIN TABLE].[TTL BID$],
[MAIN TABLE].[TTL WON$], [MAIN TABLE].[MARGIN$], [MAIN TABLE].[CONTINGENCY$],
[MAIN TABLE].[REVENUE$], [MAIN TABLE].[TTL MHR], [MAIN TABLE].[MHR RATE$],
[MAIN TABLE].[TTL DIR LBR$], [MAIN TABLE].[EST MHR], [MAIN TABLE].GROUP,
[MAIN TABLE].[W L], [MAIN TABLE].[LOW BIDDER], [MAIN TABLE].RANK, RANK.[Rank
Description], [MAIN TABLE].OWNER, [MAIN TABLE].DESC, [MAIN TABLE].LOC, [MAIN
TABLE].TYPE, [MAIN TABLE].[PROJ COMPL], [MAIN TABLE].[CONTR TYPE], [MAIN
TABLE].NOTES FROM [MAIN TABLE] LEFT JOIN RANK ON [MAIN TABLE].RANK=RANK.Rank
ORDER BY [MAIN TABLE].[BID NO];

If the combo box doesn't solve the problem, just be sure that [RANK]
is the Primary Key of the Rank table, and (since it's ambiguous, being
the name of one table and two fields) always enclose it in square
brackets. Do you really need a Left Join? This would show NULL values
for all records in the Rank table which don't happen to have a [MAIN
TABLE] entry. If it's just intended to look up the [RANK DESCRIPTION]
change the LEFT JOIN to INNER JOIN.

John W. Vinson[MVP]

 

[Guest]

Thank you John.
Assigning the primary key in the rank table did the trick. It is this type
of small problem that always seems to eat up the clock hours and I really
appreciate your help in solving it.


--
dale

Hello,

i have built a form which was based on a table. when i did this, i was able
to write data directly into the table. i needed a look-up description added
to the form

A Lookup field is not necessary. If you're just looking up a single
value, one possibility is to use a Combo Box on the form. Set its
bound column to your table's field and its first visible column to the
field value you want looked up - the user will see the meaningful
value (Rank Description), the computer will see the cryptic ID [RANK],
and both will be happy.

so i used the query builder to add another table with the
look-up. now the form will not allow any records to be updated. i read that
forms built off of queries will not allow edits or updates in most cases,

That is INCORRECT. Simple two-table joins usually *are* updateable.

however i have done this before on a similar database and that one works
fine. i know that i'm probably missing something small. i have pasted my SQL
statement below. i would appreciate any input.

SELECT [MAIN TABLE].[BID NO], [MAIN TABLE].YEAR, [MAIN TABLE].[TTL BID$],
[MAIN TABLE].[TTL WON$], [MAIN TABLE].[MARGIN$], [MAIN TABLE].[CONTINGENCY$],
[MAIN TABLE].[REVENUE$], [MAIN TABLE].[TTL MHR], [MAIN TABLE].[MHR RATE$],
[MAIN TABLE].[TTL DIR LBR$], [MAIN TABLE].[EST MHR], [MAIN TABLE].GROUP,
[MAIN TABLE].[W L], [MAIN TABLE].[LOW BIDDER], [MAIN TABLE].RANK, RANK.[Rank
Description], [MAIN TABLE].OWNER, [MAIN TABLE].DESC, [MAIN TABLE].LOC, [MAIN
TABLE].TYPE, [MAIN TABLE].[PROJ COMPL], [MAIN TABLE].[CONTR TYPE], [MAIN
TABLE].NOTES FROM [MAIN TABLE] LEFT JOIN RANK ON [MAIN TABLE].RANK=RANK.Rank
ORDER BY [MAIN TABLE].[BID NO];

If the combo box doesn't solve the problem, just be sure that [RANK]
is the Primary Key of the Rank table, and (since it's ambiguous, being
the name of one table and two fields) always enclose it in square
brackets. Do you really need a Left Join? This would show NULL values
for all records in the Rank table which don't happen to have a [MAIN
TABLE] entry. If it's just intended to look up the [RANK DESCRIPTION]
change the LEFT JOIN to INNER JOIN.

John W. Vinson[MVP]