Updating Combo box if Table is in SQL Backend

  • Thread starter Thread starter dunkster via AccessMonster.com
  • Start date Start date
D

dunkster via AccessMonster.com

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
 
Hum, this looks more to me to be a MDB file with linked tables (other
newsgroup) than an ADP project (this newsgroup); so the following might be
totally wrong:

With SQL-Server, I think that you must the parameter dbSeeChanges. In this
case, using dbAppendOnly and making sure that no record are retrieving would
also be good ideas:

sqlCompetiton = "Select * from Competition WHERE 1 = 0"
Set rstCompetitor = db.OpenRecordset(sqlCompetition, dbOpenDynaset,
dbSeeChanges or dbAppendOnly)

Finally, it's possible that the primary key has not be been created for the
table on SQL-Server or that the identity (or autoincrement in SQL parlor)
property has not been set for this field.
 
dunkster via AccessMonster.com said:
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
Click to expand...
 
Back
Top