If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Help with VB Code for Search Button

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Location: Kansas City, MO
Posts: 17
Help with VB Code for Search Button

I am a beginner with Microsoft Access databases. Is is possible someone could help me with how I would place VB code behind a search button?
The user will open the form, Select the fund name from a drop down combo box. (Combo7). Then he/she will have a start date (DTPicker4) option along with an end date (DTPicker5). A Search button will then be selected that populate all the data for a particular fund and all the data associated from the date range the user selected. What type of code would I place behind the "on click" command?


Fund Name = FundId
Fund Combo Box Name = Combo 7
Start Date = DTPicker4
End Date = DtPicker5
Search Button = Search

Thanks
__________________
Karol
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,885
Hi ther Karol!

A couple of tips for you first off:
1) Combo7 is not a ncie name for a control, is it? It tells the programmer (i.e. you and any future devs) anything useful about what this control is, let alone what it does! Same goes for your other control names - try make them linked to their purpose/meaning so that your code becomes more human readable!

2) how do you wish to display your results? Different controls have different methods of showing results!

Ok, now that's out the way, on to our pseudo solution (using a listbox)
Code:
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT * FROM MyTable"
strWhere = " WHERE CriteriaField = '" & Me.Combobox.Column(n) & "'"

'-- Note that n is used to denote the index of the column you wish to use from your combobox

Me.MyListBox.RowSource = strSQL & strWhere
Me.Requery
You may (will ) want to have a read of this to learn the basics of SQL!

If you have any questions just post them back her.
Hope this helps!
__________________
George
Home | Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Location: Kansas City, MO
Posts: 17
I do not have a listbox. So, is this going to work??? I must have not explained very well that what I have is 2 date fields and a button once the user enters the begin date and end date and selects the search button the code behind it tells the data for that particular FundId to populate its data.

I appreciate the information about naming controls properly, as explained prior to this reply I am a beginner.

So, that being said, I return to my original request for assistance what type of code would go behind the search button that would populate the data from the date range and FundId?
__________________
Karol
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,885
Please answer question 2 from my previous post
__________________
George
Home | Blog
Reply With Quote
  #5 (permalink)  
Old
Stuck on my opinions...
 
Join Date: Nov 2003
Posts: 1,487
In the OnClick Event of your Search button copy and past this code:

Code:
' Declare Required Variables For This Code...
Dim StrgSQL As String, FundName As String
Dim StartDate As Date, EndDate As Date

' Fill Our Variables...
' Fill the FundName String Type Variable. If nothing is selected 
' in the ComboBox then we will assume that we want to
' see ALL Funds which fall between the  date-range selected.
FundName = Nz(Me.Combo7, "ALL")

' Fill the StartDate Date Type Variable and the EndDate Date Type
' Variable. We do a couple things here in order to handle certain
' conditions. First off, we take care of the fact that if a Date is
' not selected (in either the StartDate or the EndDate) and the 
' Search button is selected then we make sure that at least todays
' date is automatically choosen. The Nz() function takes care of 
' this. Secondly, We make sure that the date provided follows the
' same format held within our Fund table. As you can see, the
' Short Date format is used and we use the Format() function to
' take care of this. You need to ensure that the proper Format is 
' used (check the field in table). You can have either: "Short Date",
' "Medium Date", or "Long Date". This also ensures that if a date
' is typed manually into either the Start Date or End Date Form fields
' that whatever format is entered will automatically be reformated to
' that which is used within our Fund table.  :P
StartDate = Format(Nz(Me.DTPicker4.Value, Now), "Short Date")
EndDate = Format(Nz(Me.DTPicker5.Value, Now), "Short Date")

' With the following code, what we are going to do is basically tell
' our Form what to display and we do this by using a SQL statement
' within the Record Source property of our Form. Every time the 
' Record Source property of the Form is modified the Forms RecordSet
' is automatically updated. and therefore you should see the results
' right away. So....

' If nothing was selected from the ComboBox we will assume that ALL
' Funds are to be viewed. We had taken care of determining this when
' we filled the FundName String Type Variable in the code above. Let's
' make our SQL statement to pull up ALL Fund data between our Date-
' Range.
If FundName = "ALL" Then
   ' Make sure the Table Field Names within the SQL string below are
   ' proper.
   StrgSQL = "SELECT * FROM [yourFundTableNameHere] WHERE " & _
                  "[yourFundDateFieldNameInTable] BETWEEN #" & StartDate & _
                  "# AND #" & EndDate & "#;"
Else
   ' In this SQL String we actually utilize the the Fund Name selected with
   ' the Combo7 selection that way only records pertaining to that particular
   ' fund will be pulled up and displayed.
   ' Make sure the Table Field Names within the SQL string below are
   ' proper.
   StrgSQL = "SELECT * FROM [yourFundTableNameHere] WHERE " & _
                 "[yourFundNameFieldNameInTable] = '" & FundName & "' AND " & _
                 "[yourFundDateFieldNameInTable] BETWEEN #" & StartDate & _
                 "# AND #" & EndDate & "#;"
End If

' Here we take our determined SQL string and pump it into the
' RecordSource property of our Form. 
Me.RecordSource = StrgSQL
' Hang onta yer Hat.......
Now...this all off the top of my head and not tested but I think you get the idea. Hmmmm....Should work

Anyways....hope this helps.
__________________
Environment:
Self Taught In ALL Environments.....And It Shows!

Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Location: Kansas City, MO
Posts: 17
OK, that looks good. I will try it. Thanks for your assistance. I will keep you posted.
__________________
Karol
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On