HowTo access a field in another main form from a main form?

[raylopez99]

What am I doing wrong with DLookup below? I think the solution lies
in how to access a field in another main form from a main form.

I keep getting an error saying "You Canceled the Previous Operation".

The key: DCount below--see the line after "'''''''''''''''''''''''
DCOUNT HERE ''''''''''''''''''''''''''''''''''"--I am trying to
reference a field "LoginUserPassword" found in another table called
"TblLoginUser". As you can see for yourself below, this field, on the
RHS of the "=" sign, is being compared to the LHS that comes from a
form where the below _Click() event resides, and comparing a form's
field (bound to another table) called "LogPasswordEntered" to
"LoginUserPassword"; if they are equal, then we open a form called
"FrmSwitchboard"; if not, we exit (please ignore the MsgBox stuff,
which is just there to debug).

This code looks standard / benign/ generic to me, and is adapted
from: http://chazelleconsulting.com/Resources/Database/Password-protect_a_form.htm

In short, how to reference (using the "=" sign) a bound field found in
another table? See also this post, which implies it's tougher than a
simple reference (or so it seems): http://blog.vishalon.net/Post/50.aspx

RL


Private Sub CmdLogin_Click()
''''''''''' _Click() is an event associated with a button on a form,
the same form having the field "LogPasswordEntered"

On Error GoTo Err_CmdLogin_Click

'''''''''''''''''''''''''''''''''
Dim msg220 As String
Dim title220 As String
'Dim style As MsgBoxStyle
Dim response220

msg220 = LogLoginUserLoginName.Value & LogPasswordEntered.Value
title220 = "Data Initial."
response220 = MsgBox(msg220, 0, title220) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim VarXS As String
VarXS = CStr(LogLoginUserLoginName)

''''''''''''''''''''''' DCOUNT HERE ''''''''''''''''''''''''''''''''''

If (VarXS = DLookup("[LoginUserLoginName]", "TblLoginUser",
"[LoginUserPassword]= Me.LogPasswordEntered")) Then
DoCmd.OpenForm "FrmSwitchboard"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Not IsNull(LogLoginUserLoginName) Then

'''''''''''''''''''''''''''''''''''''''''''''
Dim msg221 As String
Dim title221 As String
'Dim style As MsgBoxStyle
Dim response221

msg221 = LogLoginUserLoginName.Value
title221 = "Data True ' Define title."
response221 = MsgBox(msg221, 0, title221) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim msg222 As String
Dim title222 As String
'Dim style As MsgBoxStyle
Dim response222

msg222 = "LogLoginUserLoginName.Value is NULL!"
title222 = "Data False ' Define title."
response222 = MsgBox(msg222, 0, title222) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If


Me.LogProblem = -1
DoCmd.Quit
End If

'Forms.frmLogin.Visible = False

Exit_CmdLogin_Click:
Exit Sub

Err_CmdLogin_Click:
MsgBox Err.Description
Resume Exit_CmdLogin_Click

End Sub

 

[Douglas J. Steele]

In order to refer to the value of a control in a aggregate function such as
DCount, the reference to the control needs to be outside of the quotes:

If (VarXS = DLookup("[LoginUserLoginName]", "TblLoginUser",
"[LoginUserPassword]= '" & Me.LogPasswordEntered & "'"))
Then

Exagerated for clarity, that last bit is

"[LoginUserPassword]= ' " & Me.LogPasswordEntered & " ' "

That's necessary because presumably LoginUserPassword is a text field. If it
was numeric, all you'd need would be

"[LoginUserPassword]= " & Me.LogPasswordEntered

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What am I doing wrong with DLookup below? I think the solution lies
in how to access a field in another main form from a main form.

I keep getting an error saying "You Canceled the Previous Operation".

The key: DCount below--see the line after "'''''''''''''''''''''''
DCOUNT HERE ''''''''''''''''''''''''''''''''''"--I am trying to
reference a field "LoginUserPassword" found in another table called
"TblLoginUser". As you can see for yourself below, this field, on the
RHS of the "=" sign, is being compared to the LHS that comes from a
form where the below _Click() event resides, and comparing a form's
field (bound to another table) called "LogPasswordEntered" to
"LoginUserPassword"; if they are equal, then we open a form called
"FrmSwitchboard"; if not, we exit (please ignore the MsgBox stuff,
which is just there to debug).

This code looks standard / benign/ generic to me, and is adapted
from:
http://chazelleconsulting.com/Resources/Database/Password-protect_a_form.htm

In short, how to reference (using the "=" sign) a bound field found in
another table? See also this post, which implies it's tougher than a
simple reference (or so it seems): http://blog.vishalon.net/Post/50.aspx

RL


Private Sub CmdLogin_Click()
''''''''''' _Click() is an event associated with a button on a form,
the same form having the field "LogPasswordEntered"

On Error GoTo Err_CmdLogin_Click

'''''''''''''''''''''''''''''''''
Dim msg220 As String
Dim title220 As String
'Dim style As MsgBoxStyle
Dim response220

msg220 = LogLoginUserLoginName.Value & LogPasswordEntered.Value
title220 = "Data Initial."
response220 = MsgBox(msg220, 0, title220) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim VarXS As String
VarXS = CStr(LogLoginUserLoginName)

''''''''''''''''''''''' DCOUNT HERE ''''''''''''''''''''''''''''''''''

If (VarXS = DLookup("[LoginUserLoginName]", "TblLoginUser",
"[LoginUserPassword]= Me.LogPasswordEntered")) Then
DoCmd.OpenForm "FrmSwitchboard"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Not IsNull(LogLoginUserLoginName) Then

'''''''''''''''''''''''''''''''''''''''''''''
Dim msg221 As String
Dim title221 As String
'Dim style As MsgBoxStyle
Dim response221

msg221 = LogLoginUserLoginName.Value
title221 = "Data True ' Define title."
response221 = MsgBox(msg221, 0, title221) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim msg222 As String
Dim title222 As String
'Dim style As MsgBoxStyle
Dim response222

msg222 = "LogLoginUserLoginName.Value is NULL!"
title222 = "Data False ' Define title."
response222 = MsgBox(msg222, 0, title222) 'o is OK only button

''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If


Me.LogProblem = -1
DoCmd.Quit
End If

'Forms.frmLogin.Visible = False

Exit_CmdLogin_Click:
Exit Sub

Err_CmdLogin_Click:
MsgBox Err.Description
Resume Exit_CmdLogin_Click

End Sub

 

[raylopez99]

In order to refer to the value of a control in a aggregate function such as

Exagerated for clarity, that last bit is

"[LoginUserPassword]= ' " & Me.LogPasswordEntered & " ' "

That's necessary because presumably LoginUserPassword is a text field. If it
was numeric, all you'd need would be

"[LoginUserPassword]= " & Me.LogPasswordEntered

--

Amazing, good spot. Makes sense and is another example of why Access
needs a better, smarter complier.

With your suggestion the code worked perfectly, as modified.

BTW, as an aside, I found that commenting out "DoCmd.Quit" and
replacing it with a MsgBox warning in the original code is better
since it prevents, upon an error, from Access completely exiting,
which is not what most users expect I think.

Thanks.

RL