Access 10: Need help writing data from listbox to table.

[mariaanthony50]

Hi,
I am an "Advanced Novice" user. I know a little but not a lot!

I have a database that I did not design but have been asked to make changes to.

I have a Multiselect Listbox lstCAR that I need to write the results from to a table when the record is created. The old database was using a bunch of checkboxes and we ran into some issues.

I can get the Listbox to correctly display the required data - good there! But the data field called Problem is not part of the Nonconformance_Record which is the Record Source for the form.

So how do I connect the results of the listbox to the Nonconformance_record?
lstCAR = Displays Problem field from the Problem Table.
Problem ID in Problem tbl needs to connect to Problem ID in Problem_Record tbl and to Nonconformance_Record tbl

I have attached a copy of the Relationships and the Form if that helps?

Many thanks!!

 

[jdraw]

Perhaps I have misunderstood your post, so before changing anything --you should clarify the situation.

I responded but have since re-read your post and am making a best guess at understanding your issue.
According to your relationships, multiple problems can be selected and assigned to a NonConformance record by means of the Problem_Record table and the relationship between it and the Problem table and the nonConformance table.

I have made a small model with those tables to show that the Problems associated with a NonConformance record can be found with a query involving these tables. For a given NonConformance record with a problem(s), you can get the associated ProblemId from the NonConformRecordProblems table and get the ProblemDescription from the Problem table.

I don't understand putting the "problem" into the nonConformance record. I don't believe it belongs there. Perhaps you can tell us more.

As for the multiselect list, I think you would take the RecordId of the record being reviewed, and any Problem selections from the listbox, then insert records into the junction table (NonConformRecordProblems table) in the diagram.

Good luck.

Note: First attempt to include a model from Toad free.

 

[mariaanthony50]

You are correct. I made a mistake in my last message. My lstCAR pulls both the ProblemID and Problem. I am only displaying the Problem for the user to see.

So, the ProblemID needs to write to the ProblemID in the Problem_Record tbl which ties to the ProblemID in the Nonconformance_record that it is linked to.

I provided a screen shot to show how the records are writing up until now but since I have changed the form to this lstbox I am not sure how to write an VBA statement to link all of this together.

 

[jdraw]

I was updating my last post when I saw your message. Please see my last post.

Update:
When you cross post, advise users that you have done so.

This is why.

 

[mariaanthony50]

Thanks for the insight! I didn’t realize this was already a junction table setup until you explained it. It is called Problem_Record.
My lstCAR displays a multiselect list of Problems with the Record Source as the Problem tbl.
Now I need to write the selected values to the Problem_Record tbl.
The problem I am having is that the form Record Source is the Nonconformance_record tbl.
Is there a Select statement I use to modify the code below to write the selected items to the Problem_Record tbl. I am currently using this code to run a report but I am not sure how to modify it to write the selected items to a table.

'Compliments of Allen Browne

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "rptProblem"

'Loop through the ItemsSelected in the list box.
With Me.lstCAR
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ProblemID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Problem: " & Left$(strDescrip, lngLen)
End If
End If

 

[jdraw]

The listbox will have a Rowsource ProblemTable

Your code shows how to take the Selected Problems associated with this NonConformanceRecord and make a list (numbers with commas).
You have to take that list and create a new record in the Problem_Record table (for each selection) by using the current NonConformance record id, and then loop through the SelectedItems.
The loop would have an rs.AddNew, then the NonConformance record id, your Selected items as ProblemID and an rs.Update


Here is a link to a post by Pat Hartman with some sample listbox code to add records to a table that may show the concept better than my text.

 

[mariaanthony50]

The listbox will have a Rowsource ProblemTable

Your code shows how to take the Selected Problems associated with this NonConformanceRecord and make a list (numbers with commas).
You have to take that list and create a new record in the Problem_Record table (for each selection) by using the current NonConformance record id, and then loop through the SelectedItems.
The loop would have an rs.AddNew, then the NonConformance record id, your Selected items as ProblemID and an rs.Update


Here is a link to a post by Pat Hartman with some sample listbox code to add records to a table that may show the concept better than my text.

Help! I have been playing around with this since yesterday and still can't get it to work. I don't know how to write the code for the
"The loop would have an rs.AddNew, then the NonConformance record id, your Selected items as ProblemID and an rs.Update"

Also, I have will have several listboxes on the form and each lstbox would have multiple items selected. Can I use this same code for each listbox or would I need to combine all this into one set of code to run something like a commandbutton at the end of the page to run and check all the listboxes.

The fields in the top of the form write directly to the Nonconformance_Record tbl, but the bottom of the form which has the listboxes needs to loop through and pick up whatever is selected box and create a new Problem_Record for each Nonconformance_Record. And the listboxes are tied to both tables using the ProblemID.

I'm a little out of my league with this and it is the last piece I need to complete this project.

Any help would be greatly appreciated.

 

[mariaanthony50]

Here is my latest attempt!!!!

I am getting an error on the Select and Where statement so I must not be writing this correctly.

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
'Loop through the ItemsSelected in the list box.
With Me.lstCAR
    For Each varItem In .ItemsSelected
        If Not IsNull(varItem) Then
        'Build up the filter from the bound column (hidden).
        strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
        'Build up the description from the text in the visible column. See note 2.
        strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
        End If
    Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
    strWhere = "[Problem ID] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
        strDescrip = "Problem: " & Left$(strDescrip, lngLen)
        rst.AddNew
        Select [Problem ID] FROM problem
        Where (((Problem_record.[Problem ID])=(Nonconformance_record.[Problem ID])));
        rs.Update
        End If
End If

 

[jdraw]

I put some code tags on your code.
I'm heading out for a few hours but will look at your material later.

I did notice that you have an SQL statement in the middle of your vba??

      [B] rst.[/B]AddNew
       [B][COLOR="Red"] Select [Problem ID] FROM problem
        Where (((Problem_record.[Problem ID])=(Nonconformance_record.[Problem ID])));[/COLOR][/B]
        [B]rs[/B].Update

That's not how it works.

you have to use recordset methods. Take a look at Pat Hartman's example again.
Also you need to Dim the DAO.Recordset and what ever variable you assign rs, or rst you have to be consistent when working with that recordset.

The specifics are here.

 

[mariaanthony50]

I see that Pat's code uses a Public Function and I was using a Private Sub. Not sure how these two work together. I also wasn't sure if I should keep my existing code and integrate Pat's or scrap mine so I added his to the bottom.
The fields at the top of the form are writing correctly to the Nonconformance_record. It's jus that the listboxes are not creating an associated record in the Problem-record tbl.

problem tbl = ProblemID
problem_record tbl = ProblemID and NonconformanceID
nonconformance_record tbl = NonconformanceID

Lastly the form as 7 listboxes which are all multiselect. How do I integrate these into the code or should. Each of the multiselect items would be a separate record in the problem_record tbl.


Private Sub Add_Record_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Public Function CreateAttendanceRecords(lstCAR As ListBox) As String 'My other listboxes are lstMod, lstCOR, lstJust, lstPrice, lstPPMAP, lstSmall
End Function
'On Error GoTo Err_CreateAttendanceRecords

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim i As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QASA!qryMultiselect '
Set rst = qd.OpenRecordset
'Loop through the ItemsSelected in the list box.
With Me.lstCAR
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Problem ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Problem: " & Left$(strDescrip, lngLen)

For Each i In lstCAR.ItemsSelected
rst.AddNew
rst!ProblemID = lstCAR.ItemData(i)
rst!ProblemID = probelm_record.ProblemID
rst!probelm_record.NonconformanceRecordID = nonconformance_record.NonconformanceRecordID
rst.Update
Next i
Set rst = Nothing
Set qd = Nothing
' CreateAttendanceRecords = "Records Created"
End If
End If

 

[jdraw]

It isn't the Public Function that is important in Pat's code.
It's this series of statements which identifies the recordset;
gets selected item from the listbox; assigns values to the fields in the new record in Problem_record;
then Updates the Problem_record with the new record.

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb
Set rs =db.OpenRecordset("problem_record")

' code to loop through  selectedItems from the listbox
...
   rs.AddNew  'indicates about to add a new record to table Problem_record
   rs!ProblemId = the ProbleId of this selectedItem in the listbox
   rs!NonconformanceRecordID = Me.NonconformanceRecordID 'This is the id of the record in the Form
'add any other fields for Problem_Record here
    rs.Update  'update the Problem_Record table with this record
....
..Loop

You have a function definition from Allen Browne in the middle of your other code. That is not how to define a function or call/use it.

Let's get a record created from a form with a list box first.

Update: I have created a sample database to mockup the issue. I have attached it in zip format.

 

[mariaanthony50]

JDraw

Thank you so much for all your help. Especially the Mock db. I learned so much from reviewing your code and comments!! I copied everything over to mine and I got the following error on these two lines.

Compile Error - Syntax error.
90 rst!Problem ID = lstCAR.ItemData(varItem)
100 rst!nonConformance ID = Me.nonConformance ID

The code looked ok and I didn't get any error but it also wasn't writing to the Problem_Record tbl. So I looked over the code and realized my field names had spaces in them so I changed them. (Poor design by the person who developed this). that's when I got the error. Before I go changing fields in tables I thought I would get your input on whether I am right or if it is something else.

 

[jdraw]

Glad you liked and found the sample database helpful.

I do not use spaces in field names, so my guess is that is the cause of the issue.
I f you check the tables and the code in my sample, I'm sure there will not be spaces in the names.

90 rst!Proble[B][COLOR="Red"]m I[/COLOR][/B]D = lstCAR.ItemData(varItem)
100 rst!nonConformanc[COLOR="Red"][B]e I[/B][/COLOR]D = Me.nonConforman[COLOR="Red"][B]ce ID[/B][/COLOR]

 

[mariaanthony50]

SUCCESS! The logic works perfectly once I changed the fields names! (no small feat in an existing database).

I will need to use the extra line of code for the Description. The Description field which was part of the old form was a "comment" field that was there for explanations. The old form allowed you to enter a Description for each item selected. It is just a text field. It is in the Problem_Record tbl.

ProblemRecordID, NonconformanceRecordID, ProblemID, Description

My lstCAR is using the Problem tbl as it's Record Source. 3 Columns, hiding column1 & 2.

rst!desc = .Column(2, varItem)

You are showing it as Column 2 in your sample which is tied to the Problem tbl, mine is tied to the Problem_Record tb.

How would I change the code to write the Descrption field to the Problem_Record for each ItemSelected.

Lastly!!! There are multiple listboxes on the table to be checked. What is the best way to integrate the ItemSelect routine for each box?

 

[jdraw]

The Problem Description belongs in the Problem table.
If you need to see it along with info in Problem_record, you build a query. Since Problem_record has the ProblemID, you can get any/all fields in Problem by means of the ProblemID

Select Problem.* from Problem INNER JOIN Problem_REcord
ON Problem.ProblemID = Problem_Record.ProblemID
WHERE Problem_Record.ProblemID = Put a recordId in this position to test

I would suggest
a) don't try to reuse that lstbox
b) use the code as a sample and create similar event procs for your other listboxes
c) you could write a function with parameters, but that is more complex and needs to be analyzed and designed(may be more work than b).

Glad you have success,

 

[mariaanthony50]

I'm having trouble getting this Description box to work correctly.
I added the field to my form using the Field List. I selected the field Description_Probelm_record.

I can't test it because it won't let me enter data in the field. I made sure it wasn't locked and looked for other things that would cause it not allow edits.

Each lstbox such as lstCAR would only have a Description if in this case ProblemID = 8 (which is Other). For Other we require a Description.

Again, this needs to write to that field when 8 is selected.

How can I modify the code to pick up this Description for this ProblemID?

 

[jdraw]

As I said, "Problem" Description belongs in the Problem table, NOT the Problem_record table.

When you select a Problem from the listbox, you are picking up ProblemID and creating a record identifying that Problem is associated with this nonConformance record and add it to the Problem_Record Table.

In the code I gave in the mockup, it shows where Description statement would go, but I made it a comment, since it doesn't belong in the Problem_record table.

The Problem description is shown in the listbox.

The Problem description can be displayed by using the ProblemID and selecting from the Problem table.

 

[mariaanthony50]

Thanks
I was trying to get around putting the Problem Description in the other tbl but I will give it a shot. It does seem to make more sense with the lstbox.

I copied this loop section of code fir the fist lstbox and added it right below the last End With for the next lstbox but I am getting an Error 3421 when I run it.

Option Compare Database
Private Sub cmdAddRec_Click()

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
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")
'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
'*********You don't need to store desc of problem in the the Problem_Record table ****************
'*********But this where it would go *******************
' rst!Description_problem_record = ProblemID "8"
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
'*********You don't need to store desc of problem in the the Problem_Record table ****************
'*********But this where it would go *******************
'rst!desc = .Column(2, varItem)
rst.Update
End If
Next
End With
rst.Close
Set rst = Nothing
MsgBox recCnt & " records added to Problem_record table by review of NonconformanceRecordID " & Me.NonconformanceRecordID

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

 

[mariaanthony50]

I rechecked putting the Problem Description in the Problem tbl and here is why it won't work.
Each time a Nonconformance Record is created it is selecting the type of Problem from the Problem table (finite list) via the ProblemID.
It then creates both a Nonconformance Record which is contract information and the Problem information for that record is stored in the Problem Record tbl.
The Problem Record table has the following:
ProblemRecord ID (auto) NonconformanceRecID, ProblemID and Description.
For each Problem record it allows the user to enter a Description or "Comment" of the issue with that assocated NonconformanceRec and ProblemID.

I sent some screen shots to help.

 

[mariaanthony50]

READ FIRST
I just looked at your Mock db and you have it correctly in there.
The problem_record tbl does have the Description in it!
How do I get that field for each Item.Selected in the listbx.