How to implement autocomplete?

[Sandro997]

Ok, here's my situation:

I've got 2 tables, tblPeople and tblParents. tblPeople has parentID which references tblParents. Multiple people can have the same parents. I am developing the form for tblPeople and domain constraints prevent me from using a form/subform style. The recordsource of the form is tblPeople joined to tblParents and I have a combobox which displays parentName, but is bound to parentID. In addition, all fields in tblParents are displayed directly. This works perfectly when the parent exists, but I want to also be able to add new parents, and I want to use the same form to do it(a new form for creating parents is not an option). On the theoretical level, how do I accomplish this?

[PianoMan64]

Ok, here's my situation:

I've got 2 tables, tblPeople and tblParents. tblPeople has parentID which references tblParents. Multiple people can have the same parents. I am developing the form for tblPeople and domain constraints prevent me from using a form/subform style. The recordsource of the form is tblPeople joined to tblParents and I have a combobox which displays parentName, but is bound to parentID. In addition, all fields in tblParents are displayed directly. This works perfectly when the parent exists, but I want to also be able to add new parents, and I want to use the same form to do it(a new form for creating parents is not an option). On the theoretical level, how do I accomplish this?

The way that you're describing it, there isn't a way to add parents unless there is a Mainform/SubForm concept.

Unless you're planning to have all the parents information loaded by another process, there isn't any way to do what you speak of.

Hope that helps,

Joe P.

[Sandro997]

Ugh, that's not what I wanted to hear. Anyway, this is what I've learned since I posted. If I make my combobox unbound, and have a stored procedure to update txtParentID(which is the hidden ID field within my form) in AfterUpdate of the combobox, then I can have my desired autocomplete functionality. I've also noticed that if my combobox is bound to ParentName and a new name is entered, then a brand new parent record is created in tblParents. I am currently working to merge these two bits of functionality.

Hmm, seems that the new parents are only created if it's a new person and they don't yet have parents.

I'm sure I can get this thing to work eventually....

[NeoPa]

Have you considered implementing this a bit more logically?

By this I mean :
Parents are people too :)

What about losing the tblParents table completely and implementing a self-referencing tblPerson table instead?