Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: ComboBox and GetFocus

    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.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    From your last comment, it seems to me that the sub cboResponse_GotFocus() fires even before you can see the form. Obviously the combobox gets the focus while the form is still loading. That being the case, VBA is trying to retrieve values from controls that do not yet contain those values. I would change the sub from the GotFocus event to the Click event so you can control when VBA retrieves the values.

    Another thing I don't understand, is that you didn't define the variable intResponseID, although maybe it's a global variable. In any case, what type of variable is it, Recordset, Integer, etc.? To say intResponseID = Me.Recordset requires a Recordset variable, but to say intResponseID = Me.ResponseID requires an entirely different one.

    Sam

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Another thing I don't understand, is that you didn't define the variable intResponseID, although maybe it's a global variable. In any case, what type of variable is it, Recordset, Integer, etc.? To say intResponseID = Me.Recordset requires a Recordset variable, but to say intResponseID = Me.ResponseID requires an entirely different one.

    On my original Recordset Variable the intResponseID= me.ResponseID was from the last line in my SQL WHERE tblCSATResponseType.ResponseID = " & intResponseID

    So not using that Recordset, if I use the intResponseID= rs then I assume I need to delare intResponseID as Integer?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I would assume so. However, that's not the entire problem, as I explained before.

    Sam

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Sam has pointed out the main problems, and, as he says there are others.

    Correct me if I'm wrong but I think we have had this conversation over a week ago on another forum (different question), ie. you seem to have opened a recordset but don't use it!?

    Could you just say again where you are gettin the value of intResponseID = ???? from!

    Is a (bound?) control on the form?

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •