ComboBox_GetFocus

[Jez]

I have a sub form on my form and on that there is a ComboBox, when the
ComboBox is activated the GotFocus is applied, I am trying to be able to have
the options to choose in my ComboxBox selectable.

Am not sure I have explained it very well, but here is my code, any
questions please ask and will try explain

Private Sub cboResponse_GotFocus()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'On Error GoTo Err
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
intResponseID = Me.Recordset
'intResponseID = Me.ResponseID
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
With Me.cboResponse
.RowSource = _
"SELECT tblCSATResponseType.ResponseItem " & _
"FROM tblCSATResponseType " & _
"WHERE tblCSATResponseType.ResponseID = " & intResponseID
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic
End With
End Sub

As on this code I have intResponseID = Me.Recordset then when the main form
opens it shows the record in one line, I dont understand why, because this
section is a set of questions and there are 5 in the table.

If I use this line which I thought might work intResponseID = Me.ResponseID
I get an error on load of main form.

Jez

 

[Paolo]

Is tblCSATResponseType.ResponseID an integer?
In this case you can't compare it with a recordset as you do in your where
condition.
If you want to use the integer value uf then recordset you have to do that:

intResponseID = Me.'name of the value in the recordset you want to filter
your combo with'

if 'name of the value in the recordset you want to filter your combo with'
is in the recordset source of your form.

after setting the where of your combo I suggest to put a combo.requery so it
canfetch the new data.

Cheers

 

[Jez]

Paolo,
Yes tblCSATResponseType.ResponseID is an integer, do I need to declare that
anywhere?
So what your saying is that as the value is an integer then I need to use
intResponseID = Me.ResponseID?

Also you mean put cboResponse.requery after the line
WHERE tblCSATResponseType.ResponseID = " & intResponseID
and before the rs.cursor line?

Jez

 

[Paolo]

Let's me understand:
You have a form with the combo box right? From where you take the value to
filter the combo?
Me.recordset refer to what?

 

[Jez]

Basically on this sub form are 3 textboxes, and ComboBox. What I am trying to
get it to do is go to the table where the questions are and input each
question like this
QuestionNumber QuestionText Response ResponseValue.
1 How was your flight Excellent,Good,Bad 1000,500,250
2 etc

So I want to be able to select the Excellent, Good and so on, from this I
want the Response to be given a value so Excellent = 1000 and so on. In this
the Excellent, Good and so on have all been given an ID, in this case its 2

 

[Paolo]

Ok,
I would do in this way:
First I would fetch the question from the question table.
If the value for the combo are the 3 you said you can make a combo with the
first column containing the id of the answer, the second column the text of
the answer and the third column the value of the answer. first col width 0 so
isn't shown, second col width of the combo box and the third 0 again.
To populate your combo with your value:

combo.rowsource="1;Excellent;1000;2;Good;500;3;Bad;250"

On the after update of the combo you can save the value of the answer
choosen in the combo where you prefer. e.g. valuechoosen=combobox.column(2).
If you bound the combo to a field of your question table (e.g. givenanswer)
when you choose a value from the combo this value is saved in the field.
Have I answered your question?
Paolo

 

[Jez]

I currently have that option on...
Private Sub cboResponse_Change()
Me.txtResponseValue = Me.cboResponse.ListIndex
End Sub

I tried your previous thread about putting cboResponse.Requery after the
WHERE statement, but it still doesnt like this line

intResponseID = Me.ResponseID

I have declared intResponseID as a Public varaible and as a Integer, but for
some reason it still doesnt like it.

I cant see where I am going wrong


Private Sub cboResponse_GotFocus()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
intResponseID = Me.ResponseID
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
With Me.cboResponse
.RowSource = _
"SELECT tblCSATResponseType.ResponseItem " & _
"FROM tblCSATResponseType " & _
"WHERE tblCSATResponseType.ResponseID = " & intResponseID
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic
cboResponse.Requery
End With
End Sub

Private Sub cboResponse_Change()
Me.txtResponseValue = Me.cboResponse.ListIndex
End Sub

 

[Paolo]

Try to modify your select in this way this
"SELECT tblCSATResponseType.ResponseItem,
tblCSATResponseType.ResponseID " & _
"FROM tblCSATResponseType " & _
"WHERE tblCSATResponseType.ResponseID = " & intResponseID

 

[Jez]

I have this code below, and am having trouble with being able to choose the
options within the combobox. It keeps telling me that I cant assign a value
to this objects. Can someone point out where I am going wrong...

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATQuestions.QuestionID, tblCSATQuestions.QuestionNo, " &
vbCrLf & _
"tblCSATQuestions.QuestionText, tblCSATQuestions.ResponseID, " &
vbCrLf & _
"tblCSATResponse.Response, tblCSATResponse.CSATNumber, " & vbCrLf & _
"tblCSATResponse.JobNumber, tblCSATResponse.ResponseValue " & vbCrLf
& _
"FROM tblCSATQuestions " & vbCrLf & _
"INNER JOIN tblCSATResponse ON tblCSATQuestions.QuestionID =
tblCSATResponse.QuestionID "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic
rs.MoveFirst
Me![txtQuestionNo] = IIf(IsNull(rs![QuestionNo]), nullvalue,
rs![QuestionNo])
Me![txtQuestionText] = IIf(IsNull(rs![QuestionText]), nullvalue,
rs![QuestionText])
Me![cboResponse] = IIf(IsNull(rs![Response]), nullvalue,
rs![Response])
Me![txtResponseValue] = IIf(IsNull(rs![ResponseValue]), nullvalue,
rs![ResponseValue])
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub

Private Sub cboResponse_GotFocus()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
'intResponseID = Me.ResponseID
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
With Me.cboResponse
.RowSource = _
"SELECT tblCSATResponseType.ResponseItem, " & _
"tblCSATResponseType.ResponseID " & _
"FROM tblCSATResponseType " & _
"WHERE tblCSATResponseType.ResponseID = " & intResponseID
rs.CursorLocation = adUseClient
rs.Open .RowSource, cnn, adOpenForwardOnly, adLockReadOnly
cboResponse.Requery
End With
End Sub

Private Sub cboResponse_Change()
Me.txtResponseValue = Me.cboResponse.ListIndex
End Sub

 

[Jez]

Paolo,

I thought I would try this another way round. What I have done is the tables
in my back end DB that are just reference tables are all loaded into the
front end DB on Database_Open().

So instead of trying to be complicated on the ComboBox_GetFocus() it uses a
table that is in the local database.

Private Sub cboResponse_GotFocus()
Dim intResponseID As Integer
intResponseID = Me.RecordSource
With Me.cboResponse
.RowSource = _
"SELECT tblCSATResponseType.ResponseItem, " & _
"tblCSATResponseType.ResponseID " & _
"FROM tblCSATResponseType " & _
"WHERE tblCSATResponseType.ResponseID = " & intResponseID
End With
End Sub

But I still have a problem as the line intResponseID = Me.RecordSource
doesnt seem to work no matter what I input here, I have tried recordset,
recordtype and dont get any joy.
When I run the form and choose a drop down there are no values to choose
from and cant find out why.

Where am I going wrong?

Jez