set combobox to display newly added record

claudiak · #1 (**permalink**) 11-15-12, 06:44

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.

Sinndho · #2 (**permalink**) 11-15-12, 07:16

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

claudiak · #3 (**permalink**) 11-15-12, 07:22

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.

claudiak · #4 (**permalink**) 11-15-12, 07:54

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

Sinndho · #5 (**permalink**) 11-15-12, 13:17

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

claudiak · #6 (**permalink**) 11-16-12, 10:07

Thank you very much, that's really helpful!

Sinndho · #7 (**permalink**) 11-16-12, 10:23

You're welcome!

claudiak · #8 (**permalink**) 11-23-12, 10:52

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

Sinndho · #9 (**permalink**) 11-23-12, 17:00

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.

claudiak · #10 (**permalink**) 12-21-12, 07:01

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

Internet Services

Web Development

Digital Marketing

Consumer Tech