Listbox Code

[manrique83]

I have this form that updates a table once the user has tabbed to the end of the form and on to a new one.

The problem comes when I try and add the data from a multi-select listbox to the field in the table. Instead of adding the data in the same record, it adds a new record with only the chosen options from that listbox and nothing else, can someone help?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim ctl1 As Control
Dim varItm As Variant
Dim myStr As String
Dim strSQL As String
Set ctl1 = Me.InvestHistory
For Each varItm In ctl1.ItemsSelected
myStr = myStr & ctl1.ItemData(varItm) & " ,"
Next varItm
myStr = Left(myStr, Len(myStr) - 2)
strSQL = "Insert Into tbl17a3(invHistory) Values ('" & myStr & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

 

[gol4]

although I strongly advise against doing this because It violates database normalization.
Your problem is you are doing an append query. Try using an update query to the aready created-(Currrent) record.

 

[manrique83]

Thanks gol4,
What will the update query look like?

I know my database is not normalized, I dont have a normalized database and neither does it enforce referential integrity. This is just one big table in temporary use.

But I really need to know how to successfully update a table field with the choices picked in a listbox so someone help???

 

[gol4]

Update tbl17a3 set invHistory = "'" & myStr & "'" where recordid = me.recordid

my guess you don't even need to do the query
unbind your listbox add a textbox bind the texbox to the invHistory field then update the textbox with your string

Dim ctl1 As Control
Dim varItm As Variant
Dim myStr As String
Set ctl1 = Me.InvestHistory
For Each varItm In ctl1.ItemsSelected
myStr = myStr & ctl1.ItemData(varItm) & " ,"
Next varItm
myStr = Left(myStr, Len(myStr) - 2)
me.newtextbox = myStr
End Sub

However I predict Your next postwill read “Need to load listbox with parsed values from field”
Did you follow the link and read the information that PHV referred to on your last post?
What you are trying to do violates first normal form. Once you store the string of data in the field what are you going to do with it. How will you sort data. How will you display it.
You indicated you were new to VBA I urge you spend the time to get the design of your tables correct. Time spent up front learning good design will save you a lot of time on the other end trying make a bad design work

 

[manrique83]

Thanks gol4 for the reply.

I understand the whole concept of designing a normalized database and enforcing referential integrity in your tables. But perhaps I am not making it clear, there isn't a need for a well designed database. This is just a temporary table that will act as a dataset for further processing. And if there was, you think I'd do it in Access?

But I really appreciate the help with the VB CODE though.