vba - query

[verdes]

I'm using access 2007

i have a query that joins 2 tables - 1 table has the FK of the other table in it.

Here is the query in sql

SELECT address_tbl.*, abatement_tbl.*
FROM address_tbl INNER JOIN abatement_tbl ON address_tbl.addressID = abatement_tbl.abateAddrID;

When I view it in datasheet view it shows the correct data and list the new row for both tables.

If I add an existing addressID in the abateAddrID on the "new" row, the details for the corresponding address record are populated in that query row.

I want to duplicate that behavior using a form based on that query.

Currently, if I open the form new it automatically updates the addressID to the next autonumber....

Is there anything I can do to prevent that from happening? I want the user to be able to build a new query record by selecting an existing address.

 

[mdlueck]

When I view it in datasheet view it shows the correct data and list the new row for both tables.

I want to duplicate that behavior using a form based on that query.

Forms act a bit more true to what the SQL says than GUI query objects. A SELECT query is simply that, SELECT records which are already in the table. If you want to add a record, you need a SQL INSERT to do that / change a record, you need a SQL UPDATE to do that.

It is pure voo doo that Access GUI allows you to add/edit when only a SQL SELECT has been issued. Beginners luck. That runs out when you graduate to the VBA world.