Access 2000 / Queries, forms. Macros

[Abay]

Hello .. I'm not sure where to post the following. Originally I posted it
in the general access newsgroup but have had no reply .. apologies if this
is also the wrong newsgroup, if it is, some re-direction would be most
welcome .. and the problem is:

I have a tabular form who's source is derived from a query.

There is a yes/no switch which can be ticked to select individual records
which are used to create a new table.

There are two command buttons, one pressed to select all records and the
other is pressed when only some records are selected. When some are
selected and the button pressed, it runs a macro containing 2 queries ..
one to create the table from the selected records by checking the yes/no
switch, the other to clear the yes/no switch (clears the switches on all
records .. or that's the idea) after the new table is created.

The problem is, the last record which is ticked on the form, is not
selected. & doesn't get cleared after the new file is created. When only
one record is selected it works but not for more than one i.e. when four
records are ticked only three are selected. The last record ticked is never
selected.

If I add a record at the end of the form all four ticked records are
selected, but obviously I can't ask people to add a dummy record.

I know a little of Access but am still a newbie ..

There is probably a better way to do this.

Any help would be much appreciated.

Abay

 

[John Spencer]

What is the code behind the buttons?

It sounds as if the "selected record" has not been saved (and therefore is
not marked) when the code is executed.

First thing I would try would be to add
If Me.Dirty = True then Me.Dirty = False
at the beginning of the code to force the record change (checked the box) to
be saved.

I do wonder WHY you are creating a new table of data that you already have
selected. That is almost (almost) never needed, since you can use a query
in most places where you use a table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Abay]

Thank you for your reply, I will add the code you suggest ... I am creating
a new table as it will be used as mail merge input to word, using the Tools
Office link option. Is there a way to do this (Select the table, then,
Tools, Office Link, Word) behind the scenes, thru code?

The code behind the button: (Generated with the command button wizard)

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

Dim stDocName As String

stDocName = "M_realtor_merge_all" ... 2 Macros containing the query to
create the table, and clear the source afterwards
DoCmd.RunMacro stDocName

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

Your help is greatly appreciated

Many thanks

Abay