Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unanswered: Query Manipulation via VBA help needed!

    Hi all,

    I am in the process of designing an NCR database for my work, and have a continuous form that displays all of the NCR's that have been logged.

    What i need to do now is to display the following critera seperately:

    Open NCR's
    Closed NCR's
    Rejected NCR's
    Wish NCR's
    NCR's By Product Name
    NCR's By Product Category
    NCR's By client

    I have tried using numerous queries and subforms to do this, but it causes quite a considerable memory drain, so i need a simpler way to do this.

    I think it's possible to change the criteria of the All_NCRs query to suit my needs, but im not sure how to do this.

    Can anyone help?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Since this is a continuous forms why not have either radiobuttons or checkboxes for the selection filters you want ... Then you merely hide those that do not match ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    There are a number of ways of doing this. The quickest and easiest way is to use the right-click of your mouse and use the built in Filter By Selection options on the menu. You can add this yourself by creating a shortcut menu. See the Access help on how to do this

    If you have a large number of records (anything over a 1000) you should really be using a query to return a smaller number of records. You could then change the forms Recordsource to reflect any criteria you want such as:

    Me.Recordsource = "SELECT * FROM ALL_NCR WHERE NCR_STATUS = 'Open'"

    For all open NCRs

    The SQL can be built on the fly, this means you can use a main form to set the criteria and use a sub form to show the results. The syntax for changing the recordsource of a sub form is:

    Me.subSubFormControlName.Form.Recordsource = strMySQL

    Regards
    Justin

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    I already have control buttons that sort the table between ascending and descending (two rwos of control buttons on top of each other to enable the switching of the forms, although i know this could be done with the one set of buttons).

    Now, as this "table" for want of a better word, is a sub form with a dynamic menu bar down the left hand side, i.e The Main categories for the database are in the header, and each selection then spurs a second, more detailed menu in the Detail section. From there, any subforms will have further buttons on them to allow reports to be printed etc.

    Sounds complicated, but once it's going it should be pretty easy to use.

    Now, the way i thought of doing this, if it's possible, is to create a recordset based on the query, manipulate it, and then run the subform off the results.

    I can introduce the necessary data handler (i.e. checks to see what the table is displaying before it makes any changes etc.), but im just struggling with manipulating the code.

  5. #5
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    The SQL can be built on the fly, this means you can use a main form to set the criteria and use a sub form to show the results. The syntax for changing the recordsource of a sub form is:

    Me.subSubFormControlName.Form.Recordsource = strMySQL

    Regards
    Justin

  6. #6
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    It's almost the end of the day here, I'll give you way a shot tomorrow Justin, I have a feeling that it's going to work a charm, once i sort through the teething troubles.

    Thanks, and i'll let you know how it goes tomorrow.

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Wouldn't just qreating each query that i require, and then change the subforms RecordSource property, with a simple name reference to the appropriate query, without all the SQL coding?

  8. #8
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You use the same query, but you are manipulating the WHERE clause of the SQL statement to reflect the criteria the user has made. It's very straight forward and very powerful - give it a go! you just need to be careful about the syntax used for Text, numbers and dates the Access requires

    Justin

  9. #9
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    im getting a little lost here, so please bear with me if i come out with some stupid sounding comments.

    The command to update the query will come from a different form that has a combo box on it, i have the following data in the combo box:

    Opened
    Closed
    Rejected
    On Hold
    Fixed
    ReOpened
    Wishes

    Now, i understand that the query code will be placed in the AfterUpdate event of the combobox (named Col1).

    So how would i go about accessing a premade query (AllNCRs) and modify the criteria?

    That and i'd need a hand with the SQL as im not all that familiar with it.

    I have got all the queries that i need created already, but when i try to change the subforms recordsource, the database can't seem to find the form.

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    I assume you have a saved query called AllNCRs or even just a table of the same name. To create the SQL for your sub form you would need to use something along these lines:

    ' The best place for this code would be under a button on the main form that can collect all the query criteria that the user has entered

    Dim strSQL As String

    strSQL = "SELECT * FROM AllNCRs WHERE (NCR_STATUS = '" & Me("My NCR Status Combo Box").Value & "')"

    Me("MySubForm").Form.RecordSource = strSQL

    This will change the subforms record source and show the filtered data. To see the full syntax for the WHERE clause you will need to read the Help file. You could also use the query builder and view the SQL that gets created as a template
    Regards
    Justin

Posting Permissions

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