Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2012
    Posts
    10

    set combobox to display newly added record

    my set-up is an Access front end linking to an sql server instance. I am trying to work out how to set the value of a combobox to the newly added record in the rowsource table. users will do this if the ethnicity they want to select is not in the rowsource of the combobox. here is the code I have so far:

    Code:
    Private Sub btnAddEthnicityOther_Click()
    Dim strCat As String
    Dim strCatSet As String
    Dim rst As Recordset
    Dim i As Integer
    
    
      If MsgBox("Do you want to add a new Other category to the Ethnicity Other drop down list?", vbYesNo + vbInformation) _
            = vbYes Then
             strCat = InputBox("Enter the new ethnicity name.")
             strCatSet = strCat
            If strCat <> "" Then
                   Set rst = CurrentDb.OpenRecordset("dbo_tbllEthnicityOther", dbOpenDynaset, dbSeeChanges)
                    rst.AddNew
                    rst!EthOther = strCat
                    rst.Update
                    Set rst = Nothing
            End If
            
        i = "SELECT dbo_tbllEthnicityOther.EthOtherID FROM dbo_tbllEthnicityOther WHERE dbo_tbllEthnicityOther.EthOther like (" ' & strCatSet & '") "
        Me.cmbEthnicityOtherType.Requery
        Me.cmbEthnicityOtherType = Nz(i, 200)
    End If
    End Sub
    the ethnicity other table has one identity column and a text column. I get a type mismatch on the i="sql statement" line. I'm cleary am missing something basic about passing sql results to a variable. Any help very much appriciated. Thanx.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,251
    You cannot assign a string (which "SELECT dbo_tbllEthnicityOther.EthOtherID FROM..." obviously is) to a variable you declared as an Integer:
    Code:
    Dim i As Integer
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    10
    Thank you for the reply. I thought it would be something like this, but how do I get the result of the sql and pass it on to the integer? do I do something like this?

    dim strSQL as string

    then after the new value was added to the table

    Code:
    docmd****nsql
    but then what? I know this is basic, but I just can't seem to work this out.

  4. #4
    Join Date
    Nov 2012
    Posts
    10

    Semi-solved

    I found a way which will do:
    Code:
    Dim strCat As String
    Dim strCatSet As String
    Dim rst As Recordset
    
    
      If MsgBox("Do you want to add a new Other category to the Ethnicity Other drop down list?", vbYesNo + vbInformation) _
            = vbYes Then
             strCat = InputBox("Enter the new ethnicity name.")
             strCatSet = strCat
            If strCat <> "" Then
                   Set rst = CurrentDb.OpenRecordset("dbo_tbllEthnicityOther", dbOpenDynaset, dbSeeChanges)
                    rst.AddNew
                    rst!EthOther = strCat
                    rst.Update
                    Set rst = Nothing
            End If
            
    
        Me.cmbEthnicityOtherType.Requery
        Me.cmbEthnicityOtherType.RowSource = "SELECT dbo_tbllEthnicityOther.EthOtherID, dbo_tbllEthnicityOther.EthOther FROM dbo_tbllEthnicityOther WHERE dbo_tbllEthnicityOther.EthOther like ('" & strCat & "'); "
        Forms!frmMainCase!sfrmChildren.Form!cmbChildEthnicityOtherType.Requery
    changes the rowsource of the combo to the new value, and in the after update of the field I change it back to all records:

    Code:
    Private Sub cmbEthnicityOtherType_AfterUpdate()
        Me.cmbEthnicityOtherType.RowSource = "SELECT dbo_tbllEthnicityOther.EthOtherID, dbo_tbllEthnicityOther.EthOther FROM dbo_tbllEthnicityOther; "
        Me.cmbEthnicityOtherType.Requery
    
    End Sub

  5. #5
    Join Date
    Mar 2009
    Posts
    5,251
    When you assemble a string containing a SQL statement (the technique is usually called Dynamic SQL or Dynamic Query), according to it's purpose you can:

    1. Have it executed (action query):
    Code:
    Sub ClearTable(ByVal TableName As String)
    
        Const c_SQL As String = "DELETE FROM @D;"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@D", TableName)
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Sub
    2. Create a new query or modify an existing one:
    Code:
    Sub QuerySQL(ByVal QueryName As String)
    
        Const c_SQL As String = "SELECT * FROM MyTable WHERE CustomerID = '@C';"
        
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@C", Me.ComboCustomers.Value)
        Set dbs = CurrentDb
    '
    ' Existing Query.
    '
        Set qdf = dbs.QueryDefs(QueryName)
        qdf.SQL = strSQL
    '
    ' New Query
    '
        Set qdf = dbs.CreateQueryDef(QueryName)
        qdf.SQL = strSQL
        
    End Sub
    3. Assign it to the RecordSource (for a Form or a Report) or the RowSource (for a ListBox or a ComboBox) of a data object:
    Code:
    Sub SourceSQL()
    
        Const c_SQL As String = "SELECT * FROM MyTable WHERE CustomerID = '@C';"
        
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@C", Me.ComboCustomers.Value)
    '
    ' To a subform.
    '
        Me.SubForm1.Form.RecordSource = strSQL
    '
    ' To a ListBox.
    '
        Me.List1.RowSource = strSQL
        
    End Sub
    4. Open a RecordSet:
    Code:
    Sub RecordsetSQL()
    
        Const c_SQL As String = "SELECT * FROM MyTable WHERE CustomerID = '@C';"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@C", Me.ComboCustomers.Value)
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        With rst
            Do Until .EOF
                ' Do something with the rows of data...
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    5. Use it to send a command to a SQL Server (pass-through query):
    Code:
    Sub PassThroughSQL()
    
        Const c_SQL As String = "ALTER TABLE [Tbl_Channels] ADD CONSTRAINT [DF_Tbl_Channels_Inactive] DEFAULT ((0)) FOR [Inactive];"
        Const c_Cnn As String = "ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
        
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
    '
    ' Create a (temporaty) Pass-Through Query.
    '
        Set qdf = dbs.CreateQueryDef("")
        With qdf
            .Connect = c_Cnn
            .SQL = c_SQL
            .Execute
        End With
        Set qdf = Nothing
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Nov 2012
    Posts
    10

    Thumbs up

    Thank you very much, that's really helpful!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,251
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Nov 2012
    Posts
    10

    Solved!

    Hi - n light of the lovely summary of things that can be done with sql statements in VBA, I have revised my code, and got it to do what I want.

    Code:
    Private Sub btnAddEthnicityOther_Click()
    Dim strCat As String
    Dim rst As Recordset
    Dim rstSetcmb As DAO.Recordset
    Dim setcmb As Long
    
    
      If MsgBox("Do you want to add a new Other category to the Ethnicity Other drop down list?", vbYesNo + vbInformation) _
            = vbYes Then
             strCat = InputBox("Enter the new ethnicity name.")
    
            If strCat <> "" Then
                   Set rst = CurrentDb.OpenRecordset("dbo_tbllEthnicityOther", dbOpenDynaset, dbSeeChanges)
                    rst.AddNew
                    rst!EthOther = strCat
                    rst.Update
                    Set rst = Nothing
            End If
            
    
        Me.cmbEthnicityOtherType.Requery
        
        Set rstSetcmb = CurrentDb.OpenRecordset("SELECT dbo_tbllEthnicityOther.EthOtherID FROM dbo_tbllEthnicityOther WHERE dbo_tbllEthnicityOther.EthOther like ('" & strCat & "'); ", dbOpenDynaset, dbSeeChanges)
            setcmb = rstSetcmb!EthOtherID
            Me.cmbEthnicityOtherType = setcmb
            rstSetcmb.Close
        Set rstSetcmb = Nothing
        Forms!frmMainCase!sfrmPeople.Form!cmbEthnicityOtherType.Requery
    
    End If
    End Sub

  9. #9
    Join Date
    Mar 2009
    Posts
    5,251
    You could also use:
    Code:
        dim strSQL As String
    
        If MsgBox("Do you want to add a new Other category to the Ethnicity Other drop down list?", vbYesNo + vbInformation) _
            = vbYes Then
             strCat = InputBox("Enter the new ethnicity name.")
            If strCat <> "" Then
                strSQL = "INSERT INTO dbo_tbllEthnicityOther (EthOther) VALUES ('" & strCat & "');"
                CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
            End If
    Note: You need to use the parameter dbSeeChanges because dbo_tbllEthnicityOther seems to be SQL Server attached table with an Identity column.
    Have a nice day!

  10. #10
    Join Date
    Nov 2012
    Posts
    10
    mmhh, so is it typically preferable, because less resource intensive, to use insert statments, if there is only one row to be inserted?

    Am also wondering if where exactly the sql embedded in vba gets executed - server side or client side? would it be better the create a stored procedure doing the data manipulation and call it it from vba?
    C

Posting Permissions

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