code conflicting

[so10070]

[FONT=&quot]I first want to copy a table and then to amend it. This is the code.[/FONT]

  [FONT=&quot]DoCmd.CopyObject , "tblLijstProducten_Kopie_" & Now(), acTable, "tblLijstProducten"[/FONT]
  [FONT=&quot]           [/FONT]
  [FONT=&quot]    sqlUpdate = "UPDATE tblLijstProducten SET tblLijstProducten.ProductActief = False;"[/FONT]
  [FONT=&quot]    DoCmd.RunSQL sqlUpdate[/FONT]

However, when running it, in the copied table the field [Product Active] is also changed to “False”. How can I prevent this? Is the first code line not ended before starting the new and how do I prevent this?

 

[JHB]

Is the first code line not ended before starting the new and how do I prevent this?

If you think that is the reason, then place a DoEvents between the 2 code lines.

 

[so10070]

It doesn't work with DoEvents? Can anyone say me what's the problem?

 

[JHB]

Post your database with some sample data, zip it.

 

[arnelgp]

try something with a Loop:

dim lngSourceRecordCount As Long
dim strTarget As String
' get how many records to copy
strTarget = "tblLijstProducten_Kopie_" & Now()
lngSourceRecordCount = DCount("*","tblLijstProducten")
DoCmd.CopyObject , strTarget, acTable, "tblLijstProducten"

'loop till the record count is same as source table
while nz(dcount("*",strTarget), 0) <> lngSourceRecordCount
wend

sqlUpdate = "UPDATE tblLijstProducten SET tblLijstProducten.ProductActief = False;"
DoCmd.RunSQL sqlUpdate

 

[so10070]

It's sad, but this also doesn't work!

 

[JHB]

Couldn't you post your database with some sample data, (zip it because you haven't post 10 post yet)?

 

[so10070]

In the database I have made which I want to post, the procedure runs without any problem (see enclosed file). But in my application it doesn't run properly. Why? I can't figure out why!

 

[JHB]

Put in a breakpoint in the codeline and step through the code to see when it change to false:

  [FONT=&quot]DoCmd.CopyObject , "tblLijstProducten_Kopie_" & Now(), acTable, "tblLijstProducten"[/FONT]

 

[arnelgp]

it's working alright. why don't you compact and repair your application.

 

[so10070]

Problem solved. With this command I created a linked table (Back office).

  DoCmd.CopyObject , "tblLijstProducten_Kopie_" & Now(), acTable, "tblLijstProducten"

I change this command into

strKopieTabel = "SELECT tblLijstProducten.* INTO tblLijstenProducten_Kopie FROM tblLijstProducten;"

and create a local table.
That's why the enclosed file perfectly worked!