displaying search result in an excel format or else

[NasirMunir]

I have created a table in access (copied from excel). Then I created a form which contains a text field and a list box. The text field is actually a look-up field, where a user can enter a searchable text and the results are displayed in the list box(the search is done on the table which I copied from excel). Everything works fine as I want.
The problem: I cannot copy the results from that list box to the excel sheet. It just copies the first column of the displayed rows in the list box. I had tried using a command button to display the result in the tabular-excel form, but it only takes a pre-defined saved query. I can't save the query as it is generated based on the search key word a user enters in the text box.
How can I display the results in some kind of format that can be copied/ exported to the excel sheet? Can anyone suggest something ?.
My code is :

Option Compare Database
 
Private Sub List3_BeforeUpdate(Cancel As Integer)
 
 
End Sub
 
Private Sub Text0_BeforeUpdate(Cancel As Integer)
    Dim holdVal As String
    holdVal = Me.Text0.Value
    Me.List3.RowSourceType = "Table/Query"
    Me.List3.RowSource = "SELECT * FROM Contents" & _
                            " WHERE PART_NUM LIKE '" & holdVal & "*'"
    Me.List3.Requery
 
End Sub
 

and when I try to insert the code for command button, replacing the stored query with mine, I get the error that it cannot find the object and then list my generated query. Code for that part is:

Private Sub Get_Fields_Click()
On Error GoTo Err_Get_Fields_Click
 
    Dim storeVal As String
    storeVal = Me.Text0.Value
    Dim qrRetrieve As String
    qrRetrieve = "SELECT * FROM Contents" & _
                            " WHERE PART_NUM LIKE '" & storeVal & "*'"
    DoCmd.OpenQuery qrRetrieve, acNormal, acEdit
 
 
Exit_Get_Fields_Click:
    Exit Sub
 
Err_Get_Fields_Click:
    MsgBox Err.Description
    Resume Exit_Get_Fields_Click
 
End Sub
 

Thanks !

[DarrenOxley]

I have created a table in access (copied from excel). Then I created a form which contains a text field and a list box. The text field is actually a look-up field, where a user can enter a searchable text and the results are displayed in the list box(the search is done on the table which I copied from excel). Everything works fine as I want.
The problem: I cannot copy the results from that list box to the excel sheet. It just copies the first column of the displayed rows in the list box. I had tried using a command button to display the result in the tabular-excel form, but it only takes a pre-defined saved query. I can't save the query as it is generated based on the search key word a user enters in the text box.
How can I display the results in some kind of format that can be copied/ exported to the excel sheet? Can anyone suggest something ?.
My code is :

Option Compare Database
 
Private Sub List3_BeforeUpdate(Cancel As Integer)
 
 
End Sub
 
Private Sub Text0_BeforeUpdate(Cancel As Integer)
    Dim holdVal As String
    holdVal = Me.Text0.Value
    Me.List3.RowSourceType = "Table/Query"
    Me.List3.RowSource = "SELECT * FROM Contents" & _
                            " WHERE PART_NUM LIKE '" & holdVal & "*'"
    Me.List3.Requery
 
End Sub
 

and when I try to insert the code for command button, replacing the stored query with mine, I get the error that it cannot find the object and then list my generated query. Code for that part is:

Private Sub Get_Fields_Click()
On Error GoTo Err_Get_Fields_Click
 
    Dim storeVal As String
    storeVal = Me.Text0.Value
    Dim qrRetrieve As String
    qrRetrieve = "SELECT * FROM Contents" & _
                            " WHERE PART_NUM LIKE '" & storeVal & "*'"
    DoCmd.OpenQuery qrRetrieve, acNormal, acEdit
 
 
Exit_Get_Fields_Click:
    Exit Sub
 
Err_Get_Fields_Click:
    MsgBox Err.Description
    Resume Exit_Get_Fields_Click
 
End Sub
 

Thanks !

Hi

Have to make this quick as I have a train to catch!

Use the Column function

public vValArray ()
 
Redim vValArray(me.ListBox.ListCount)
 
For vloop 0 to ListBox.Listcount
vValArray(vloop) = ListBox.Column(Column,vloop) ' (vloop is each row in the 
listbox)
Netx vloop
 
 

vValArray(n) now contains each value, if you want this to only to apply to the values selected then use

 If me.Listbox.Selected(vloop) = True then 

and don't forget to change the properties of the listbox to Multiselect = Simple.

Daz

[NasirMunir]

didn't work, can you explain a bit more ?
thanks