Automatically add new record in form

  • Thread starter Thread starter Trev Clarke via AccessMonster.com
  • Start date Start date
T

Trev Clarke via AccessMonster.com

Hi there,

I have a main form based on a table where the key is a week-ending date.
I have added control buttons (not the standard navigation buttons) that
allow the user to move backwards and forwards through the records.

If the form is at the first record and the user clicks the 'Previous Week'
button, a MsgBox pops up and asks if they want to add a record for the
previous week.

What I seem to be unable to do is, if they respond 'Yes', to add the new
record via vb code and have the form go to that new record.

I have tried various combinations of AddNew, GoToRecord, INSERT INTO,
Refresh, Requery, etc - so much so that I'm not sure whether I know my a**
from my elbow. But the form always seems to throw up an error saying that
it can't find the new record.

This is using Access 97 by the way.

Any assistance would be gratefully received. I hope I've included all the
important information.

Cheers,

Trev.
 
are you wanting the user to *complete* the new record, once s/he goes to it?
if so, have you tried setting a command to "go to new record", then setting
the value of the key field (i'm assuming that you're calculating that value
rather than letting the user enter it). then you could either save the
record immediately, or allow the user to either complete and save it, or
cancel it.
you should be able to do the above using either a macro or VBA.

hth
 
Thanks so much for replying.

Although your reply didn't give me the exact answer, it started me thinking
about how I was trying to do it (so I'd be able to explain it to you). I've
spent all evening trying things out - which is why I haven't replied sooner.

I thought I was trying to make things simple and efficient by using
positional data (like CurrentRecord) to move around within the recordset.
Although this was working fine with the records that already existed, it
was confusing itself when a new record was added.

I've changed this now so that, after adding the new record, it does a
Requery on the main form and then a Find on the key of the new record. This
seems to be working much better - (i.e. working, I guess, which is much
better than not working). As a bonus, it also seems to have forestalled the
next problem I was expecting (there is a subform and I add the relevant
subform records to their table before I do the Requery and Find on the main
form and, crikey, it works).

I'm not sure it's the best way to do it and whether there might not be a
way to use record numbers, etc that is robust and understandable, but, Hey,
it's nice to get progress :o)

At least until the next brick wall I run into.

Thanks so much.

Trev.
 
Oh yes, a short PS.

This one is more of an irritation than a showstopper.

Having run many tests to get the records added correctly naturally involves
me deleting the new records again between tests. This is giving me an error.

The records are deleted ok (directly in table view), but then when I run
the form again and try to add the same record, it kind of thinks it's there
already and tells me it has been deleted. I'm figuring that the key value
has been retained in the index and marked as deleted.

The problem goes if I completely exit and reopen the database after
deleting the test record. Records won't be deletable in the finished app,
which is why it's an irritation rather than a showstopper. But I wonder if
I'm doing something wrong, or missing a trick. Perhaps it's down to the
recordset type I open as (I leave it to the default).

Anyway, even though it's trying to get two fixes for the price of one, any
ideas?

Cheers again,

Trev.
 
Back
Top