Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Mark,

    I applied your method to my data and it looks like it's going to work great. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

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

    Sarah

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  10. #10
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks for all the advice Mark!

    It's working great. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Sarah

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  12. #12
    Star Lounger
    Join Date
    May 2002
    Location
    Vienna, Austria
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Set Primary Key with Make Table Query? (A2K)

    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

  14. #14
    Star Lounger
    Join Date
    May 2002
    Location
    Vienna, Austria
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Primary Key with Make Table Query? (A2K)

    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.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set Primary Key with Make Table Query? (A2K)

    AFAIK you should be able to use the ALTER TABLE statement, this is described in the help.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •