Update Form Display after Record Entry

[Nerida via AccessMonster.com]

Hi there,

I have a continuous form that displays data in descending order by a document
no. [DocNo] - this allows the most recent number to be at the top of the
screen. When I add a new record it is added to the end of the set of records.


I'd like to be able to have this new record displayed at the top of the list
when the I click the Update button (this is just Refresh Form Data on a
button). At the moment though I have to close the form and then reopen it.
I'm sure it's just a matter of adding some code to the Update code but I
really don't know where to begin.

I hope someone can help.

Cheers,
Nerida.

 

[John Vinson]

I'd like to be able to have this new record displayed at the top of the list
when the I click the Update button (this is just Refresh Form Data on a
button). At the moment though I have to close the form and then reopen it.
I'm sure it's just a matter of adding some code to the Update code but I
really don't know where to begin.

Add a line

Me.Requery

John W. Vinson[MVP]

 

[Nerida via AccessMonster.com]

Thanks John,

I added your line in as the last line before End Sub and it doesn't seem to
work. Below is what is in the module (this was created by the button wizard
as a result of selecting Refresh Form Data.

On Error GoTo Err_btnUpdate_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_btnUpdate_Click:
Exit Sub

Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_btnUpdate_Click

Me.Requery

I know that clicking in the DocNo field and clicking the Descending Order
button does just what I need but I don't know the code to iniate that.

Thanks for your help. It's much appreciated!

Nerida.

 

[John Vinson]

Thanks John,

I added your line in as the last line before End Sub and it doesn't seem to
work. Below is what is in the module (this was created by the button wizard
as a result of selecting Refresh Form Data.

Ok, let's play computer and see what this is actually doing.

This next line sets up an error trap - if there is an error execution
will go to the label Err_btnUpdate_Click.

On Error GoTo Err_btnUpdate_Click

This (ugly and obsolete) code calls a Menu Item in the Access97 menu
style to (I guess) refresh the form. It's probably not necessary.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

The next line is a label for the Resume below... it does nothing other
than provide a "hook" that code can jump to.

Exit_btnUpdate_Click:

This next line exits the subroutine... so when it runs, unless there's
an error, it will refresh the form and then exit.

Exit Sub

Here's the Err_btnUpdate_Click label specified in the On Error code
above.

Err_btnUpdate_Click:

If there's an error, it will pop up a Message Box displaying the error
description...

MsgBox Err.Description

and jump to the label Exit_btnUpdate_Click.

Resume Exit_btnUpdate_Click

This next line will never be executed, because you've already exited
the Sub.

Me.Requery

I know that clicking in the DocNo field and clicking the Descending Order
button does just what I need but I don't know the code to iniate that.

Just REPLACE the line

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

with

Me.OrderBy = "[DocNo] DESC"
Me.OrderByOn = True

to set the Form's order and enable it.

John W. Vinson[MVP]

 

[Nerida via AccessMonster.com]

That is BEAUTIFUL! Thank you so much. You guys make it look so easy. So
much to learn and so little time.

Thank you and have a great day!
Nerida.

PS: Will look more carefully when implementing responses from now on.

Thanks John,

I added your line in as the last line before End Sub and it doesn't seem to
work. Below is what is in the module (this was created by the button wizard
as a result of selecting Refresh Form Data.

Ok, let's play computer and see what this is actually doing.

This next line sets up an error trap - if there is an error execution
will go to the label Err_btnUpdate_Click.

On Error GoTo Err_btnUpdate_Click

This (ugly and obsolete) code calls a Menu Item in the Access97 menu
style to (I guess) refresh the form. It's probably not necessary.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

The next line is a label for the Resume below... it does nothing other
than provide a "hook" that code can jump to.

Exit_btnUpdate_Click:

This next line exits the subroutine... so when it runs, unless there's
an error, it will refresh the form and then exit.

Exit Sub

Here's the Err_btnUpdate_Click label specified in the On Error code
above.

Err_btnUpdate_Click:

If there's an error, it will pop up a Message Box displaying the error
description...

MsgBox Err.Description

and jump to the label Exit_btnUpdate_Click.

Resume Exit_btnUpdate_Click

This next line will never be executed, because you've already exited
the Sub.

Me.Requery

I know that clicking in the DocNo field and clicking the Descending Order
button does just what I need but I don't know the code to iniate that.

Just REPLACE the line

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

with

Me.OrderBy = "[DocNo] DESC"
Me.OrderByOn = True

to set the Form's order and enable it.

John W. Vinson[MVP]