Appending last entry Access 2000

[don]

Hi Group,

Hope someone can help. I have an address Db with a user route that
checks to see if a person is in the Db if not it goes on to search the
address to tie the user to, if the address is not found (new
development) the search criteria info is appended to a possible new
address table for checking before migration into the main gazetteer.
The bit I'm stuck on is being able to update an alternate address ID
in the contacts table with an auto number created when I appended the
new address.

So far I have tries using an append query to get the new address into
the table and a select last query to find last id in table but am
unable to apply the result of this in an update query to the contacts
table.

I hope that makes sense.

Any help either with this or a better solution would be appreciated.
I am still pretty much a novice with this.

Many thanks

DonH

 

[aaron_kempf]

I personally-- don't think that you're really doing that bad.

I'd give us your queries; that should help.
I just think that you're on the right track.

If that is what you want to do-- send us the SQL Statements and we'll
help you out.

For an 'action query' if you want to see the SQL, select 'SQL View'
from the <VIEW> menu.

-Aaron

 

[don]

I personally-- don't think that you're really doing that bad.

I'd give us your queries; that should help.
I just think that you're on the right track.

If that is what you want to do-- send us the SQL Statements and we'll
help you out.

For an 'action query' if you want to see the SQL, select 'SQL View'
from the <VIEW> menu.

-Aaron









- Show quoted text -

Aaron,

Thanks for your reply.

I've done a little more work on this since I wrote and found a
different solution but would still like to know if I can do this. So
far I used

INSERT INTO NewAddresses ( [House Number], Road, Town )
SELECT [Forms]![Form1]![Text0] AS Expr2, [Forms]![Form1]![Street] AS
Expr1, [Forms]![Form1]![Town] AS Expr3;


To add my sample search criteria into a new table (new address/address
not found). This works fine

I then used

SELECT Max(NewAddresses.AutoID) AS MaxOfAutoID
FROM NewAddresses;

To find the last AutoID in the New Addresses table to act as my
foriegn key in the contacts table. That worked fine

I then tried to use

UPDATE Contacts SET Contacts.[Alternate Address ID] =
"Max[NewAddresses]![AutoID]";

Which didn't

To get around this I've used an update query and inserted a
concatenated text string of the address into the contacts table. This
is executed at the same time as the update to the new addresses table.

Thanks for your help

Regards

Don