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.
Prisoner362670Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Prisoner362670Author Commented:
Also, if I navigate on and off the next form (the one I'm trying to open to pointing to the new record) eventually the record writes to the table and I open to the new record.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
My first thought is that you will need to requery the form's record source after the insert.

Example:
Me.Requery

Open in new window


If that doesn't fix the issue then please post the code you are using to insert the record.
0
Prisoner362670Author Commented:
If IsNull(DLookup(strFieldName, strTableName, strLinkCriteria)) Then
        ' 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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
That looks like standard code that should would without issue.

It could be an table index issue with inserting new records. To clean up the indexed you need to regularly backup aun the the compact function.  Have you compacted the database with the tables lately?  If not, make a backup and then run the compact function.

If compacting does not fix the issue then:

Turn on the system warnings and confirmations.  When you run the code you will get confirmation messages about the append query and if any records were inserted.

If you are still having issues then it would also help to know:

1) What version of Access you are running?
2) is this a linked table?
3) If linked, is the back end an Access database?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Prisoner362670Author Commented:
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.
0
Prisoner362670Author Commented:
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!
0
Prisoner362670Author Commented:
Constant application of basics makes for efficient troubleshooting!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.