Use VBA to retrieve records in form and dump into Excel 1

[alicey]

Hi,

I am a beginner in Access VBA. I have an unbound textbox on a form. I want users to be able to type in a CompanyID in the main form and then have only those records that match such as Company Name and Address appear in the main form, and the corresponding EmployeesID and Name appear in the subform. After that, I want to grab the records that appear in both the main form and subform and dump it to Excel. How can I do this? Should I use macro or module?
Pls help. Thanks a lot.

Cheers,
Alicey

 

[TLady]

This procedure will do following:
You will need text box txt_Search and ListBox List_Result
and cmd button cmd_Search

On click event of cmd_Search you will paste this code - type in Search word and all records that corresponds to your search will appeare in List Box.

You will also need a table that contain all the Info (I took example CoID, CoNAME
TEST1 will contain Results you need

It does work, but it just a beginning for you
While you test it - someone may post continuation ...
Best of luck
TIA

Private Sub cmd_Search_Click()

Dim dbR As Database, rsResp As Recordset, mSQL As String, _
StrRS As String, txtStr As String

Set dbR = CurrentDb()
Set rsResp = dbR.OpenRecordset("TEST", dbOpenDynaset)

DoCmd.SetWarnings False
dbR.Execute "Delete from TEST1" 'TEST1 is a table wich will contain Records you WANT

mSQL = "INSERT INTO TEST1 (CoID, CoNAME) " _
& "SELECT CoID, CoNAME" _
& "FROM YourTable WHERE YourTable.CompanyID = ([Forms]![frm_Find_Company]![txt_Search]) ;" 'Your text box where you type what you are searching for
DoCmd.RunSQL mSQL

List_Result.SetFocus
List_Result.RowSource = "Select CoID, CoNAME from TEST1"
DoCmd.SetWarnings True

End Sub

 

[SBendBuckeye]

Look at the DoCmd TransferSheet and TransferText methods. They might be what you need.

Good Luck!

 

[scking]

I suggest you get change your text box to a combo box and let the combo box display all valid employees. If you use a text box it is free form and everbody gets to guess at the ID or name.

Build a query from the selected values and use TransferSpreadsheet with the query as a parameter.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[alicey]

Thanks guys ............ I am working on it right now ... I like to understand what is the object and methods for Currentdb() and if I have a Customer table of 100 rows , if I use a combo box, then I will have a drop down list of 100 CoID, so I thought having the user to type in CoID and press Enter is a better way. Any comment on this? Thanks again.

 

[scking]

Yes. You can type values into a combo box the same way as you can into a text box. The only difference is that as you type a value it will automatically prompt you with a value that is currently in the database. You never HAVE to use the drop down part of the combo box.

Type Value
a The first value in the Db field starting with a
ab starting with ab
abc starting with abc


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------