My string does not want to execute

[Marlie]

Hello, this is my first post here. I hope to have a long relationship with
everyone here. My forum's website changed so dramatically, I can't even find
my posts, so I am giving this one a go.

I am struggling a bit with a module that I am writing.

I am setting up a permission module. There is a table that list a form and a
control on that form.

When a form is opened, the module runs first to find out on that specific
form, what can be seen and what not.

My code worked fine with the Docmd.SetProperty formula, but I cant get it to
work on subforms, so I tried something else.

The following module works on the principle eg. me.control.visible.true But
noting happens. Seems like I need to action it somehow. I think my string
comes out as
"Forms!frmDocumentNumberEntry!DocTitle.Visible = False"
insted of
Forms!frmDocumentNumberEntry!DocTitle.Visible = False

Public Function SetPermToForm()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim frmname As String
Dim frm As Form
Set frm = Screen.ActiveForm
frmname = "like '" & frm.Name & "'"
MsgBox frmname


Dim strSQL As String
strSQL = "SELECT tblEmployeePermission.Emp_ID,
tblEmployeePermission.PermYes, tblEmployeePermission.PermForm,
tblEmployeePermission.PermItem, tblEmployeePermission.PermFunct " & _
"FROM tblEmployeePermission " & _
"WHERE (((tblEmployeePermission.PermForm) " & frmname & "))"

Set rs = db.OpenRecordset(strSQL)


rs.MoveFirst 'move to the first record in the recordset
Do Until rs.EOF

Dim strprop As String

'The following command does not happen. In the Debug print window it shows
as if you would have typed in Forms!frmDocumentNumberEntry!DocTitle.Visible =
False


strprop = "Forms!" & rs!permform & "!" & rs!permitem & "." & rs!permfunct &
" = "
strprop = strprop & rs!permyes


'Answer to STRPORP IS:
'Forms!frmDocumentNumberEntry!DocTitle.Visible = Fals
'Forms!frmDocumentNumberEntry!ChildfrmDocumentNumberEntryRev.Form!DocStatus_ID.Vi
sible = False

rs.MoveNext 'move to the next record in the recordset
Loop 'loop back to 'Do Until rs.EOF'
rs.Close 'cleanup
db.Close
Set rs = Nothing
Set db = Nothing





End Function

 

[Allen Browne]

On a line just before the Set rs = ... line, add:
Debug.Prnit strSql

When if fails, open the Immediate Window (Ctrl+G), and look at what came
out. Then examine your string, to see what's wrong.

You will see that it's missing an equal sign, and that you don't have quotes
around frmname.

You'll need to double those quotes up, as explained in this article:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Stefan Hoffmann]

hi Marlie,

Public Function SetPermToForm()

Change this to

Public Function GetPermission(AForm As Access.Form) As Boolean
End Function

and use it in every Form Open event:

Private Sub Form_Open(Cancel As Integer)

Cancel = GetPermission(Me.Form)

End Sub

Set frm = Screen.ActiveForm

Use the AForm parameter instead of Screen.ActiveForm.

Dim strSQL As String
strSQL = "SELECT tblEmployeePermission.Emp_ID,
tblEmployeePermission.PermYes, tblEmployeePermission.PermForm,
tblEmployeePermission.PermItem, tblEmployeePermission.PermFunct "& _
"FROM tblEmployeePermission "& _
"WHERE (((tblEmployeePermission.PermForm) "& frmname& "))"

Use table aliases to write shorter SQL statements. E.g.

sql = "SELECT Q.* " & _
"FROM yourTable Q " & _
"WHERE Q.PermForm = " SqlQuote(AForm.Name)

Don't use LIKE.

Public Function SqlQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SqlQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

'Answer to STRPORP IS:
'Forms!frmDocumentNumberEntry!DocTitle.Visible = False
'Forms!frmDocumentNumberEntry!ChildfrmDocumentNumberEntryRev.Form!DocStatus_ID.Vi
sible = False
on

You may try using Eval(), but I would use explicit referencing with an
appropriate structure:

Forms("FORMNAME").Control("TEXTBOX").Visbible = AValue

Set subform "permissions" in its own Form Open event.


mfG
--> stefan <--

 

[Marlie]

OK, I have rewritten the code with Stefan's help, but I am still stuck where
the property get set. Look below


Option Compare Database
Option Explicit
Public Function GetPermission(AForm As Access.Form) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Dim strsql As String

strsql = "SELECT tblEmployeePermission.* " & _
"FROM tblEmployeePermission " & _
"WHERE tblEmployeePermission.PermForm = " & SqlQuote(AForm.Name)

Debug.Print strsql ' works perfect

Set rs = db.OpenRecordset(strsql)


rs.MoveFirst
Do Until rs.EOF


'Here is where the property get set
'Your code

Forms("FORMNAME").Control("TEXTBOX").Visbible = AValue

'The FORMNAME must get its information from rs!permform,
'The TEXBOX from rs!permitem
'The the property from rs!permfunct
'The AVALue from rs!permyes

'This line
'Forms(rs!permform).Control(rs!permitem).rs!permfunct = rs!permyes
'does not work

'How do you suggest I write the line?

rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


End Function

Public Function SqlQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SqlQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

 

[Stefan Hoffmann]

hi Marlie,

OK, I have rewritten the code with Stefan's help, but I am still stuck where
the property get set. Look below
'Here is where the property get set
'Your code

Forms("FORMNAME").Control("TEXTBOX").Visbible = AValue

'The FORMNAME must get its information from rs!permform,
'The TEXBOX from rs!permitem
'The the property from rs!permfunct
'The AVALue from rs!permyes

'This line
'Forms(rs!permform).Control(rs!permitem).rs!permfunct = rs!permyes
'does not work

'How do you suggest I write the line?

A form references are sometimes hard to fiddle out.

Forms("FORMNAME").Control("TEXTBOX").Visbible = AValue

As we have a form object, we don't need the Forms() collection.
Basically this should work:

Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
If Not rs.Bof And Not rs.Eof Then
Do While Not rs.Eof
AForm.Controls(rs!permitem).Visible = rs!permyes
rs.MoveNext
Loop
Else
' No records found, deny loading of the form.
GetPermission = False
End If

You need to address the single properties directly.


mfG
--> stefan <--

 

[Marlie]

That works great. Just one more thing. I am having a problem with the
subforms.

I did put the code in the subform onopen event, but seems like there is a
sequence problem.

Is there a code that says, Finish with the Main form opening then open the
subform so that the subform code can run?

Or do you think we should set the property in the comand line which gets its
info from the table?

Thanks again

Marlie