Access 10: Need help writing data from listbox to table. (1 Viewer)

[mariaanthony50]

Before we changed to this new form, the users had the ability to provide a Description for anything they wanted which led to a lot of anecdotal information.

By going with the form we are using this as a way to control their input.
I would "Love" to be able to limit the input to allow only when the ProblemID =8.

 

[jdraw]

I'm not following all the boxes on your form, but here is a method to add a AuxProblemText when ProblemID 8 is selected.
I think(again I don't know your business) that your form's recordsource is wrong. I think it should be the nonConformance table.
As I understand the purpose of the FORM is to create records in the junction table Problem_Rec.

rst is referring to the Problem_Rec not some combination of nonConformance and Problem_rec.

I'm confused with your form and your code --still trying to figure out WHAT you're trying to do.
I don't know which button to select, and I don't see why you need a GoTo newrec. With a form bound to nonconformance table, you process the records in sequence.
.
Here is altered code to open a window for user to enter the Other Problem text and record it in Problem_Rec

20        Set dbs = CurrentDb
30        Set rst = dbs.OpenRecordset("Problem_Record")    'Form Recordsource = NonconformProbRec qry
          'Loop through the ItemsSelected in the list box.
40        With Me.lstCAR

              'here we are getting the Problemid of the selected Items '*********************************
50            For Each varItem In .ItemsSelected
60                If Not IsNull(varItem) Then
70                    rst.AddNew
80                    recCnt = recCnt + 1
90                    rst!ProblemID = lstCAR.ItemData(varItem)
                      'rst!NonconformanceRecordID = Me.NonconformanceRecordID  'Neither of these two lines worked once I added the IF for the txtCARDes
                      'rst!NonconformanceRecordID = nonconformance_record.NonconformanceRecordID
100                  [COLOR="Purple"][B][I] If rst!ProblemID = 8 Then
110                       Me.txtCARDes = InputBox("Other problem in your own words --JED", "SpecialDescription for Other")
120                       rst!Description = Me.txtCARDes
130                   End If
[/I][/B][/COLOR]

 [COLOR="Green"]                     ' Me.txtCARDes.ControlSource = "Description"

                      '*********You don't need to store desc of problem in the the Problem_Record table ****************
                      '*********But this where it would go *******************
                      'SELECT nonconformance_record.*, problem_record.Description AS Description_problem_record, problem_record.Description  FROM nonconformance_record INNER JOIN problem_record ON nonconformance_record.NonconformanceRecordID = problem_record.NonconformanceRecordID;
                      'rst!desc = .Column(2, varItem)
                      'rst!txtCARDes = Me.Description[/COLOR]
140                     rst.Update
150
160               End If
170           Next
180       End With
190                   rst.Close '===================jed I put this in to force a write to problem_rec without processing all the other boxes/etc on the form.

I wrote a test problemText and it wrote to the Problem_rec.(attached jpg)

Because you use rst through out you code and addNew and rst.Update, you are writing various records to Problem_Rec --at least as I read the code (and I admit I don't fully understand the list boxes nor the Form and what all the pieces/controls represent.

 

[mariaanthony50]

jed,
This part of the code work correctly ( it writes the NonconformanceRecID and PRoblem ID for each record in the Problem_Record table

With Me.lstCAR

50 For Each varItem In .ItemsSelected
60 If Not IsNull(varItem) Then
70 rst.AddNew
80 recCnt = recCnt + 1
90 rst!ProblemID = lstCAR.ItemData(varItem)
rst!NonconformanceRecordID = Me.NonconformanceRecordID ]

When I add your code I get an error message.

If rst!ProblemID = 8 Then
110 Me.txtCARDes = InputBox("Other problem in your own words --JED", "SpecialDescription for Other")
120 rst!Description = Me.txtCARDes
130 End If

Error 3201 You can not add or change a record because a related record is required in nonconformance_record table.
To get your code to work I need to comment out this line.
'rst!NonconformanceRecordID = Me.NonconformanceRecordID

When I do this your code now works and provides the msg Input box for the Description and it writes to the Problem_Record table BUT
if you look at the test record you created (record 391 in the .png) , it is writing the ProblemID and Description from the box but is missing the NonconformanceRecID which ties this to the other table.

I can't figure out why that one line of code won't work. It works correctly before I add your new IF code for the Description.
But they won't work together!!
This is the last piece!!
WE are so close!!!!

BTW.... The rst.Addnew, rsUpdate are correct. It allows me to create multiple records based on the potential number of itemsSelected in the lstbox.

Again, your assistance is greatly appreciated.

 

[jdraw]

Error 3201 You can not add or change a record because a related record is required in nonconformance_record table.
To get your code to work I need to comment out this line.
'rst!NonconformanceRecordID = Me.NonconformanceRecordID

This error is saying that you can't add a record to a child or junction table without having a corresponding record in the parent table.

As mentioned in previous post, I don't understand the buttons on the NonConformance form.

Perhaps the Recordsource of the Form should be the nonConformance table. With a bound form, you can proceed through the recordsource in sequence, which is how I understood your initial set up.

If you have a number of NonConformance records that you want to review and possibly edit, and create related Problem_rec records if appropriate, then the bound form should work.

My concern with your form at the moment is the repeated use of rst in the code. Not sure why you need it with the other listboxes???

For your own benefit, and to assist communication, write (even point form) WHAT the purpose of the form is.

As for the code I showed, InputBox will allow the user to enter some data which gets placed in your textbox control on the form Me.txtCARDes, and then it assigns that value to the Description field in Problem_Record ( rst!Description = Me.txtCARDes ). The Problem_record gets written to the table with the rst.Update command.

 

[mariaanthony50]

The database is used for Quality Assurance of contracts.

Reviewers are assigned a number of contracts per period to review for accuracy. This form allows them to enter the contract information in the database via this form. The top of the form captures the contract info and the listboxes provide an easy visual way to display the various categories and related errors for them to select. The only caveat is that with each listbox there is an Other field to allow them to enter a Description for a problem that relates to that category (CAR, Justification, Price etc) but is not part of the defined list.

I duplicated the rst code for the other lstboxes because if seemed like a easy way to duplicate a process once I got one of the listboxes working correctly.
I don't know enough about coding to write a sequence to:
1. read all the listboxes for the ALL the ItemsSelected in one routine
2. provide a Description "input box" for each Other category
3. write the contract record to Nonconformance_Rec
4. create a separate record for each lstbox item selected in the Problem_Rec tbl along with the NonconformanceRecID and the Descritpion when applicable.

If there is a better way to code this to limit the confusion I am open.
The only other button on the form is the Save and Exit (ignore) I couldn't decide if I should use a Save and Exit button or an Add Record. I deleted the Save and Exit button.

Perhaps the Recordsource of the Form should be the nonConformance table.

The form is using the Nonconformance_Rec tbl as the Recordsource.

 

[jdraw]

??OK then where does Contract fit?
My understanding from the beginning is that the user is reviewing nonConformance records
and is associating/assigning ProblemIDs into a junction table Problem_rec. The new wrinkle is that if ProblemID 8 is selected, you want to write a user entered text into the Problem_record table in field Description.

Good luck