By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

displaying search result in an excel format or else

P: 27
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub List3_BeforeUpdate(Cancel As Integer)
  4.  
  5.  
  6. End Sub
  7.  
  8. Private Sub Text0_BeforeUpdate(Cancel As Integer)
  9.     Dim holdVal As String
  10.     holdVal = Me.Text0.Value
  11.     Me.List3.RowSourceType = "Table/Query"
  12.     Me.List3.RowSource = "SELECT * FROM Contents" & _
  13.                             " WHERE PART_NUM LIKE '" & holdVal & "*'"
  14.     Me.List3.Requery
  15.  
  16. End Sub
  17.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Get_Fields_Click()
  2. On Error GoTo Err_Get_Fields_Click
  3.  
  4.     Dim storeVal As String
  5.     storeVal = Me.Text0.Value
  6.     Dim qrRetrieve As String
  7.     qrRetrieve = "SELECT * FROM Contents" & _
  8.                             " WHERE PART_NUM LIKE '" & storeVal & "*'"
  9.     DoCmd.OpenQuery qrRetrieve, acNormal, acEdit
  10.  
  11.  
  12. Exit_Get_Fields_Click:
  13.     Exit Sub
  14.  
  15. Err_Get_Fields_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Get_Fields_Click
  18.  
  19. End Sub
  20.  
Thanks !
Jul 19 '07 #1
Share this Question
Share on Google+
2 Replies


P: 5
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub List3_BeforeUpdate(Cancel As Integer)
  4.  
  5.  
  6. End Sub
  7.  
  8. Private Sub Text0_BeforeUpdate(Cancel As Integer)
  9.     Dim holdVal As String
  10.     holdVal = Me.Text0.Value
  11.     Me.List3.RowSourceType = "Table/Query"
  12.     Me.List3.RowSource = "SELECT * FROM Contents" & _
  13.                             " WHERE PART_NUM LIKE '" & holdVal & "*'"
  14.     Me.List3.Requery
  15.  
  16. End Sub
  17.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Get_Fields_Click()
  2. On Error GoTo Err_Get_Fields_Click
  3.  
  4.     Dim storeVal As String
  5.     storeVal = Me.Text0.Value
  6.     Dim qrRetrieve As String
  7.     qrRetrieve = "SELECT * FROM Contents" & _
  8.                             " WHERE PART_NUM LIKE '" & storeVal & "*'"
  9.     DoCmd.OpenQuery qrRetrieve, acNormal, acEdit
  10.  
  11.  
  12. Exit_Get_Fields_Click:
  13.     Exit Sub
  14.  
  15. Err_Get_Fields_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Get_Fields_Click
  18.  
  19. End Sub
  20.  
Thanks !
Hi

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

Use the Column function

Expand|Select|Wrap|Line Numbers
  1. public vValArray ()
  2.  
  3. Redim vValArray(me.ListBox.ListCount)
  4.  
  5. For vloop 0 to ListBox.Listcount
  6. vValArray(vloop) = ListBox.Column(Column,vloop) ' (vloop is each row in the 
  7. listbox)
  8. Netx vloop
  9.  
  10.  
vValArray(n) now contains each value, if you want this to only to apply to the values selected then use

Expand|Select|Wrap|Line Numbers
  1.  If me.Listbox.Selected(vloop) = True then 
and don't forget to change the properties of the listbox to Multiselect = Simple.

Daz
Jul 19 '07 #2

P: 27
didn't work, can you explain a bit more ?
thanks
Jul 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.