Combo Box

[Hammy]

OK, I have a small problem. I am building a member db for my church, and am stuck on something.

I have and Access db with...

families table with FaimlyID primary key

individuals table with family field related to the family id in the families table, and relationship field (value list head;spouse;child;other values from a combo box)

On the Family form where the user enters family members (which is a subform accessing the indivduals table) I want to set it up that if they choose head or spouse for any idividual in that family and there is already a head or spouse named, that it won't allow them to do so.

My thought was to have a query that selected all records where relationship = "head" and run it in the onchange event of the combo box....if the recordcount <> 0 then throw up a msg box saying no can do.

However, I think that's the Cold Fusion developer in me coming out and I can't seem to find a solution with Access and vb.

Any ideas?
Hammy

 

[Howlsta]

You could try something like this in the beforeupdate event of the combo.

dim head

head = DCount("[relationship]", "tblIndividuals", _

"[relationship] = 'head' AND [familyID] = " & Me.[familyID])

if head <>0 then
your msg here
cancel=true
end if

i'm presuming each member of the same family has the same familyID, so the Dcount will count all records in the individuals table where the relationship is head and the familyID is the same as the familyID on your form.

Rich

 

[Hammy]

Thanks so much for you help!

Works great, only "glitch" is if you update a record to an exisiting one (say head to spouse) when that relationship already exists, if you change it back to what it was, you still get the error box (I assume because the record wasn't changed, and now it thinks you are trying to change it to something that exists still). You have to change it to child or other, then leave the record and go back and change it back.

Somewhere in the If block I Need to set the value of the combo box back to what it was and let the user get out of there.

Any ideas?

Hammy

 

[Hammy]

I kinda overlooked adding a simple

Me![Relationship].Undo

Works like a charm now!

Thanks again for helping me get on the right path!

Hammy