UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error Inserting Record Into Table Using Vba And SQL, Access 2013    
 
   
ScottyBee
post Jul 18 2019, 06:18 PM
Post#1



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello Everyone,

I have a form with multiple subforms. Each subform has radio buttons contained within a frame. I have a click event that updates the Rating in the Ratings table using the values selected. If the main form has no corresponding records in the subforms, radio buttons are null, then the code inserts the value chosen into the Ratings table.

When I click on a Rating for the first subform, no error appears. When I click on a rating for the second subform, I get error "You cannot add or change a record because a related record is required in table Categories". I have to escape out but when I do, the Ratings have been entered into the table.


Here is my code:

CODE
Private Sub Frame1_Click()
    Dim dbs As Database
    Dim sSQL As String
    Dim RatingValue As Integer
    Dim SubSystemIDValue As String
    Dim frmCategory As String
    RatingValue = Frame1.Value
    SubSystemIDValue = [Forms]![EER Form]![SubSystemID]
    frmCategory = 1
    Set dbs = CurrentDb
    
    If DLookup("Rating", "Ratings", "[fk_SubsystemID]=" & SubSystemIDValue & " AND [fk_CategoryID]=" & frmCategory) Then
        MsgBox "Record already exists so will be updated"
                
        Else
        MsgBox "Record does not exist so will be inserted"
        
         sSQL = "INSERT INTO Ratings (fk_SubSystemID, Rating, fk_CategoryID) " & _
             "VALUES ('" & SubSystemIDValue & "','" & RatingValue & "','" & frmCategory & "');"
         dbs.Execute sSQL, dbFailOnError
        
    End If
    
End Sub


Any ideas on why?

I have inserted an image below with screenshots of my form, table relationships and the query that the first subform is based on. Thanks!

Attached File  Insert_Records_Error.jpg ( 352.13K )Number of downloads: 8
Go to the top of the page
 
June7
post Jul 18 2019, 09:31 PM
Post#2



Posts: 730
Joined: 25-January 16



You have a subform for each category?

If you want to provide db for analysis, follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
ScottyBee
post Jul 19 2019, 02:13 PM
Post#3



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello June 7,

Thanks for your offer to check out the database. It is attached below:

Attached File  Database.zip ( 96.15K )Number of downloads: 2


The EER Form should load automatically. You will see 12 subforms. Only the first two, from left to right, have code for the click event of the frames. At bottom of main form, you will see there are nine records or SubSystems. The SubSystem ID field is at the top of the main form. Records 1-4 and their respective subforms have ratings already entered. Record 5 has entries for just the first 2 categories.

To reproduce the error, go to record 6, SubsystemID = 6, and click any rating 1 to 5 on the first subform. Next, click any rating on the second category (subform) and you will get the error. You will have to ESC out. If you open up the Ratings table, you will notice those ratings have been entered. Sometimes I would have to do a refresh on the form before the entries appeared in the table.

Fk_SubSystemID fk_CategoryID Rating
6 1 ?
6 2 ?

Thanks
Go to the top of the page
 
ScottyBee
post Jul 22 2019, 06:22 PM
Post#4



Posts: 38
Joined: 6-June 05
From: Portland Oregon


I was able to figure it out. I had a default value for the CategoryID field of 0 in the underlying table. Since the Category ID's are only from 1 to 12, I was getting the referenced error. This was a situation where all of the code debugging in the world wasn't going to solve my issue and I made the false assumption that my code was in error when it was the underlying table.

With that said, I did learn some new Debugging tips thanks to the link that June7 posted. I also discovered some other potential problems with duplicate object names so this effort was not in vain. smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 02:03 PM