Lookup Mod Code Question

[Dave Elliott]

I have a main form (frmAutoPayrollReport) that opens another form
(Timecards) with criteria from drop down lists.
The drop down list name is Client, it is unbound
On the lookup form (Timecards) where it opens with the criteria chosen from
main form (frmAutoPayrollReport) the control for the client is Client ID
with a table/query lookup.
The below code is in a module called ModLookUp that is called from the on
click event of the main form.(frmAutoPayrollReport)
It does not work, Why? The field Client ID is in the query QDates

Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Mywhere & (" And [Client ID]Like '" + Var1![Client] + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"
LookUp_Exit:
Exit Function

LookUp_Err:
MsgBox Error$
Resume LookUp_Exit

End Function

 

[Ken Snell [MVP]]

Does not work.... please explain. It gives wrong result? It doesn't run when
you expect it to run? Something else?

 

[Dave Elliott]

It opens the form when there is no result from the criteria, prefer it to
not open and instead give message; No Client Found.
Otherwise the code works.

Does not work.... please explain. It gives wrong result? It doesn't run
when
you expect it to run? Something else?

--

Ken Snell
<MS ACCESS MVP>

I have a main form (frmAutoPayrollReport) that opens another form
(Timecards) with criteria from drop down lists.
The drop down list name is Client, it is unbound
On the lookup form (Timecards) where it opens with the criteria chosen from
main form (frmAutoPayrollReport) the control for the client is Client ID
with a table/query lookup.
The below code is in a module called ModLookUp that is called from the on
click event of the main form.(frmAutoPayrollReport)
It does not work, Why? The field Client ID is in the query QDates

Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Mywhere & (" And [Client ID]Like '" + Var1![Client] + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"
LookUp_Exit:
Exit Function

LookUp_Err:
MsgBox Error$
Resume LookUp_Exit

End Function

 

[Douglas J. Steele]

I think the problem is your use of Var1.

Technically, that should be

Dim Var1 As Variant

Set Var1 = [Forms]![frmAutoPayrollReport]

However, since everything's hard-coded, why not simply use

Mywhere = Mywhere & (" And [Client ID] Like '" +
[Forms]![frmAutoPayrollReport]![Client] + "'")

Why are you using Like, by the way, if you're not using any wildcard
characters? And why is Mywhere declared as a variant when you know it's
going to contain a string?

 

[Ken Snell [MVP]]

Doug has posted info re: other issues with your code. I'll focus on the way
to not have the form display if there are no data.

You can put code in the TimeCards form's Load event to test if the form's
Recordset has any records, and then to close the form if not:

Private Sub Form_Load()
If Me.Recordset.RecordCount = 0 Then
MsgBox "No data"
DoCmd.Close acForm, Me.Name
End if
End Sub


--

Ken Snell
<MS ACCESS MVP>

It opens the form when there is no result from the criteria, prefer it to
not open and instead give message; No Client Found.
Otherwise the code works.

Does not work.... please explain. It gives wrong result? It doesn't run
when
you expect it to run? Something else?

--

Ken Snell
<MS ACCESS MVP>

I have a main form (frmAutoPayrollReport) that opens another form
(Timecards) with criteria from drop down lists.
The drop down list name is Client, it is unbound
On the lookup form (Timecards) where it opens with the criteria chosen from
main form (frmAutoPayrollReport) the control for the client is Client ID
with a table/query lookup.
The below code is in a module called ModLookUp that is called from the on
click event of the main form.(frmAutoPayrollReport)
It does not work, Why? The field Client ID is in the query QDates

Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Mywhere & (" And [Client ID]Like '" + Var1![Client] + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"
LookUp_Exit:
Exit Function

LookUp_Err:
MsgBox Error$
Resume LookUp_Exit

End Function