Lookup Mod Code Question

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

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
 
Does not work.... please explain. It gives wrong result? It doesn't run when
you expect it to run? Something else?
 
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.

Ken Snell said:
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>

Dave Elliott said:
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
Click to expand...
Click to expand...
 
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?
 
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>

Dave Elliott said:
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.

Ken Snell said:
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>

Dave Elliott said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top