Form based on a query

[PDLG]

I have a form that is based on a query. The query that it is based on
consists of two tables joined by a project number field.

The project number field is the first field on the form and it is a
users can select a value based on a drop down box:

SELECT qry_Proj_Lookup.EDPN, qry_Proj_Lookup.Client,
qry_Proj_Lookup.ProjectName FROM qry_Proj_Lookup;

When I open the form, I can edit the "Project Number" field in any
existing record using the drop down box but . . . when I try to create
a new record and select the project number from the drop down, I get
the error ping noise and it will not allow me to select any value.

 

[Jeff Boyce]

Queries that join multiple tables may not always be updateable.

Can you confirm that the underlying query is, itself, updateable?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[PDLG]

Queries that join multiple tables may not always be updateable.

Can you confirm that the underlying query is, itself, updateable?

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Thanks for responding.

Looks like no since I get the exact same results in the query -- I can
update existing records by using the drop down but cannot create a new
record. I'm stumped. I need the form to have fields from two separate
tables displayed but I also need people to be able to add new
records. I'm pulling my hair out.

 

[John W. Vinson]

Thanks for responding.

Looks like no since I get the exact same results in the query -- I can
update existing records by using the drop down but cannot create a new
record. I'm stumped. I need the form to have fields from two separate
tables displayed but I also need people to be able to add new
records. I'm pulling my hair out.

You need to DISPLAY fields from two tables - but do you in fact need to
include both tables in the query? What is the sql of qry_Proj_Lookup?

A two table query *can* be made to allow insertion or records into both
tables; you need to have an Autonumber field in the parent table, and include
both that field and the foreign key field from the other table. Row Fixup will
pop a new autonumber and insert it into the other table. But... generally it's
not necessary to do this; you can have a Combo Box on the form which *stores*
a value (a foreign key typically) in the form's recordsource table while
displaying a field from the lookup table.


John W. Vinson [MVP]