Solved: Autofill a text box using Dlookup or something

[NY2DR]

Hello all,

Here is what I would like to do:

In the attached file, in the FORMS section, When I enter the "Team Number" in the box,I would like the Members name to automactically appear in the "Members Name" box that corresponds to the "Team Number"

I would like this because about 80% of my team have some of the most unpronouncable names I could imagine, and typing it in manually drives me crazy because the way it's pronounced is not exactly the way its spelled.

This will also give my teammates the dignity and respect by having their names spelled correctly when I print out a list for them with their results.
(Else I permanantly change their names to letters and number!)

In the future, I would like to expand it as such:

1-Combo Box - with (21 Teams)
2- Enter the Team Members Number
3- Autofill the Team Members Name that corresponds to the Team Name and Team Number.

Thank you.

[mattj]

The easiest way would be to have the team numbers in a combo box, where you could include the other info (such as player name) in extra columns. The you could set the control source of text boxes to:

=Forms!YourForm!yourComboBox.Column(n) where "n" is the column numnber in the combo box that contains the info you wish to display.

For your "in the future" request, do a search for cascading combo boxes.

HTH
Matt

[DarkSprout]

I've Fixed your problem, see _Answer.zip

Changed TextBoxes to Combos x2 - in form:= “Team Scores”
Added SQL RowSource to New Combos

Changed Table:= “Team Members Name” structure (auto number field)

Added VBA in ‘Team_Number_AfterUpdate’
Will display User, if only one user is within the Team Group:=

Private Sub Team_Number_AfterUpdate()
Me.[Member Name].Requery
If Me.[Member Name].ListCount = 1 Then
Me.[Member Name] = Val(DLookup("[anMemberID]", "Team Members Name", "[Team Number] = " & [Team Number]))
End If
End Sub


Other...
Please remove all spaces from Field Names,
And start using Prefixes for field descriptions:
n = Number
an = AutoNumber
txt = TextField
dte = Date
obj = Object
ie. nTeamNumber; txtTeamName

Please, let me know how you got on.

=DarkSprout=