Updating Combo box if Table is in SQL Backend

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have SQL 2000 back end and Access 2003 front. I am able to update the tbl
via the Combo Box if it's created in Access with primary key as Autonumber
see code below. I cannot update the Combo Box if the tbl is created in SQL.
What code must I include to achieve this ?

Private Sub Competitor_NotInList(NewData As String, Response As Integer)

Response = acDataErrContinue
'Propmpt user to verify if they wish to add new value
If MsgBox("Competitor" & NewData & " is not in list. Add it ?", vbYesNo) =
vbYes Then

Dim db As Database
Dim rstCompetitor As Recordset
Dim sqlCompetition As String

Set db = CurrentDb()
sqlCompetiton = "Select * from Competition"
Set rstCompetitor = db.OpenRecordset(sqlCompetition, dbOpenDynaset)
rstCompetitor.AddNew
rstCompetitor![Competitor] = NewData
rstCompetitor.Update
Response = acDataErrAdded
rstCompetitor.Close
End If
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200606/1
.

Relevant Pages

  • Update Table via Combo Box
    ... I cannot update the Combo Box if the tbl is created in SQL. ... Response As Integer) ... Dim rstCompetitor As Recordset ...
    (microsoft.public.access.gettingstarted)
  • Updating Combo box if Table is in SQL Backend
    ... I want to update the Vendor tbl with a new entry if it is not already present ... code below) but it does not update the tbl if I move it onto the SQL back end. ... Dim rstVendor As Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Way to test if transactions are working?
    ... On Error GoTo Err_cmdCommit_Click ... Dim ws As DAO.Workspace ... Dim sql As String ... Dim Response As String ...
    (comp.databases.ms-access)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)