Recordset FindFirst Not Working, Why?

[wvhines]

Hello:

I am an ambitious novice...I have a main form "frmGeneralContracting"
that has a subform on it called "frmsubDeliverables" and I am trying
to restrict editing if the current user's login name is in another
subform "frmUserNameRightsProject2Names" that is also attached to the
main form. I want to allow edits, deletes, and additions on the
frmsubDeliverables form to the record and if not they only can view
that record.

I call my GetUserNameRowSecurity function on the OnCurrent event of
the subform frmsubDeliverables.

I have looked at this all day and I can't figure out why my recordset
findfirst isn't working. It seems to totally disregard my code...if I
set the subform properties on the form to false for all 3 allows I
can't edit any records even if my username is in the
tblUserNameRightsProject2Names and if I set the subform properties to
true I can edit all the records regardless if my name is is in
tblUserNameRightsProject2Names for that particular record.

My module code is below. Any help is greatly appreciated. PLEASE
help me.

- Wilma

***********************************

Function GetUserNameRowSecurity()
On Error GoTo RowError_Handler
'************** Code Start **************
Dim myForm As Object
Dim donothing As String
Dim stUser As String
Dim rst As DAO.Recordset
Dim DB As DAO.Database
stUser = Environ("UserName")

Set myForm = Forms!frmGeneralContracting

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT
tblUserNameRightsProject2Names.fldUserNameProjectEdit FROM
GeneralContracting INNER JOIN tblUserNameRightsProject2Names ON
GeneralContracting.ID = tblUserNameRightsProject2Names.ID",
dbOpenSnapshot)
rst.FindFirst ("[tblUserNameRightsProject2Names].
[fldUserNameProjectEdit]" = "'" & stUser & "'")
If rst.NoMatch Then
With myForm
.AllowAdditions = False
.AllowDeletions = False
.AllowEdits = False
End With
Else
With myForm
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
Set myForm = Nothing
End If

'************** Code End **************

Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function

RowError_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function

 

[storrboy]

I believe that this...

rst.FindFirst ("[tblUserNameRightsProject2Names].
[fldUserNameProjectEdit]" = "'" & stUser & "'")

Should likely look like this...

rst.FindFirst ("[tblUserNameRightsProject2Names].
[fldUserNameProjectEdit] = '" & stUser & "'")

Your useage of double and single quotes does not build a valid
statement.

 

[wvhines]

I believe that this...

rst.FindFirst("[tblUserNameRightsProject2Names].
[fldUserNameProjectEdit]" = "'" & stUser & "'")

Should likely look like this...

rst.FindFirst("[tblUserNameRightsProject2Names].
[fldUserNameProjectEdit] = '" & stUser & "'")

Your useage of double and single quotes does not build a valid
statement.

Thanks storrboy my quotes were messing it up but now I have another
problem that I think is related to findfirst and the recordset query.
I want the code to allow the user to edit, add, delete only if the ID
field in the subtables match AND the login names match. Instead it is
allowing editing, adding, and deleting for all project IDs just as
long as the users login name is somewher in the subtable
"tblUserNameRightsProject2Names".

Can you figure out why? How can I fix it? I'm stumped...my revised
code is below. Thanks in advance

'******************************************************************
Function GetUserNameRowSecurity()
On Error GoTo RowError_Handler
'************** Code Start **************
Dim myForm As Object
Dim donothing As String
Dim stUser As String
Dim rst As DAO.Recordset
Dim DB As DAO.Database
stUser = Environ("UserName")

Set myForm = Forms!frmGeneralContracting.frmsubDeliverables.Form

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT
tblUserNameRightsProject2Names.fldUserNameProjectEdit,
GeneralContracting.ID, tblUserNameRightsProject2Names.ID FROM
GeneralContracting INNER JOIN tblUserNameRightsProject2Names ON
GeneralContracting.ID = tblUserNameRightsProject2Names.ID",
dbOpenSnapshot)


rst.FindFirst "( [tblUserNameRightsProject2Names]!
[fldUserNameProjectEdit] = '" & stUser & "' ) AND
( [GeneralContracting]![ID] = [tblUserNameRightsProject2Names]![ID] )"
If rst.NoMatch Then
With myForm
.AllowAdditions = False
.AllowDeletions = False
.AllowEdits = False
End With
Else
With myForm
.AllowAdditions = True
.AllowDeletions = True
.AllowEdits = True
End With
Set myForm = Nothing ' Release the object
End If

'************** Code End **************

Exit_Here:
rst.Close
Set rst = Nothing ' Release the object
Set DB = Nothing ' Release the object
Exit Function

RowError_Handler:
MsgBox Err.Number & ": " & Err.Description
Exit Function
End Function

 

[storrboy]

Try this. I've added and removed (I hope not too much) as somethings
were redundant, some were not used and some were hard to follow.
Compile the module before running to catch syntax or spelling errors.
If it seems to work at first, try it a few more times to make sure.

'******************************************************************
Function GetUserNameRowSecurity()
On Error GoTo RowError_Handler

Dim myForm As Form '------------CHANGED TO FORM
'Dim donothing As String '---NOT USED, COMMENTED OUT
Dim stUser As String
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim sqlStr As String '-----------------ADDED BY STORRBOY
Dim bvAllowRights As Boolean '--ADDED BY STORRBOY

stUser = Environ("UserName")
Set myForm = Forms!frmGeneralContracting.frmsubDeliverables.Form

'THIS MAKES THE SQL A BIT SMALLER AND EASIER TO READ
'INSTEAD OF USING FIND METHODS, USE CRITERIA IN QUERY
'THE FINDFIRST USAGE SHOULD NOW BE TAKEN CARE OF BY THE
'CRITERIA AND THE INNER JOIN BEING EQUAL ALREADY

sqlStr = "SELECT tUNRP2.fldUserNameProjectEdit, tUNRP2.ID AS
NameID,"
sqlStr = sqlStr & " GC.ID AS ContrID FROM GeneralContracting GC"
sqlStr = sqlStr & " INNER JOIN tblUserNameRightsProject2Names
tUNRP2"
sqlStr = sqlStr & " ON GC.ID = tUNRP2.ID WHERE("
sqlStr = sqlStr & " tUNRP2.fldUserNameProjectEdit = '" & stUser &
"');"

Set DB = CurrentDb
Set rst = DB.OpenRecordset(sqlStr, dbOpenSnapshot)
If rst.RecordCount > 0 Then bvAllowRights = True

With myForm
.AllowAdditions = bvAllowRights
.AllowDeletions = bvAllowRights
.AllowEdits = bvAllowRights
End With

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing ' Release the object
Set DB = Nothing ' Release the object
Set myForm = Nothing ' Release the object
Exit Function

RowError_Handler:
MsgBox Err.Number & ": " & Err.Description
Exit Function
End Function