search code for vba

[Adish]

Hi All.

I have a search form as per the image attached. I want to strings for the unit no and suffix into my string as below but get an error message. Any help would be appreciated. Also when I select a street name from my drop down combo box. nothing gets populated in the bottom fields. the house number, locality and postcode gets populated but not the selected street from the combo box. I have set the data source of my combo box to the street table with street number and street name fields and set the bound column to 1 which is the street no as the primary key in the property sheet.
Also when I comment out the code in red then my immediate window result is ...(SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO WHERE HOUSE_NO=112 AND dbo_NUCSTREET.STREET_NO=6206) But this does return the street number but does not populate the form with selected streetname as it should


If OptionGroup.Value = 1 Then
Dim strSQL As String

If Not IsNull(Me.HouseNo) Then
strSQL = "HOUSE_NO=" & Me.HouseNo


End If

If Not IsNull(Me.StreetID) Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "dbo_NUCSTREET.STREET_NO=" & Me.StreetID
End If


If Not IsNull(Me.Suffix) Then strSQL = strSQL & " AND "
strSQL = strSQL & "HOUSE_NO_SUFFIX=" & Me.Suffix
End If

If Not IsNull(Me.UnitNo) Then strSQL = strSQL & " AND "
strSQL = strSQL & "UNIT_NO=" & Me.UnitNo
End If

strSQL = "SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME " _
& "FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO " _
& " WHERE " & strSQL
' Debug.Print strSQL

Me.Property_Information.Form.RecordSource = strSQL

[June7]

strSQL = strSQL & " AND " must be on another line or you need another If Then line.

Post lengthy code with [CODE] tags and indentation will be retained and code is more readable.

Why set the RecordSource? Why not just filter the records by setting Filter and FilterOn properties with strWHERE?

[Adish]

Hi , sorry are you able to do a quick e.g. I don't understand. Just started Access and VBA still in early stages

[June7]

See your code:

If Not IsNull(Me.Suffix) Then strSQL = strSQL & " AND "
strSQL = strSQL & "HOUSE_NO_SUFFIX=" & Me.Suffix
End If

This is lacking an If Then to go with the End If. The following will not compile error.

If Not IsNull(Me.Suffix) Then
strSQL = strSQL & " AND "
strSQL = strSQL & "HOUSE_NO_SUFFIX=" & Me.Suffix
End If

But maybe you really want something like was done for the StreetID:

If Not IsNull(Me.Suffix) Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "HOUSE_NO_SUFFIX=" & Me.Suffix
End If

[Adish]

Now I have the combo box returning the string but the 2 codes in red font still gives an error as in the attachment. My immediate window has this result now........ SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO WHERE HOUSE_NO=211 AND dbo_NUCSTREET.STREET_NO=6206 AND UNIT_NO= AND HOUSE_NO_SUFFIX=

I don't want to be checking for unit_no and suffix if it is null and if the address does not contain that at all. Only check and search if it is in address. But error comes up

Private Sub SearchButton_Click()
If OptionGroup.Value = 1 Then
Dim strSQL As String

    If Not IsNull(Me.HouseNo) Then
        strSQL = "HOUSE_NO=" & Me.HouseNo
    End If
    If Not IsNull(Me.StreetID) Then
        If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
        strSQL = strSQL & "dbo_NUCSTREET.STREET_NO=" & Me.StreetID
    End If
    
    If Not IsNull(Me.UnitNo) Then
        strSQL = strSQL & " AND " & "UNIT_NO=" & Me.UnitNo
    End If
    If Not IsNull(Me.Suffix) Then
        strSQL = strSQL & " AND " & "HOUSE_NO_SUFFIX=" & Me.Suffix
    End If
    
    strSQL = "SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME  " _
            & "FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO " _
            & " WHERE " & strSQL
    Debug.Print strSQL
    
    Me.Property_Information.Form.RecordSource = strSQL    
End If
End Sub

[June7]

Suffix is probably a text field. Text field parameters must be within apostrophe delimiters.

strSQL = strSQL & " AND HOUSE_NO_SUFFIX='" & Me.Suffix & "'"

Date parameters would use #.

[Adish]

used the line with apostrophe delimiters but still same error. my immediate window result is ...SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO WHERE HOUSE_NO=211 AND dbo_NUCSTREET.STREET_NO=6206 AND UNIT_NO= AND HOUSE_NO_SUFFIX=''

It should not be assigning unit no and suffix to string if it is null. Only assign if not null which is what I am checking in my code. ...If Not IsNull(Me.UnitNo) Then

[June7]

Then UnitNo and Suffix are not actually Null. Could there be empty string in the controls? To handle possibility of Null or empty string:

If Me.UnitNo & "" <> "" Then

Step debug the code. Check that values are what you expect and code runs as expected. See where it deviates and why. Refer to link at bottom of my post for debugging guidelines.

[Adish]

Hi, Thanks. It worked and didn't give error