Putting a Combo Box in the main field of a Form in Design View

[RuffianRan]

I'll start off by saying that this is out of a book for a college course. We have built a database for a Medical Clinic and went through these forms a couple 'Tutorials' back. I never got the Combo Box in the main form to work but didn't realize it until I went back to see how it was supposed to work when I was studying for my mid-term. Guess she missed that one. So, the instructions read:

Position the Combo Box, select "I want the combo box to get the values from another table or query" --Next-- select "Query: qryPatientesByName" --Next-- select "Patient" and then "PatientID" --Next-- choose to sort ascending by "Patient" --Next-- re-size -- Next -- foreign key is "PatientID" --Next-- "Store that value in this field" option select "PatientID" --Next-- rename "PatientID".

I'm attaching a copy of what we are up to now, after Tutorial 8. The Form I need help with is frmVISITSANDINVOICES. It's the only thing in caps.



I haven't had the chance to look into this sooner because I've been working on my imaginary Equine Hospital database, trying to figure out what kind of queries, forms, and...haven't got to reports yet. How do you make up a business and then make up a complete database for it?!

[RuralGuy]

The Combo Box wizard will do all of this for you.

[RuffianRan]

Right, these are the steps through the Wizard. The problem is that when you go to Form View and click on a different client nothing happens. The Combo Box in the Header takes you to a different Visit like it's supposed to but the one in the main form does nothing.

[RuralGuy]

What do you expect the Combo Box in the Detail area to accomplish?

[RuralGuy]

It looks like it will only change the patient's name.

[RuffianRan]

I thought it was supposed to change the form to the selected patient's form. Otherwise, what's the point?

[RuffianRan]

I just thought I must be doing something wrong somewhere and that was why it wasn't doing anything productive.

[RuralGuy]

I thought it was supposed to change the form to the selected patient's form. Otherwise, what's the point?

I guess it was put there in case the original entry had the wrong patient!

[RuffianRan]

All this Combo Box does is make it easier to change the name on the VisitAndInvoices Form, linking clients to the wrong medical information. You can't use the connected PatientID to type in anywhere on the current Form to go to that Patient's Form. Doesn't make any sense to me.

In my Individual Case Study, where I created a database from scratch for an imaginary Equine Hospital, I have a Horses Form with similar Combo Boxes. The difference is that I didn't add them in Design View, they were already there from being in Lookup Fields in the Query I drew the Form from. I can change the Farm Name and the Gender Classification with the Combo Boxes but it doesn't change anything else on the Form. And I don't see why it would? There are multiple Horses per Farm, etc. So we come back to, "What's the point in adding this as a Combo Box?"

It's nice that you can't change any info in a Report, but I want to show the info laid out like a Form without letting it be altered. This is very frustrating!

oops...guess I was typing while you were replying

[RuralGuy]

It looks like you called this thread as Solved. ComboBoxes are very useful and powerful if you know what you want to accomplish. Lookup Field on the other hand are a nightmare. They successfully hide what is really in the table. I don't use them as is the case with most developers.

[RuffianRan]

Oh, is it not solved? I thought the decision was that there was no other reason for the Combo Box other than to allow the user to only type in a name already in the Database. I thought you meant that I was looking for something that wasn't there. I'll change it back to Unsolved until you say otherwise.

[RuralGuy]

What do you want the ComboBox to achieve? Almost anything is possible.

[RuffianRan]

Well, I looked back at the book and it doesn't really say. Really helpful ! So part of me wonders why I want to make it do anything. But that's just me.

It just seems to me that if you're going to go to the trouble of putting a Combo Box in where you can only enter peoples names from the Database, no new ones, that by doing so the Form would then switch over the that person's Invoice. If you just change the Name you are actually changing the Name and PatientID connected to the InvoiceID for the whole Database. This should not be allowed! I checked it with another copy of the Database and does indeed change the order of the PatientIDs on the tblVisits.

But your question was simply, "What do I want it to do?" I want the Form to switch over to the Patient's Invoice when you type that Patient's Name into the Combo Box.

I want to know how to put more restrictions in a Form. I like the form layout but want the fixed fields in a Report. On the other hand, I don't think this is relevant to the class at all. But that's just me. ....and I always type too much, sorry

[RuralGuy]

Just so you know, you could add a new name with a ComboBox if that's what you wanted. It is called the "Not In List" event. I take iy you do NOT want this form to change the patient ID/ Name for this visit?

[RuffianRan]

Right, I do not want to change the Patient ID/Name for the selected Visit ID. I want to pull up the Form with the Visit ID that is connected to the Patient ID on the Visits Table. After all, I am selecting the Patient ID, it should show me that Patient's information.