Code run correctly once but errors each time after that

[PinkLady50]

I have this code working correctly. I have tested it numerous times.
The first time it runs through correctly but each subsequent time it errors out.


I know which line is causing the issue but I don't know why.



I don't change anything in the codes or the form from one test to the other! Help.... It's driving me crazy.

If I go in and delete and type the Me.nonconformanceRecordID when it fails it seems to work again. It doesn't like a cut and paste either. I actually have to retype this section of code.

While writing this post I commented out the 'DoCmd.GoToRecord , , acNewRec and it works but then it doesn't clear the form!

What can you suggest,
Should I use a routine to clear the controls?

Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("Problem_Record") 'Form Recordsource = Nonconformance_Record tbl
    
    'Loop through the ItemsSelected in the list box.
    With Me.lstCAR
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstCAR.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
                 
            End If
             rst.Update
        Next
   End With

With Me.lstCAR
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
        
    'DoCmd.GoToRecord , , acNewRec
   On Error GoTo 0
   Exit Sub
cmdAddRec_Click_Error:
   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure cmdAddRec_Click of VBA Document Form_Nonconformance_Record_form"
End Sub

[PinkLady50]

So I added this code to the clear the controls.

My records writing correctly to the tables but I get and error 2448 - You can't assign a value to this object. And the control are not clearing.

Dim ctl as Control
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Or ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then ctl.Value = Null
Next ctl

[PinkLady50]

It seems like when I use the 'DoCmd.GoToRecord , , acNewRec to clear the controls it is causing the link between the form Recordsource - Nonconformance_Record tbl and the Set rst = dbs.OpenRecordset("Problem_Record") to disconnect.
I am novice use so this is my best guess!

[andy49]

Several small points which may or may not help. Are you using option explicit at the top of your code as you haven't declared your recordset rst.

You should also always use rst.close and set rst =nothing.


Sent from my iPhone using Tapatalk

[davegri]

You can't assign a value to this object

If a textbox is bound to the primary key, you cannot update it via code.

I think the rst.update should be within the for..next loop.

      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstCAR.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update   
            End If
             
        Next

Right before the Docmd.gotorecord,,,acnewrec
Put

If me.dirty = true then me.dirty = false

Your posts are confusing because you are describing two problems (or three?), and we don't know which explanation goes with which problem. It's best to stay on one problem at a time. Then a new thread for the next problem.
Also, we really need to know which line caused the error!

[PinkLady50]

I added the changes you suggested.

I figured out the problem but I don't know how to fix it. I can duplicate this over and over.

When I run the code the first time I get an error 3201 You cannot add or change a record because a related record is required in table “nonconformance_record”.

I right click on the Form tab and click Save, I then click Form view and it runs.

It's the last piece I need to finish this!

I would appreciate anyone's help!!!! This is due on Monday 3/20 to management to demo!!!

Option Compare Database
Private Sub cmdAddRec_Click()
    
    Dim varItem As Variant    'Selected items
    Dim strDelim As String    'Delimiter for this field type.
    Dim i As Variant
    Dim recCnt As Integer  'record count to identify how many problem_record
    'were added based on this nonconformance record
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    On Error GoTo cmdAddRec_Click_Error
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("Problem_Record") 'Form Recordsource = Nonconformance_Record tbl
    
    'Loop through the ItemsSelected in the list box.
    With Me.lstCAR
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
             recCnt = recCnt + 1
              rst!ProblemID = lstCAR.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
               rst.Update
            End If
             
        Next
   End With
  With Me.lstJust
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstJust.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
With Me.lstMod
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstMod.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
  
 With Me.lstPrice
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstPrice.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
       
  With Me.lstCOR
 
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstCOR.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
With Me.lstSmall
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstSmall.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
   With Me.lstPPMAP
        'here we are getting the Problemid of the selected Items '*********************************
      For Each varItem In .ItemsSelected
          If Not IsNull(varItem) Then
              rst.AddNew
              recCnt = recCnt + 1
              rst!ProblemID = lstPPMAP.ItemData(varItem)
              rst!NonconformanceRecordID = Me.NonconformanceRecordID
              rst.Update
            End If
      Next
   End With
                 
       rst.Close
       Set rst = Nothing
    MsgBox recCnt & "  records added to Problem_record table by review of NonconformanceRecordID "
    
    
 For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then ctl.Value = Null
        If ctl.ControlType = acCheckBox Then ctl.Value = False
     Next ctl
    
    With Me.lstCAR
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
    With Me.lstJust
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
    With Me.lstMod
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
     
    With Me.lstPrice
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
    With Me.lstSmall
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
    With Me.lstPPMAP
        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm
    End With
    
    
    If Me.Dirty = True Then Me.Dirty = False
    DoCmd.GoToRecord , , acNewRec
   
   On Error GoTo 0
   Exit Sub
cmdAddRec_Click_Error:
   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure cmdAddRec_Click of VBA Document Form_Nonconformance_Record_form"
End Sub

[Micron]

One thing I NEVER do is give fields and controls the same name. You're expecting Access to know that in this line

NonconformanceRecordID = Me.NonconformanceRecordID

one is a recordset field, the other is a form control. Impossible, I'd say, especially if the control is bound to that field.
You are also making solving this harder by not showing which line errs out as you were asked to do.

[PinkLady50]

Micron,
The error says it line 0.
The line 'NonconformanceRecordID = Me.NonconformanceRecordID' should be doing the following"

Problem_record.NonconformanceRecordID = Nonconformance_record.NonconformanceRecordID

For each ItemSelected in the lstbox, I need it to write the Nonconformance_record.NonconformanceRecordID to the Problem_record.NonconformanceRecordID

I thought they needed to be the same name if they are primary keys or fields linked across tables.

I added a copy of my Relationships if that helps.

[davegri]

You don't want to AddNew for each listbox. You want to addnew at the beginning and then edit/update that record for each of the listboxes.

[PinkLady50]

So where in the codes does the AddNew belong? Should it still have rst! ??

[davegri]

do the addnew at the beginning, right after

Set rst = dbs.OpenRecordset("Problem_Record") 'Form Recordsource = Nonconformance_Record tbl

[PinkLady50]

I changed the AddNew. It still comes up with this error.

When I run the code the first time I get an error 3201 You cannot add or change a record because a related record is required in table “nonconformance_record”.

I right click on the Form tab and click Save, I then click Form view and it runs.

Works PERFECTLY once I click Save on the form tab and then click Form view!!

[davegri]

At the end you can now remove

    DoCmd.GoToRecord , , acNewRec

To see exactly where the error occurs, comment out

'''On Error GoTo cmdAddRec_Click_Error

[Micron]

I thought they needed to be the same name if they are primary keys or fields linked across tables.
Not sure if this comment is related to what I wrote
One thing I NEVER do is give fields and controls the same name

I'm saying the table field name and the control should never be the same. If I understand the structure, you have table.field as ProblemRecord.NonconformanceRecordID and your form control is NonconformanceRecordID. If your form control is a text box, it's name should be txtNCrecID or if you like to type, txtNonconformanceRecordID. Or whatever - just not the same. This is what you get when you create a form with a wizard, which is OK, but I rename the controls right away (and usually have Name AutoCorrect option turned off).

I agree with the AddNew advice but I also think you need a check that the selected items count is not zero, else errors will be raised if nothing is selected (unless the Is Null test will circumvent that). Try clicking the button when nothing is selected when you get the main problem sorted out.

As for that, not sure exactly what you're trying to do. Looks like you write form control values into ProblemID and NonconformanceRecordID in table problem_id.
Your relationships show that this is not possible if there is no matching record (nonconformance_recordID) in table nonconformance_record. You're trying to insert something on the many side when it doesn't exist on the one side.

By clicking save, I believe you're saving a record in an abnormal way. Check the tables and see if you have any orphaned records. If not, I think there is something about this process that comes before which you haven't revealed. Maybe you're missing a save operation somewhere before all this. I have to wonder how you get the values into the listbox yet they're not in your table. So is it possible that something before this rst operation hasn't been saved?

[PinkLady50]

1. I need a Select Items count is not zero.. There is a checkbox on the form for NO Findings. If this is selected, there should not be any ItemsSelected in the listboxes. The ItemsSelected in the listboxes are referring to Findings. I am not currently checking for that but I agree with putting something in there for that. Any suggestions?
Before the listbox routines I could put

If "No Findings" = Yes THEN ????

2. I do not have any controls on the form for NonconformanceRecordID. Each time the form creates a record I need it to create a record in the nonconformance_record tbl via the NonconformanceRecordID and multiple records in the Problem_Record tbl via
the NonconformanceRecordID.
When it runs correctly it is creating both sets of records. When it doesn't I get orphaned records in the Problem_record tbl.

3. The listbox values come from the Problem tbl. ProblemID, Category ID, Problem.
LstCAR Row Source - SELECT problem.ProblemID, problem.[Category ID], problem.Problem FROM problem WHERE (((problem.[Category ID])=1));
No Control Source for the listboxes.

Got an error on rst!Update at the end.
I commented it out.

It now runs without any errors but is only writing to the Nonconformance_record tbl. No records are being created for the Problem_Record tble.
I have included a copy of the form in case it helps. The top part of the form writes to the Nonconformance_Record tbl and the listboxes create records in the Problem_Record tbl for each contract (PIIN) in the Nonconformance_record tbl.
At the bottom of the forms is a cmdAddRec button.

Many thanks for all your help!!!