Prisoner362670
asked on
MS Access SQL INSERT doesn't write to table!
Greeting to All Experts,
I have a series of forms. When a user creates record on one subform and click a button a sQL insert statefires without error. However, when I look at the table, I don't see the record inserted. When I open the next form (which is not a subform) which relies on the record the form query returns null, of course.
Does Access SQL require a commit statement? Do I need to refresh? requery?
I'm really very frustrated with this and hope one of you can help me.
I have a series of forms. When a user creates record on one subform and click a button a sQL insert statefires without error. However, when I look at the table, I don't see the record inserted. When I open the next form (which is not a subform) which relies on the record the form query returns null, of course.
Does Access SQL require a commit statement? Do I need to refresh? requery?
I'm really very frustrated with this and hope one of you can help me.
My first thought is that you will need to requery the form's record source after the insert.
Example:
If that doesn't fix the issue then please post the code you are using to insert the record.
Example:
Me.RequeryIf that doesn't fix the issue then please post the code you are using to insert the record.
ASKER
If IsNull(DLookup(strFieldNam
' record not found code
Dim strSQL As String
'if the record does not exist create the record
strSQL = "INSERT INTO " & strTableName & " (" & strFieldName & ") VALUES (" & strFieldContent & ");"
DoCmd.RunSQL strSQL
Me.Requery 'this requeries the coursesite form
'then open the form to the new record
DoCmd.OpenForm strFormName, , , strLinkCriteria
Else
'record found code
'since the record already exists, open the form to the existing record
DoCmd.OpenForm strFormName, , , strLinkCriteria
End If
If I check the table after the insert (which runs without error) the new record does not appear. when next form opens it opens to a record without the identifiers.
' record not found code
Dim strSQL As String
'if the record does not exist create the record
strSQL = "INSERT INTO " & strTableName & " (" & strFieldName & ") VALUES (" & strFieldContent & ");"
DoCmd.RunSQL strSQL
Me.Requery 'this requeries the coursesite form
'then open the form to the new record
DoCmd.OpenForm strFormName, , , strLinkCriteria
Else
'record found code
'since the record already exists, open the form to the existing record
DoCmd.OpenForm strFormName, , , strLinkCriteria
End If
If I check the table after the insert (which runs without error) the new record does not appear. when next form opens it opens to a record without the identifiers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The database compacts on close.
Yesterday I also tracked the variables--they all seem fine.
Access version 2003 with SP3 on XP.
Not linked tables.
I will try running with the user warnings on within the hour.
Yesterday I also tracked the variables--they all seem fine.
Access version 2003 with SP3 on XP.
Not linked tables.
I will try running with the user warnings on within the hour.
ASKER
This was an issue of timing.
When the warnings for the INSERT into tbl2 was removed it indeed revealed that the insert was not performed because it would cause key violations. The key violation was not from a duplicate identifier in tbl2 it was because the record had created an identifier in tbl1 and a record had to exist in tbl1 before tbl2 (referrential integrity).
There was also a second issue. In the code I pass the linkcriteria yet on the second form I set the form query to look at a textbox containing the linking information on the first form using Forms!etc. When I removed this criteria, form2 opened perfectly pointing to the correct records.
Kudos to the HiTechCoach!
When the warnings for the INSERT into tbl2 was removed it indeed revealed that the insert was not performed because it would cause key violations. The key violation was not from a duplicate identifier in tbl2 it was because the record had created an identifier in tbl1 and a record had to exist in tbl1 before tbl2 (referrential integrity).
There was also a second issue. In the code I pass the linkcriteria yet on the second form I set the form query to look at a textbox containing the linking information on the first form using Forms!etc. When I removed this criteria, form2 opened perfectly pointing to the correct records.
Kudos to the HiTechCoach!
ASKER
Constant application of basics makes for efficient troubleshooting!
ASKER