Search Form query

[GraemeG]

Hello.

I have the following code which I use one my forms to search and bring up a record. It works great. However it requires the user to know the actually unique property reference number to search.

Private Sub cmdSearchPropref_Click()
    Dim Rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set Rs = Me.RecordsetClone
        Rs.FindFirst "[propref] = """ & Me.cboMoveTo & """"
        If Rs.NoMatch Then
            MsgBox "Record not found please check number and try again?", vbOKOnly, "Search Error"
        Else
            'Display the found record in the form.
            Me.Bookmark = Rs.Bookmark
        End If
        Set Rs = Nothing
    End If
End Sub

I was wondering if I could use two different fields hsno and address 1 for a search I.e. the user can enter a house number in the textbox search for hsno and then enter address 1 in textbox search for address1. To search any ideas much appreciated.

I thought maybe something like the following but it does not work.

Private Sub cmdSearchAddress_Click()
    Dim Rs As DAO.Recordset
    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set Rs = Me.RecordsetClone
        Rs.FindFirst "[hsno] = """ And "[address1] = """ & Me.cboMoveTo & """"
        If Rs.NoMatch Then
            MsgBox "Record not found please Address and try again?", vbOKOnly, "Search Error"
        Else
            'Display the found record in the form.
            Me.Bookmark = Rs.Bookmark
        End If
        Set Rs = Nothing
    End If
End Sub

 

[boblarson]

Why not just make a selection from a combo box (which, if the AutoExpand property is set to YES then when they type it will basically bring up the selections they can choose from in the combo as they type).

 

[GraemeG]

Why not just make a selection from a combo box (which, if the AutoExpand property is set to YES then when they type it will basically bring up the selections they can choose from in the combo as they type).

There is 10000 records.

 

[boblarson]

Okay, so what is different about having them type a number in and then the address when you could have the same two together in the combo and the minute they start typing it moves to those selections which match? I'm not sure I understand why that is so different from what you've said.

Also, as a side question, how long has it taken to get 10,000 records?

 

[GraemeG]

Okay, so what is different about having them type a number in and then the address when you could have the same two together in the combo and the minute they start typing it moves to those selections which match? I'm not sure I understand why that is so different from what you've said.

Also, as a side question, how long has it taken to get 10,000 records?

We have 80000+ properties to complete. 10000 is just a start and we have not populated all data yet, but we have got approx 600 full complete surveys against 10000 populated address which has taken 8 months.

I do not understand how to create that joint combo box for when they start typing?

 

[boblarson]

Okay, quick question - isn't the hsno a partial duplicate of Address1? What is an example of both?

 

[GraemeG]

Okay, quick question - isn't the hsno a partial duplicate of Address1? What is an example of both?

Sorry no the:
hsno is just the number of the house.
Address1 is the street name.
So combined would find the property.

i.e hsno 45
Street name: Smith Street.
However there could be many 45 hsno against street names.

 

[boblarson]

Okay, so here's my idea using a text box and a combo:

In the text box's after update event you would use:

Me.ComboNameHere.RowSource = "Select * From YourTableName Where [hsno] = " & Me.TextBoxNameHere

And if hsno is text then

Me.ComboNameHere.RowSource = "Select * From YourTableName Where [hsno] = " & Chr(34) & Me.TextBoxNameHere & Chr(34)

and then they should be able to select the rest and I would have enough of the address, including anything else so that you can also have the property ID in the combo so they can just pick the one they want and the code doesn't have to try to use find first.

Private Sub ComboNameHere_AfterUpdate()
   Me.Filter = "[propref] = " & Chr(34) & Me.ComboNameHere & Chr(34)
   Me.FilterOn = True
End Sub

So then they are taken directly there.

 

[GraemeG]

I cant get any sort of textbox and combo to work

 

[DCrake]

Why not use the postcode in your combo to search. Even if you have more than one property with the same postcode you are a lot nearer than using house numbers.

 

[GraemeG]

Why not use the postcode in your combo to search. Even if you have more than one property with the same postcode you are a lot nearer than using house numbers.

Thanks.

I have resolved this issue now.

I created a query of Propref and then concatenated hsno and address 1 with space. and then used my search code like previous to link the proprefs. So user no enters for example 32 smith street and if this address has a propref it finds it and brings it up in form.