Insert not working

  • Thread starter Thread starter NevilleT
  • Start date Start date
N

NevilleT

Spent about 2 hours on this without success. I want to put a button on a
main form to add a record to a subform which is a datasheet. The reason I
want to do this is that the subform is sorted in a sequence number order and
I want to be able to position the cursor on a subform record, add the new
record, give it the sequence number of the selected record, and increase the
following sequence numbers by 1.

Example: Records with sequence numbers 1 to 5. Position the cursor on
record 3. Select the button on the main form and the new record becomes
sequence number 3. Original records 3, 4 and 5 become 4, 5 and 6.

The main form is frmGanttData. The subform is frmGanttDataSub

intCurrentSeqNo = Forms!frmGanttData.frmGanttDataSub.Form!txtSeqNo

strSQL = "INSERT into tblTask(MSPID, ProjectNo) VALUES(" &
intCurrentSeqNo & ", " & Forms!frmTitlePage.cmbProject & ");"

subRunUpdateQuery (strSQL)

subRunUpdateQuery is a generic sub I use for updates.

Public Sub subRunUpdateQuery(strSQL As String)
' Run a query in the sql string passed to the sub to update some data
On Error GoTo Error_subRunUpdateQuery

DoCmd.Hourglass (True) ' Turn
on the hourglass
DoCmd.SetWarnings False

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute dbSeeChanges
' Run the insert query

Exit_subRunUpdateQuery:
Set dbs = Nothing 'Clean up
Set qdf = Nothing
strSQL = ""
DoCmd.Hourglass (False) ' Turn
off the hourglass
DoCmd.SetWarnings True
Exit Sub

Error_subRunUpdateQuery:
MsgBox "Error in subRunUpdateQuery: " & Err.Number & " - " &
Err.Description
Resume Exit_subRunUpdateQuery
End Sub

The query runs without an error but does not create a new record. What am I
missing?
 
One more piece of information. The generated SQL seems fine.
INSERT into tblTask(MSPID, ProjectNo) VALUES(3, 1);
 
Even more information... Works perfectly with an Access back end but not
with SQL Server.
 
Back
Top