Lookup woes?

[WCDoan]

Let me first thank everyone for their help over the past several months. It
has been greatly appreciated. Also, let me apologize for not acknowledging or
responding as promptly as I should have, but me job keeps me going in several
directions at once and having come to it from being a musician with no
deadlines and such, at times it's overwhelming. And, also from posting
questions that might have been previously answered, but sometimes when I
search for these previously asked questions, I don't always find them so I
end up asking them again...sorry for that. Please bear with me and overlook
these indiscretions. I have learned a great deal from this group and have
been able to use this info to figure out some things on my own. I'm still
hanging in and struggling through, but just when I think I've got a handle on
things, I find out I don't have quite the grasp I thought I did. Thanks again
for yall's continuing help and patience. On to the problem at hand.

I have a database I'm developing for the City I'm working for that will be
used for the new Animal License Program. I have 3 tables :
1. Owners - PK auto - OwnerID
2. Animals - PK auto - Foreign Key link to Owners Table - OwnerID-AnimalTable
3. Fees - PK auto - Foreign Key link to Animal Table - AnimalID-FeeTable

I have a form for entering the animal information and fees. There is a
look-up combo box to look up the Owner. Also, a command button to enter a new
owner from this Animal form and a command button to view the Owner's info.
There is sub-form to enter the fees for the Animal on the form.

1. Problem - When I use the look-up box to select the owner, the animal
information for that owner does not change from whatever is there. For
example, when the form opens, the first owner's animal record is displayed
and if I use the look-up box to change it to the second owner's animal
record, the information stays the same as for the first owner's animal
record. What do I need to do to clear out the previous owner's animal info
and pull up the animal info for the owner I selected in the look-up box.

2. If I selected the second owner in the look-up box, then the first animal
for the first owner now belongs to the second owner in the look-up box. I can
go back and select the first owner and get animal info back to the correct
owner.

I am understanding Access a little bit more than before, but still am a
novice, so any guidance and direction will be truly appreciated.

Thanks,
RandyM

 

[Steve Schapel]

Randy,

On your form, you have a combobox which is bound to the OwnerID field.
When you select an item (i.e. an owner) from the drop-down list, this is
a data entry process, i.e. you are assigning the owner to the form's
current animal record. You are apparently thinking that you can use
this combobox for navigation, which is an entirely different process
than data entry/editing. Hope you understand what I mean. If you want
to navigate to another record, based on your selection in a combobox,
then this needs to be an *unbound* combobox. And in this case, maybe
positioned and/or coloured in such a way to make it distinct from the
data bound controls. But here's the next question to consider... if you
have an unbound combobox where you can select an owner, and you
apparently want to then go to the record of the animal owned by that
owner (this is the Animals form, after all). So what if that person
owns more than one animal? Which of the animals should it go to?
Based on what you have told us so far, I would imagine you would be
better, from the point of view of managing owners, to have a separate
Owners form, possibly with a subform where all the animals owned by that
owner can be seen.

 

[WCDoan]

Thanks Steve for replying. After looking at my problem, reading some more on
subforms and from what you said, I used the form wizard to set up the form
using linked subforms. This seems to accomplish what I needed to get done, at
least from what I can see right now. I'm adding some more data to make sure,
but I think that's probably what I need to do. Thanks again for the info,
every bit that I get from all of your 'gurus' I'm putting in a notebook for
future reference and for study purposes. I'm slowly beginning to see the
'Access light', but I do emphasize slowly

Thanks again for replying,
Randy M

 

[Steve Schapel]

Randy,

... but just when I think I've got a handle on
things, I find out I don't have quite the grasp I thought I did.

Be assured that this is normal