Populate combo box list with other field values on form

[yoshe]

Hello,
I have a form that has primary and secondary contact information, and a subform to track phone calls. I want to populate a combo box list (not limited to list) for "PersonContacted" with the names of the primary and secondary contact names. I've tried to set the row source for the combo box control to the form fields using the form On Current event, but it doesnt work if I have just entered the primary and secondary contact names, until I leave the record and come back. Any thoughts? Thanks much....

 

[Remou]

What code did you use to set the Row Source? Is the combo Row Source Type set to Value List?

 

[yoshe]

Yes, it is set to value list. The code I'm using is pretty simple

Private Sub Form_Current()
SpokeTo.RowSource=Me.Contact1 & ";" & Me.Contact2
End Sub

Where SpokeTo is an unbound combo box. Maybe it being unbound is the problem? The reason it's unbound is because once it and other unbound text boxes are filled, I have the user click a button to create a record in the subform. I figured that way they can view the history but can't edit it since it's locked. If there's a better way, I'm all ears - my knowledge is very basic and I'm just trying to wear a different hat for the moment.

 

[Remou]

I am not quite sure what you wish to achieve, because it seems that you are showing the same data twice, which is usually frowned upon, so it is difficult to see a simpler way.

You can add the rowsource code to the After Update events of Contact1 and Contact2, or you can create a function and call it.

 

[ProgramError]

create a query with the desired results then use that as your rowsource. This way it can be updated without having to resort to VBA code to do so.


Ian Mayor (UK)
Program Error
If people say I have bad breath, then why do they continue to ask me questions and expect me to answer them?

 

[Remou]

ProgramError
That will mean that the entries for Contact1 and Contact2 are saved to a table, which may not be the case, and / or may not be desirable in a new record. It is odd that the content of these controls are added to a combo, do you not think?

 

[MajP]

If you normalized your tables this would be a lot easier

tblContactInfo
contactID
contactName
other fields unique to a contact
contactType (primary or secondary)
someKey_fk (relates a contact back to some table)

Now you can have 1,2 or many contacts for the same record.

My guess is that this strange work around is because your db is not properly designed.

 

[yoshe]

Ok, I got it to work by using my code in the On Enter event for the field rather than the form's On Current.

Thanks for all your helpful responses. I have no doubt my DB is not properly designed (I just recently started dabbling in Access for certain projects), and perhaps if this totally bombs I can convince my company to hire one of you all to do some consulting work in the near future....

That said, I am doing my best to normalize the data, and I don't think the data is shown twice - it's entered into the unbound boxes (Date of call, who you spoke to, description of call) and then when the button is clicked to "add a new record" the boxes clear and it shows up on the locked subform. My main concern was that I didn't want the history to be able to be edited once a call was made and recorded.