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
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