Form Structure and Not In List Question

[Paul Cooke]

Hi All

After hours of searching the net to solve this myself I have finally accepted I need Help !!

I currently have 3 forms designed on my DB

DetailsForm
TreatSubForm
NewTreatForm

When a user opens the NewTreatform, they first have to enter a persons 'FirstName' if the name they enter is not in the list, I need the Not In List event to be triggered by stating a simple msgbox like Name Not in list add it yes no ect ect...

If they say yes then the DetailsForm opens on a new record and they enter the new name (and other details) on this form. Once this done They click a button to save and close the Detailsform and return back to the NewTreatFrom.

When back in the NewTreatForm any corrosponding fields from the DetailsForm are automatically populated with the information they have just entered.

The other Idea I have pondered on is to maybe have a filtered form pop up when the something is entered in the FirstName field. So If I enter 'P' a "form"? or something pops up with all names beginnging with P and the user can select one of the items on this filtered list if it has other details on it that match what they are entering. If not then its back to the Not In List Event

I really hope i have explained this enough (I'm confused myself now!)

I know what I need in my mind but not sure if it can be done in practice?

Thanks you for any advice you anyone can offer


Paul

 

[Mr. B]

Paul,

I may have misunderstoon, but it sounds like you are using a text box where you expect your users to enter the first name. If this is true then you need change the text box to a combo box where you would display the list of names. In the combo box you can even combine the first and last names, displaying the entire name of the individual but have the bound value of the combo box to be the ID value for the individuals record.

I would also assume that you have a table of these individuals and that you really want to have the selected name to be linked to the current record. If this is true then you should have a unique identifying (Key) field that is an autonumber type field in the table that holds these records. You would then have a number type field (long integer) in the table where the New Treatment is being entered that will hold the ID value of the individual selected in the combo box.

Using this method, you can then use the OnNotInList event of the combo box to cause the actions you are wanting to perform.

 

[Paul Cooke]

Hi Mr B

Many thanks for the reply .. in answer to your points

Just to confirm there are 1 to many relationships set up already and all relevant "ID" fields are in the various tables.

The FirstName field in the NewTreatForm is a combo box referring to my DetailsTable. this was set up using the wizard and it does pick up all the FirstNames already listed in the related table. Although On looking in the design view of the combo box it does say Unbound?

If I click on a the name Paul for example and click it that goes into the field no problems and it moves onto the next field "Surname (I am pretty sure I know what you mean by having more "lines" in the combo list by the way)

What I was hopinng for was if I clicked on the dropdown list saw the name of the person I wanted and selected Paul it would enter this in the control but also automatically fill in the surname, dob, occupation ect..fields that are also on this NewTreatForm - sort of autofill ??

The Not in List event would be triggered if the name was not already in the table and I would need to add it?

Doe that make sense?

Many thanks

Paul

 

[Mr. B]

Paul,

The first thing that gets my attention is the fact that your combo box is indicating that it "unbound". This tells me that you are not storing the selected value in your combo box in the field in your table that would record the selection.

What I meat when I said that you could show both first and last names, was that in the sql statement that populates your combo box, you can concatenate the first and last names together to actually show the entire individuals name. You can then have the ID value for that individuals record to be the bound value of the combo box and when you provide the combo box with the "control source" you would be identifying the field in your table where the bound value of the combo box is to be stored. This is what will later tell you which of the individuals was selected from the combo box.

When you have this working correctly, you can display on your form any additiional information about the selected individual provided that data is in a table with or related to the selected individual.

 

[Paul Cooke]

This has been resolved - Many thanks Mr B