Easy Msgbox Issue

[benkingery]

I have the following code below that works to append a table within my database. What I can't figure out is how to create a Msgbox at the end of the code that says something like "There are _____ new records in your table", the ____ is the part I can't figure out. I want it to return a numerical result based on how many records are populated to the table.

Can someone help? Sorry, the code doesn't paste too pretty here.

Public Function Pop_SH_Build()
Dim Apd_Pop_SH_Build As String
Dim Update_Pop_SH_Build As String

Apd_Pop_SH_Build = "INSERT INTO SH_Build ( Parent_SKU, Child_SKU, Title, [Size], Color, Brand, Country_of_Origin, Cost, Site_Price, Retail_Price ) SELECT Parent_Table.Parent_SKU, Child_Table.Child_SKU, Parent_Table.AZ_Title, Child_Table.Size, Child_Table.Color, Parent_Table.Brand, Parent_Table.Country_of_Origin, Child_Table.SH_Cost, Child_Table.SH_Site_Price, Child_Table.MSRP FROM Parent_Table INNER JOIN Child_Table ON Parent_Table.Parent_SKU = Child_Table.Parent_SKU WHERE (((Parent_Table.TBB_SH)=Yes) AND ((Parent_Table.Built_on_SH)=No) AND ((Parent_Table.Populate_SH_Table)=No))"

Update_Pop_SH_Build = "UPDATE Parent_Table SET Parent_Table.Populate_SH_Table = Yes WHERE (((Parent_Table.TBB_SH)=Yes) AND ((Parent_Table.Populate_SH_Table)=No))"

DoCmd.RunSQL Apd_Pop_SH_Build
DoCmd.RunSQL Update_Pop_SH_Build
MsgBox ("There are _ new records in the table")
End Function

 

[Brianwarnock]

Assuming that you have counted the new records in Countofnewrecords then

MsgBox "There are " & Countofnewrecords & " new records in the table"

Note the space at the end/start of the surrounding text.

Brian

 

[benkingery]

Thanks for the update. I do know how to create a count query that will give me that information, but I'm not exactly sure how to assign a value to the variable "Countofnewrecords".

Thanks again for your help.

 

[Brianwarnock]

I assumed that you would be counting your INSERTs

 

[benkingery]

I assumed that you would be counting your INSERTs

It sounds like a good idea, but I'm not sure how. Can you explain briefly?

 

[Banana]

You may want to use RecordsAffected property. But I wouldn't use RunSQL. CurrentDb.Execute is preferable because it doesn't show you that annoying dialogs without having to remembering to turn warning back on.

Dim l As Long

With CurrentDb
   .Execute Apd_Pop_SH_Build, dbFailOnError
   l = .RecordsAffected
   .Execute Update_Pop_SH_Build, dbFailOnError
   l = l + .RecordsAffected
End With

MsgBox ("There are " & l & " new records in the table")

 

[benkingery]

Thanks for the update. I actually just solved the problem with a Dlookup.

I will look into the CurrentDb.Execute. I wasn't aware of that function.

Thanks again for your help.