Results 1 to 4 of 4

Thread: Search form

  1. #1
    Join Date
    Sep 2004
    Posts
    48

    Unanswered: Search form

    Hi,

    I hope that I can explain this. I have a form that the user will type in either a known record number, product or username and it will search another form and bring up the appropriate record(s).

    1)The Username is in a M:M table with NCR so it is a subform. Whenever I try and search with this critieria it doesn't work. I just get my own error message.

    2) Whenever I search with the NCRNumber, if I type in a 1, it will pull up 11,12,13, etc...... the product search works fine

    3) I am trying to avoid using a filter directly.

    Code:
    Private Sub cmdSearch_Click()
    Dim varWhere As Variant
    
        ' Initialize to Null
        varWhere = Null
        
      
        If Not IsNothing(Me.txtNCRNumber) Then
            varWhere = "[NCRNumber]  LIKE '" & Me.txtNCRNumber & "*'"
        End If
        
       
        If Not IsNothing(Me.txtprodName) Then
    varWhere = (varWhere + " AND ") & "[AssName] LIKE '" & Me.txAssName & "*'"
        End If
        
    
        If Not IsNothing(Me.cboProcessOwner) Then
                   varWhere = "[Forms]![frmNCR]![sfrmProcessOwner].IDNumber & " = " & Me.cboProcessOwner.column(0)"
        End If
    
        If IsNothing(varWhere) Then
            msgbox("You must enter at least one search criteria.", , "NCR SELECTOR")
            Exit Sub
        End If
        
        ' DLookup
        If IsNothing(DLookup("NcrNumber", "qryNCR       ", varWhere)) Then
            Call CustomError("No NCR meet your criteria.", , "NCR SELECTOR")
            Exit Sub
        End If
        
    txtNCRNumber.Value = Null
    txtproduct.Value = Null
        '
     
        DoCmd.OpenForm "frmNCR", WhereCondition:=varWhere
      
        DoCmd.Close acForm, Me.Name


    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Greets;

    Not sure if this helps but take a look anyway;

    http://www.dbforums.com/showthread.php?t=1217158

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  3. #3
    Join Date
    Sep 2004
    Posts
    48
    The search works except for 1 thing, trying to search on the subform.

    Code:
        If Not IsNothing(Me.cboProcessOwner) Then
                   varWhere = "[Forms]![frmNCR]![sfrmProcessOwner].IDNumber & " = " & Me.cboProcessOwner.column(0)"
        End If

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    First off, lol @ "txtAssName". LOL.

    Secondly,

    Shouldnt your varWhere clause include the controls name? You have:

    Forms]![frmNCR]![sfrmProcessOwner].IDNumber

    Which is referenceing a value....then = Me.cboProcessOwner.column(0) which is another value.

    Try something like:


    varWhere = "[Value you want to be filtered]= '" & [forms]![frmNCR]![sfrmProcessOwner].IDNumber & "'"
    Last edited by TerpInMD; 05-31-06 at 14:30.

Posting Permissions

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