Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Add a Postcode field (Office 97 SR2)

    Hi

    I have a table called Arcust01 which contains Customers, Address's, and Post Codes.
    and another table called Machine, which contains the customers but no Post Codes.

    The main form is called Machine which is based on the table called Machine. hence no field for Post Code.
    I would like to put a text box called Post Code on the main form, but I don't know how to call it from the Arcust01 table.

    This is to enable users to search by Post Code.

    Sorry if this sounds confusing I can hardly understand it myself <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Novice User
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    If you are a fool at forty, you will always be a fool

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

    Re: Add a Postcode field (Office 97 SR2)

    I assume that the Arcust01 and Machine tables are linked by a field identifying the customer (CustomerID would be an obvious name).
    What kind of relationship is it? Can one customer have several machines, or can one machine serve several customers?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    The table are linked by the customer field

    One customer can have many machines, each machine belongs to one customer only , they are not shared.

    Thanks
    If you are a fool at forty, you will always be a fool

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

    Re: Add a Postcode field (Office 97 SR2)

    Braddy,

    Create a query based on the Machine and Arcust01 tables. They will be joined by the customer ID field. If there can be machine without customer, double click the join and specify that you want *all* records from Machine. Add * from Machine to the query grid, and those fields from Arcust01 you want to use, including the postal code field.

    Use this query as record source of the Machine form. You can put a combo box on the form that looks up records by postal code. The Combo Box wizard can do the work for you. Make sure that the Wizard ("magic wand") button on the Toolset is on when you add the combo box to the form.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    Thanks for your patience. I am not sure whether I followed you instruction correctly but I have attached a screen shot.

    Ideally I would like the record to change to the selection in the combo box.

    Thanks again
    If you are a fool at forty, you will always be a fool

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

    Re: Add a Postcode field (Office 97 SR2)

    I have attached a zipped demo.

    It contains two tables:
    <UL><LI>tblMachine contains data on machines
    <LI>tblCustomer contains customer data.[/list]The tables are linked on the CustomerNo field.

    There are two queries:
    <UL><LI>qryMachine returns all records from tblMachine, and related info from tblCustomer, such as Company name and PostCode. This is the record source of the form.
    <LI>qryUniquePostCodes returns a list of unique post codes (without duplicates.) This is the row source of the combo box on the form.[/list]There is one form:
    <UL><LI>frmMachine, based on qryMachine.[/list]The form header section contains an unbound combo box with qryUniquePostCodes as row source. The AfterUpdate code was generated by the Combo Box Wizard and then modified a bit by me. The code looks like this, with comments added:

    Private Sub cboPostCode_AfterUpdate()
    Dim rst As DAO.Recordset
    ' Make a copy of the record source of the form
    Set rst = Me.RecordsetClone
    ' Find the first record with the selected post code
    rst.FindFirst "[PostCode] = '" & Me![cboPostCode] & "'"
    If rst.NoMatch Then
    ' Sound an annoying beep if not found
    Beep
    Else
    ' Jump to record if found
    Me.Bookmark = rst.Bookmark
    End If
    ' Clean up
    Set rst = Nothing
    End Sub

    I also added a "Find Next" command button. The code behind it is very similar, only with FindNext instead of FindFirst. Please have a look at the design of the queries and of the form, and at the code behind the form. I hope that you'll be able to adapt this simple example for use in your form.
    Attached Files Attached Files

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    Thank you for the file, I wont be able to study it for a couple of days, but I will let you know how I get on.

    Many Thanks


    Bradddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    I got to it a bit quicker than I expected. I think I must have gone wrong somewhere see attached

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    This is the other error code

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Add a Postcode field (Office 97 SR2)

    Hi Braddy,

    There are several possible explanations for this.
    <UL><LI>The form in my demo database is not based on the Machines table, but on a query that adds (among others) the post code field from the Customers table. If the post code is not a field in the record source of your form, you'll get this error message.
    <LI>Since I didn't know the exact names of the fields in your tables, I made them up myself. If you have different field names, and if you copied code from my demo to your database, you'll also get this error message. In particular, I used "PostCode" as field name. If you named it "Post Code" (with a space between the words) or "PostalCode" or something else, you must modify the code accordingly.[/list]Check carefully - a small difference in spelling could be all that is behind this...

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Add a Postcode field (Office 97 SR2)

    Hi Hans

    I did what you did, I created the query and created a form from it, set up the combo box and now I get no errors.

    But it does not select the record relating to the postcode

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Add a Postcode field (Office 97 SR2)

    Hello Braddy,

    Try the following: in the module, click in the line
    <font face="Georgia">Private Sub cboPostCode_AfterUpdate()</font face=georgia>
    and press F9. This will set a breakpoint.

    Now, open the form, and select a post code from the combo box. The code will halt at the start of the AfterUpdate procedure. You can execute it one step at a time by pressing F8. When you get to
    <font face="Georgia">If rst.NoMatch Then</font face=georgia>
    hover the mouse over Nomatch to see what the value is. If you see rst.Nomatch = True, no record was found, which should be impossible. Also hover the mouse over Me![cboPostCode]. You should see something like Me![cboPostCode] = "MA2 1RY", with a valid post code between the quotes. If there is something not looking like a post code between the quotes, e.g. a Company number or name, the combo box hasn't been set up correctly. Its row source should be a query or SQL statement that selects all available post codes, the way it is done in the demo.

Posting Permissions

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