After Update event in Form Adds Blank Rows

[NeedsHelp101]

Hi,

I have a multiselect list box in a form, with a loop on the After Update() event to write all selected values to the table.
However, each time the old record loses focus, the form enters a blank record onto the table.
Also, I don't know if this is important, but when I check the datasheet view for the list box, no values appear in it, though the correct number of rows are present (and the table behind the form gets populated).
Please help me figure out how to prevent this! Thanks!

 

[yummy7]

I am not sure but I think After update event is not right event.Because You are selecting values and after selction ,you want those values to be stored in table. Isnt it?

 

[NeedsHelp101]

Exactly, I want my values to be stored in the table after selection. The thing is, I'm using an *extended* multiselect list box, so when I select several values at the same time, any other event enters those values more than once (and still gives me a blank row). What do you suggest?

 

[yummy7]

Sorry for late response.
The event should be fired when the selection is made. SO evnts may be exit,lost focus or on button click or may be after getting focus on another control.Let me see your code that how you are selcting your values.

 

[NeedsHelp101]

Here it is:
lstMarket is the extended multiselect list box
TrafficForForm is the table to be populated
FMarket is a value in the list box

************

Private Sub lstMarket_AfterUpdate()
Dim dbMarketing 'As Database
Dim FMarket As Variant
Dim rec 'As DAO.Recordset


Set dbMarketing = CurrentDb()
Set rec = dbMarketing.OpenRecordset("tblTrafficForForm")


For Each FMarket In Me!lstMarket.ItemsSelected
    rec.AddNew
    rec!Market = Me!lstMarket.ItemData(FMarket)
    rec.Update
    Debug.Print Me!lstMarket.ItemData(FMarket)
Next FMarket
    rec.Close

Set rec = Nothing
Set dbMarketing = Nothing
End Sub

***********************

Thanks!

 

[NeedsHelp101]

I think your idea about running the loop after the focus goes on another control is a good one... how would I do that?

 

[yummy7]

First check the Foreach loop,if it is selecting all the values.May use MsgBox for this purpose,to see the values.
Then paste all code on required control's Got Focus.
Actually I havnt use Foreach loop.If your loop is right then you should not have any problem.

 

[randy700]

Instead of putting your code in the AfterUpdate event of the list box, I'd try putting it in the Click event of a separate button. Select the items from the list box, then click the button to run the code. You may want to include additional code for what to do if no items are selected.


Randy

 

[NeedsHelp101]

Thank you for the replies, but for some reason I still keep getting blank fields each time focus is removed or the click button is used... I'm not sure what's going on...

 

[yummy7]

You are getting blank fields.It means on clicking button it is not inserting values in tables. Ok tell me that Do you want
1.To insert selected values in one field of a table?
2.And comma(,) should come between values.Like (jam,juice,corn).

 

[NeedsHelp101]

What's happening is that I'm getting all my selected values into the table, plus a blank row for each time I used the command button. So, for example, I could get:
1.
2.
3. New York
4. Kentucky
5. Alabama

and this would come after first selecting New York and Kentucky, then moving to the next record and selecting Alabama.

 

[yummy7]

I used this code on button click event. Put your listname and field name in appropriate places.
Private Sub Command2_Click()
Dim dbMarketing As Database
Dim FMarket As Variant
Dim rec As DAO.Recordset


Set dbMarketing = CurrentDb()
Set rec = dbMarketing.OpenRecordset("YourTable")
For Each FMarket In Me.YourList.ItemsSelected
rec.AddNew
rec!yourField = Me.YourList.ItemData(FMarket)
rec.Update
Debug.Print Me.YourList.ItemData(FMarket)
Next FMarket
rec.Close

Set rec = Nothing
Set dbMarketing = Nothing

End Sub
Its inserting values in my table.hopes it will works on your end also.Any confusion so feel free.

 

[NeedsHelp101]

That's exactly the code I had before, and yes, it does insert values into my table. The problem I'm having is that each time I go to the next record, it also inserts a blank record.
Do you know how to fix that? Did you check if your table has problems with that?

 

[yummy7]

No my table has no problem. I dont know why are you facing this problem. Are you putting this code in button click event?

 

[randy700]

NeedsHelp101,

I see one difference between the code you posted and the code yummy7 posted. Not sure if it makes a difference, but it's worth looking at.

Your code:
Dim dbMarketing [COLOR=red yellow]'[/color]As Database
Dim FMarket As Variant
Dim rec [COLOR=red yellow]'[/color]As DAO.Recordset

yummy7's code:
Dim dbMarketing As Database
Dim FMarket As Variant
Dim rec As DAO.Recordset

Randy