Close Form if No records - Microsoft Access / VBA

David Mitchell

I have a form based on a totals query which lists a recordset of
peoples name where there is no match with tblrelman.

The user then selects from a combo box on the same form the correct
name and clicks a command button which runs an update query correcting
the name and then requeries the recordset.

It is used to correct typos on an imported spreadsheet.

Obviously the recordset decreases by one each time the command button
is clicked.

My problem is that after the last record has been amended and the
requery run I am left with a blank screen.

I need the user to be advised by MsgBox that "All Data has Been
Cleansed" and then the form close.

I have tried various bits of code to count the records and if 0 or
null display message box and have put the code in various event
procedures but can't get it to work.

Any help is greatly appreciated.

Nov 12 '05 #1

Subscribe Post Reply

9762

Allen Browne

If Me.RecordsetClone.RecordCount = 0 Then ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David Mitchell" <da**************@talk21.com> wrote in message
news:c3**************************@posting.google.c om...

I have a form based on a totals query which lists a recordset of
peoples name where there is no match with tblrelman.

The user then selects from a combo box on the same form the correct
name and clicks a command button which runs an update query correcting
the name and then requeries the recordset.

It is used to correct typos on an imported spreadsheet.

Obviously the recordset decreases by one each time the command button
is clicked.

My problem is that after the last record has been amended and the
requery run I am left with a blank screen.

I need the user to be advised by MsgBox that "All Data has Been
Cleansed" and then the form close.

I have tried various bits of code to count the records and if 0 or
null display message box and have put the code in various event
procedures but can't get it to work.

Any help is greatly appreciated.

Nov 12 '05 #2

David Mitchell

Thanks Allen, but I have already tried this in various event
procedures and it wont work. When the requery is instructed what
events are triggered for the form?

"Allen Browne" <al*********@SeeSig.invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>...

If Me.RecordsetClone.RecordCount = 0 Then ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Nov 12 '05 #3

Allen Browne

The events to interact.

There may be other events that need to complete before this one can, e.g. if
you try to run this in the AfterUpdate of a control, it will trigger the
form's BeforeUpdate, AfterUpdate, etc, before the Requery can run.

If new records can be added to the form, then the last event should be
Form_Current as it moves to a new record (because there are no more records
left).

Can't you just test the count in the same code that performs the requery?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David Mitchell" <da**************@talk21.com> wrote in message
news:c3**************************@posting.google.c om...

Thanks Allen, but I have already tried this in various event
procedures and it wont work. When the requery is instructed what
events are triggered for the form?

"Allen Browne" <al*********@SeeSig.invalid> wrote in message

news:<3f**********************@freenews.iinet.net. au>...

If Me.RecordsetClone.RecordCount = 0 Then ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Nov 12 '05 #4

David Mitchell

> Can't you just test the count in the same code that performs the requery?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Did that, works a treat, thank you very much.

David Mitchell

Nov 12 '05 #5

Similar topics

close form if query returns no records

by: highway of diamonds | last post by:

I have a form based on a query. I would like to generate a msgbox and close the form should the query return no records. TIA R. PS I would even be happy to lose the form and just do the...

Microsoft Access / VBA

Close form in C#

by: Vivek Sharma | last post by:

How can I close a form in C#? Here is the code that I am using but closes everything? private void frmSplash_Click(object sender, EventArgs e) { this.Close(); Form frmLogin = new...

C# / C Sharp

VB.net, launch procedure, close form, the whole APP is deactivated

by: dwilliamjoe | last post by:

VB.net, launch procedure, close form, the whole APP is deactivated. When I say deactivated, other applications (Wind Explorer, My computer, Ect...) come to the foreground and my app goes to the...

Visual Basic .NET

close form macro

by: Mindy | last post by:

I have two questions here: (1) what is the difference of close form and close table? (2) How "Prompt" works I used close form macro in my database. I hope when a user close the form, he/she will...

Microsoft Access / VBA

What is the event to trap when user clicks X to close form?

by: Robert Dufour | last post by:

On my form if the user clicks the upper right hand corner to close the form I want to trap that event and do a check to see if closing is allowed, if not, I want to stop the form closing action. ...

Visual Basic .NET

button events-save, close form and then open form

by: kev | last post by:

Hi folks, I have a form for registration (frmRegistration) whereby i have two buttons. One is Save which saves record using the OnClick property. I used wizard to create the save button. The...

Microsoft Access / VBA

close form don't go to first record on related form?

by: AccessIdiot | last post by:

Hello all, I have a form (frm_Entrainment) with a button that opens a 2nd form (frm_Specimen_Entrainment). The 2nd form shares an ID field with the first form (Entrainment_ID - its like opening...

Microsoft Access / VBA

How to add two fields to a single combobox and how to modify the close form button

by: Hulas | last post by:

Guys, I have two questions. (a) How do I add two fields of the same table to a single combo box. For example if I have two fields ID1 and ID2 in a table called Identification, than how should I...

Microsoft Access / VBA

When using macro to close form, how do I force the 'prompt' on save?

by: ncsthbell | last post by:

I have an MS2007 access database. On one of the forms that is used to edit/change data, there is a button 'Return to main menu'. Once the user has finished making the changes on the form and clicks...

Microsoft Access / VBA

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++