how do i populate a field from another field. 2

[czarjosh]

I have a table for Registrants to an event. In the table I have field for the registrant’s roommate, and the row source is:

SELECT Registration.RecNumber, [First name] & " " & [last Name] AS [Full name] FROM Registration;

So let’s say there is a record Jim Smith, and I want to make Jim’s roommate Sally Mae. So I go to Jim Smith’s record and pick Sally Mae out of the drop down. How do I make Sally Mae’s record automatically Populate Jim Smith into the roommate field?

 

[NorthNone]

Are you willing to do this in VBA code or do you want to do it solely within the table itself?

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[czarjosh]

I'll do whatever is easier. I know very little abput VBA stuff, but have enough of an understanding to get around the code builder.

 

[willir]

Josh

Do the parents know that Sally and Jim are room mates? ;-)

I think what you are saying is that after asiging Sally to Jim's room, you want to automatically update Sally's record to reflect that Jim is her roommate??

I suspect the simplest way is to use the DoCmd.RunSQL method.

Assumptions:
- The room mate field uses a combo box where the record sourse is [tt]
SELECT Registration.RecNumber, [First name],[last Name] AS [Full name] FROM Registration[/tt]
- RecNumber is numeric (long)
- The name of the Room Mate field is RooMateRec

For the AfterUpdate event for the room mate field.

Dim strSQL as String

If Nz(Me.RoomMateRec, 0) > 0 Then
    strSQL = "UPDATE Registration SET RoomMateRec = " _ 
    & Me.RecNumber & " WHERE RecNumber = " & RoomMateRec

    DoCmd.SetWarnings (False)  'turn off wanring prompt
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings (True)   'turn warnings back on

End If

Okay, that is one answer. Now something to ponder. What happens is Sally does not want to be Jim's room mate?? Let's say, she want to bunk up with a school friend, Betty Anne. Hmmm, now you have to adjust Jim's record, Sally's record and Betty Anne's record. Now, let's make it more complicated. Betty's registration was handled by some one else - this data entry person updated Betty's record and Sally's record without even knowing about Jim!! But Jim will have receive a confirmation letter saying he is bunking with Sally, Betty will receive a letter indicating that she bunking with Sally, and poor Sally will receive two letters that she is bunking with both Sally and Jim!

This makes All In The Family look tame.

When you start this type maintenance and integrity issues, start thinking abour re-evaluating your design.

I suspect you may have more success by assining registrants to "beds", where a room mate is assigned bed in the same room. NOW, if you receive conflicting bunk mates, you are dealing with the issue at the room / bed level where conflicts will be less likely.

Your design is still very workable, but you may have to figure out a process to prevent poor Sally from being assigned different bunk mates. ;-)

Richard

 

[NorthNone]

Richard, great response! I was working on a similar approach using ADO, but it wasn't ready for prime time. Excellent overall comment about assigning people to beds, not to one another You answered the actual question and raised the higher level design issue as well.
I didn't post the original question, but your thorough response deserves a star!
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[czarjosh]

Thank you very much. THat was super easy and the point of assigning beds is noted. I had thought of it before and soemone else had brought it up. I think may use this design for the tiem being and when I get free time, try to reconstruct the design more properly.

Can this be taken one step further. So this works excellently, in the Roommate Combo box, would there be a way to eliminate records that have been assigned roommates? THis would be so we can only see records who have not yet recieved a roomate.