lookup value dependant on previous field entry - Requery?

[oOoaimeeoOo]

I think I'm on the right track, but I've got a bit stuck.

I'm creating a database which contains information for keeping dog grooming clients. It has 3 tables, table 1 is customer, table 2 is dog, and table 3 is booking.

I want to be able to choose the customer table and within that you can see the pet linked to that customer and within the dog table you can see any bookings with that dog.
What I have done is linked the pet and booking table to the customer details via mobile no. being the primary key.

Where I am stuck is here, I want a form called appointments which contains fields *ID, date, mobile, dog name, grooming stlye and other. What i want to happen is this, once the mobile number is entered, i then when it to lookup the mobile number in the dog table and pull the pet name from that, then in the dog name box it will then provide a drop down of dogs related to that mobile number.

I understand a query needs to be made in the dog name field, so in that field i created a query to look up the mobile no. in the appointment table, and to then lookup the pet name in the pets table. This kind of works. The two problems are this, the drop down in the dog name field shows all of the dogs in the database not just the ones assigned to the mobile no. in the field before. If i choose one of the dogs not related to that mobile it changes the mobile number in the field before to the relevant one (but i don't want to be able to see the dogs which arent related to the mobile no. in the field before.
The other issue is that on one of the customers there are two dogs, now on the drop down you can see both dogs, but it will only allow you to select the dog that was entered first into the database. If i select the other dog it simply chooses the data for the one first entered.

How can I fix this? I have read about doing Requeries on the afterupdate of the field and attempted this by choosing the dog name field and in the afterupdate telling it to requery pet name (I've also tried mobile), this hasn't worked. I have a feeling I'm getting a bit confused by which fields i'm supposed to use etc.

Any help would be appreciated. If i haven't been clear enough please let me know.

 

[plog]

What I have done is linked the pet and booking table to the customer details via mobile no. being the primary key.

This is incorrect. The customer should not be directly linked to the booking table. It would be done indirectly via the pet table. People to pets, pets to bookings.

Additionally, a form that directly interacts with data (add/edit/delete) should be based on a table, not a query. You can still achieve what you want, but if you want to set a booking, the form needs to be based on bookinsg alone, not a query its part of.

The way my forms for this system would work is as such:

Search Form - unbound, user enters mobile number and it has a sub-form that populates with all pets associated with that customer. Next to each pet is a button, when clicked goes to the Pet Form to show that pets data.

Pet Form - Based on pet, top part allows user to edit/enter data about a pet (name, dob, breed, etc). Bottom portion is a sub-form, based on bookings that lists that pets bookings. For a new booking, user would enter data into the bottom of this sub-form.

Again, though, you should first set your relationships properly.