Else if statements

[swift_electrical]

Hi,

I have a customer database running in Microsoft Access and at the moment in the design view I have created another text field so I can look up customers via a Post Code as at the moment I can only look up via Customer Name.

I have created a new textfield called (txtpostcode) but I need to change the search button function to allow the text field to be looked up as its only looking up (txtCustomer).

Code as follows:

Option Compare Database
Option Explicit

Private Sub Command7_Click()

End Sub
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim custid
    stDocName = "Customers"
    
    If Not IsNull(txtInvoice) Then
        If LCase(Left(txtInvoice, 1)) = "s" Then
            Dim SaleID
            SaleID = Mid(txtInvoice, 2)
            custid = DLookup("CustomerID", "Sales", "SaleID=" & SaleID)
        Else
            Dim RepairID
            RepairID = Mid(txtInvoice, 2)
            custid = DLookup("CustomerID", "Repairs", "RepairID=" & RepairID)
        End If
        
        If Not IsNull(txtPostcode) Then
        If LCase(Left(txtPostcode, 1)) = "s" Then
            Dim SaleID
            SaleID = Mid(txtPostcode, 2)
            custid = DLookup("CustomerID", "Sales", "SaleID=" & SaleID)
        Else
            Dim RepairID
            RepairID = Mid(txtPostcode, 2)
            custid = DLookup("CustomerID", "Repairs", "RepairID=" & RepairID)
        End If
        
        If IsNull(custid) Then
            custid = 0
        End If
        stLinkCriteria = "[CustomerID] = " & custid
    Else
        stLinkCriteria = "[Name] Like '*" & Me![txtCustomer] & "*'"
    End If
    Me![subform].Form.Filter = stLinkCriteria
    Me![subform].Form.FilterOn = True
    
    'DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFind_Click:
    Exit Sub

Err_cmdFind_Click:
    MsgBox Err.Description
    Resume Exit_cmdFind_Click
    
End Sub
Private Sub cmdAdmin_Click()
On Error GoTo Err_cmdAdmin_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Parameters"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAdmin_Click:
    Exit Sub

Err_cmdAdmin_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdmin_Click
    
End Sub

I'm looking to add a section like this:-

stLinkCriteria = "[Postcode] Like '*" & Me![txtpostcode] & "*'"

Any help would be great!

 

[James Dudden]

Do you want to be able to filter by customer and postcode at the same time or just one or the other?

 

[michaeljryan78]

I would create a form with a combo box that has a unbound cbo of what you want to search (Customers, Postal Code) and an unbound textbox for a searchvalue input. then create a subform based on those results...

Create your "base" SQL string and apply the build the where condition based on the string value in the cbo and value in the searchvalue input:

Dim strSQL As String
        strSQL = "SELECT tblTicketInformation.TicketID, tblTicketInformation.AssignmentNumber, tblTicketInformation.EMPLID, tblTicketInformation.ConsultantName, " _
                & "tblTicketinformation.ClientID, tblTicketinformation.ClientName, tblTicketInformation.RelatedCases, tblTicketInformation.InvoiceNumber " _
                & "FROM tblTicketInformation "

  If gcfHandleErrors Then On Error GoTo PROC_ERR
  PushCallStack "Command2_Click"
 
Select Case cboSearchBy
    Case "Ticket Number"
        strSQL = strSQL & "WHERE (((tblTicketInformation.TicketID) Like '*" & Me.txtValue & "*'))"
    Case "Assignment Number"
                strSQL = strSQL & "WHERE (((tblTicketInformation.AssignmentNumber) Like '*" & Me.txtValue & "*'))"
    Case "EMPLID"
                strSQL = strSQL & "WHERE (((tblTicketInformation.EMPLID) Like '*" & Me.txtValue & "*'))"
    Case "Consultant Name"
                strSQL = strSQL & "WHERE (((tblTicketInformation.ConsultantName) Like '*" & Me.txtValue & "*'))"
    Case "Client ID"
                strSQL = strSQL & "WHERE (((tblTicketInformation.ClientID) Like '*" & Me.txtValue & "*'))"
    Case "Client Name"
               strSQL = strSQL & "WHERE (((tblTicketInformation.ClientName) Like '*" & Me.txtValue & "*'))"
                
    Case "Related Cases"
                strSQL = strSQL & "WHERE (((tblTicketInformation.RelatedCases) Like '*" & Me.txtValue & "*'))"
                
    Case "Invoice Number"
    
        strSQL = strSQL & "WHERE (((tblTicketInformation.InvoiceNumber) Like '*" & Me.txtValue & "*'))"
    End Select
    
    Forms!qbftickets!sfrmTicketLookup.Form.RecordSource = strSQL
    Me.sfrmTicketLookup.Requery
PROC_EXIT:
  PopCallStack
  Exit Sub
PROC_ERR:
  GlobalErrHandler
  Resume PROC_EXIT

This defines the subforms recordsource. The CASE statement is a bit brute firce, but it works...

 

[swift_electrical]

@James:

My Amended code:-

Option Compare Database
Option Explicit

Private Sub Command7_Click()

End Sub
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim custid
    stDocName = "Customers"
    
    If Not IsNull(txtInvoice) Then
        If LCase(Left(txtInvoice, 1)) = "s" Then
            Dim SaleID
            SaleID = Mid(txtInvoice, 2)
            custid = DLookup("CustomerID", "Sales", "SaleID=" & SaleID)
        Else
            Dim RepairID
            RepairID = Mid(txtInvoice, 2)
            custid = DLookup("CustomerID", "Repairs", "RepairID=" & RepairID)
        End If
        
        If IsNull(custid) Then
            custid = 0
        End If
        stLinkCriteria = "[CustomerID] = " & custid
    Else
        stLinkCriteria = "[Name] Like '*" & Me![txtCustomer] & "*'"
        
    End If
    Me![subform].Form.Filter = stLinkCriteria
    Me![subform].Form.FilterOn = True
    
    'DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFind_Click:
    Exit Sub

Err_cmdFind_Click:
    MsgBox Err.Description
    Resume Exit_cmdFind_Click
    
End Sub

Private Sub cmdAdmin_Click()
On Error GoTo Err_cmdAdmin_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Parameters"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAdmin_Click:
    Exit Sub

Err_cmdAdmin_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdmin_Click
    
End Sub

I want two textfields as opposed to one, I have at the moment txtCustomer which basically checks what I have typed into the field e.g. Customer Name and checks it against my tables containing customer details.

I want an additional textfield e.g.txtpostcode which checks my table of customers against a postcode which I have typed in txtpostcode.

I want to be able to use one or the other to display the results.

I have tried re-writing the else if statements but I keep hitting a brick wall.

Any help would be great.

 

[michaeljryan78]

Perhaps an option group that the user can select to define what to look up by. based on that selection you can determine what validations and searches you wish to perform. Enable and disable appropriate fields on the afterupdate of the option group to force the user down a path...

 

[James Dudden]

Try replacing this line:

stLinkCriteria = "[Name] Like '*" & Me![txtCustomer] & "*'"

with this:

if Me![txtCustomer] <> "" then
stLinkCriteria = "[Name] Like '*" & Me![txtCustomer] & "*'"
end if

if Me![txtpostcode] <> "" then
if stLinkCriteria <> "" then stLinkCriteria = stLinkCriteria & " AND "
stLinkCriteria = stLinkCriteria & "[Postcode] Like '*" & Me![txtpostcode] & "*'"
end if

 

[swift_electrical]

Thanks all!

Thanks to James whose trick has worked wonders!

Kind Regards

Aleister