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 similar to this:

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

 

[jdraw]

Can you tell us more about your tables?
I see sales and Repairs.
I don't see a table for Customer or Orders or OrderItems.

I'm just wondering if your structure matches your business.

You could have a drop down on your form for
SearchType
--PostalCode
--CustomerId
--....

and use the selected value in your logic of what Search to do , and what parameter to use.

 

[swift_electrical]

Sorry this is my 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

The code above is for a Button called Find Customer, I want to be able to add another textfield similar to txtCustomer called txtpostcode where i can search for a customer with a specific postcode. The postcode textfield i have in the design view is called txtpostcode.

Could you help?

I have tables that are working for Customer and Orders.

 

[swift_electrical]

I don't normally programme in Microsoft Access much so that explains why my knowledge is quite poor when it comes to this.

Dropdown box would be great but can't implement it!

Thanks

 

[jdraw]

I'm only seeing part of your code. For some reaso I am not seeing the whole procedure.

I don't see an Orders table or search code for one.

combobox info at http://www.techonthenet.com/access/comboboxes/

 

[swift_electrical]

The search code is the

Private Sub cmdFind_Click()

What files would I need to show you? Tables?

My Customer table consists of:
CustomerID, Name, Address, Town, County, Postcode, Telephone, Email.