If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > set combobox to display newly added record

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,175
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,175
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!
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 10
Thumbs up

Thank you very much, that's really helpful!
Reply With Quote
  #7 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,175
You're welcome!
__________________
Have a nice day!
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,175
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!
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On