Setting the value of the combobox 1

[Junkie88]

i have a form based on a query. one of the controls, combo box, on the form is not bound to any field of the query. however, initially when the record on the form is changed i want to set the value of the combo box to one of the text boxes that is on the form. but i am unable to do so. can anyone tell me why this is so?

If Me.Recordset.RecordCount = Me.CurrentRecord Then
DoCmd.Close acForm, "frmQuestions", acSaveNo
Else
DoCmd.GoToRecord , , acNext
Me!ComboAnswer.Value = txtAnswer.Value
End If

 

[boblarson]

Are you sure that the combo has that value? Also, does your combo actually have an ID field that is the "bound" field and another field which is displaying the value. If so, you need to set the value of the combo to the ID value, not the value displayed.

Bob Larson, Access MVP

http://www.btabdevelopment.com

 

[Junkie88]

yes, my combo has an ID field that is the bound field and another field which is displaying the value. i don't understand what you are trying to say, but i also tried to set the value of the combo box using the following method.

Me!ComboAnswer.Column(1) = txtAnswer.Value

but that did not work either. can you explain more what you are saying?

 

[boblarson]

What I'm saying is you need to find out what the value associated with txtAnswer is and set the combo to that.

So, if you have data like this:

ID Description
1 MyField1
2 MyField2
3 MyField3


And you want to set your combo to display MyField2 which is what is entered in the text box, then you need to set the value by

Me.ComboAnswer = DLookup("ID", "YourTableOrQueryName", "[Description]='" & Me.txtAnswer & "'")

Hope that helps.

Bob Larson, Access MVP

http://www.btabdevelopment.com

 

[Junkie88]

I have a table with the following fields

Id, Option, Score

and i want to set the value list of the combo box using certain id. i am not able to do it successfully.

Forms!frmQuestions!ComboAnswer.RowSource = "SELECT Score, Option FROM tblLikertScale WHERE Id = " & idx & ";"

what am i doing wrong?

 

[boblarson]

Well, for one I would rename any field in your table that is currently using an Access Reserved Word. Option is an Access Reserved Word, so I would rename that.

Then, if ID is a number (and if the code is on the actual form in use) it would be:

Me.ComboAnswer.RowSource = "SELECT Score, MyOption FROM tblLikertScale WHERE Id =" & Me!idx & ";"

Or if Text

Me.ComboAnswer.RowSource = "SELECT Score, MyOption FROM tblLikertScale WHERE Id ='" & Me!idx & "';"


Bob Larson, Access MVP

http://www.btabdevelopment.com

 

[Junkie88]

id is a number and i made the changes that you suggested but still not working. instead i see in the value list the following:

MyOption FROM tblLikertScale WHERE Id = 1

the first part of the SQL statement has disappeared for some reason.

 

[boblarson]

Actually, you don't need the semi-colon at the end:

Me.ComboAnswer.RowSource = "SELECT Score, MyOption FROM tblLikertScale WHERE Id =" & Me!idx

And I used MyOption but you should use the actual name of the field you renamed Option to. If you actually did use MyOption then that would be okay. The other thing question is - is any part of this on a subform?

Bob Larson, Access MVP

http://www.btabdevelopment.com

 

[Junkie88]

yes, i am now calling that field MyOption.

Nothing is on a subform. all the fields of the form are based on a query which extracts fields from different tables.

I removed the semicolon but still no luck.

 

[boblarson]

Is your combo's rowsource TYPE set to Table/Query and not Value List? If using the rowsource this way, it should be set to Table/Query.

Bob Larson, Access MVP

http://www.btabdevelopment.com

 

[Junkie88]

thank you. that worked.

but coming back to the first problem

Me!ComboAnswer = DLookup("Score", "tblLikertScale", "[Description]='" & Me!txtAnswer & "'")

is not working.

 

[Junkie88]

okay, never mind...

i figured that out

thank you so much for your help tonight

 

[boblarson]

Well, if you only have the fields

ID, MyOption, and Score then you don't have a description field and would need:

Me.ComboAnswer = DLookup("[ID]", "tblLikertScale", "[Score]=" & Me.txtAnswer)

OR, if Score is a text field:

Me.ComboAnswer = DLookup("[ID]", "tblLikertScale", "[Score]='" & Me.txtAnswer & "'")


Bob Larson, Access MVP

http://www.btabdevelopment.com