Create Msgbox that states nbr of Records appended (Acess 2000)

Topic

New Reply

I have a macro that runs a query launched from a button on a form. The query appends records to a table. I would like a message box to appear that states the number of records appended to the table after the user clicks on the button.

Can I set this up in the macro, or do I need to do this in code? If in code, how do I generate the count of records and get that to show in a messagebox?

Thanks.
Sarah

Reply | Quote

Replies

Hi Sarah,
Access normally gives you the record count when you run an append query – it sounds like you may have turned off Warnings. You can turn those on and off in a macro, so you may want to explicitly turn them on before you run the step that executes the query. Let us know if you need detailed instructions on how to do that.

Reply | Quote

Hi Wendell,

Yes, I have turned off Warnings. I would like to generate my own message box. If I turn on warnings, it’ll first ask me if I want to run an append query, and I don’t want my user to click NO. All I want them to see is the number of records appended and an OK button. Is there a way to capture this number in the warning and store it to a variable? Then I could use the msgbox function to display this variable in code some how.

Sarah

Reply | Quote

You can use the QueryDef RecordsAffected property to capture the number of records affected when running an action query. Here is example of how to use this property using a temporary QueryDef. In example, records are being appended, then deleted, from a copy of Employees table in Northwind.mdb – this table typically has only 9 records. To try this, copy Employees table and Paste As “Employees2” (structure only), then run sub. Sample code:

Option Compare Database
Option Explicit

Public Sub TestActionQueryRecordsAffected()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim strTbl As String
Dim strSQL As String
Dim strMsg As String

Set db = CurrentDb
strTbl = “Employees2” ‘ Copy of Employees table
strSQL = “INSERT INTO ” & strTbl & ” ” & _
“( LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, ” & _
“Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo ) ” & _
“SELECT LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, ” & _
“PostalCode, Country, HomePhone, Extension, Notes, ReportsTo ” & _
“FROM Employees; ”
Set qry = db.CreateQueryDef(“”, strSQL)
qry.Execute
strMsg = qry.RecordsAffected & ” records have been appended to the ” & strTbl & ” table.”
Beep
MsgBox strMsg, vbInformation, “APPEND QUERY”

‘ Place breakpoint here when testing:
Set qry = Nothing
strSQL = “DELETE * FROM ” & strTbl & “;”
Set qry = db.CreateQueryDef(“”, strSQL)
qry.Execute
strMsg = qry.RecordsAffected & ” records have been deleted from the ” & strTbl & ” table.”
Beep
MsgBox strMsg, vbInformation, “DELETE QUERY”

Exit_Sub:
Set db = Nothing
Set qry = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Sub

NOTE: When using Execute method to run action query, it is not necessary to turn warnings off, they will not be displayed. When testing recommend set breakpoint at location noted so you can verify the append query worked OK before running the delete query. If using A2K or later ensure reference to DAO 3.6 object library has been set.

HTH

Reply | Quote

PS: I neglected to mention, if you are using saved queries to append/delete/update records, you can simplify sub by using DAO Database object which also has an Execute method and RecordsAffected property. You simply specify name of query as a text string. Example:

Public Sub TestRecordsAffected()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim strTbl As String
Dim strQry As String
Dim strMsg As String

Set db = CurrentDb
strTbl = “Employees2”
strQry = “qryAppend”
db.Execute strQry
strMsg = db.RecordsAffected & ” records have been appended to the ” & strTbl & ” table.”
Beep
MsgBox strMsg, vbInformation, “APPEND QUERY”

‘ Set breakpoint here:
strQry = “qryDelete”
db.Execute strQry
strMsg = db.RecordsAffected & ” records have been deleted from the ” & strTbl & ” table.”
Beep
MsgBox strMsg, vbInformation, “DELETE QUERY”

Exit_Sub:
Set db = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub
End Sub

This maybe simpler if you don’t need to modify the action query’s SQL statement on the fly when running procedure. In example, “qryAppend” & “qryDelete” are saved append/delete queries with same SQL as shown in previous example. As with QueryDef Execute method, warnings will not be displayed when sub runs.

HTH

Reply | Quote

Mark, I had a similar problem to Sarah’s so I first searched the Lounge and found this thread. I need to call a make table query (creating the underlying table for a tree view control), to set the primary key and delete the table when I close the form containing the tree view. I don’t know how and where to specify the primary key, can you please help me?

Reply | Quote

Sorry, you can’t define a primary key when using a make table query. You could create “temporary” table using DAO code to define table, fields, and indexes, populate table using an append rather than make table query, then delete table when it has served its purpose. Simple example of creating table with one field defined as primary key:

Public Sub MakeTempTable(strTbl As String, strFld As String, strIndex As String)
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim strMsg As String

Set db = CurrentDb
Set tbl = db.CreateTableDef(strTbl)

‘ Note: cannot append table to TableDefs unless at least _
one fld defined & appended first:

Set fld = tbl.CreateField(strFld, dbText, 10)
tbl.Fields.Append fld
tbl.Fields.Refresh
db.TableDefs.Append tbl
db.TableDefs.Refresh

Set idx = tbl.CreateIndex(strIndex)
idx.Fields.Append idx.CreateField(strFld)
‘ Define as primary key before appending:
idx.Primary = True
tbl.Indexes.Append idx
tbl.Indexes.Refresh

‘ For test purposes open temp table in design view:
DoCmd.OpenTable strTbl, acViewDesign
‘ Then close (set breakpoint here for test purposes):
DoCmd.Close acTable, strTbl

‘ Add code here to append records to table (append query)
‘ After temp table serves it purpose, delete:
db.TableDefs.Delete strTbl

Exit_Sub:
Set db = Nothing
Set tbl = Nothing
Set fld = Nothing
Set idx = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Sub

Example of use:

MakeTempTable “Table1″,”Field1″,”Index1”

This will create table named Table1, with one field, Field1, defined as primary key. Once table is defined you could use append query to populate table using Database Execute method as illustrated previously.

HTH

Reply | Quote

Thank you, Mark, for the code. The problem is I have to use fields from a lot of related tables. I thought I could create a table using the make table query and after that set the index – on the new table.

Reply | Quote

AFAIK you should be able to use the ALTER TABLE statement, this is described in the help.
Pat

Reply | Quote

Thank you, patt. My MA Help file doesn’t know anything about ALTER TABLE, nor does the VB Help. I found it in the MSDN library though and I think I get the general idea.

Reply | Quote

To set primary key index for an existing table (whether created via make-table query or by other means) you can use DAO methods as shown in this modified example:

Public Sub CreatePKIndex(strTbl As String, strFld As String)
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim strMsg As String

Set db = CurrentDb
Set tbl = db.TableDefs(strTbl)
Set fld = tbl.Fields(strFld)
Set idx = tbl.CreateIndex(“PrimaryKey”)
idx.Fields.Append idx.CreateField(strFld)
‘ Define as primary key before appending:
idx.Primary = True
tbl.Indexes.Append idx
tbl.Indexes.Refresh

‘ For test purposes open temp table in design view:
DoCmd.OpenTable strTbl, acViewDesign
‘ Then close (set breakpoint here for test purposes):
DoCmd.Close acTable, strTbl

Exit_Sub:
Set db = Nothing
Set tbl = Nothing
Set fld = Nothing
Set idx = Nothing
Exit Sub
Err_Handler:
Select Case Err
Case 3283 ‘ Primary Key already exists
strMsg = “A Primary Key is already defined for ” & strTbl & ” table.”
MsgBox strMsg, vbExclamation, “PRI KEY ERROR”
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “CREATE PK ERROR MESSAGE”
Resume Exit_Sub
End Select

End Sub

Example of use:

CreatePKIndex “Table1″,”Field1”

This will create PK index on single field named “Field1” in “Table1.” Obviously, if PK already exists an error will result. I usually use DAO methods for DDL purposes; I find it more intuitive to use than SQL ALTER TABLE statements, etc.

HTH

Reply | Quote

Another option: use DDL (Data Definition Language) Create Index statement in SQL Data Definition query. Example:

CREATE INDEX PrimaryKey
ON TABLE1 (FIELD1)
WITH PRIMARY

This will create Primary Key index named PrimaryKey on field named FIELD1 in table named TABLE1. An example of using this SQL in VBA:

DoCmd.RunSQL “CREATE INDEX PrimaryKey ON TABLE1 (FIELD1) WITH PRIMARY”

Note that in Access Data Definition Queries can be designed in SQL View only (from Query menu, select SQL Specific, then Data Definition). For more details on Create Index & other DDL SQL statements refer to “Help” files (In Access, not VBA, Help, select “Microsoft Jet SQL Reference” topic in Contents, then look at Data Definition Language subtopic).

HTH

Reply | Quote

One final option: If you’re feeling adventurous you can use ActiveX Extensibility Objects (ADOX) methods instead of old-fashioned, old-hat DAO methods. Example:

Public Sub CreatePKIndexADOX(strTbl As String, strFld As String)

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As New ADOX.Index

cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTbl)

‘ Define a primary key index with one column (field):
idx.Name = “PrimaryKey”
idx.Columns.Append strFld

‘ Must define as PK before appending to table’s Indexes collection:
idx.PrimaryKey = True
tbl.Indexes.Append idx

Set cat = Nothing
Set tbl = Nothing
Set idx = Nothing

End Sub

Example of use:

CreatePKIndexADOX “TABLE1″,”FIELD1”

This will create Primary Key index on Field1 in Table1. Actual sub should include error handling, etc. To use this code you need to set a reference to “Microsoft ADO Ext 2.x for DDL and Security” object library (msadox.dll). Be advised that ADOX is relatively new object model & can be a bit flaky – do a MSKB search on “ADOX” & you’ll see what I mean – you may want to do things the old-fashioned way using DAO or SQL DDL. Above code works OK using Access XP/2002 with reference set to ADOX version 2.7. For what you are doing using SQL DDL might be simplest approach.

HTH

Reply | Quote

Well, Mark, what can I say beside thank you for all the options you showed me? I feel adventurous enough to try them all out since I’m still at the very beginning of learning Access.

Reply | Quote

Mark,

I applied your method to my data and it looks like it’s going to work great.

Where would you suggest I call this subprocedure from? Can I just call it from the on Click event of a button?

Sarah

Reply | Quote

If you are going to run this from a form, then you could simply call the sub using a command button’s On Click event. If the sub itself is located in same form module it can be declared as Private, but if the sub is located in a standard module, it would have to be declared as Public to be able to call it from the form. If the sub is only going to be used with this particular form, then recommend place sub in form’s code module & use button click event to run it.

HTH

Reply | Quote

Is there a performance gain by storing/running the code in the OnClick event of a button rather than calling a Public sub from the On Click event?

Sarah

Reply | Quote

No, I wouldn’t recommend placing the sub itself in the button’s click event procedure. Rather, call the sub like this:

Private Sub Command0_Click()
TestRecordsAffected
End Sub

As a general rule, I incorporate simple procedures and tests only in a button click event procedure. In a case like this I would create a separate sub to run the action queries, and then call it from the click event.

As noted, if you are only going to use this sub with a particular form, the TestRecordsAffected sub (or whatever you name it) should be stored in the form’s code module, and should be declared as Private. If you were going to try to generalize this procedure so it could be called from anywhere in the database, then the sub would be placed in a standard module, and be declared as Public. As far as performance goes, when you open form that has a code module, the form’s module is loaded in memory. If the form has an event procedure that calls a procedure located in another module, and the event is triggered, then that entire module is loaded in memory too, even tho you may be only using a single sub or function located in the module. So unless you plan to generalize this routine (which would entail adding variable parameters to be passed as arguments to sub, etc), from a perfomance viewpoint I’d recommend keeping sub in the form module and calling sub as shown above.

HTH

Reply | Quote

Thanks for all the advice Mark!

It’s working great.

Sarah

Reply | Quote

Sorry Sarah, I got sidetracked on other issues, but it looks like Mark has given you the slickest way of getting what you wanted. It’s quite a jump from a macro running a query to VBA modifying the query definition and getting data out of it’s properties, but most people end up abandoning macros fairly quickly anyhow, and VBA opens up entire new worlds to you. Glad we could help.

Reply | Quote