DoCmd.RunSQL

[Jim]

I am using Access 97 on a PC running Windows NT 4.0 SP6a.

I have some code (shown below) intended to add a set of records to one
table (tblGradeProps) when a new record is created in another
(tblGrades)

The oddity is that when the procedure Detail_Click is called from the
procedure cmbMktSect_Exit, the RunSQL statement doesn't add the
required records but it does so when invoked by the Detail_Click
event!

I've been messing with this for days without success. At one time I
was using an Append Query with DoCmd.OpenQuery and while it wouldn't
add records when called by the code it did so when opened manually
from the database window. Now the query is just a convenient place to
store the SQL string.

Any help or suggestions will be gratefully received.

************START OF CODE FRAGMENT**********************
Private Sub cmbMktSect_Exit(Cancel As Integer)
'this field can be exited on all records so we need to find out if
we're dealing with a new record
If Me![cmbMktSect].BackColor = vbYellow Then 'this is a new
record
'we're dealing with a new record and must add property record to
the table "tblGradeProps"

'reset the grade and sector fields to the default condition.
Me![cmbMktSect].Locked = True
Me![Grade].Locked = True
Me![Grade].BackColor = vbWhite
Me![cmbMktSect].BackColor = vbWhite

Debug.Print "Called from cmbMktSect_Exit"

Call detail_click


'reset the record source so that the market sector text can be
read
cmbMktSect.RowSource = "tblMktSector"

'reset the drop-down menu to show only 1 row
Me![cmbMktSect].ListRows = 1

End If


End Sub

Sub detail_click()
Dim strSQL As String

strSQL = CurrentDb.QueryDefs("qryAppSecProps").SQL

Debug.Print Me![cmbMktSect]
Debug.Print Me![GradeID]
Debug.Print strSQL

DoCmd.RunSQL strSQL
'DoCmd.OpenQuery "qryAppSecProps", acViewNormal, acAdd
'Debug.Print CurrentDb.QueryDefs("qryAppSecProps").SQL
Me.Refresh

End Sub
***************END OF CODE FRAGMENT

 

[Peter]

I've used the following with success.
set db=currentdb()
db.execute "INSERT INTO tblGradeProps (f1, f2, f3) SELECT
Me![Grade] AS Expr1, Me![cmbMktSect] AS Expr2 , 1 AS Expr3"