.addnew

[nvus]

This is driving me NUTS!!!!!!!!

I have the following code that works great ..... with 2 small exceptions

Private Sub lstGoals_AfterUpdate()
Dim ctl As Control
Dim varItem As Variant
Dim sTemp As String

Dim DB As Database
Dim rst As Recordset
Dim iRN As Integer

    Set ctl = Me.lstGoals      ' Name of the Listbox
    
    Set DB = CurrentDb
    Set rst = Nothing
    Set rst = DB.OpenRecordset("tbOpenGoals", dbOpenDynaset)
    iRN = Forms![Member Data Entry]![RecordNo]
    
'For each item in the list box we are going to append each one with the
'respective project # to tbOpenGoals then we are going to combine each
'item together in one field

    For Each varItem In ctl.ItemsSelected
        With rst
            .AddNew
                ![fkCSMGoalsID] = varItem + 1
                ![fkRecordNo] = iRN
            .Update
        End With
    Next varItem
        rst.Requery
        rst.Close
        DB.Close

'Need to do something with the remaining space and comma
    For Each varItem In ctl.ItemsSelected
        sTemp = sTemp & ctl.ItemData(varItem) & ", "          'Adds the comma and space to the string
    Next varItem
    
    If sTemp = "" Then
    Else
      'Remove the last comma and space
      sTemp = Left$(sTemp, Len(sTemp) - 2)
    End If
 
    'This part puts the string of information into the field named tOpenGoals ***
    Me!tOpenGoals = sTemp

End Sub

NOTES:
For this case I will say the user has selected ITEMS # 2 and 4


1. ![fkCSMGoalsID] = varItem + 1 ----I shouldn't have to put the +1, but it I don't it stores 1 and 3--hmmm
2. The rst shows the following results: 2, 2 and 4-- adding 1 extra item that WASN'T selected

WHY??????

I'm not overly concerned with #1, just curious..but need to resolve #2 in a very bad way....


The only other code that's attached to the lstbox is:

Me.[Data Entry].SetFocus
pgOpenGoals.Visible = False
Me.Requery

Which has nothing to do with the process.

Thanks in advance.

 

[DJkarl]

You do know that the listbox index starts at zero and not one right?

So the second item selected in the listbox would be Varitem #1 because the first item selected would be Varitem zero.

 

[pbaldy]

Plus, why go through the selected items twice? It's inefficient, and it may be causing your other problem. Just do the

sTemp = sTemp & ctl.ItemData(varItem) & ", "

at the same time you do the append.

 

[nvus]

Paul,

Thanks for your suggestions...and yes I did know the list items start at 0....guess I was so determined to figure out the 2nd issue and lost my mind on the 1st issue.

As far as the 2nd issue, I didn't realize the error (effeciency) I was making and did as you suggested for

sTemp = sTemp & ctl.ItemData(varItem) & ", "

, but it didn't resolve the issue off adding 1 extra record.

Again, thanks for showing me the error of my ways.....but if you or anyone else has another suggestion, I would certainly love to hear it.

 

[pbaldy]

I'm sorry, I think I misread your OP. I thought just the string had the extra. You're saying 3 records are added to the table instead of 2? I'm not sure it affects your problem but I'd disambiguate your declarations, to:

Dim DB As DAO.Database
Dim rst As DAO.Recordset

Presuming that doesn't help any, can you post a sample?

 

[nvus]

No that didn't help....I'm attaching a very very stripped down version of the db.


I had to delete every peice of data in the tables to get it small enough. In tCSMGoals you need to input a couple of test records.


Thanks so much.....

 

[pbaldy]

Your code is in the after update event of the listbox. It will fire every time they click or unclick a selection. You probably want to put it behind a button they click when they're done selecting.

 

[nvus]

OMG....that's what it was....but why? I don't understand the difference?

Once it worked on the OnClick event, I tried it in an OnExit event and it worked there also (one less move the user needs to take...as the only thing on this tab is the list box, so trying to avoid the need for them to move to a button).

You have no idea how greatful I am and thank you so much. This step was critical because the users MUST be able to make multiple choices from the list box, but the choices made from the list box must later be used to close the case. So the choices COULDN'T be stored like 1, 2, 3, etc. rather had to be stored in another table as a record for each choice and include the recordID and and the way it was working it was storing 1, 1, 2, 3...which of course incorrect data.

Again, thanks so much and have a great day.

 

[pbaldy]

OMG....that's what it was....but why? I don't understand the difference?

The "why" is simple when you think about it. As I mentioned, the update event fires with every click selecting or unselecting an item. Keep in mind that when you click to select the second item, the first is still selected. That means when the code fires for the second selection, your loop through selected items gets the first item again. If you selected 3 items, you'd have ended up with 3 of the first, 2 of the second and 1 of the third. You either needed to use a button or find an event that would only fire once, which you did.