SQL Insert statement with a custom function

[Chris Guld]

I usually use queries for everything, but I'm trying to
use a SQL Insert statement this time and I can't get the
syntax right. Here's the situation, I have a table of
Franchisees with a key field called 'Source'. I also have
a custom function that figures the minimum royalty that
each Franchisee owes based on their contract type, years
in business etc. RoyaltyMiniumum() - all I have to pass it
is the 'Source' and it does the rest. I want this
procedure to cycle thru all the active franchisees and
enter the minimum royalty they owe for the current month.
It needs to insert a record into tblSales for each
franchisee that includes the Source, Month, Year, Sales of
0, MinimumRoyalty due, an entry date and a sort date.

Here's my non-functioning code:
----------------------------------
Private Sub cmdMinimums_Click()
Dim rFranchisees As Recordset, sSource As String

Set rFranchisees = CurrentDb.OpenRecordset("SELECT Status,
SOURCE FROM [GM CONTACT] WHERE Status= 'active'", _
dbOpenForwardOnly, dbReadOnly)

With rFranchisees
DoCmd.SetWarnings False
Do While Not .EOF
INSERT INTO tblSales (SOURCE, SalesAmount,
RoyaltyAmount, EntryDate, SortDate )
SELECT !Source , 0 , RoyaltyMinimum(!Source),
Format([date],"mm/dd/yy"), Format(Date(),"yyyymm")
.MoveNext
Loop
End With
DoCmd.SetWarnings True
End Sub
-------------------------------

Any help would be appreciated.

Thanx!
Chris

 

[Roger Carlson]

I don't see where you are executing your query. In VBA, you have to create
your query in a string and then use one of several methods to execute it. I
generally use the Execute method of the Database Object, something like
this:

Private Sub cmdMinimums_Click()
Dim db as DAO.Database
Dim rFranchisees As Recordset, sSource As String
Dim strSQL as String

Set db = CurrentDb
Set rFranchisees = db.OpenRecordset("SELECT Status,
SOURCE FROM [GM CONTACT] WHERE Status= 'active'", _
dbOpenForwardOnly, dbReadOnly)

With rFranchisees
DoCmd.SetWarnings False
Do While Not .EOF
strSQL = "INSERT INTO tblSales (SOURCE, SalesAmount, " & _
" RoyaltyAmount, EntryDate, SortDate ) " & _
" SELECT !Source , 0 ,
RoyaltyMinimum(!Source), " & _
" Format([date],""mm/dd/yy""),
Format(Date(),""yyyymm"")"
db.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
DoCmd.SetWarnings True
End Sub

Also, because I don't understand your table structure, I have not modified
your SQL statement (other than to format it for the screen). Seems to me,
your SELECT requires a FROM clause. Get the query working in the Query
Builder and then go to the SQL window and copy and paste it in the code.
After that you have to format it as a string. Notice that any embedded
quotes (like around the format strings) have to be doubled or you will get
an error.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I usually use queries for everything, but I'm trying to
use a SQL Insert statement this time and I can't get the
syntax right. Here's the situation, I have a table of
Franchisees with a key field called 'Source'. I also have
a custom function that figures the minimum royalty that
each Franchisee owes based on their contract type, years
in business etc. RoyaltyMiniumum() - all I have to pass it
is the 'Source' and it does the rest. I want this
procedure to cycle thru all the active franchisees and
enter the minimum royalty they owe for the current month.
It needs to insert a record into tblSales for each
franchisee that includes the Source, Month, Year, Sales of
0, MinimumRoyalty due, an entry date and a sort date.

Here's my non-functioning code:
----------------------------------
Private Sub cmdMinimums_Click()
Dim rFranchisees As Recordset, sSource As String

Set rFranchisees = CurrentDb.OpenRecordset("SELECT Status,
SOURCE FROM [GM CONTACT] WHERE Status= 'active'", _
dbOpenForwardOnly, dbReadOnly)

With rFranchisees
DoCmd.SetWarnings False
Do While Not .EOF
INSERT INTO tblSales (SOURCE, SalesAmount,
RoyaltyAmount, EntryDate, SortDate )
SELECT !Source , 0 , RoyaltyMinimum(!Source),
Format([date],"mm/dd/yy"), Format(Date(),"yyyymm")
.MoveNext
Loop
End With
DoCmd.SetWarnings True
End Sub
-------------------------------

Any help would be appreciated.

Thanx!
Chris