Code run correctly once but errors each time after that

[davegri]

Whoa!!
Let's get an understanding here. Do you want to write many new records to Problem_Record when the button is clicked? Many, because the listboxes are multiselect.
If so, you DO need to do an AddNew/Update for each one.


Now, for ProblemID. Your listboxes show a text field. You require a long integer to satisfy ProblemID. That means you have to have a multicolumn listbox which will include the ProblemID and the Description. Have you taken that into account? Have a look at this:
http://software-solutions-online.com...umn-listboxes/

[PinkLady50]

Yes I do want to write many new records to the Problem-Record. When this works it is doing just that! Perfect.
Here is an expanded copy of my form which shows three columns
ProblemID, Category ID, Problem

Here is the Row Source for the first box. Each box uses a different Category ID to filter it.
LstCAR Row Source - SELECT problem.ProblemID, problem.[Category ID], problem.Problem FROM problem WHERE (((problem.[Category ID])=1));

[davegri]

This should get the problemID:

rst!problemID = lstWhatever.Column(0, varItem)

[Micron]

1) If Me.lstMyListBox.ItemsSelectedCcount = 0 Then
msgbox...
Exit Sub
End If

As for the other, hope my fellow forum members chime in if they have an opportunity. I will have to look at this in more depth later (gotta run); just had a moment to address the first question. Thought you might have orphaned records. If you have a one to many relationship and are creating records, the one side has to exist before you can create an entry on the many side. Not sure if that helps.

[PinkLady50]

Ok. I have put the rst!Addnew and rst!Update back in my code. My code now looks like it did in post #6.

It is getting an error on this line.

Me.nonconformance_record_NonconformanceRecordID = problem_record.NonconformanceRecordID

(when I was in Debug mode and I hovered over the first part it shows the correct NonconformanceRecordID - which is currently 445)
(when I was in Debug mode and I hovered over the second part it shows the NonconformanceRecordID =Null)

I have tried several version of this line of code and still can't get it to work.

SO I know this is holding the correct value. What I need the other end of this statement to write that NonconformanceRecordID to the Probblem_Record tbl

It is creating a record in the Nonconformance_record tbl but it is not "linking" the NonconformanceRecordID to the Probblem_Record tbl so I can pick up the records from the listboxes.

Any suggestions would be greatly appreciated.

[davegri]

I assume your form is bound to nonconformance_record. It appears that the current record for nonconformance_record does not yet exist when looping thru the listboxes. In that case, let's try to force a save:

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

    if Me.dirty then me.dirty = false   'force record save
    debug.print "Key is " & me.nonconformanceRecordID 

    '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

Make sure you have a textbox named nonconformanceRecordID bound to that field.
Then down near the end, take out the "if form.dirty..." and the "docmd.goto..."

You can determine if the record exists yet by leaving out the "if me.dirty..." and see if you get an error with the debug.print because the field is empty.

[PinkLady50]

Dave!
You just said something that clicked. I don't have a text box for NonconformanceRecordID on the form. Do I need to add one and make it non-Visible?

[PinkLady50]

Added the text box which shows the record number on the form! Made the other code changes.
Now I get

Me.nonconformance_record_NonconformanceRecordID = problem_record_NonconformanceRecordID

Error
You can't assign a value to this object

[davegri]

You cannot change/update an autonumber primary key in its own table. Ever.
Where is that code? I don't see it in any previously posted...

[PinkLady50]

I don't want to change it. I want to pass it to the field NonconformanceRecordID in the Problem_Record tbl to create the linking records.

How do I write a line(s) to do that. The textbox is showing the correct NonconformanceRecordID so I know it is being picked up.

[davegri]

Me.nonconformance_record_NonconformanceRecordID = problem_record_NonconformanceRecordID

That code makes no sense. Again, where is it in your posted code?
The field on the left of the "=" is the receiving field...

[PinkLady50]

Here is the latest code

      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("Problem_record") 'Form Recordsource = Nonconformance_Record tbl
      If Me.Dirty Then Me.Dirty = False   'force record save
    Debug.Print "Key is " & Me.nonconformance_record_NonconformanceRecordID
    
    '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)
               Me.problem_record_NonconformanceRecordID = Me.nonconformance_record_NonconformanceRecordID
               rst.Update
            End If
             
        Next
   End With

OH!! I thought the field on the left was the sending field!

I flipped it around. It is now creating the linking records but look at the screen shot. It is only writing the NonconformanceRecordID once and it should be on every record like the records above.

This is the line I have had trouble with from the beginning.

REcord 448 is the latest record.

[davegri]

Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("Problem_record") 'Form Recordsource = Nonconformance_Record tbl
      If Me.Dirty Then Me.Dirty = False   'force record save
    Debug.Print "Key is " & Me.nonconformance_record_NonconformanceRecordID
    
    '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)
              debug.print "added from lstCAR # " & reccnt & ", " & lstCAR.itemdata(varItem) 
               Me.problem_record_NonconformanceRecordID = Me.nonconformance_record_NonconformanceRecordID
               rst.Update
            End If
             
        Next
   End With

Add the redline to see what's happening

Also note that your code will not update existing records in Problem_Record. It will only create new records.

[PinkLady50]

It does not return any errors. It is running like before.

Also note that your code will not update existing records in Problem_Record. It will only create new records.

I only need it to create records not update.

[davegri]

The debug.print displays in the VB Editor Immediate window.
CTRL-G while in the editor.