Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TextBox on Form (97/2k)

    I know for sure that this is an easy question but I can't quite get the answer I want.
    I am modelling a vet's appointment's system and I have an appointment form containing combo boxes and textboxes. One combobox displays a list of Animal owners. I simply want the animal's name to appear in a textbox when an owner is selected. They are both stored in the same table - tblCustomer.

    Please help.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    The what you need to do is put the following code in the control source of the text box. Where cboName is the name of your combo box and the column number (3) is the number of the column counting from the left which has the data you want displayed in. Remember, the first column is (0)

    =[cboName].[Column](3)

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    Thanks Rupert. I now understand. However, another problem has arisen. I can select any owner and vet which is saved to a booking table except the first owner in the list. I get a message which reads " You cannot add or change a record because a related record is required in table tblCustomer. I don't understand what is going on because I am only looking up the owners from this table:the control source for the owners combobox points to a field in the bookings table. Everything works fine with every other owner selection.
    Any ideas? I hope I've expressed the problem adequately.

    Thanks

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    The I would guess that you are looking at a record based on a query between two or more tables, and that one of the records is missing.

    If your first customer in the customers table is linked to a vet in the vets table and that vet has been deleted for some reason then this is a sort of message you get.

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    Thanks Rupert for responding. Mystery! I have made no queries yet in the project and no vet information is stored in the owner's table. Everything appears as it should.

    Thanks again.

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    On a second reading of your question I see that you have the following tables customer, owner, vet and booking. I would suggest that you might have a customer listed in the customer table but no reference to it in another table.

    I'm pretty sure you've got a reference to a record, which has been deleted. I would suggest you look in all the tables that have relationships with your customer table and see if the reference to the first customer is present where it should be.

    As a last resort a method of resolving this type of situation I use is to delete all the information in all the tables and start again. However before you contemplate doing this I should make a backup copy of your database.

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    Thanks Rupert for taking the time to offer your help yet again. I made a mistake when I replied to your post. I used the term 'Customer' when I meant 'Owner. I only have three tables: Owner, Vet and Booking and no queries. I will check carefully your suggestions.

    Again, thank you,

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: TextBox on Form (97/2k)

    Can I add a couple of sugestions here.
    If you build forms using wizards, Access creates queries for you and stores them as an sql string in the form's record source. So you may have queries that you haven't built! What is the record source of the bookings form?

    My second point is an observation on the info you have supplied, not an answer to the question you have raised. As the owner of two dogs and two rabbits, can I suggest that owners and animals really need to be in different tables because there is a one to many relationship between them. Otherwise you will have to repeat owner info for each animal.
    Regards
    John



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

    Re: TextBox on Form (97/2k)

    Unless you follow John's suggestion, you'll quickly get yourself entirely tangled up. Pets and owners can actually be a many to many relationship, assuming that you may have more than one person in a household (i.e., husband and wife) who could be considered an owner. So you need a table for owners, a table for pets and a table that joins the two. In that table you would have a key from the owner table and a key from the pet table that would create a unique connection between a single owner and a single pet. That way, if you brought up an owner, you could go through that table to find all the pets that person might have.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TextBox on Form (97/2k)

    Thanks John and Charlotte for your suggestions which I will follow through. I was aware that third form normalisation of the data would require an owners_pets table to be set up. I was working through some initial ideas when I came across my original problem.
    With help from yourselves and others in the forum, my old and saturated brain is beginning to see a glimmer of light.

    Many thanks

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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