Simple Questions RE Arrays?

[painterz]

Hi All,

I've been using this code for years without problems (or so I think). I've added another table to my array and incremented my variable. When I step through the module, sqlstr returns a rec for tblFamily but the delete statement doesn't delete the record.

I've copied both sqlstr and sqlstr1 to a query to make sure they work--they do.

If I have an array of 4 tables, then loop through those tables--shouldn't my count go from 0 to 3? Please see my code below:

...
If formname.Name = "Neighborhood Input Form" Then
table(0) = "tblPhone"
table(1) = "tblTeenJobs"
table(2) = "tblPeople"
table(3) = "tblFamily"
Else
Exit Function
End If

For x = 0 To 3

'the following query shows all of the people living at the address

sqlstr = "SELECT tblFamily.FamilyID, tblPeople.PeopleID " & _
"FROM tblFamily INNER JOIN tblPeople ON tblFamily.FamilyID = " & _
"tblPeople.FamilyID " & _
"WHERE (((tblPeople.FamilyID)=" & FamilyID & "));"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
Do Until rs.EOF
If rs.RecordCount > 0 Then
PersonNumber = rs(1)

'this query deletes all of the people at an address for the above listed tables
sqlstr1 = "DELETE * " _
& " FROM " & table(x) _
& " WHERE " & Identifier & " = " & PersonNumber 'these are passed through the function

db.Execute sqlstr1
End If
rs.MoveNext
Loop
Next x
...

Thanks for any suggestions you can offer

 

[Cronk]

Put a break point in your code on the statement

db.Execute sqlstr1

and examine the value of sqlstr1 in the immediate window.

You might find your sql construction should be

sqlstr1 = "DELETE * " _
& " FROM " & table(" & x & " )" _
& " WHERE " & Identifier & " = " & PersonNumber

 

[painterz]

Sorry, I thought I made it clear that I did step through my code and evaluate the variables. Everything seems proper. What is "&x&" when it should be "x"???

I did run through the code again with the break point and tblFamily did not execute.

 

[jdraw]

Comment the
db.Execute sqlstr1 in your code,
and use debug.print sqlstr1 just to see what Access has understood sqlstr1 to mean.

x in your case is an iterator from 0 to 3, and you want to use
table(0)...table(3),
so "table" is constant and must be concatenated with the value of x to get table(0) etc.

 

[MarkK]

I don't see where 'Identifier' is ever assigned a value . . .

sqlstr1 = "DELETE * " _
& " FROM " & table(x) _
& " WHERE " & [COLOR="Blue"]Identifier[/COLOR] & " = " & PersonNumber

. . . and you can do this . . .

table(0) = "tblPhone"
table(1) = "tblTeenJobs"
table(2) = "tblPeople"
table(3) = "tblFamily"

. . . a little tidier like this . . .

vTable = Split("tblPhone tblTeenJobs tblPeople tblFamily")

. . . and you could use For...Each...Next to good effect here too, like . . .

dim var
for each [COLOR="Indigo"]var[/COLOR] in Split("tblPhone tblTeenJobs tblPeople tblFamily")
   ...
   ...
   CurrentDb.Execute _
      "DELETE FROM " & [COLOR="Indigo"]var[/COLOR] & " " & _
      "WHERE " & [COLOR="Blue"]Identifier[/COLOR] & " = " & PersonNumber
next

 

[painterz]

Identifier is being passed through the function as string.

 

[MarkK]

DAO does not pass it's errors on to VBA by default, so try this . . .

db.Execute sqlstr1[COLOR="DarkRed"], dbFailOnError[/COLOR]

. . . , which raises a VBA error if there is a DAO error. Maybe an error is occurring and not being raised.

 

[painterz]

@jdraw

When I use the debug.print, this is what I see:

sqlstr1 = "DELETE * FROM tblFamily WHERE PeopleID = 2495"

Then I execute.

So, this is new to me. People ID = 2495 has been deleted from tblFamily but the remainder of the record is still there, only the PeopleID is gone. Is that possible????

 

[painterz]

@MarkK

I've never used the split function so I'll give that a try.

I'll also implement the dbFailonError on my current code and see if that raises anything.

 

[jdraw]

When you do a DELETE, the dbms deletes the record, not just some parts/fields.

If you have deleted the record for People 2495, it appears there is an issue with your logic somewhere since that PeopleId is still showing.