New data entry from combo box

[RuralGuy]

Are the SubForms bound to the same query/table as the MainForm? What are you using in the LinkChild/MasterFields of the two SubForms?

 

[Banana]

Yes. All are bound by same ID; PersonID. and all subforms are linked by this ID. This comes from tblPerson

tblPerson
PersonID
FirstName
LastName
GenderID
CategoryID
EthnicityID

 

[RuralGuy]

The SubForms should *not* be bound to the same query/table as the MainForm. I would expect the [Address] subform to have a RecordSource of a query/table with all of the address in it and a ForeignKey (FK) of the PersonID from tblPerson. It would be the many side of a Person table relationship since thay can have more than one address (such as a mailing address). The [Contact] SubForm should have a RecordSource from a Contact query/table that would be the many side of yet another relationship for the Person and contain the PersonID FK from tblPerson. Is this not the case?

 

[Banana]

When I said they were bound by PersonID, I should have clarified that they are bounded by their respective PersonID. Addresses and Contacts subform has their own tables with PersonID as a FK to tblPerson.

In other word, it's already set up exactly as you described it....

 

[RuralGuy]

Hmmm... that all sounds like it is set up correctly. Are all of those ???ID fields AutoNumbers? I was thinking the problem was with the subforms. Were you thinking it was with null [cboGender], [cboCategory] and [cboEthnicity] fields?

 

[Banana]

Yes- I cannot have null values in those fields. This is even though the table has them set as not required, and indexed with duplicates.

Regardless of which form I use, I MUST fill in any related data (this is true for any other tables that is on a many side relationship to its own lookup tables) or I will get the error of saying related data is required in table X before I can save the record. Since the behavior was consistent, I've assumed it was the way Access was designed to do.

Oh, to answer your question, yes they are all autonumbers.

 

[RuralGuy]

I'm pretty sure your tblPerson is on the One side of a 1-M relationship. You may be forced to open another form in the NotInList event of [cboName] and get the other values before allowing the user to return. It is a simple form and worth throwing something together to see if it resolves the issue. Just be sure and open it acDialog to stop the code in the first form.

 

[Banana]

Will give it a shot.

Thanks for your effort in this matter.

(Yes, the tblPerson is a one side; though the three fields are on the many side to their respective lookup tables. Why can't they be left blank, is beyond me.)

 

[RuralGuy]

What kind of code do you have in the AfterUpdate event of [cboName]? I think you should be able to leave those other cbo's blank and fill them in later. I'd still like to see a small db that demonstrates the problem. I think something else is going on that we're not looking at.

 

[Banana]

Sure- I can email you a sample. PM me.

 

[RuralGuy]

Boing!!!! I'm starting to understand what you have been telling me all along about GenderID, CategoryID and EthnicityID being the many side of a relationship. RI does not allow you to populate those fields in tblPerson with anything other than what is in their respective LookUp tables. It is working as I would expect. Looks like the separate PopUp form will need to be the answer in this case.

 

[Banana]

Ah, so it is truly expected behavior of Access?

Well, that's reassuring then!

Again, thanks!!!!

 

[Banana]

*bump*

Okay, so I basically made a pop up form to pause the code in execution and fill in three required information before a new person can be added to the list for the combobox.

However, the form only has three controls, and would be nice to be viewable, and somehow it screws up the fast data entry. So I wasn't quite satisfied.

However, I found a solution- basically I declare in form's module-

Public NewPerson As Boolean

,

and in the NotInList event, I have:

'Person Name is added to the table.
'All required data are given a value of 1 (e.g. the first record of the lookup table)
Response = acDataErrAdded
NewPerson = True

which goes to the AfterUpdate event:

If NewPerson=True Then
'Goes through all controls and set the value to 0, and set focus on the next control, to prompt for user's input

With Ghudson's Better Mousetrap, this will work out quite nicely.

HTH.

 

[Banana]

*bump*

While I usually require both First Name and Last Name for other situation, one situation I need to give my users flexiblity of adding only First or Last Name because they may not have the information when they add a new attendee. I will code a reminder to ensure the missing information is filled in at a later date.

However, how would I fix it up so a user can go to a existing record that has only one part of Name and add the missing piece to the name without adding a new record because of NotInList event's firing. I could trap for new records, but not sure if that is foolproof (e.g. if a user made a wrong selection and want to modify the selection?).

Any suggestions?