Combo Box Changing Values in Table - Still Can't Make It Work

[Chris Lines]

Forum

I have exactly the same problem that G posted in Apr-2009 and that John
Vinson answered. There are also many other posts seeking help on this issue.
Trouble is, notwithstanding all the responses posted, I still can't get mine
to work.

I have tblTeamMembers, which holds team data about names, contact numbers,
employer and etc. Primary key is autonumber TeamID. I have linked this table
to another table called tblTeamLocation. Its primary key is autonumber
TeamLocationID. This is a foreign key in tblTeamMembers. Relationship is one
team location for many team members.

I have a single form called frmTeamMembersAll. I have based it on
qryTeamMembersAll. The query allows me to show TeamMembers and TeamLocations
on the same form. So the form shows all team members' personal data plus the
different locations where they work. I have used a bound cbo, called
cboTeamLocationOrgLong, to give users the options of the various locations.
Once the user chooses the location, the cbo automatically populates its
sister text boxes (building, address, zip and etc).

I have read various posts about the use of bound and unbound txt boxes and
cbo. I want to see the location and other data in tblTeamMember, so the
unbound txt doesn't work for me. But the bound cbo is changing the values in
the tblTeamLocation. When I close tblTeamMember I find that a record has
changed in tblTeamLocation. So, tblTeamLocation goes from data, say, A B C D
to, say, data D B C D. I am happy that I have the column numbers right.

My code for AfterUpdate, which I have drawn from various posts, is as follows:

**Code Start**
Option Compare Database
Option Explicit

Private Sub cboTeamLocationOrgLong_AfterUpdate()
Me.txtTeamLocationBuilding.Value = Me.cboTeamLocationOrgLong.Column(2)
Me.txtTeamLocationAddress.Value = Me.cboTeamLocationOrgLong.Column(3)
Me.txtTeamLocationCity.Value = Me.cboTeamLocationOrgLong.Column(4)
Me.txtTeamLocationZip.Value = Me.cboTeamLocationOrgLong.Column(5)
Me.txtTeamLocationCountry.Value = Me.cboTeamLocationOrgLong.Column(6)
End Sub
**Code End**

Is it not working because I'm using linked tables rather than a single
table. I would appreciate any advice to solve this issue. Thank you in
advance.

Chris

 

[Maurice]

Let me see if i get this correct.

You should have an unbound cbo which pulls the data from your table. The
fields that should be populated should be bound fields to the table or query
which should be populated.

The cbo should have no controlsource only a recordsource.

try it and let us know..

 

[Chris Lines]

Maurice - You're a Legend. I was confused about the bound and unbound aspects
of my form. I've got it to work thanks to your advice. I appreicate it very
much. Thanks again.

Regards

Chris