Access acting weird

[DomZ]

Access for some reason, when I delete the content of the Table TblPartsTracking, doesn't insert into or update anymore....

here's a sample of a code that i'm using to insert into:

Private Sub cmdOkWO_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim DelStr As String
Dim varItem As Variant
Dim strWhere As String

strSQL = "INSERT INTO TblWOTmp ( DrumsBoxes, CageNumber, Quantity, Weight, PartNumber, TagNumber ) " & _
"SELECT TblPartsTracking.DrumsBoxes, TblPartsTracking.CageNumber, TblPartsTracking.Quantity, TblPartsTracking.Weight, TblPartsTracking.PartNumber, TblPartsTracking.TagNumber " & _
"FROM TblParts INNER JOIN TblPartsTracking ON TblParts.PartNumber=TblPartsTracking.PartNumber "

strWhere = " WHERE "
For Each varItem In Me.lstParts.ItemsSelected
   strWhere = strWhere & "(" & "TblPartsTracking.PartNumber = '" & Me.lstParts.Column(1, varItem) & "'" & " AND " & " TblPartsTracking.DateIn = #" & Me.lstParts.Column(2, varItem) & "#" & " AND " & "TblPartsTracking.TrackID = " & Me.lstParts.Column(3, varItem) & "" & ")" & " OR "
Next

strWhere = Left(strWhere, Len(strWhere) - 4)
strSQL = strSQL & strWhere

DelStr = "DELETE * FROM TblWOTmp;"

If Me.lstParts.ItemsSelected.Count = 0 Then
MsgBox "Please Select At Least 1 Part Number"
lstParts.SetFocus
Else
CurrentDb.Execute DelStr
CurrentDb.Execute strSQL
Debug.Print "strSQL = " & strSQL
DoCmd.OpenReport "RptWorkOrder", acViewPreview
End If

ErrorHandlerExit:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
End Sub

Debug.Print shows the following...
However it doesn't insert into..

strSQL = INSERT INTO TblWOTmp ( DrumsBoxes, CageNumber, Quantity, Weight, PartNumber, TagNumber ) SELECT TblPartsTracking.DrumsBoxes, TblPartsTracking.CageNumber, TblPartsTracking.Quantity, TblPartsTracking.Weight, TblPartsTracking.PartNumber, TblPartsTracking.TagNumber FROM TblParts INNER JOIN TblPartsTracking ON TblParts.PartNumber=TblPartsTracking.PartNumber WHERE (TblPartsTracking.PartNumber = '1200 A' AND TblPartsTracking.DateIn = #01/08/2003# AND TblPartsTracking.TrackID = 29) OR (TblPartsTracking.PartNumber = '3000 A' AND TblPartsTracking.DateIn = #01/08/2003# AND TblPartsTracking.TrackID = 28) OR (TblPartsTracking.PartNumber = 'DC 700104100 ALU-P' AND TblPartsTracking.DateIn = #01/08/2003# AND TblPartsTracking.TrackID = 30) OR (TblPartsTracking.PartNumber = 'DC 700110100 ALU-P' AND TblPartsTracking.DateIn = #01/08/2003# AND TblPartsTracking.TrackID = 31) OR (TblPartsTracking.PartNumber = 'DC 700139100 ALU-P' AND TblPartsTracking.DateIn = #01/08/2003# AND TblPartsTracking.TrackID = 32)

I did a test.. I took a backup of the DB before I did the modifications, deleted manually everything from TblPartsTracking and Compacted and repaired the database...
the same thing happened...

If I take the backup without doing anything it works..

From my testing, whenever i clear manually TblPartsTracking or even if I copy the design of the table and make another table with the same name, the insert into or whatever doesn't work no more...
Anyway to fix that?

 

[MattS]

If I understand correctly, you say that when you delete the contents of TblPartsTracking before running this code, nothing happens?

Well, your SQL contains an INNER JOIN between TblParts and TblPartsTracking, so if you delete the contents of TblPartsTracking, your SQL will automatically return no records.

I can see nothing wrong with the code, it's simply that the SQL returns nothing for it to insert.

HTH.

Matt.

 

[DomZ]

well,
What I do:
I delete the content, then add a part to that table using a form I made, and then run the code..

Doesn't work

 

[mission2java_78]

well,
What I do:
I delete the content, then add a part to that table using a form I made, and then run the code..

Doesn't work

Can you explain your problem in summary..

You want to delete a part...simple
you want to add a part...simple
you want to run code ... simple

You could use recordsets to delete or just SQL.

If recordsets just apply the .Delete method.
To add use .Add
then .Update

Then a requery or a refresh of the data and continue executing your code. I hate the DoCmd object..but you can use DoCmd.RunCommand(strSQL).


Jon

 

[DomZ]

Ok I'm doing this EXACTLY:
Now the TblPartsTracking Has some test parts that I entered.
If I use them through my DB it works fine. I can use my SQL Statements well.

NOW:
I delete the content of TblPartsTracking, Meaning I open the table, select all the records and press delete. I open my form, FrmNewTracking, Which is bounded to that Table and add some parts there. Seems fine, when I open my TblPartsTracking, everything is there.
Now I go lets say in my form FrmPartsTracking. I enter 2 dates in 2 textboxes(from date and to date) and select a customerID.. this shows me everything that is IN the company within these 2 dates from this CustomerID.
Now I select 1 of the parts from the listbox.. I then press on Work Order Preview Button and I should see the part in that report, since when I click preview, it clears the TblWOTmp which is the source of a subform in my report and then inserts the selected pieces from the listbox, into TblWOTmp.

I should then see the parts i selected into my report... Now it stays blank

I also have another form, Qty-Out, which modifies the quantity/weight/box numbers of a Part. This uses Insert Into and UPDATE.. it doesn't insert into nor update
Also, I have a button where when the parts are selected from the listbox, and the user press OUT, it sets in the TblPartsTracking the Yes/No Value of that table for the selected pieces to TRUE..
That doesn't work as well

Everything works if I don't delete the TblPartsTracking.. If I do.. doesn't

 

[MattS]

I'm assuming that in TblParts, PartNumber is the Primary Key? Just a hunch, but if everything works perfectly with the test data you have in there, but doesn't when you replace it, is it because you've lost the relationship between the tables (i.e. PartNumber in TblPartsTracking no longer corresponds to any PartNumber in TblParts).

Have you set up relationships and enforced RI ?

 

[DomZ]

I did try what you said...
No success...
What i'll do is prolly leave it as it is and delete all the records except 1 and replace it manually and continue working with it....

however, never had a relationship between both... always worked.. and even before I did reset the table couple times and nothing of this happened

 

[DomZ]

UPDATE:
I updated office xp to SP2 and it seem to have fix the problems on 2 of the 4 computers that had problems..

Thanks for your support mates