automatically fill in text fields - more help pls

[Chris Lines]

Guys - I have read all the posts here and have found the answer to my
question. But I can't for the life of me get it to work. I therefore seek
more help pls to resolve the problem. Two tables: tblTeamMembers and
tblTeamLocation. All fields from these tables are on a query-based, single
form. Location details are at the bottom of the form. I am trying to fill in
several txtboxes using query-based cboTeamLocation. I am using the code
provided in several posts. It is as follows:

Private Sub cboTeamLocation_AfterUpdate()
Me.TeamLocationBuilding.Value = Me.cboTeamLocation.Column(2)
Me.TeamLocationAddress.Value = Me.cboTeamLocation.Column(3)
Me.TeamLocationCity.Value = Me.cboTeamLocation.Column(4)
Me.TeamLocationZip.Value = Me.cboTeamLocation.Column(5)
Me.TeamLocationCountry.Value = Me.cboTeamLocation.Column(6)
End Sub

When I use cboTeamLocation it works and so does TeamLocationBuilding. But
when I debug it shows all the other txtboxes as returning a null value. And I
get runtime error 3314 telling me that I must enter a value. I understand
that message, but cannot work out why I keep getting a null value. Have I got
the code wrong? I would appreciate help pls.

Regards

 

[Ken Snell MVP]

Post the SQL statement of the combobox's RowSource query. Be sure that it
has at least 7 fields in it, based on the number of columns you expect to
use in your code.

 

[Chris Lines]

Ken - Thank you for your quick response. Here is the SQL statement:

SELECT tblTeamLocation.TeamLocationID, tblTeamLocation.TeamLocationOrgLong,
tblTeamLocation.TeamLocationBuilding, tblTeamLocation.TeamLocationAddress,
tblTeamLocation.TeamLocationCity, tblTeamLocation.TeamLocationZip,
tblTeamLocation.TeamLocationCountry
FROM tblTeamLocation
ORDER BY tblTeamLocation.TeamLocationOrgLong;

It has 7 columns, starting with zero-based TeamLocationID. The 6 remaining
columns are the ones I want: column1 = TeamLocationOrgLong; column2 =
TeamLocationBuilding ... etc. Have I answered your question properly?

 

[Chris Lines]

Ken - I have just read another question/answer post that came up today and I
have the answer to my question. I had the column count wrong and had not put
in the correct column widths. It works!! Thank you so much for your effort. I
very much appreciate it. This is a great forum. Thanks again.

Regards

Chris