Results 1 to 6 of 6
  1. #1
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    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

  3. #3
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    Please answer question 2 from my previous post
    George
    Home | Blog

  5. #5
    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!


  6. #6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •