Help needed in adding records

Hi all,

I've got a frmCustomer form (designed via access 2000 form wizard) that uses
a qryCustomerName query (also designed by query wizard) ordered by customer
last name, first name, mi. Of course, it adds records to the end of the
dataset rather than putting new entries into alpha order. And, there they
sit until I leave the form and re-enter it.

Is there a way to input the new record so that once it's entered it pops it
into alpha order, without having to leave the form and re-enter it, or
clicking a key to re-query (and thus put the user back to the beginning of
the file)?

TIA
ron

Ron:

You can add this line to the code of the AfterInsert event of the Form:

Me.Requery

Note: This will take the user back to the first record of the data
set.

HTH,
Jana

Hi Jana,

Thanks for trying to help. As I indicated though, for other reasons, it's
REALLY cumbersome to have the user save the record and then pop back to the
first record in the query rather than still be at the record they just
added. VERY cludgy.

As with other attributes that seem to make Access almost useless, I'll try
and figure out a way around this problem as well. The way that Access seems
to work right out of the box, I might have found it easier to keep track of
records with an IBM typewriter.

Again, thanks for trying to help.
ron

"Jana" <Bauer.Jana@gmail.com> wrote in message
news:1145054839.358106.317170@i39g2000cwa.googlegr oups.com...[color=blue]
> Ron:
>
> You can add this line to the code of the AfterInsert event of the Form:
>
> Me.Requery
>
> Note: This will take the user back to the first record of the data
> set.
>
> HTH,
> Jana
>[/color]

"Ron" <ronSPAMBLOCKERwest777@earthlink.com> wrote
[color=blue]
> I've got a frmCustomer form (designed via access 2000 form wizard) that
> uses a qryCustomerName query (also designed by query wizard) ordered by
> customer last name, first name, mi. Of course, it adds records to the end
> of the dataset rather than putting new entries into alpha order. And,
> there they sit until I leave the form and re-enter it.[/color]

Yes, indeed, "of couse," is the operative phrase. Relational Tables (which
is what Access tables are) are, by definition, UNordered. You have to use a
Query that Sorts them when you want to make use of the Tables, such as
display the information to humans.

Larry Linson

[color=blue]
> Is there a way to input the new record so that once it's entered it pops[/color]
it[color=blue]
> into alpha order, without having to leave the form and re-enter it, or
> clicking a key to re-query (and thus put the user back to the beginning[/color]
of[color=blue]
> the file)?[/color]
[color=blue]
>[/color]

Thanks for helping me find a solution that seems to be working.

I used your requery, but before I did, I made a variable equal to the
customer number assigned to the new customer. I requery, then use
FindRecord to pop back to the appropriate record.

varCustNo = Me.CustomerNumber
Requery
DoCmd.GoToControl "CustomerNumber"
DoCmd.FindRecord varCustNo, , False, , False, acCurrent

May not be elegant, but it seems to work. I'll continue to play with it.

Thanks, Jana!
ron

"Jana" <Bauer.Jana@gmail.com> wrote in message
news:1145054839.358106.317170@i39g2000cwa.googlegr oups.com...[color=blue]
> Ron:
>
> You can add this line to the code of the AfterInsert event of the Form:
>
> Me.Requery
>
> Note: This will take the user back to the first record of the data
> set.
>
> HTH,
> Jana
>[/color]