Custom Message Box for verifing RunSQL Insert

[robsworld78]

Hi, I'm trying to create a custom message box which replaces the access dialog which tells you "You are about to append 1 row".



I can hide that dialog box with DoCmd.SetWarnings False which is good but how do I add a message box which tells me how many records where appended?

I can easily add a message box saying "your records have been imported" but I need it to say "32 records have been imported".

Access obviously knows how many were imported, how do I tell that to my users elegantly?

Thanks

[JoeM]

One way is to use the DCOUNT function, and count the number of records before the insert, then use it again to count the number of records after the insert.
Subtract the two, and you will have the number of new records added to use in your message box.

[robsworld78]

Thanks but I have to stay away from Dcount and all the other D's as I've heard they don't work with the runtime version and have had issues using them.

[JoeM]

Thanks but I have to stay away from Dcount and all the other D's as I've heard they don't work with the runtime version

Where did you hear that? I did quick Google search, and could find nothing that says that.

There are other ways of doing it too, including using Aggregate Queries to count the records before and after, as well as using DAO or ADO recordsets to count the records before and after.

[robsworld78]

Well it might not be incompatible but its glitchy, one or the other, can't remember. I was doing a complex form once and had major issues and it was determined it was the D functions causing the problem. It was with DLast and maybe DMax sometime it wouldn't show the last record. It was glitchy and caused me much grief so i vowed to stay away from them, lol.

Do you have an example, the only way I can think of doing it is having the files imported into a temp table first, counted and then moved to when they need to be but that seems so much just to find out how many when access knows right then and there.

This is the closest I could find but it doesn't work.
How to stop an Access Dialog Warning Box Popping Up and Replace it with a Custom Message Box

1. Immediately before the code that you use to add the record to the database table add these lines of code: Dim strMyText As String
strMyText = Me.textbox1
2. then after the code used to add the record to the database table add:
MsgBox strMyText & " was added to the database"
The full code (including the sql to add the record to the database) would be:
Private Sub btnAdd_Click()
Dim strMyText As String
strMyText = Me.textbox1

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblAccessories (accessoryName) " _
& "VALUES(" _
& "'" & Me!textbox1 & "') "
MsgBox strMyText & " was added to the database"
End Sub


I tried adding that to this but got no where, it keeps hanging on strMyText = Me.textbox1 I did add a textbox1 to the form.

DoCmd.RunSQL "INSERT INTO PurchaseOrders (PurchaseOrderNumber, SupplierID, EmployeeID, DateReceived)" & _
"Values ('" & ORDNO & "', '" & 1 & "', '" & 9 & "', '" & Date & "') "

[JoeM]

I believe that function like DLast can be a little flaky, but I have never had an issue with DCOUNT when simply counting the number of records in a table or query. I use it quite a bit. It is very straightforward, i.e.

myCount =Dcount("*","TableName")

Alternatively, I have created a User Defined Function to do record counts in the past too, i.e.

Function MyRecordCount(myQuery As String) As Long
'   Counts records in a query

    Dim myRecords As Long
    Dim myRS As DAO.Recordset

    Set myRS = CurrentDb.OpenRecordset(myQuery)
    If Not myRS.EOF Then
        myRS.MoveLast
        MyRecordCount = myRS.RecordCount
    Else
        MyRecordCount = 0
    End If

End Function

You just need to be sure to select the DAO reference in the VB Editor in order to use it.

[robsworld78]

Ok, well I guess I got the numbers I was after, I made a query that compared what was imported to what was in stock and if something was missing the numbers were different.

Do you know of a way to count the records in a CSV file without opening and importing it?

[JoeM]

Have a look here: http://www.utteraccess.com/forum/Cou...-t1686189.html
(there's lot of good stuff out there to be found with Google searches!)

[robsworld78]

Thanks, I did do lots of searching but couldn't find anything. This did help, I ended up using the following.

Dim File As String
Dim strFile As String
Dim intFile As Integer
Dim strImportData As String
Dim lngcnt As Integer

File = Me.FileName
strFile = File
intFile = FreeFile

Open strFile For Input As #intFile
Do Until EOF(intFile)
Line Input #intFile, strImportData
lngcnt = lngcnt + 1
Loop
Close #intFile

MsgBox lngcnt - 1