DBSeeCHanges

S

Synergy

Hello I am having a problem editing new records which are inserted from
forms. The GotoRecord method has worked in the past, but now there is a
problem. I think it may have to do with changing the primary key, but am
not sure. The initial primary key was on two field, but now on an Identity
Insert field. Neither of the original fields are indexed, but I get errors
sometimes indicating a duplicate index is the problem. This problem showed
up after months when I refreshed the (SQL Server) table links.

One form uses this method to insert and then I cannot edit: The record gets
inserted properly.

Dim i As Integer
i = Me.RecordsetClone.RecordCount + 1
Dim strSql As String
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber ) " & _
"SELECT " & [Custid] & " AS Custid, " & i & " AS cpnumber; "

CurrentDb.Execute strSql, dbSeeChanges

Me.Requery


This method in another form allows editing after the record is inserted:

'Add new Press
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber, cpManuf,
cpmanumodel, cpspecsig, ccenterline, pressremarks, cppreadh, cpgps, cpbcc,
cppc, cphcc, cpccc, cpremarks1, cpremarks2, " & _
"dgripper, d1stknife, dleft, dright, dback, dsizex, dsizey, dcoat,
dchaseholes, dtnut, dstriprule, dbalance, drubber, dnick, dieremarks1,
dieremarks2, fsmountholes, fsgripper, fsrout, " & _
"fsbreakers, mstnuts, msgripper, msheight, msfoam, sremarks1,
sremarks2, fbfinnished, fbbars, fbsingle, fbsplit, fbdouble, mbfinnished,
mbpushers, mbtnuts, mbrubber, blremarks1, blremarks2, EntryDate,
TestRecord ) " & _
"SELECT " & lngCustID & " AS custID, " & CPNumberNew & " AS
cpnumber, cpManuf, cpmanumodel, cpspecsig, ccenterline, pressremarks, "
& _
" cppreadh, cpgps, cpbcc, cppc, cphcc, cpccc, cpremarks1, " &
_
" cpremarks2, dgripper, d1stknife, dleft, dright, dback,
dsizex, " & _
" dsizey, dcoat, dchaseholes, dtnut, dstriprule, dbalance,
drubber, " & _
" dnick, dieremarks1, dieremarks2, fsmountholes, fsgripper,
fsrout, fsbreakers, " & _
" mstnuts, msgripper, msheight, msfoam, sremarks1, sremarks2,
fbfinnished, " & _
" fbbars, fbsingle, fbsplit, fbdouble, mbfinnished, mbpushers,
mbtnuts, mbrubber, blremarks1, blremarks2, #" & setEntryTime & "# AS
EntryDate, " & bolTestRecord & " AS TestRecord " & _
"FROM [Customer ST Presses] " & _
"WHERE (((cpid)=" & CPIDsource & "));"

'Debug.Print "Press- " & strSQL

CurrentDb.Execute strSql, dbSeeChanges


Thanks for any help. The back end is SQL server. When I created a local
table from the data, I had no problems.

God Blesss,


Mark A. Sam
 
S

Synergy

The problem seems to be, simply, that I can't edit a record that has been
added through a form or by code, using any insert method.

God Bless,

Mark A. Sam


Synergy said:
Hello I am having a problem editing new records which are inserted from
forms. The GotoRecord method has worked in the past, but now there is a
problem. I think it may have to do with changing the primary key, but am
not sure. The initial primary key was on two field, but now on an
Identity Insert field. Neither of the original fields are indexed, but I
get errors sometimes indicating a duplicate index is the problem. This
problem showed up after months when I refreshed the (SQL Server) table
links.

One form uses this method to insert and then I cannot edit: The record
gets inserted properly.

Dim i As Integer
i = Me.RecordsetClone.RecordCount + 1
Dim strSql As String
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber ) " & _
"SELECT " & [Custid] & " AS Custid, " & i & " AS cpnumber; "

CurrentDb.Execute strSql, dbSeeChanges

Me.Requery


This method in another form allows editing after the record is inserted:

'Add new Press
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber, cpManuf,
cpmanumodel, cpspecsig, ccenterline, pressremarks, cppreadh, cpgps, cpbcc,
cppc, cphcc, cpccc, cpremarks1, cpremarks2, " & _
"dgripper, d1stknife, dleft, dright, dback, dsizex, dsizey, dcoat,
dchaseholes, dtnut, dstriprule, dbalance, drubber, dnick, dieremarks1,
dieremarks2, fsmountholes, fsgripper, fsrout, " & _
"fsbreakers, mstnuts, msgripper, msheight, msfoam, sremarks1,
sremarks2, fbfinnished, fbbars, fbsingle, fbsplit, fbdouble, mbfinnished,
mbpushers, mbtnuts, mbrubber, blremarks1, blremarks2, EntryDate,
TestRecord ) " & _
"SELECT " & lngCustID & " AS custID, " & CPNumberNew & " AS
cpnumber, cpManuf, cpmanumodel, cpspecsig, ccenterline, pressremarks,
" & _
" cppreadh, cpgps, cpbcc, cppc, cphcc, cpccc, cpremarks1, "
& _
" cpremarks2, dgripper, d1stknife, dleft, dright, dback,
dsizex, " & _
" dsizey, dcoat, dchaseholes, dtnut, dstriprule, dbalance,
drubber, " & _
" dnick, dieremarks1, dieremarks2, fsmountholes, fsgripper,
fsrout, fsbreakers, " & _
" mstnuts, msgripper, msheight, msfoam, sremarks1, sremarks2,
fbfinnished, " & _
" fbbars, fbsingle, fbsplit, fbdouble, mbfinnished,
mbpushers, mbtnuts, mbrubber, blremarks1, blremarks2, #" & setEntryTime
& "# AS EntryDate, " & bolTestRecord & " AS TestRecord " & _
"FROM [Customer ST Presses] " & _
"WHERE (((cpid)=" & CPIDsource & "));"

'Debug.Print "Press- " & strSQL

CurrentDb.Execute strSql, dbSeeChanges


Thanks for any help. The back end is SQL server. When I created a local
table from the data, I had no problems.

God Blesss,


Mark A. Sam
 
S

Synergy

I found an article where someone set default value dfor bit fields, so I set
them all to 0 and it solved it. I can't figure out why this came on after 3
years.

God Bless,

Mark A. Sam


Synergy said:
Hello I am having a problem editing new records which are inserted from
forms. The GotoRecord method has worked in the past, but now there is a
problem. I think it may have to do with changing the primary key, but am
not sure. The initial primary key was on two field, but now on an
Identity Insert field. Neither of the original fields are indexed, but I
get errors sometimes indicating a duplicate index is the problem. This
problem showed up after months when I refreshed the (SQL Server) table
links.

One form uses this method to insert and then I cannot edit: The record
gets inserted properly.

Dim i As Integer
i = Me.RecordsetClone.RecordCount + 1
Dim strSql As String
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber ) " & _
"SELECT " & [Custid] & " AS Custid, " & i & " AS cpnumber; "

CurrentDb.Execute strSql, dbSeeChanges

Me.Requery


This method in another form allows editing after the record is inserted:

'Add new Press
strSql = "INSERT INTO [Customer ST Presses] ( Custid, cpnumber, cpManuf,
cpmanumodel, cpspecsig, ccenterline, pressremarks, cppreadh, cpgps, cpbcc,
cppc, cphcc, cpccc, cpremarks1, cpremarks2, " & _
"dgripper, d1stknife, dleft, dright, dback, dsizex, dsizey, dcoat,
dchaseholes, dtnut, dstriprule, dbalance, drubber, dnick, dieremarks1,
dieremarks2, fsmountholes, fsgripper, fsrout, " & _
"fsbreakers, mstnuts, msgripper, msheight, msfoam, sremarks1,
sremarks2, fbfinnished, fbbars, fbsingle, fbsplit, fbdouble, mbfinnished,
mbpushers, mbtnuts, mbrubber, blremarks1, blremarks2, EntryDate,
TestRecord ) " & _
"SELECT " & lngCustID & " AS custID, " & CPNumberNew & " AS
cpnumber, cpManuf, cpmanumodel, cpspecsig, ccenterline, pressremarks,
" & _
" cppreadh, cpgps, cpbcc, cppc, cphcc, cpccc, cpremarks1, "
& _
" cpremarks2, dgripper, d1stknife, dleft, dright, dback,
dsizex, " & _
" dsizey, dcoat, dchaseholes, dtnut, dstriprule, dbalance,
drubber, " & _
" dnick, dieremarks1, dieremarks2, fsmountholes, fsgripper,
fsrout, fsbreakers, " & _
" mstnuts, msgripper, msheight, msfoam, sremarks1, sremarks2,
fbfinnished, " & _
" fbbars, fbsingle, fbsplit, fbdouble, mbfinnished,
mbpushers, mbtnuts, mbrubber, blremarks1, blremarks2, #" & setEntryTime
& "# AS EntryDate, " & bolTestRecord & " AS TestRecord " & _
"FROM [Customer ST Presses] " & _
"WHERE (((cpid)=" & CPIDsource & "));"

'Debug.Print "Press- " & strSQL

CurrentDb.Execute strSql, dbSeeChanges


Thanks for any help. The back end is SQL server. When I created a local
table from the data, I had no problems.

God Blesss,


Mark A. Sam
 
Top