Assign Alpha Code to Child Record

L

liz malcolm

I have an non-profit assistance db. Tables Applicant, Applicant
History, Child and Child History. Form Applicant, sub Forms Applicant
History and Child (child has another subform Child History). I have
existing code that from the Applicant form command button a case
number is assigned to Applicant History and Child History. In the past
in the Child History form a 1 digit alpha code was entered manually
for each record. I need to automate the alpha code, for first child
record A, second child record B, etc. I've run into a brain freeze and
need advice.

Sample data: First applicant has 2 children this is the first year
they come for assistance. Second applicant has 3 children and they
have come 3 years for assistance.

It assigns an A to both of the First applicants childrens history, but
nothing is assigned for Second applicants childrens history. Here is
my code, any help is greatly appreciated.

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

Dim db As Database
Dim qd1 As QueryDef
Dim qd2 As QueryDef
Dim fSubApp As Form
Dim fSubChild As Form
dim strChildCode as String
Dim intChildCode As Integer

Set db = CurrentDb
Set qd1 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update")
Set qd2 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update_Child")
Set fSubApp = Me.Applicant_Household_Info.Form
Set fSubChild = Me.[Child Info].Form![Child History].Form


'Message box check Data Entry if all okay continue
If Me.[Applicant_Household_Info].Form![int_appYr] <> Forms!
frm_deMenu.txtDefaultAppYr Then
MsgBox "You are not on the correct year record! Please
review", vbOKOnly, "Data Entry Validation"
DoCmd.CancelEvent
Exit Sub
Else

If IsNull(Me.Child_Info.Form![Child History].Form.
[ID_childHistory]) Then
MsgBox "There is no child data for this year. Please
review", vbOKOnly, "Data Entry Validation"
DoCmd.CancelEvent
Exit Sub
Else

'Assign the case nbr and save the applicant record

fSubApp![int_caseNbr].Value = DMin("int_caseNbr",
"tbl_ToyShop_CaseNbrs", "IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
fSubApp![dte_giftsAppt].Value = DLookup("dte_appt",
"tbl_ToyShop_CaseNbrs", "int_CaseNbr=" & fSubApp!int_caseNbr)
fSubApp![dte_giftsTime].Value = DLookup("dte_Time",
"tbl_ToyShop_CaseNbrs", "int_CaseNbr=" & fSubApp!int_caseNbr)
Me.ynShareInfo.Value = True
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]") = fSubApp![int_caseNbr]
qd1.Parameters("forms!frm_applicant![Applicant Household
Info].form!id_contact") = fSubApp![ID_contact]
qd1.Execute
DoCmd.SetWarnings False ' turn off user prompts
DoCmd.OpenQuery "qry_ToyShop_CaseNbrs_Update"
'Print the Signature and Appointment Form
'DoCmd.OpenReport "rpt_ToyShop_ApptSigForms"
End Select
'Run the update query to assign the case number to
the child's record
fSubApp.Requery
fSubChild.Requery
qd2.Parameters("[forms]![frm_applicant].[id_app]")
= Me.ID_app
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = fSubChild![int_appYr]
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = fSubApp![int_appYr]
qd2.Execute
DoCmd.OpenQuery
"qry_ToyShop_CaseNbrs_Update_Child"
'Works up to this point
'
Do While [Forms]![frm_deMenu].[txtDefaultAppYr] = DMax
("[int_appYr]", "tbl_child_History", "[int_appYr]=" & fSubChild!
[int_appYr])
intChildCode = 64 'starting with CHR 64 so that first
alpha code is upper case A
intChildCode = intChildCode + 1
strChildCode = Chr$(intChildCode)
fSubChild.txt_childCode = strChildCode
DoEvents
Exit Do
Loop
DoCmd.SetWarnings True ' turn off user prompts
End If
End If

End Sub
 
M

Mark Andrews

I don't have time to look at your code but I would think the approach would
be to do the following:

- create a public function to retrieve the new letter to use (pass the
applicantID)
this function would do a Nz(DMax(fill in table and field details and where
clause here),"A") to retieve the maximum letter used (if null return "A")
and then add one asc to the letter to return the correct letter.

Example: if for Applicant "Bill Jones" id number 45 has three children

When the forth child is added the Letter is calculated by calling
AssignLetter(45)

which looks up the maximum letter in the three children which is "C" and
then returns "D"

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com


liz malcolm said:
I have an non-profit assistance db. Tables Applicant, Applicant
History, Child and Child History. Form Applicant, sub Forms Applicant
History and Child (child has another subform Child History). I have
existing code that from the Applicant form command button a case
number is assigned to Applicant History and Child History. In the past
in the Child History form a 1 digit alpha code was entered manually
for each record. I need to automate the alpha code, for first child
record A, second child record B, etc. I've run into a brain freeze and
need advice.

Sample data: First applicant has 2 children this is the first year
they come for assistance. Second applicant has 3 children and they
have come 3 years for assistance.

It assigns an A to both of the First applicants childrens history, but
nothing is assigned for Second applicants childrens history. Here is
my code, any help is greatly appreciated.

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

Dim db As Database
Dim qd1 As QueryDef
Dim qd2 As QueryDef
Dim fSubApp As Form
Dim fSubChild As Form
dim strChildCode as String
Dim intChildCode As Integer

Set db = CurrentDb
Set qd1 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update")
Set qd2 = db.QueryDefs("qry_ToyShop_CaseNbrs_Update_Child")
Set fSubApp = Me.Applicant_Household_Info.Form
Set fSubChild = Me.[Child Info].Form![Child History].Form


'Message box check Data Entry if all okay continue
If Me.[Applicant_Household_Info].Form![int_appYr] <> Forms!
frm_deMenu.txtDefaultAppYr Then
MsgBox "You are not on the correct year record! Please
review", vbOKOnly, "Data Entry Validation"
DoCmd.CancelEvent
Exit Sub
Else

If IsNull(Me.Child_Info.Form![Child History].Form.
[ID_childHistory]) Then
MsgBox "There is no child data for this year. Please
review", vbOKOnly, "Data Entry Validation"
DoCmd.CancelEvent
Exit Sub
Else

'Assign the case nbr and save the applicant record

fSubApp![int_caseNbr].Value = DMin("int_caseNbr",
"tbl_ToyShop_CaseNbrs", "IsNull([tbl_ToyShop_CaseNbrs].[dte_assgnd])")
fSubApp![dte_giftsAppt].Value = DLookup("dte_appt",
"tbl_ToyShop_CaseNbrs", "int_CaseNbr=" & fSubApp!int_caseNbr)
fSubApp![dte_giftsTime].Value = DLookup("dte_Time",
"tbl_ToyShop_CaseNbrs", "int_CaseNbr=" & fSubApp!int_caseNbr)
Me.ynShareInfo.Value = True
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]") = fSubApp![int_caseNbr]
qd1.Parameters("forms!frm_applicant![Applicant Household
Info].form!id_contact") = fSubApp![ID_contact]
qd1.Execute
DoCmd.SetWarnings False ' turn off user prompts
DoCmd.OpenQuery "qry_ToyShop_CaseNbrs_Update"
'Print the Signature and Appointment Form
'DoCmd.OpenReport "rpt_ToyShop_ApptSigForms"
End Select
'Run the update query to assign the case number to
the child's record
fSubApp.Requery
fSubChild.Requery
qd2.Parameters("[forms]![frm_applicant].[id_app]")
= Me.ID_app
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = fSubChild![int_appYr]
qd2.Parameters("[Forms]![frm_deMenu].
[txtDefaultAppYr]") = fSubApp![int_appYr]
qd2.Execute
DoCmd.OpenQuery
"qry_ToyShop_CaseNbrs_Update_Child"
'Works up to this point
'
Do While [Forms]![frm_deMenu].[txtDefaultAppYr] = DMax
("[int_appYr]", "tbl_child_History", "[int_appYr]=" & fSubChild!
[int_appYr])
intChildCode = 64 'starting with CHR 64 so that first
alpha code is upper case A
intChildCode = intChildCode + 1
strChildCode = Chr$(intChildCode)
fSubChild.txt_childCode = strChildCode
DoEvents
Exit Do
Loop
DoCmd.SetWarnings True ' turn off user prompts
End If
End If

End Sub
Click to expand...
 
L

liz malcolm

I don't have time to look at your code but I would think the approach would
be to do the following:

- create a public function to retrieve the new letter to use (pass the
applicantID)
this function would do a Nz(DMax(fill in table and field details and where
clause here),"A") to retieve the maximum letter used (if null return "A")
and then add one asc to the letter to return the correct letter.

Example: if for Applicant "Bill Jones" id number 45 has three children

When the forth child is added the Letter is calculated by calling
AssignLetter(45)

which looks up the maximum letter in the three children which is "C" and
then returns "D"

HTH,
Mark Andrews
RPT Softwarehttp://www.rptsoftware.com
Click to expand...

Thanks Mark

I will definitely give your solution a try.

Liz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup doesn't return next record 3
Assign Child Records from Template Parent Record to Current Parent Record 1
save record in continuous subform before opening child form 2
Open a second form that is related to the first form 1
VBA code to loop append query fails 3
Cannot pass record id to child form 2
Setting Controls Via Code 1
Linking Parent/Child forms where both forms' Recordset properties are bound to ADO Recordset 0

Top