reference table and where condition

  • Thread starter Thread starter johnlute
  • Start date Start date
J

johnlute

I have a combobox beforeupdate event that I can't get to work. I'm
trying to validate the EXISTING value in the combobox against a user's
ID and "permission".

I have a table with UserID values and their numLocationAddressID
values which represent the values that a User has permission to edit.
I want to validate these values against the value in a form. In other
words:
cbFacility.Value should = tblUsersLocationIDs, numLocationAddressID
WHERE UserID = [Forms]![frmLogin].[Form]![numUserID]

The combobox currently filters tblUsersLocationIDs so that it only
displays the values that a user has access to:
SELECT tblLocationIDsAddresses.numLocationAddressID AS ID,
qryLocIDsAddresses.FullAddress, tblLocations.txtLocationID,
tblLocationIDsAddresses.numLocationAddressID
FROM tblLocations INNER JOIN (tblLocationsLocationIDs INNER JOIN
((tblLocationIDsAddresses INNER JOIN qryLocIDsAddresses ON
tblLocationIDsAddresses.numLocationAddressID =
qryLocIDsAddresses.numLocationAddressID) INNER JOIN
tblUsersLocationIDs ON tblLocationIDsAddresses.numLocationAddressID =
tblUsersLocationIDs.numLocationAddressID) ON
tblLocationsLocationIDs.numLocID = tblLocationIDsAddresses.numLocID)
ON tblLocations.txtLocationID = tblLocationsLocationIDs.txtLocationID
WHERE (((tblLocations.Class)="FAC") AND
((tblUsersLocationIDs.UserID)=[Forms]![frmLogin].[Form]![numUserID]))
ORDER BY tblLocationIDsAddresses.numLocationAddressID;

Having set the stage....

I've been tinkering around now for a couple days and came up with
something but it's behaving strangely:

If Me.cbFacility <> DLookup("[numLocationAddressID]",
"tblUsersLocationIDs", "[UserID] =" _
& Forms![frmLogin]!numUserID) Then
'fire messagebox and other procedures

Let's say that I'm UserID "1" and I have permissions to 1, 2, 3. So it
looks like this in the table:
UserID | numLocationAddressID
1 | 1
1 | 2
1 | 3

If the existing record is 46 (NOT in my permissions list) and I try to
change it to 1 then the message box fires as desired because 46 isn't
in my permissions list. However if I try to change 46 to 2 then the
message box does NOT fire and I can change the record to 2! If I try
to change 46 to 3 then the message box fires!

???

Now, if the existing record is 1 (IN my permissions list) and I try to
change it to 3 then the message box fires. It shouldn't because 3 is
in my permissions list. If I try to change 1 to 2 then the message box
does NOT fire and I can change the record to 2!

This doesn't make sense to me but hopefully my error will be readily
visible to more experienced people here.

Thanks!
 
I have a combobox beforeupdate event that I can't get to work. I'm
trying to validate the EXISTING value in the combobox against a user's
ID and "permission".

I have a table with UserID values and their numLocationAddressID
values which represent the values that a User has permission to edit.
I want to validate these values against the value in a form. In other
words:
cbFacility.Value should = tblUsersLocationIDs, numLocationAddressID
WHERE UserID = [Forms]![frmLogin].[Form]![numUserID]

The combobox currently filters tblUsersLocationIDs so that it only
displays the values that a user has access to:
SELECT tblLocationIDsAddresses.numLocationAddressID AS ID,
qryLocIDsAddresses.FullAddress, tblLocations.txtLocationID,
tblLocationIDsAddresses.numLocationAddressID
FROM tblLocations INNER JOIN (tblLocationsLocationIDs INNER JOIN
((tblLocationIDsAddresses INNER JOIN qryLocIDsAddresses ON
tblLocationIDsAddresses.numLocationAddressID =
qryLocIDsAddresses.numLocationAddressID) INNER JOIN
tblUsersLocationIDs ON tblLocationIDsAddresses.numLocationAddressID =
tblUsersLocationIDs.numLocationAddressID) ON
tblLocationsLocationIDs.numLocID = tblLocationIDsAddresses.numLocID)
ON tblLocations.txtLocationID = tblLocationsLocationIDs.txtLocationID
WHERE (((tblLocations.Class)="FAC") AND
((tblUsersLocationIDs.UserID)=[Forms]![frmLogin].[Form]![numUserID]))
ORDER BY tblLocationIDsAddresses.numLocationAddressID;

Having set the stage....

I've been tinkering around now for a couple days and came up with
something but it's behaving strangely:

If Me.cbFacility <> DLookup("[numLocationAddressID]",
"tblUsersLocationIDs", "[UserID] =" _
    & Forms![frmLogin]!numUserID) Then
'fire messagebox and other procedures

Let's say that I'm UserID "1" and I have permissions to 1, 2, 3. So it
looks like this in the table:
UserID | numLocationAddressID
1         | 1
1         | 2
1         | 3

If the existing record is 46 (NOT in my permissions list) and I try to
change it to 1 then the message box fires as desired because 46 isn't
in my permissions list. However if I try to change 46 to 2 then the
message box does NOT fire and I can change the record to 2! If I try
to change 46 to 3 then the message box fires!

???

Now, if the existing record is 1 (IN my permissions list) and I try to
change it to 3 then the message box fires. It shouldn't because 3 is
in my permissions list. If I try to change 1 to 2 then the message box
does NOT fire and I can change the record to 2!

This doesn't make sense to me but hopefully my error will be readily
visible to more experienced people here.

Thanks!
Click to expand...

Please disregard this post!
 
Back
Top