DLookup doesn't return next record

  • Thread starter Thread starter emalcolm_FLA
  • Start date Start date
E

emalcolm_FLA

Hello Everyone and Thanks in Advance

I have created a Christmas applicant database for a non-profit. We
assign case numbers for gift pickup with date and time. The case
numbers are pre-assigned to a specific date and time and I use DMIN to
find the lowest case number and DLookup to see which date and time are
to be used for that case. It worked perfect last year and this year
on the 3rd day it stopped at case 2242 (record 733) and skipped 518
records and now will only assign the same date and time over and over
again, however it does increment the case number.

Here is the code behind the form which is called from a command
button.

Private Sub cmdAssgnCaseNbr_Click()
'assign case number - use next avail from tbl_ToyShop_CaseNbrs
'update eligible child records with the same case number
'print the appointment and signature forms

Dim db As DAO.Database
Dim qd1 As DAO.QueryDef
Dim qd2 As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set qd1 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update")
Set qd2 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update_Child")

Me.[Applicant_Household_Info].Form![int_caseNbr].Value =
DMin("int_caseNbr", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
Me.[Applicant_Household_Info].Form![dte_giftsAppt].Value =
DLookup("dte_appt", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
Me.[Applicant_Household_Info].Form![dte_giftsTime].Value =
DLookup("dte_Time", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
If Me.ynShareInfo.Value = False Then
Me.ynShareInfo.Value = True
End If
DoCmd.Save
'Run the update query to take the case number out of the
pool
qd1.Parameters("[forms]![frm_applicant]![Applicant
Household Info].[form]![int_caseNbr]") = Me.Applicant_Household_Info.
[Form]![int_caseNbr]
qd1.Parameters("forms!frm_applicant![Applicant Household
Info].form!id_contact") = Me.Applicant_Household_Info.[Form]!
[ID_contact]
qd1.Execute
'DoCmd.SetWarnings False ' turn off user prompts
DoCmd.OpenQuery "qry_ToyShop_CaseNbrs_Update"
'Run the update query to assign the case number to
the child's record
Me.Applicant_Household_Info.Form.Requery
Me.Child_Info.Form![Child History].Form.Requery
qd2.Parameters("[forms]![frm_applicant].[id_app]")
= Me.ID_app
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = Me.[Child_Info].Form![Child History].Form!
[int_appYr]
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = Me.Applicant_Household_Info.[Form]![int_appYr]
qd2.Execute
DoCmd.OpenQuery
"qry_ToyShop_CaseNbrs_Update_Child"
'DoCmd.SetWarnings True ' turn off user prompts
'Print the Signature and Appointment Form
DoCmd.OpenReport "rpt_ToyShop_ApptSigForms",
acViewPreview
Me.txtSearch.SetFocus
End Sub

We are using Access 2003/WinXP.

Here is a sample of the table data.

ID_ToyShopCardPrint Toy Shop Case Number Appointment Date Appointment
Time Date Assigned Logger Info
733 2242 12/18/2008 2:30:00 PM 10/23/2008 100
734 2243 12/18/2008 2:30:00 PM 10/23/2008 100
735 2244 12/18/2008 2:30:00 PM 10/23/2008 100
736 2245 12/18/2008 2:30:00 PM 10/23/2008 100
737 2246 12/18/2008 2:30:00 PM 10/23/2008 100

But the form and the report show appointment date as 12/19/2008 and
appointment time as 8:30 am over and over.

Any help is greatly appreciated.

LizM
 
Liz,

I think you have a problem with your Dlookups. Your first Dlookup (the
DMin, actually) asks for the lowest int_CaseNbr in your CaseNbrs table where
dte_assgnd is null. So far, so good, but it's all downhill from here.
Here's your second lookup:

Me.[Applicant_Household_Info].Form![dte_giftsAppt].Value =
DLookup("dte_appt", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")

You are asking Access to return the value of the dte_appt field in the
CaseNbrs table WHERE dte_assgnd IS NULL. However, this is a Dlookup which
returns the value in the first record it finds that meets the criteria.
Access is not required to read through your table in any given order. It's
going in looking for the first record where dte_assigned is null. The fact
that it used to find the lowest case number is coincidence.

If I understand this correctly, you are looking for the appointment date,
etc. from your CaseNbrs table corresponding with the case number you found
in your DMin statement. In this case, the criteria clauses of your Dlookups
should be - "intCaseNbr = " &
Me.[Applicant_Household_Info].Form![dte_giftsAppt].Value.

One other note - your code has way too many words which makes it hard to
read. You can really cut things down. Based on your code, your
Applicant_Household_Info form is a subform. You can delcare a variable and
use it to replace the form name. Try something like this.

Dim f as Form
Set f = Me.Applicant_Household_Info.Form 'You don't need brackets
unless you have embedded spaces

'Use Dmin to find lowest case number where date assigned is null
f!int_CaseNbr = DMin("int_caseNbr", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")

'Use Dlookups to find appt date and time corresponding to the case
number we just looked up.
f!dte_giftsAppt = Dlookup("dte_appt", "tbl_ToyShop_CaseNbrs",
"intCaseNbr = " & f!int_CaseNbr)
f!dte_giftsTime = Dlookup("dte_Time", "tbl_ToyShop_CaseNbrs", intCaseNbr
= " & f!int_CaseNbr)






emalcolm_FLA said:
Hello Everyone and Thanks in Advance

I have created a Christmas applicant database for a non-profit. We
assign case numbers for gift pickup with date and time. The case
numbers are pre-assigned to a specific date and time and I use DMIN to
find the lowest case number and DLookup to see which date and time are
to be used for that case. It worked perfect last year and this year
on the 3rd day it stopped at case 2242 (record 733) and skipped 518
records and now will only assign the same date and time over and over
again, however it does increment the case number.

Here is the code behind the form which is called from a command
button.

Private Sub cmdAssgnCaseNbr_Click()
'assign case number - use next avail from tbl_ToyShop_CaseNbrs
'update eligible child records with the same case number
'print the appointment and signature forms

Dim db As DAO.Database
Dim qd1 As DAO.QueryDef
Dim qd2 As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set qd1 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update")
Set qd2 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update_Child")

Me.[Applicant_Household_Info].Form![int_caseNbr].Value =
DMin("int_caseNbr", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
Me.[Applicant_Household_Info].Form![dte_giftsAppt].Value =
DLookup("dte_appt", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
Me.[Applicant_Household_Info].Form![dte_giftsTime].Value =
DLookup("dte_Time", "tbl_ToyShop_CaseNbrs",
"IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
If Me.ynShareInfo.Value = False Then
Me.ynShareInfo.Value = True
End If
DoCmd.Save
'Run the update query to take the case number out of the
pool
qd1.Parameters("[forms]![frm_applicant]![Applicant
Household Info].[form]![int_caseNbr]") = Me.Applicant_Household_Info.
[Form]![int_caseNbr]
qd1.Parameters("forms!frm_applicant![Applicant Household
Info].form!id_contact") = Me.Applicant_Household_Info.[Form]!
[ID_contact]
qd1.Execute
'DoCmd.SetWarnings False ' turn off user prompts
DoCmd.OpenQuery "qry_ToyShop_CaseNbrs_Update"
'Run the update query to assign the case number to
the child's record
Me.Applicant_Household_Info.Form.Requery
Me.Child_Info.Form![Child History].Form.Requery
qd2.Parameters("[forms]![frm_applicant].[id_app]")
= Me.ID_app
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = Me.[Child_Info].Form![Child History].Form!
[int_appYr]
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = Me.Applicant_Household_Info.[Form]![int_appYr]
qd2.Execute
DoCmd.OpenQuery
"qry_ToyShop_CaseNbrs_Update_Child"
'DoCmd.SetWarnings True ' turn off user prompts
'Print the Signature and Appointment Form
DoCmd.OpenReport "rpt_ToyShop_ApptSigForms",
acViewPreview
Me.txtSearch.SetFocus
End Sub

We are using Access 2003/WinXP.

Here is a sample of the table data.

ID_ToyShopCardPrint Toy Shop Case Number Appointment Date Appointment
Time Date Assigned Logger Info
733 2242 12/18/2008 2:30:00 PM 10/23/2008 100
734 2243 12/18/2008 2:30:00 PM 10/23/2008 100
735 2244 12/18/2008 2:30:00 PM 10/23/2008 100
736 2245 12/18/2008 2:30:00 PM 10/23/2008 100
737 2246 12/18/2008 2:30:00 PM 10/23/2008 100

But the form and the report show appointment date as 12/19/2008 and
appointment time as 8:30 am over and over.

Any help is greatly appreciated.

LizM
Click to expand...
 
Thanks Scott, I updated my code using your suggestion and my test was
successful. One other question for the group - Is there a way to lock
the record? I had several duplicate case numbers assigned this year.
Thanks in advance for any suggestions on preventing duplicate case
numbers. Liz M.
 
emalcolm_FLA said:
Thanks Scott, I updated my code using your suggestion and my test was
successful. One other question for the group - Is there a way to lock
the record? I had several duplicate case numbers assigned this year.
Thanks in advance for any suggestions on preventing duplicate case
numbers. Liz M.
Click to expand...

Index the number and don't allow duplicates.

Not indexing something important is usually a mistake. Your app will run
fine until there are a lot of records, then you will get intermetant reports
of it slowing down.

And while admitting nothing it should have an index even if it is part of a
two part key.
 
Back
Top