Passing muiltiple values from Listbox to seperate table

[fishen88]

Hi,

I currently have a form set up with cascading comboboxes to act as a search interface for my database. The comboboxes filter down an unbound listbox to the desired options.

Once I have the record(s) selected in the list box, I am wanting to have a command button to "export" them to an already created table. The value being exported is a taxonomy number.

Is there an easy macro to do for this type of thing, or do I need to delve into some VBA code?

Thanks.

 

[Mr. B]

If you are wanting to have multiple items from the listbox written to the table, then you will need VBA code to iterate through the remaining items in the listbox and write the records to the table.

 

[fishen88]

I have the following code at the moment attempting an update query with the selected records from the list box.

I am wanting to be able to do the selecting of the appropiate records, and then clicking an update button to commence the process.

lsttaxonomyno is the list box, cmdUpdate is the command button, and record is the name of the table.

Any suggestions?

Private Sub cmdUpdate_click()



Dim intlistcount
Dim intx As Integer
Dim strsql As String

intlistcount = Me!lstTaxonomyno.ListCount

For intx = 0 To intlistcount
If Me!lstTaxonomyno.Selected(intx) Then
If strsql & "" = "" Then
strsql = "(qrytaxonomyno.taxonomyno)='" & Me!lstTaxonomyno.ItemData(intx) & "' "
Else
strsql = strsql & " or " & "(qrytaxonomyno.taxonomyno)='" & Me!lstTaxonomyno.ItemData(intx) & "' " '
End If
End If
Next intx
If strsql & "" = "" Then
MsgBox "No criteria selected, all records will be displayed", vbInformation, "System Message..."
strsql = "update Me.records set taxonomyno = " _
& " Where taxonomyno = " & Me.lstTaxonomyno.Column(0, intx)
Else
strsql = "update Me.records qrytaxonomyno.taxonomyno from qrytaxonomyno Where ((" & strsql & "));"
End If

Me!cnttaxonomyno.Form.RecordSource = strsql

End Sub

 

[pbaldy]

As resolved elsewhere, fix is:

Well, let's start with the fact that you can't set the record source of a form to an action query. If you're trying to execute that SQL:

CurrentDb.Execute strsql, dbFailOnError

Also, see here for a more efficient loop:

http://www.baldyweb.com/MultiselectAppend.htm

 

[B51132909]

I want to Update multiple data in multiple table by using forms