G
Guest
Hi,
I am pretty new to VB.
Basically, what I REALLY want to do is just count the number of rows that
are being appended from one table to another (from SUB to MASTER) and display
it in a MsgBox. [which will be copied and modified to be used from MASTER to
ARCHIVE]
In my macro, I have turned Warnings Off, because I just want a clean cut
MsgBox.
The code below works pretty well, but it has some flaws. First, though...
I have a table called UPDATE_HISTORY that is populated by Action Queries
that stores the date (In_Date) that the query was run[Now()], a count of the
number of rows (No_Lines) by primary key, and the table that it was appended
to (Inserted_Into).
Essentially, it just looks in the UPDATE_HISTORY table, and retrieves the
most recent Number of Lines inserted into the MASTER table and adds that
number to my MsgBox.
Public Function Work() As String
'Establish connection to ActiveX Data Objects
Dim rs As ADODB.Connection
Set rs = CurrentProject.Connection
'Declare Recordset
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = rs
'SQL statement to populate Recordset
Dim mySQL As String
mySQL = "SELECT [Update_History].[No_Lines]"
mySQL = mySQL + " FROM Update_History"
mySQL = mySQL + " WHERE In_Date=ALL(SELECT Max(In_Date)"
mySQL = mySQL + " FROM Update_History)"
mySQL = mySQL + " AND Inserted_Into = 'MASTER'"
'Run SQL Select statement
myRecordset.Open mySQL
'Retrieve the value from the upper-left most of the Recordset
Dim X As String
X = (myRecordset.Fields(0).Value)
Dim prompt, buttons, title
prompt = "You have added "
prompt = prompt + X
prompt = prompt + " rows."
buttons = vbOKOnly
title = "Number of Lines Appended to MASTER table"
Work = MsgBox(prompt, buttons, title)
'Close Recordset and terminate connection
'myRecordset.Close
'Set myRecordset = Nothing
'Set rs = Nothing
End Function
As you may have already realized, one problem with this is that if there are
not any rows to append from one table to another and someone clicks on the
Update button on my form, then the MsgBox will tell them the most recent
Number of Rows appended to the MASTER table instead of telling them that ZERO
rows were appended.
Another problem that I run into occurs if I clear out all the sample data to
include the Update_History table, then I get a BOF or EOF error. I am
guessing because there is actually nothing in the table to look up.
Questions:
Is there a better way to do this? If so, how?
When do I need to worry about closing a Recordset?
What do I need to do about EOF or BOF?
I apologize for the sheer length of this post, and I appreciate your help.
Sincerely,
David
I am pretty new to VB.
Basically, what I REALLY want to do is just count the number of rows that
are being appended from one table to another (from SUB to MASTER) and display
it in a MsgBox. [which will be copied and modified to be used from MASTER to
ARCHIVE]
In my macro, I have turned Warnings Off, because I just want a clean cut
MsgBox.
The code below works pretty well, but it has some flaws. First, though...
I have a table called UPDATE_HISTORY that is populated by Action Queries
that stores the date (In_Date) that the query was run[Now()], a count of the
number of rows (No_Lines) by primary key, and the table that it was appended
to (Inserted_Into).
Essentially, it just looks in the UPDATE_HISTORY table, and retrieves the
most recent Number of Lines inserted into the MASTER table and adds that
number to my MsgBox.
Public Function Work() As String
'Establish connection to ActiveX Data Objects
Dim rs As ADODB.Connection
Set rs = CurrentProject.Connection
'Declare Recordset
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = rs
'SQL statement to populate Recordset
Dim mySQL As String
mySQL = "SELECT [Update_History].[No_Lines]"
mySQL = mySQL + " FROM Update_History"
mySQL = mySQL + " WHERE In_Date=ALL(SELECT Max(In_Date)"
mySQL = mySQL + " FROM Update_History)"
mySQL = mySQL + " AND Inserted_Into = 'MASTER'"
'Run SQL Select statement
myRecordset.Open mySQL
'Retrieve the value from the upper-left most of the Recordset
Dim X As String
X = (myRecordset.Fields(0).Value)
Dim prompt, buttons, title
prompt = "You have added "
prompt = prompt + X
prompt = prompt + " rows."
buttons = vbOKOnly
title = "Number of Lines Appended to MASTER table"
Work = MsgBox(prompt, buttons, title)
'Close Recordset and terminate connection
'myRecordset.Close
'Set myRecordset = Nothing
'Set rs = Nothing
End Function
As you may have already realized, one problem with this is that if there are
not any rows to append from one table to another and someone clicks on the
Update button on my form, then the MsgBox will tell them the most recent
Number of Rows appended to the MASTER table instead of telling them that ZERO
rows were appended.
Another problem that I run into occurs if I clear out all the sample data to
include the Update_History table, then I get a BOF or EOF error. I am
guessing because there is actually nothing in the table to look up.
Questions:
Is there a better way to do this? If so, how?
When do I need to worry about closing a Recordset?
What do I need to do about EOF or BOF?
I apologize for the sheer length of this post, and I appreciate your help.
Sincerely,
David