Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching on a postcode field of a form (Access 2003)

    Hi,

    How would i set up a box (text box?) on a form that would allow users to type in the first few digits of a postcode and then bring up all address records that match this criteria (on a seperate form)? Is there some way of setting something up so that they can enter criteria as we would into a query but on a form? i.e. using the usual *, #, and, or etc. operators.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    To filter on the first characters of the postcode, you can put a text box txtFilter on the form, and a command button cmdFilter next to it. The On Click code for the command button could look like this:

    Private Sub txtFilter_Change()
    Dim strFilter As String

    On Error GoTo Err_Sub

    ' Assemble filter string
    strFilter = "[PostCode] LIKE " & Chr(34) & Me.txtFilter & "*" & Chr(34)
    ' Open form
    DoCmd.OpenForm FormName:="frmDetails", WhereCondition:=strFilter
    Exit Sub

    Err_Sub:
    MsgBox Err.Description, vbExclamation
    End Sub

    where PostCode is the name of the field, and frmDetails is the name of the form to be opened.

    If you want to be able to use AND and OR conditions, that is best done on the form displaying the records itself. You can use Records | Filter | Filter by Form or Records | Filter | Advanced Filter/Sort.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Hi,

    Have tried the code you suggested. The code on the on click of the command button is now:

    Private Sub Command20_Click()

    Dim strfilter As String
    On Error GoTo err_sub
    strfilter = "![view businesses by location frm]![bltbl postcode] like" & Chr(34) & Me.Text18 & "*" & Chr(34)
    DoCmd.OpenForm FormName:="view businesses by location frm", wherecondition:=strfilter
    Exit Sub

    err_sub:
    MsgBox Err.Description, vbExclamation
    End Sub

    However, when i click on it, i get a parameter box up asking for the parameters for the postcode (rather than using the info from the text box). Even if i enter the first couple of postcode digits into the parameter box, it opens the form but displays all the records rather than those selected.

    What could be wrong with my code?

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    In the instruction

    strfilter = "![view businesses by location frm]![bltbl postcode] like" & Chr(34) & Me.Text18 & "*" & Chr(34)

    you should use the name of the postcode field in the table, not the name of a control on the form. If the field is named bltbl postcode, it should be

    strfilter = "[bltbl postcode] like " & Chr(34) & Me.Text18 & "*" & Chr(34)

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Thanks.

    Have amended the code as you suggested but i still get a parameter query box up asking for the parameter on postcode.

    Any ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Without seeing the database it's hard to say what exactly causes this. If you wish, you can post a stripped down copy of your database so that Loungers can investigate the problem directly; see <post#=401925>post 401925</post#> for instructions.

  7. #7
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Thanks.

    Here is a copy of the database. The form concerned is 'Search by Category or Location FRM' with the unformatted text box and command button being the relevant ones.

    If possible, could you also take a look at the 'Add a New Contact FRM'. This is something i've been corresponding with you guys about regarding the synchronising of two combo boxes. I'm trying to get the office names to appear in the 'office name' combo based on which business is selected in 'business name'. I have put the code in (the after update property of business name combo) that you suggested but it doesn't appear to be working.

    Thanks for your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    The field is named BLTBL Post Code, not BLTBL PostCode.

    I'll look at your other question later on.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    In the first place, the name of the combo box bound to CTBL Office Name is Combo43. You must either give it the same name as its control source, or use Combo43 in the code to set its row source (you MUST use the control name). You'll probably want to display only the office name in the combo box, you don't need the business name - you already know that. (The lookup properties for the Office field in the Contacts table must also be modified).

    I have attached a modified version of the database, in Access 2000 format.

  10. #10
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Thank you so much - both of those items are now working.

    The only thing i'm still having a problem with is the row source of the TABLE (have the form working). The statement i've tried to insert is the same as in the VBA i.e. "SELECT [BLTBL Business Location ID], [BLTBL Office Name] FROM [Business Name and Locations] WHERE [bltbl business name] = " & Me.[CTBL_Business_Name] & " ORDER BY [BLTBL Office Name]".

    This brings up an error saying the record source cannot be found. Am i trying the wrong thing?

    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    Sorry, what do you mean by "the row source of the TABLE"?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Searching on a postcode field of a form (Access 2003)

    I assume you're talking about a lookup combobox in a table, but which table and why are you trying to use a query on a query as the source? If my assumption is correct, you can't filter the rows in the lookup like that.
    Charlotte

  13. #13
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    You mentioned that i would also have to change the row source of the lookup for the 'office name' field in the contacts table. At the moment, if you use the form the correct office name options appear (based on which business name is input) but if you use the table this doesn't happen. As i said, it doesn't seem to work with the code i sent but i'm probably doing it wrong!

    Thanks.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Searching on a postcode field of a form (Access 2003)

    You can't do it that way in a table since you can't filter the lookups in a table the way you can in a form, and the Me keyword is invalid outside an instance of a class object (which includes forms, reports, and class modules). It is irrelevant anyhow, since you should NOT be doing data entry in a table! <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Charlotte

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Searching on a postcode field of a form (Access 2003)

    I already modified the row source of the CTBL Office Name field in contacts TBL in the database I attached in <post#=467972>post 467972</post#>. You cannot let the row source adjust itself to the selected Business in the table - tables simply don't work that way. But, as Charlotte mentioned, it is not important anyway, since end users shouldn't work directly in the table.

Posting Permissions

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