Sariff
asked on
Access Query from Form Data
Experts - I am having trouble getting the data to feed to the below query from a Form. If I enter the criteria directly it works fine.
SELECT TBL_MAIN.PH_NUM, TBL_MAIN.EE_STATUS
FROM TBL_MAIN
WHERE (((TBL_MAIN.PH_NUM)=[Forms]![frmPolicyData]![txtPOL_NUM]));
is the form "frmPolicyData" open when you are running the query? it should be.
Use the SQL below. Also add following in a module:
Function fnPH_NUM() As Variant
On Error GoTo 10
If Nz([Forms]![frmPolicyData]
fnPH_NUM() ="<All>"
Else
fnPH_NUM() =Forms]![frmPolicyData]![t
End IF
Exit Function
10:
fnPH_NUM() ="<All>"
End Function
Mike
Function fnPH_NUM() As Variant
On Error GoTo 10
If Nz([Forms]![frmPolicyData]
fnPH_NUM() ="<All>"
Else
fnPH_NUM() =Forms]![frmPolicyData]![t
End IF
Exit Function
10:
fnPH_NUM() ="<All>"
End Function
Mike
SELECT PH_NUM, EE_STATUS FROM TBL_MAIN WHERE PH_NUM= iif(fnPH_NUM()="<all>",[PH_NUM],fnPH_NUM());
Also make sure the MsgBox (Forms]![frmPolicyData]![t
Function fnPH_NUM() As Variant
On Error GoTo 10
MsgBox (Forms]![frmPolicyData]![t
If Nz([Forms]![frmPolicyData]
fnPH_NUM() ="<All>"
Else
fnPH_NUM() =Forms]![frmPolicyData]![t
End IF
Exit Function
10:
fnPH_NUM() ="<All>"
End Function
Function fnPH_NUM() As Variant
On Error GoTo 10
MsgBox (Forms]![frmPolicyData]![t
If Nz([Forms]![frmPolicyData]
fnPH_NUM() ="<All>"
Else
fnPH_NUM() =Forms]![frmPolicyData]![t
End IF
Exit Function
10:
fnPH_NUM() ="<All>"
End Function
coorection...
MsgBox Nz((Forms]![frmPolicyData]
MsgBox Nz((Forms]![frmPolicyData]
ASKER
Text/Cap: The form was open during the executing of the SQL...
eghtebas: Your fuction return the correct value...
eghtebas: Your fuction return the correct value...
re:> Your fuction return the correct value...
Is the problem solved?
Mike
Is the problem solved?
Mike
ASKER
Unfortunately, no...
I need the SQL to take the input from a cotnrol on a form for use in a DELETE query. I am testing it in SELECT.
I need the SQL to take the input from a cotnrol on a form for use in a DELETE query. I am testing it in SELECT.
"The form was open during the executing of the SQL..." therefore it goes to how you are running the SQL
If you are opening a DAO recordset from the query then you use the PARAMETERS of the QueryDef object to assign the values to the FORMS reference.
Cheers, Andrew
If you are opening a DAO recordset from the query then you use the PARAMETERS of the QueryDef object to assign the values to the FORMS reference.
Cheers, Andrew
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim cnt As Long
Set db = CurrentDB
Set qd = db.QueryDefs("MyQuery")
For cnt = 0 to qd.parameters.count -1
qd.parameters(cnt) = Eval(qd.parameters(cnt).Name)
Next cnt
Set rs = qd.OpenRecordSet()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Can you please check the exact name of the control on the form.
Other than that it looks correct, does it prompt you for a value? Is so then either the form is not open in FORM or DATASHEET view or the control name is wrong.
Cheers, Andrew