CODEWITHSUNDEEP
ms-access
dani jinji
dani jinji

Reputation: 471

Access - input value dependes on a select box

I have a form in access with several fields. now, lets say a I have a table called TEAMS, which contains team's size, name and id. now, I have a form with a select box, which shows the teams names, and under the select box I want to have a text field which shows the team's size of each team, dependes what you choosed on the select box.

for example: I you select team A, with 10 members, you'll se 10 in the text field, if you changed it to team B, with 8 members now the text field will show you 8.

If I had to do it with PHP and SQL I would do something like: SELECT team_size FROM TEAMS WHERE team_id = my_form.team_select_box

and then print those results with JS on the html form... but I am new to ms access and I have no idea how to it...

any help please?

Upvotes: 1

Views: 30

Answers (1)

timbmg
timbmg

Reputation: 3328

You can add an AfterUpdate() event to you drop down. Your dropdown needs to have two columns. One for the ID and one for the team name. Within the AfterUpdate event you can execute your sql to identify the team size as you described in your question. Then set the value of the text box to the result.

This code will work for an AfterUpdate procedure in form with a dropdown called scltTeam and a text box called txtSize.

Private Sub slctTeam_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim id As Integer

    id = Me!slctTeam.Value
    strSQL = "SELECT * FROM Team WHERE ID = " & id
    Set rst = CurrentDb.OpenRecordset(strSQL)

    Me!txtSize.Value = rst!Size
End Sub

Upvotes: 1

Related Questions