Thank you for considering this problem.
I posted this question in the Active Data Objects forum before I detected
that this "General Discussion" forum seems to relate more to queries. Mea
Culpa.
I have two tables, Loc (location) and Per (person) and the rows are in many
to many correspondence to each other. Given a the primary key of a Per
record (PerID) in a variable named lngPerID which is obtained by clicking on
a row in a Per grid, my query needs to select all the locations with
which this person is association.
Table Per and table LOC are linked by an intersection table, PERLOC where
each record has a pointer to the primary key of the Loc table, and a pointer
to the primary key of the Per table. * indicates primary key
Table Table Table
Loc PerLoc Per
====== ===== ===
*LocID--------| *PerLocID ----------*PerID <---saved in
lngPerID
Adr |--------------xLocID | LastName
City xPerID----------| FirstName
State Salutation
Zip
EnvelopeName
The query following works fine in MS Access, but fails in VB6 with an error
message that says "No value given for one or
more required parameters." Err -2147217904
SELECT Loc.LocID, Loc.Adr, Loc.City, Loc.State, Loc.Zip
FROM Loc INNER JOIN PerLoc ON Loc.LocID = PerLoc.xLocID
WHERE (((PerLoc.xPerID)=[lngPerID]));
^
|____This value is given.
I have checked that the select statement names matches the table names until
I
am blue in the face. Is it possible that the "No value given for one or
more required parameters."
the only variable defined in the table is the temporary variable named
lngPerID, and it is clearly
seen in the code to hold the numeric value for the key of a record observed
in the table.
This query fails in VB6. (Code below)
Private sub DoRSLocSet()
Dim sSQL as string
sSQL = "SELECT Loc.LocID, Loc.Adr, Loc.City, Loc.State,
Loc.LocType, Loc.Zip, Loc.Country" & _
" FROM Loc INNER JOIN PerLoc ON Loc.LocID = PerLoc.xLocID" & _
" WHERE ((PerLoc.xPerID)=lngPerID);"
On Error Resume Next
rsLoc.Close
Err.Clear
On Error GoTo DoRsLocSetErr
With rsLoc
.ActiveConnection = cnnPer
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True 'remove comment to display data
.Open sSQL, , , , adCmdText
End With
Exit Sub
DoRsLocSetErr:
MsgBox "ABORT DoRSLocSetErr = " & Err.Number & " " & Err.Description
Err.Clear
'End
End Sub
--
Lamont Phemister