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

[jdraw]

You should use code tags when putting code in a post. It will keep all the formatting/indenting and make the code easier to read.

For clarity is this the code you are talking about

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

I'm not sure what lstJust is, but it is a separate listbox. It will have its own Rowsource.
So you need to establish a new recordset object to identify which data should be shown in lstJust.

 

[mariaanthony50]

lstJust is one of seven listboxes on the page that I will need to cycle through the Items.Selected. They all use the same Rowsource as lstCAR but I use CategoryID = to display the correct set of items base on a specific CategoryID.

When I copied the code and changed the listbox name I got the error3421 on row 0 which didn't make much sense. Is there any reason I couldn't duplicate the code or is there something I need to change in each iteration.

 

[jdraw]

Concerning your relationships.png. The original lines seem correct to me. Your hand drawn lines appear to violate normalization rules.

I put the Description in the Problem_record to mockup what you earlier post said. As I got into it with the mockup, I realized that you were storing the same data in multiple locations.

Database fundamental ---put all info about something in 1 table. Use relationships to show your business rules. This is how you use queries/vba to select records related to various tables.

Problem description is an attribute of Problem. It belongs in the Problem table.

Your code on the form doesn't add a new nonConformance record. The nonComformance table is the record source of your Form. As you move from 1 nonconformance record to another, you display the data for that record. You use the nonconformancerecordID and the selections from lstCAR(problemrecords) to add new records to Problem_record table. Problem_record table contains the ProblemID, and that can be used in a query to retrieve the ProblemDescription for that ProblemID.

 

[mariaanthony50]

JDraw

Thank you so much for all your help last week. I am really learning a lot from this project. I used the weekend to clean up the db as best as I could. I removed the duplicate Description field from the Nonconformance_rec tbl. As this point in time I don't think I have the knowledge to make major changes to the db. As it stands today the Description field is in the Problem_Rec tbl not the Problem tbl as you suggested.

I have the form with 7 lstboxes all working correctly and writing to the Nonconformance_rec and Problem_rec tbls!!

The last thing I need is to write the Description to the Problem_rec tbl when a specific Problem ID is selected.

Each lstbox has a specific list of items that comes from this Select statement.
SELECT problem.ProblemID, problem.[Category ID], problem.Problem FROM problem WHERE (((problem.[Category ID])=1));

The Category ID changes with each listbox.

When the 'Other' ProblemID is selected from the listbox I need to write the Description.

For example; When lstCAR ProblemID = 8 then txtCARDes writes to the Problem_Rec.Description.

For example; When lstJust ProblemID = 59 then txtJustDes writes to the Problem_Rec.Description.

The ControlSource for the txtCARDes is blank because I wasn't sure what to use. It is currently pulling a field list from the Nonconformance_rec tbl which it is getting from the Form RecordSource but since I need Description from the Problem_Rec tbl I didn't know what to put there.

Again, thanks for your help and patience as I finish this up.
I need to turn this over to management tomorrow!

 

[jdraw]

???
1)I don't understand what you are writing to the Nonconformance_rec
2) The description doesn't belong in Problem_rec tbl only the ProblemID--so you can get Description from Problem
3) If Other is a ProbemDescription with it's own ProblemID, then perhaps you ned to add more Problem definitions into Problem tbl???
4) What is txtJustDes?

 

[mariaanthony50]

1. The following items are writing to the Nonconformce_rec. :
ProbelmDivision, FiscalYear, Quarter, ContractNumber, ContractSpecialist, Contracting Officer, My Name, No Finding

2. The Description needs to follow the Problem_Record. The Problem tbl is a static list of Problems with a ProblemID and CategoryID. When ProblemID= 8 indicating Other
I need to write the Description to the Probem_Rec. The Problem_Rec is currently getting the ProblemRecID (auto), NonconformanceRecID (links to the contract info), ProbelmID, and Description.
For every contract in Nonconformance_rec tbl there can be multiple Problem_Records. The ProblemID tells me what type of Problem and Description is only allowed where ProblemID =8 which is Other.

I expanded my lstCAR to show you what I am doing. The first column is ProblemID, CategoryID and Problem. Nothing in this table will ever be updated.

But when ProblemID = 8 in the list I need to write the Description to the Problem_Rec.

lstJust is my second lstbox on the right.

 

[mariaanthony50]

Your Mockup.db has this correct.
How would you write a Description to that tbl what way you have it designed.

 

[jdraw]

OK, the fields in the NonConformance record are values to reflect changes/values for that record.
I understand.

Did you mean to add a jpg or file for this
I expanded my lstCAR to show you what I am doing. The first column is ProblemID, CategoryID and Problem. Nothing in this table will ever be updated.

Also But when ProblemID = 8 in the list I need to write the Description to the Problem_Rec.

Not necessarily. If you have ProblemID in a Problem_rec record and you need to show the Problem_rec and the ProblemDescription, you use a query along these lines:

Select Problem_Rec.[Problem Id], ProblemDescription
From Problem_Rec inner join Problem
ON Problem_Rec.[Problem Id] = Problem.[Problem Id]

The point is you don't put Description in all the tables; you have Problem Description in the Problem table. Each Description is uniquely identified by its ProblemId.
You join your tables on ProblemID and select the field(s) you want to display.

 

[mariaanthony50]

JDraw
I'm sitting here at 1am and I finally got it! Sorry you had to repeat it so many times before it finally sunk in ! Your advice has been invaluable to me. I should be able to turn this in once we get out of this East Coast blizzard!
Thank you again for your patience and coaching!

 

[jdraw]

Happy to help.
Good luck with the storm.

 

[mariaanthony50]

JDraw,

In my delirium at 1am I thought I had this working. Well it 'kind of' worked!
When I added the code for the texbox it worked by writing the Description to the Problem_Record tbl when the ProblemID=8

However, it was not writing the Description on the line which has the ProblemID =8, it was writing it to a separate line. Secondly, it only wrote the NonconformanceRecID for the first ItemSelected and not all the items selected.

Before I added the txtbox code it was writing to the Problem_Record and Nonconformance_Record tbs perfectly!

I am using a query of the Problem_Record and Nonconformance_Record tbs (like a junction table query) as the Recordsource for the form. Its called NonconformProbRec qry. This allows me to select Description as the Controlsource of the txtCARDes.

I spent my snow day trying about 20 different things. Each time I changed something I got a different error.

I think if I can get your assistance with the code provided I might actually get this finished.

Attahed: 1. file which shows the query
2. Shot of the table when it was writing the records correctly. On ProblemRecID 358/425 you will see where it writes the Description on a separate line from ProblemID=8 and the other records are missing NonconformanceRecID's
3. Shot of form showing lstCAR, txtCARDes

That's what I need to finish.
:banghead:
BTW..... My management has finally agreed to come up with some money for training!!!!!!

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Problem_Record") 'Form Recordsource = NonconformProbRec qry
'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 'Neither of these two lines worked once I added the IF for the txtCARDes
'rst!NonconformanceRecordID = nonconformance_record.NonconformanceRecordID
If ProblemID = 8 Then Me.txtCARDes = Description
End If

 

[jdraw]

We are missing something basic.
As I have said, based on your posts add graphics. ProblemDescription belongs only in the Problem table based on Normalization rules. Only the ProblemID would be in the Problem_Record table -and if more than 1 problem applied to the same Nonconformance record, then there would be multiple records in the Problem_record table. There would be (in the junction Problem-Record table) 1 record for each problem that is associated with this NonConformance record.

If you want to show the Problem description of each Problem for this NonConformance record to be displayed on the form, then will need a query (possibly a different control) to get the description for that Problem from the Problem table, put the Description on the Form and then requery the the Form to get the new value to display.

If you can describe exactly the purpose of wanting to put the ProblemDescription in the Problem-record that may clarify things.

The key thing to remember is that Data storage and Data display are separate things.
Getting the database design normalized is about storage of business fats. It will simplify display,but
on a form -which is event driven - you may have to requery (get the latest relevant values) the form to get and display the Description for the ProblemID you just selected.

Here is a link to a Steve Bishop video --pay particular attention around minute 17--19 where he explains requery. His example is using recordsource, but with a listbox or combo it would be Rowsource. It's the concept of Requery to update the display that I'm trying to emphasize. But,if this isn't helpful to current situation, then please describe the issue exactly (all details).
Also, Steve Bishop's videos are excellent for learning vba.

Good luck.

 

[mariaanthony50]

You have the concept correct.
"Only the ProblemID would be in the Problem_Record table -and if more than 1 problem applied to the same Nonconformance record, then there would be multiple records in the Problem_record table. There would be (in the junction Problem-Record table) 1 record for each problem that is associated with this NonConformance record.

For each NonconformanceRec (1 record) it is creating the multiple records in theProblem_Record tbl.
If you look at the Problem-Record.png the NonconformanceRecID is showing 7 problems for NonconrRecID 423. PERFECT.
On record 425 it is capturing 2 problems (7 and 8).
So that part is working correctly.

Now... For all the 423 records it is writing both NonconformRecID and the ProblemID for each record. PERFECT.
When I add the IF loop to add the Description, two things are changing (for the worse)
1. It is only writing the NonconformRecID once for record 425 but should be writing it for ProblemID 7 and 8.

2. It is capturing the Description on a separate line but it should be putting the Description only on ProblemRecID line 360 where the ProblemID=8.

If this were working correctly NonconformRecID 425 would have 2 entries on the Problelm_Rec table. One would be ProblemID 7 and ProblemID 8.
AND ProblemID 8 should have the Description "Another Test".
AND the NonconformRecID 425 would be associated with both
ProblemID 7 and ProblemID 8.

So the concept is working but not the mechanics. I'm thinking it has to do with the placement of the IF Description loop with regard to the ItemSelected statement.
Also, In order for the IF loop to work, I had to comment out this statement which I think is causing the NonconforRecID not to write for each record.
'rst!NonconformanceRecordID = Me.NonconformanceRecordID
When this line is included it give me and error and highlights the ME. So it doesn't like this line.

 

[jdraw]

No. You don't want an IF statement, and you don't want description in the Problem_Record table.
I think you are trying to put the problem description on the Form for user to see???
Can you post all the code related to the form, with explanation of what you're trying to do?

 

[mariaanthony50]

Here is pic of the Form.
The top section writes to the NonconformanceRec tbl. Working fine!
The lstCAR displays ProblemID, CategoryID and Problem. User can mulitselect from this box.
If they select ProblemID 8 we want them to provide a Description of the Problem because it does not meet the other options in the lstCAR box.
In the lstJust ( to the right) you will see that the ProblemID for Other =59.

The lstbox is capturing the ItemsSelected. and the database is writing to the correct tables.
I have a lot of stuff that I tried commented out!

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 = NonconformProbRec qry
'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 'Neither of these two lines worked once I added the IF for the txtCARDes
'rst!NonconformanceRecordID = nonconformance_record.NonconformanceRecordID
If ProblemID = 8 Then Me.txtCARDes = Description
End If


' 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 *******************
'rst!desc = .Column(2, varItem)
'rst!txtCARDes = Me.Description
rst.Update
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 "


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

 

[jdraw]

??I think we are dealing with some new business facts, or facts that I haven't understood.

My understanding --you have a series of standard Problems that exist in your Problem table.
It now seems that under some circumstances (not yet articulated) you can have "on the spot problems that do not exist in Problem table" and you want to record that info somewhere.

What exactly is the "business description" for these criteria?
I don't see a table specific to that.

Also what is Problem_bin?

 

[mariaanthony50]

My understanding --you have a series of standard Problems that exist in your Problem table.
It now seems that under some circumstances (not yet articulated) you can have "on the spot problems that do not exist in Problem table" and you want to record that info somewhere.

You are absolutely correct! The Problem tbl is a defined list of items per lstbox that we are tracking. The Other (ProblemID 8) allows us to track "one off" items so we see if there is pattern that we need to start formally tracking.

There is no additional Business Description. That is all this database is tracking for now.
Problems defined in the Problem Table and recording it in the NonconformanceRec and Problem_Rec tables.

We are no longer using Problem Bin and the associated table.

 

[jdraw]

So if 8 is selected you then have to put the "auxilliary problem text entered by the user" into a special field; an since it relates to this NonConformanceRecord and its related Problem_recs, you want it in the Problem_rec table is that correct?

Also, do you keep all problems for a noncoformance record OR do you remove all and start over if/when you review this nonconformance record in future?

 

[mariaanthony50]

So if 8 is selected you then have to put the "auxilliary problem text entered by the user" into a special field; an since it relates to this NonConformanceRecord and its related Problem_recs, you want it in the Problem_rec table is that correct?

That is correct. The auxillary problem text is captured in the Description field and stored in the Problem Record with the NonconformancRecID and ProblemID

All the records in the Nonconformance Record tbl are kept so we can run reports and queries on which contracts or Contract Specialist or Division is having issues.

We cross reference with the Problem_Record tbl to get the exact issues.

 

[jdraw]

Why do some records in the Problem_rec table have descriptions -even though the ProblemId is not 8?