#deleted 1

[ZOR]

I am clearibg the contents of a table having a form bound to it. How do I get rid of #deleted appearing in the textboxes?

The code i use is:

Set db = DBEngine(0)(0)
strSql = "DELETE FROM Transfer;"
db.Execute strSql, dbFailOnError

On requerying the form #deleted appears. Thanks

 

[fneily]

I just added one line and it works fine.

Set db = DBEngine(0)(0)
strSql = "DELETE FROM [employee_copy_test];"
db.Execute strSql, dbFailOnError
Me.Requery

 

[ZOR]

Many thanks.
We both have the same code, I just used words saying I requeryed my form, si its a mystery. I am copying a linked parent tables contents into a temporary table, and after deleting it I get the #deleted word in all fields. I will just have to waste more time trying to find whats wrong. Regards

 

[fneily]

I get the #deleted when I DON'T requery and move back through the records. Are you sure you're not doing something else before the requery?

 

[ZOR]

Thanks again.

On the onopen event of the form I have this:

Private Sub Form_Open(Cancel As Integer)
Dim stDocName As String
Dim db As DAO.Database
Dim strSql As String

Set db = DBEngine(0)(0)
strSql = "DELETE FROM Transfer;"
db.Execute strSql, dbFailOnError

DoEvents


End Sub

I have a button on the form that passes the table content into a temporary table "Transfer"

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim stDocName As String
Dim db As DAO.Database
Dim strSql As String

Set db = DBEngine(0)(0)
strSql = "DELETE FROM Transfer;"
db.Execute strSql, dbFailOnError

DoCmd.SetWarnings False

stDocName = "TranferTXmaster"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.SetWarnings True
Forms!Form3!SubformCompilations.Requery


Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub


The transfer code refers to a query:

INSERT INTO TRANSFER ( Barcode, TXID, SeriesName, EpisodeTitle, Typeofmaterial, TapeStandard, Noofparts, Audio1, Audio2, Audio3, Audio4, Subtitles, SportorSports, Competition, Stageofcompetition, EventDate, Venue, ContractName, ClipRights, PromoRights, LicenceStart, LicenceEnd, Territories, AdditionalInformation )
SELECT TXMASTERS.Barcode, TXMASTERS.TXID, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXMASTERS.Typeofmaterial, TXMASTERS.TapeStandard, TXMASTERS.Noofparts, TXMASTERS.Audio1, TXMASTERS.Audio2, TXMASTERS.Audio3, TXMASTERS.Audio4, TXMASTERS.Subtitles, TXMASTERS.SportorSports, TXMASTERS.Competition, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.ContractName, TXMASTERS.ClipRights, TXMASTERS.PromoRights, TXMASTERS.LicenceStart, TXMASTERS.LicenceEnd, TXMASTERS.Territories, TXMASTERS.AdditionalInformation
FROM TXMASTERS
WHERE (((TXMASTERS.ID1)=[Forms]![Form3].[Form].[ID1].[Caption]));

And thats it, Regards

 

[fneily]

Get rid of the On Open event. You're deleting the table so when the form opens it rightly says the info is deleted. Also, you delete it again in you command button code. So you don't need the On Open code.

 

[ZOR]

Thanks. The reason for the onopen clear of the table event was to cover for a user transferring a record and then closing the form. The record would still be there when opened, however maybe I could delete the record on form close if it still was there on close. The rest of the forms procedure would eventually clear the record. I think you have a good suggestion, let you know.

 

[ZOR]

Yes, that does it. Many thanks, have a star. Regards