Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Is this possible?

    I was wondering if the following was possible.

    Say I've created many queries to search for specific records based on a department.
    Then I want the user to type in a textbox the department that they are in, and open a form based on that query.

    I know that I could do this by creating more than one query and use it as the forms control source, but I was wondereing if it was possible through vba so that I could have many queries applied to one standard form.

    Thanks

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Kev!

    Look at "DmeoQryFormA2000.mdb", I think it is what you need.
    Open Form1.
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks for that but unfortunately that method won't work on mine, as the field I am using a criteria on is in another table, therfore there not on the same form.

    Also I've set up a seperate search form, where the user can enter their search criteria, then it opens another form using the corresponding query.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you considered parameterizing ONE query instead of having a query for each department?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Could someone please help me to understand creating parameters for queries?

    I have found the following code to help me do this, but I'm not sure what it all means.

    Here's the code I am using to set parameters:

    Code:
    Dim prm As ADODB.Parameter 
    Dim cmd As ADODB.Command 
    Dim rs As ADODB.Recordset 
    
    Set prm = New ADODB.Parameter 
    Set cmd = New ADODB.Command 
    Set rs = New ADODB.Recordset 
    
    'Setup the Command object 
    With cmd 
    .CommandText = "qryDeptSearch" 
    .CommandType = adCmdUnknown 
    
    'Create the parameter 
    Set prm = .CreateParameter("DeptSearch", adVarChar, adParamInput, 50) 
    .Parameters.Append prm 
    
    'Set the parameter's value 
    .Parameters("DeptSearch") = "Marketing" 
    
    'Associate the command object with a connection 
    .ActiveConnection = CurrentProject.Connection 
    
    'Request the recordset 
    Set rs = .Execute 
    
    End With
    Basically I wanted to create a parameterized query which I could link to, open a form and bring back only the results based on the parameters. The field I want to set the parameters to is set to the name of a department e.g. Marketing, Finance, I.T. etc.

    Is it possible to add more parameters for Finance and I.T. for example?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Um... It may not be of use but just to make sure the simple answer isn't the correct one, why would the below not work:

    Code:
     Select Col1, Col2
    FROM MyTable
    WHERE Col1 = Forms!MyForm!Textbox
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2005
    Posts
    240
    I've tried using SQL as well, the main problem is how do I link this SQL query to a form in vba so it only brings back that data.

    Obviously I'd put the SQL into a variable, but do I simply do an OpenForm command and place the name of the variable into the filter properties or maybe the where properties to open the form with that specified data?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    I've tried using SQL as well, the main problem is how do I link this SQL query to a form in vba so it only brings back that data.
    I don't understand this bit. Why do you need to link the query using VBA?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2005
    Posts
    240
    I had planned on creating some kind of search, using a form with a textbox and command button.
    When they enter "Marketing" for example, and click the button it will open up the form, with all records relating to marketing. But they could put any of the 7 or 8 departments into the texbox to get the related department records on that form.

    But the problem I can see us that I'm going to create 7 or 8 queries to bring back the records based on one of the 7 or 8 departments.

    But I don't want to have to create say 7 or 8 forms that are exactly the same, and assign the queries to one of those forms. It would be much better if I had just one form, and link the 7 or 8 SQL queries to get the records.

    Hopefully that makes sense

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    And it would be even better to use one query that accepted department as a parameter, as we've been saying. This isn't a new concept.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Kev

    What you are trying to do is really simple and doesn't require VB. The following query:
    Code:
    Select Col1, Col2
    FROM MyTable
    WHERE Col1 = Forms!MyForm!Textbox
    will return the records where Col1 = the entry in Forms!MyForm!Textbox. If we suppose that Col1 is your team field then if you type "marketing" and run the query then the result will be all rows with Marketing in Col1. Change your textbox to "Finance" and run the query again and it will be all rows with Finance in Col1.

    make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    [QUOTE=KevCB226]
    Why don't you use what I have which does the same. I use a ComboBox, set it to list all the choices a user can make (not having to guess or misspell). Then, as soon as they click their choice in the ComboBox it opens the Form with the data requested.

    Code:
    Private Sub FindPO_AfterUpdate()
    
         Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmOrderStatus"
        
        stLinkCriteria = "[PONumber]=" & "'" & Me![FindPO] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
    End Sub
    The above is the code in the ComboBox. The person selects the PO Number from the ComboBox, it then pulls up the frmOrderStatus with the criteria that is set.

    Code:
    SELECT DISTINCT OrderStatus.PONumber, OrderStatus.VendorName
    FROM OrderStatus;
    The above is the SQL that goes with it. I used DISTINCT to prevent it from showing all duplicates in the ComboBox, however, they show in the form.

    Give it a whirl. TextBoxes are good, but you run into people typing in Incorrect words, some just can't spell well, not to mention, you HAVE to know what Departments, or whatever you can select from. The ComboBox shows you the available choices. Just to make it real simple.

    Hope this helps you out some and have a nice one,
    BUD

    See the pic below:
    Attached Thumbnails Attached Thumbnails ComboSelection.bmp  
    Last edited by Bud; 11-19-05 at 04:46.

Posting Permissions

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