Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unanswered: Trouble Making a Query - Help Please

    Hello,

    I have an access db with a "principal" table, i've created a query that finds some data of that table, the data are name and store. I've created a form from that query, and now i need to make another form to filter only some data to the query form. Is it possible??

    Thankx in advance

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    Try using an intermdiate Search Dialogue with the Name and Store and Entry Button. On the Entry Button open your form using a filter from the Search forms the criteria from the eg:

    Name like forms![Search]![Name] & "*"

    The like and wildcard allows for short searches

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    Simon MT, thanks a lot, the like operator and the wildcard was very useful, but it still not doing what i want, i will try to show an example:

    i've made a query and give it the name of "Query1", from this query i've made a form named "form1", and now i want to make a new form, "form2" that it will contain a text box and a command button and this last form will link to "form1" to make a search and filter the results. Is it possible?

    Thanks

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    Heres a bit of code. Because I use a number of ways to filter the clients I have one textbox [Field1] and change the Caption as reqiuired. The second textbox is only used when there is an additional ask, Surname then First Name. There is a menu form to drive the application. I also only use functions there is nothing on the forms except calling the procedure.

    Function ClientsOpenName()

    DoCmd.OpenForm "Clients Dialogue Search"
    Forms![Clients Dialogue Search].[Search] = "N"
    Forms![Clients Dialogue Search].[Field1].Visible = True
    DoCmd.GoToControl "Field1"
    Forms![Clients Dialogue Search].[LabelF2].Caption = "Surname"
    Forms![Clients Dialogue Search].[LabelF2].Caption = "First Name"

    End Function

    Then the Entry Button looks like this:

    Function ClientsEntry() As String

    DoCmd.OpenForm "Clients", , , ClientsCriteria

    End Function

    Then ClientsCriteria is used due the the differing type of filtering and looks like this:


    Private Function ClientsCriteria() As String

    With CodeContextObject
    If .[Search] = "N" Then
    ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & .[Field2] & "*" & "'"
    ElseIf .[Search] = "C" Then
    ClientsCriteria = "[Client Co Name] like '" & .[Field2] & "*" & "'"
    ElseIf .[Search] = "E" Then
    ClientsCriteria = "[Client Email] like '" & .[Field2] & "*" & "'"
    ElseIf .[Search] = "M" Then
    ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & .[Field2] & "*" & "' and [Client Master]=True"
    ElseIf .[Search] = "I" Then
    ClientsCriteria = "[Client] like '" & .[Field2] & "*" & "'"
    End If
    End With

    End Function

    I know this might be a little complex for your needs but scale it down if you find it useful.

  5. #5
    Join Date
    Dec 2006
    Posts
    6
    Alright I've Already Made it

    It was a bit difficult but it's done

    In the query i've made what simonMT said on the first topic then i've used

    the next code (it's a simple code, but it makes what i want)

    Code:
    Private Sub CmBtn_Click()
    If (IsNull(TexFld1)) Then
    MsgBox "Type Something", vbOKOnly, "Type Something"
     ElseIf [Name] > "" Then
      DoCmd.OpenForm "FrmQry", acNormal, , , acFormReadOnly, acWindowNormal
     
      End If
    SimonMT Thanks for your Support

  6. #6
    Join Date
    Sep 2006
    Posts
    265
    You could also try test each of textboxes in turn and add then to the Criteria declared in the module.

    inputvalue = false

    if not isNull(.[Textbox1]) then
    Critieria = "[Field1] = '" & .[Textbox1] & "'"
    inputValue = True
    end if
    if not isnull(.[TextBox]) then
    if inputvalue = true then
    Criteria = Criteria & "and [Field2] = '" & [Textbox2] & "'"
    else
    Criteria = "[Field2] = '" & [Textbox2] & "'"
    inputvalue = true
    end if
    end if

    The only purpose the inputvalue is to test whether or not any of the preceding Textboxes have input necessitating the "and". This way you build your Criteria and include the Criteria in the where statement. At the end of the if testing you could put

    if inputvalue = false then
    DoCmd.OpenForm "FrmQry", acNormal, , , ....
    else
    DoCmd.OpenForm "FrmQry", acNormal, ,Criteria , ...
    end if

    I prefer to not open any form readonly but screw the fields down to non-input because I drop images onto the forms and the image control does not like read only. If you need this obviously don't allow deletions.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by SofaSurfer
    Simon MT, thanks a lot, the like operator and the wildcard was very useful, but it still not doing what i want, i will try to show an example:

    i've made a query and give it the name of "Query1", from this query i've made a form named "form1", and now i want to make a new form, "form2" that it will contain a text box and a command button and this last form will link to "form1" to make a search and filter the results. Is it possible?

    Thanks
    yes but it's so much simpler to do it via a control on the same from ie in the header

    just do something along the lines of

    Select *
    From principal
    Where field1 like Form1!TxtSerch.Text & "*;"

    ensure that seach box isn't bound to anything and that it's default value is ""

    PS
    For future reference the example and problem description are a little unclear in future could you give us a summary of the struture of the table(s) and and sample of the data and then a sample of what the data should look like after this process has run eg
    i've got a table
    tblPrincipal (name as text(50),age as number(Long),etc)
    it contains
    "Joe blogs", 20
    "fred west",32
    "freddy cruger", 60

    and i want to do ... so it returns
    "fred west",32
    "freddy cruger", 60
    Last edited by m.timoney; 02-07-07 at 06:21.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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