Results 1 to 14 of 14
  1. #1
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13

    VBA Code Urgent Help Require - Search Form Using Combo box

    Dear All,

    I'm trying to make a form to record rates we are procuring from various vendors, so this can help us if repeated inquiry comes then we have rates available in hand.
    Have made few tables and forms - the main form where we are inputting data rates procured from various vendors. Now I'm trying to prepare another form from that data / table this is split form where I've put two fields of dates FROM and TO and a search button so any rates during that validity are visible to us.

    I'm also trying to add another combo-button where we will select Port Of Loading name so data can appear in more filtered way - all VB coding I used while watching videos from youtube. so I need help about combo button.

    Kindly see the coding where I'm having problem + pics of the database and forms.

    As I'm not sure what exactly I should share here on this forum. Therefore please take my apologies in advance and looking forward for some help & assistance.

    VB Code for your reference as below where I need your help


    ------------------- QUOTE -------------------

    Private Sub cbopol_AfterUpdate()
    Dim polselction As String
    polselction = "Select * from Rates_Input_Table where ([POL] = " & Me.cbopol & ")"
    Me.SearchRates.Form.RecordSource = polselction
    Me.SearchRates.Form.Requery
    End Sub

    ------------------- UNQUOTE -------------------

    This code is not working
    While the other code is working fine - when I'm selecting Date Range, I just want to select POL Name in ComboBox and data below should get filter accordingly.

    Complete code is attached in TXT file + Word file which contains all the tables and forms screen. I'm sorry I don't know what I need to attach therefore I've done what was possibly done. thanks in advance for the assistance.
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As Port of Landind (POL) is probably text you need to edit the Select statement to wrap it in single quotes (or double double-quotes if you might have apostrophes in the Port of Landing names):
    polselction = "Select * from Rates_Input_Table where ([POL] = '" & Me.cbopol & "')"
    polselction = "Select * from Rates_Input_Table where ([POL] = """ & Me.cbopol & """)"

    Cheers,
    Vlad

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would be helpful to see your dB.

    I see a few issues you should resolve.
    One is you have a table named "POD_Form" and a form named "POD_Form". Not a good idea having multiple objects with the same name. Better would be "tblPOD" and "frmPOD".
    Another is that it seems that every table has a PK field named "ID". That gets confusing....
    Also:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.



    There is no such thing as a "combo-button". There is a combo box.

    The combo box should not have any code in the after update event. The code in the combo box after update event and the code for the search button are fighting each other. (BTW, "Search" is a reserved word)

    The line " Dim strCriteria, task As String" isn't doing what you think it is doing (nor what you want it to do)
    "strCriteria" is a Variant and "Task" is a string. You MUST explicitly declare the variable type. You should have
    Code:
      Dim strCriteria As String, task As String
    or
    Code:
      Dim strCriteria As String
      Dim task As String



    To search a form, you build the criteria string, then set the form filter property. Much easier that what you are trying to do.
    Code:
    Option Compare Database  '<< should be at the top of EVERY module
    Option Explicit          '<< should be at the top of EVERY module
    
    Sub btnSearch_Click()   '<<-- note that this is the click event of the button Search
        Dim strCriteria
        Dim task As String
    
        If IsNull(Me.RatesValidFrom) Or IsNull(Me.RatesValidTo) Then
            MsgBox "Please enter the date range", vbInformation, "Date Range Required"
            Me.RatesValidFrom.SetFocus
        Else
            strCriteria = "([Rates Validity] Between #" & Me.RatesValidFrom & "# And  #" & Me.RatesValidTo & "# And "
            'check to see if a POL has been selected
            If Len(Trim(Me.cbopol & "")) > 0 Then
                strCriteria = strCriteria & "POL = '" & Me.cbopol & "' And "   '<<-- if cboPOL bound column is Text
             '   strCriteria = strCriteria & "POL = " & Me.cbopol  & " And "   '<<-- if cboPOL bound column is a number
            End If
            'remove the last 5 characters (spaceANDspace)
            strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    
            'now filter the form
            Me.Filter = strCriteria
            Me.FilterOn = True
        End If
    
    End Sub
    
    
    
    Private Sub Command103_Click()  ' <<-- need a better name maybe "btnClear"
        ' I think this is the clear button
    
        Me.RatesValidFrom = Null
        Me.RatesValidTo = Null
        Me.cbo = Null
    
        Me.Filter = vbNullString
        Me.FilterOn = False
    
    End Sub

  4. #4
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    Dear Vlad,

    I entered ' where you suggested but this is still not working... I received following error

    Compile error:
    Method or data member not found

    and it is highlighting this part of the code.


    Private Sub cbopol_AfterUpdate() ---- THIS IS HIGHLIGHTED in YELLOW
    Dim polselction As String
    polselction = "Select * from SearchRates where ([POL] = '" & Me.cbopol & "')"
    Me.SearchRates.Form.RecordSource = polselction --- marked bold is found SELECTED in BLUE.
    Me.SearchRates.Form.Requery
    End Sub

    There is some problem in this part I guess.

    Me.SearchRates.Form.RecordSource = polselction
    Me.SearchRates.Form.Requery

    Should I send my complete database maybe you can look into it and suggest?
    Attached Files Attached Files

  5. #5
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    Dear Steve,

    I admitted in the very beginning I'm a noob in access and specially VBA - I've no clue about VBA and coding. I just followed what I saw in youtube so please accept my apologies for copy & paste - I tried using my head wherever required and made changes as per my requirement considering access will be like as excel.

    But to my surprise it is not as easy and without VBA understanding one cannot attempt or achieve.

    I'm enclosing my database its very small.
    Actually in form SearchRates - I'm trying to achieve that we can view rates which are valid between the date range selected.
    And to narrow that filter more - I put a combo-box (not button thanks for the correction). Where we can select the Port of Loading name so if rates of that particular Port of loading are present then they only appear rest disappear.

    Appreciate if you could assist me in achieving the same. Thank you

    Bundle of thanks for all your assistance and input.
    Attached Files Attached Files

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the updated file. Notice that I changed the select statement to refer to the table not the form and fixed the reference to the form itself (Me.Requery is what you need, not Me.SearchRates.Form.Requery). I also updated your Clear button to reset the form.

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    Dear Vlad,

    Thank you so very much!! Your updated file is working like a charm!!
    I've done some additions and that is not working...
    When a Port of Loading is selected in combo-box then it is showing all the records pertaining to that Port of Loading.
    But when I'm clicking on a new button "show all" its not showing all the records.
    When I'm using date range and searching and clicking on show all button it is working.

    Can you help me with that?

    Also I've a question in this form I've added count total

    But in other form I will be making multiple text boxes there data will needs to be counted based on some criteria that will work the same way Countif(range, criteria)?

    Can I reach out to you for any help regarding count total in textbox in other form please :-D

    Once again thank you so very much!! You are the best!!!
    Honestly I'm a noob in access and specially in VBA, but your help is greatly appreciated!!!

    Loads of best wishes!!!
    Attached Files Attached Files

  8. #8
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    One more thing I'm trying to bring rates validity column from last to rate request position.
    I did the change in Layout view and when I went to form view it was fine but as soon as I close the form and reopen it rates validity column go again back to its old position (in the second last position) can you guide me anything about this too?

    thanks again!!

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the file you just uploaded the Clear and Show All buttons have the exact same wrong code - please have a look at the code under the Clear button in the file I've sent you which I think works as you intended.
    Code:
    Private Sub Command103_Click()  'Clear button  - I recommend you use control names that are meaningful - in this case cmdClear
    Dim task As String 
        
        Me.RatesValidFrom = Null
        Me.RatesValidTo = Null
            task = "select * from form_SearchRates where id is null"
        DoCmd.ApplyFilter task
        
    End Sub
    
    
    Private Sub Command129_Click()  'Show All button  - I recommend you use control names that are meaningful - in this case cmdShawAll - not sure you need two buttons to do the exact same thing
    Dim task As String
        
      Me.RatesValidFrom = Null
        Me.RatesValidTo = Null
            task = "select * from form_SearchRates where id is not null"
        DoCmd.ApplyFilter task
    End Sub
    I see you are familiar with Excel formulas - in Access you would use a DCount (https://support.office.com/en-us/art...a-11a64acbf3d3) instead of CountIF. Or you could have a totals query and use that in your form controls, it really depends on your specifics.

    As for the location of the column (for split form) you need to change the tab index click on the rate validity control and in its property box go to Other and change the Tab Index to 1.

    Please feel free to message me with any questions.

    Cheers,
    Vlad
    Attached Files Attached Files

  10. #10
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    Dear Vlad,
    Apologies for the late reply and Thank you so very much!!
    Your updated file works like magic!!
    I've made some more changes in it and I will be coming to you for more help.
    I think I should mark this thread as solved? OR should I continue asking more questions and help in this thread?
    Sincerely thank you so much for your valuable time, input and support.
    You are the best!!!

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you stop are look at your table design/relationships.

    I removed spaces in names and special characters.
    I removed the look up FIELDS in tables - See The Evils of Lookup Fields in Tables
    I added a field and renamed a few others.

    Something like this:
    Click image for larger version. 

Name:	Relationship1.png 
Views:	23 
Size:	106.7 KB 
ID:	36718


    Good luck with your project........
    Attached Files Attached Files

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear you got it working! As Steve said a little time spent on properly designing the database will save you LOTS of time down the road... If you have questions in the future I suggest you create new threads as you will get a lot better advice from the broader community involved in this forum. If you need help from specific members (me included) you can always use the private messaging functionality.

    Cheers,
    Vlad

  13. #13
    myem1983 is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2014
    Posts
    13
    Dear Vlad,

    Thank you for the valuable input, I'm seriously overwhelmed with the response from the community.
    All the support from you and Steve is like encouraging me to learn, read and work more on MS Access.

    You have helped me a lot and will be reaching out to you for more assistance, help and guideline.

    I'm found of MS Excel - before this I had no idea about MS Access. I'm learning, reading, youtube about MS Access; forms, tables, VBA etc.

    Therefore I've no idea how my work or tables OR sheets should be designed so down the line they do not get tangled into each other.

    Your and community guide and assistance is greatly appreciated.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One thing you might do is work through the 3 tutorials at Rogers Access Library
    Don't just watch them, actually take the time to do them.... I think it will help you a lot.

    Good luck with your project.........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  2. Replies: 8
    Last Post: 08-11-2013, 07:24 PM
  3. VBA code to require user to enter a Value
    By rlsublime in forum Access
    Replies: 1
    Last Post: 03-14-2012, 04:34 PM
  4. !!!!Urgent!!!! Search code doesn't work!
    By Laetilae in forum Programming
    Replies: 4
    Last Post: 12-13-2010, 10:34 PM
  5. Require Urgent Help Regarding UNION and SUM
    By maddysanoo in forum Queries
    Replies: 1
    Last Post: 06-10-2010, 09:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums