Alert Message if Sub Form has no record.

[Sanjaylml]

I have made a Form in Access 2000 which has Main Form & Sub Form. Main Form has (Header File) and Sub-Form has (Detailed File). Relationship between the primary key field of Header Table and Detailed Table is established with Casecade Update Related Fields and Casecade Delete Related Records.

I want that whenever User entered all the inputs in Header file, he must enter necessary records in Detailed file also & if he fails to do so, an alert message is appear to ensure the same.

Kindly help me.

[missinglinq]

You have inadvertently posted your question in the Articles section rather than in the Forum section of our site, so I have moved it across to the Forum for you.

Linq ;0)>

[puppydogbuddy]

I have made a Form in Access 2000 which has Main Form & Sub Form. Main Form has (Header File) and Sub-Form has (Detailed File). Relationship between the primary key field of Header Table and Detailed Table is established with Casecade Update Related Fields and Casecade Delete Related Records.

I want that whenever User entered all the inputs in Header file, he must enter necessary records in Detailed file also & if he fails to do so, an alert message is appear to ensure the same.

Kindly help me.

Use a DCount applied to your detailed table:

Private  Sub YourHeaderForm_AfterUpdate()
If DCount("[YourDetailedFormLinkField]", "YourTable", "[YourDetailedFormLinkField] = " & Me![ YourHeaderFormLinkField]) = 0 Then 
 
MsgBox "Please enter some details."
 
End If

[Sanjaylml]

Thanks for your reply.

I have used the same code, as given by you but it does not serve my actual requirement.

If user did not punch any record and quits from the form, though a message is appear but that close the form.

How can we got the focus in field of Sub-Form in main form. Is their any command to go to field of sub-form used in main form? Actually, there is no control (field) of sub-form available in main form.

Your kind help is requested

Use a DCount applied to your detailed table:

Private  Sub YourHeaderForm_AfterUpdate()
If DCount("[YourDetailedFormLinkField]", "YourTable", "[YourDetailedFormLinkField] = " & Me![ YourHeaderFormLinkField]) = 0 Then 
 
MsgBox "Please enter some details."
 
End If

[puppydogbuddy]

How can we got the focus in field of Sub-Form in main form. Is their any command to go to field of sub-form used in main form? Actually, there is no control (field) of sub-form available in main form.

The subform control, which contains the subform, is on the main form. Tell me what fields you have and whether they are on the main form or the subform. Tell me what field you want to have focus on the subform and on the main form. Thanks.

[Sanjaylml]

The main form (against Header File) has following fields:

1. Issued To: (Combo box, which has Departments name, to whom, material issues).
2. Issue Slip No. (Numeric Primary key field, which has relation to Sub-Form also).
3. Issue Slip Date
4. Remarks (if any)

The sub-form (against Detail File) has following fields:

1. ItemID (Combo box, parts from part master table which are in stock i.e. (Total Receipt – Total Issues).
2. Quantity Issued (which has following SQL to run in the event of After Update).

UPDATE Item SET Item.IssueQty = NZ(DSum("[Quantity]","Issue_Dt l","[ItemId]=" & [ItemId]),0);

3. Issue Slip No. (Hidden)

I am also appending here below coding of main form, which will help you further in understanding my requirement.

Private Sub cmdAdd_Click()
DoCmd.GoToRecor d , , acNewRec
End Sub

Private Sub cmdSave_Click()
If (IssuingHD) = 0 Then
MsgBox ("Issued To is missing"), vbCritical
DoCmd.GoToContr ol ("IssuingHd" )
End If
If IsNull(IssueSli pNo) Then
MsgBox ("Issue Slip No. is Blank"), vbCritical
DoCmd.GoToContr ol ("IssueSlipN o")
End If
If IsNull(IssueDt) Then
MsgBox ("Date of Issue is Blank"), vbCritical
DoCmd.GoToContr ol ("IssueDt")
End If
End Sub

Private Sub Exit_Click()
DoCmd.Quit
End Sub

Private Sub Form_AfterUpdat e()
If DCount("[IssueSlipNo]", "Issue_dtl" , "[IssueSlipNo] = " & Me![IssueSlipNo]) = 0 Then
MsgBox "Please enter some details in Detailed File."
End If
End Sub

Private Sub Form_Open(Cance l As Integer)
DoCmd.SetWarnin gs Off
DoCmd.Maximize
End Sub

Private Sub Form_KeyDown(Ke yCode As Integer, Shift As Integer)
Select Case KeyCode
Case 33, 34
KeyCode = 0
End Select
End Sub

Private Sub Form_KeyUp(KeyC ode As Integer, Shift As Integer)
Select Case KeyCode
Case 33, 34
KeyCode = 0
End Select
End Sub

Private Sub Issue_SubForm_E nter()
If (Me![IssuingHD]) = 0 Then
MsgBox "You must enter details before viewing records"
Me.IssuingHD.Se tFocus
Exit Sub
End If
If IsNull(Me![IssueSlipNo]) Then
MsgBox "You must enter details before viewing records"
Me.IssueSlipNo. SetFocus
Exit Sub
End If
If IsNull(Me![IssueDt]) Then
MsgBox "You must enter details before viewing records"
Me.IssueDt.SetF ocus
Exit Sub
End If
End Sub

Private Sub Issue_SubForm_E xit(Cancel As Integer)
Dim Msg As String
Dim intX As Integer
intX = DCount("[IssueSlipNo]", "Issue_dtl" )
If intX < 1 Then
Msg = "No Records are entered"
MsgBox Msg, vbOKOnly + vbInformation
End If
End Sub

Private Sub IssueSlipNo_Aft erUpdate()
Dim dbs As Database
Dim rcd As Recordset
Dim p_find
Set dbs = CurrentDb
Set rcd = dbs.OpenRecords et("Issue_Hdr" )
Do While Not rcd.EOF
If IssueSlipNo = rcd!IssueSlipNo Then
MsgBox ("This Issue Slip No. already exists")
Me.IssueSlipNo = 0
DoCmd.GoToContr ol ("IssueDt")
Me.IssueDt.SetF ocus
DoCmd.GoToContr ol ("IssueSlipN o")
Me.IssueSlipNo. SetFocus
End If
rcd.MoveNext
If rcd.EOF Then Exit Do
Loop
End Sub

Now, please let me know how I can set focus to enter part id & quantity in the sub-form, if he/she entered any detail in header form.

If you require any further clarification, please revert back.

Thanking you in anticipation of reply.

The subform control, which contains the subform, is on the main form. Tell me what fields you have and whether they are on the main form or the subform. Tell me what field you want to have focus on the subform and on the main form. Thanks.

[puppydogbuddy]

The main form (against Header File) has following fields:

1. Issued To: (Combo box, which has Departments name, to whom, material issues).
2. Issue Slip No. (Numeric Primary key field, which has relation to Sub-Form also).
3. Issue Slip Date
4. Remarks (if any)

The sub-form (against Detail File) has following fields:

1. ItemID (Combo box, parts from part master table which are in stock i.e. (Total Receipt – Total Issues).
2. Quantity Issued (which has following SQL to run in the event of After Update).

UPDATE Item SET Item.IssueQty = NZ(DSum("[Quantity]","Issue_Dt l","[ItemId]=" & [ItemId]),0);

3. Issue Slip No. (Hidden)

I am also appending here below coding of main form, which will help you further in understanding my requirement.

Private Sub cmdAdd_Click()
DoCmd.GoToRecor d , , acNewRec
End Sub

Private Sub cmdSave_Click()
If (IssuingHD) = 0 Then
MsgBox ("Issued To is missing"), vbCritical
DoCmd.GoToContr ol ("IssuingHd" )
End If
If IsNull(IssueSli pNo) Then
MsgBox ("Issue Slip No. is Blank"), vbCritical
DoCmd.GoToContr ol ("IssueSlipN o")
End If
If IsNull(IssueDt) Then
MsgBox ("Date of Issue is Blank"), vbCritical
DoCmd.GoToContr ol ("IssueDt")
End If
End Sub

Private Sub Exit_Click()
DoCmd.Quit
End Sub

Private Sub Form_AfterUpdat e()
If DCount("[IssueSlipNo]", "Issue_dtl" , "[IssueSlipNo] = " & Me![IssueSlipNo]) = 0 Then
MsgBox "Please enter some details in Detailed File."
End If
End Sub

Private Sub Form_Open(Cance l As Integer)
DoCmd.SetWarnin gs Off
DoCmd.Maximize
End Sub

Private Sub Form_KeyDown(Ke yCode As Integer, Shift As Integer)
Select Case KeyCode
Case 33, 34
KeyCode = 0
End Select
End Sub

Private Sub Form_KeyUp(KeyC ode As Integer, Shift As Integer)
Select Case KeyCode
Case 33, 34
KeyCode = 0
End Select
End Sub

Private Sub Issue_SubForm_E nter()
If (Me![IssuingHD]) = 0 Then
MsgBox "You must enter details before viewing records"
Me.IssuingHD.Se tFocus
Exit Sub
End If
If IsNull(Me![IssueSlipNo]) Then
MsgBox "You must enter details before viewing records"
Me.IssueSlipNo. SetFocus
Exit Sub
End If
If IsNull(Me![IssueDt]) Then
MsgBox "You must enter details before viewing records"
Me.IssueDt.SetF ocus
Exit Sub
End If
End Sub

Private Sub Issue_SubForm_E xit(Cancel As Integer)
Dim Msg As String
Dim intX As Integer
intX = DCount("[IssueSlipNo]", "Issue_dtl" )
If intX < 1 Then
Msg = "No Records are entered"
MsgBox Msg, vbOKOnly + vbInformation
End If
End Sub

Private Sub IssueSlipNo_Aft erUpdate()
Dim dbs As Database
Dim rcd As Recordset
Dim p_find
Set dbs = CurrentDb
Set rcd = dbs.OpenRecords et("Issue_Hdr" )
Do While Not rcd.EOF
If IssueSlipNo = rcd!IssueSlipNo Then
MsgBox ("This Issue Slip No. already exists")
Me.IssueSlipNo = 0
DoCmd.GoToContr ol ("IssueDt")
Me.IssueDt.SetF ocus
DoCmd.GoToContr ol ("IssueSlipN o")
Me.IssueSlipNo. SetFocus
End If
rcd.MoveNext
If rcd.EOF Then Exit Do
Loop
End Sub

Now, please let me know how I can set focus to enter part id & quantity in the sub-form, if he/she entered any detail in header form.

If you require any further clarification, please revert back.

Thanking you in anticipation of reply.

Ok, if you are on the main form and you want to refer to a control on the subform, the general syntax is as shown in the paragraph after the next. I am also providing you this link which many experts use as a handy guide for referencing between the form and subform

http://www.mvps.org/access/forms/frm0031.htm

Keep in mind that the name of the subform control (also known as the subform container) may or may not be the same as the source object, which is the name of the actual subform inside the container. You should verify the names by highlighting the border between your form and subform, then invoke the property sheet. There you see the name property and the Source Object property, and also the Master Link and Child Link properties. The name property refers to the SubformControl and the Source Object refers to the name of the subform.

Note that the references refer to the Subform Control not the Source Object.

Long Ref>>>>>Forms!Y ourMainForm!You rSubformControl .Form![YourControl]
Shortcut Ref>>>>>Me!Your SubformControl. Form![YourControl]

If you want to set focus on a control on the subform:
>>Forms!YourMai nForm!YourSubfo rmControl.Form![YourControl].SetFocus
or
>>Me!YourSubfor mControl.Form![YourControl].SetFocus

_______________ _______________ _______________ _______________ _
Your syntax for the Dcount from the main form looks ok, assuming that [IssueSlipNo] is a numeric data type. I am assuming it didn't work for you because it is a text data type. If so, try the following syntax:

If DCount("[IssueSlipNo]", "Issue_dtl" , "[IssueSlipNo] = '" & Me![IssueSlipNo] & "'") = 0 Then


Happy coding. Let me know if you have any problems.

[Sanjaylml]

At the outset, sorry for the delayed reply.

After going through the reply, to restict closing the main form when some controls on the subform are empty, I am not yet very clear in which event following syntex to be used:

Forms!mainform! subform.form.co ntrolname

My main form name is : Issue Main Form
My Subform name is : Issue SubForm
Subform has following fields : ItemID & Quantity issued

Now, please let me know to set focus on controls of sub-form, how we can write the syntex & at which event whether in main form or sub-form.

Your early reply will be highly appreciated.

Ok, if you are on the main form and you want to refer to a control on the subform, the general syntax is as shown in the paragraph after the next. I am also providing you this link which many experts use as a handy guide for referencing between the form and subform

http://www.mvps.org/access/forms/frm0031.htm

Keep in mind that the name of the subform control (also known as the subform container) may or may not be the same as the source object, which is the name of the actual subform inside the container. You should verify the names by highlighting the border between your form and subform, then invoke the property sheet. There you see the name property and the Source Object property, and also the Master Link and Child Link properties. The name property refers to the SubformControl and the Source Object refers to the name of the subform.

Note that the references refer to the Subform Control not the Source Object.

Long Ref>>>>>Forms!Y ourMainForm!You rSubformControl .Form![YourControl]
Shortcut Ref>>>>>Me!Your SubformControl. Form![YourControl]

If you want to set focus on a control on the subform:
>>Forms!YourMai nForm!YourSubfo rmControl.Form![YourControl].SetFocus
or
>>Me!YourSubfor mControl.Form![YourControl].SetFocus

_______________ _______________ _______________ _______________ _
Your syntax for the Dcount from the main form looks ok, assuming that [IssueSlipNo] is a numeric data type. I am assuming it didn't work for you because it is a text data type. If so, try the following syntax:

If DCount("[IssueSlipNo]", "Issue_dtl" , "[IssueSlipNo] = '" & Me![IssueSlipNo] & "'") = 0 Then


Happy coding. Let me know if you have any problems.

[puppydogbuddy]

At the outset, sorry for the delayed reply.

After going through the reply, to restict closing the main form when some controls on the subform are empty, I am not yet very clear in which event following syntex to be used:

Forms!mainform! subform.form.co ntrolname

My main form name is : Issue Main Form
My Subform name is : Issue SubForm
Subform has following fields : ItemID & Quantity issued

Now, please let me know to set focus on controls of sub-form, how we can write the syntex & at which event whether in main form or sub-form.

Your early reply will be highly appreciated.

prevent update (closing) of main form if no details on subform-place the following code on the main form:

Private Sub Form_BeforeUpdate()
If DCount("[IssueSlipNo]", "Issue_dtl", "[IssueSlipNo] = " & Me![IssueSlipNo]) = 0 Then
Cancel = True
MsgBox "Please enter issue details in Detailed File."
Me![Issue Subform].Form![Quantity issued].SetFocus
End If
End Sub

If the above code isn't what you are looking for, you need to explain better how your form and subform interact from an entry point of view (tab order).

[Sanjaylml]

When I have tried to use the code as written by you, an error message is reflected
----------------------------------
Run-time error 2465
Microsoft Access cann’t find the field “Issue SubForm” referred to in your expression
----------------------------------
I am surprised to receive this error. As per error, they cann’t the field Issue SubForm, whereas, “Issue SubForm” is subform.

I have removed the trailing space between in “Issue SubForm” & also Square bracket [ ] from the following syntax

Me![Issue_SubForm].Form![PartID].SetFocus

As

Me.IssueSubForm .Form.PartID.Se tFocus

then I found that there is no problem in writing following syntax

Me.IssueSubForm .Part

But after inserted dot (.) after Part, I noticed that no control (fields) are showing & when controls are missing, how Setfocus will appear.

Could you please check & confirm where error exists.

Thanks in advance.

prevent update (closing) of main form if no details on subform-place the following code on the main form:

Private Sub Form_BeforeUpdate()
If DCount("[IssueSlipNo]", "Issue_dtl", "[IssueSlipNo] = " & Me![IssueSlipNo]) = 0 Then
Cancel = True
MsgBox "Please enter issue details in Detailed File."
Me![Issue Subform].Form![Quantity issued].SetFocus
End If
End Sub

If the above code isn't what you are looking for, you need to explain better how your form and subform interact from an entry point of view (tab order).

[puppydogbuddy]

I have removed the trailing space between in “Issue SubForm” & also Square bracket [ ] from the following syntax

Me![Issue_SubForm].Form![PartID].SetFocus

As

Me.IssueSubForm .Form.PartID.Se tFocus

then I found that there is no problem in writing following syntax

Me.IssueSubForm .Part

But after inserted dot (.) after Part, I noticed that no control (fields) are showing & when controls are missing, how Setfocus will appear.

sanjay,
The link I gave you previously, which I am repeating below, is considered to be the "syntax bible" when it comes to referring to methods, properties, or objects on or between a form and subform. The syntax I gave you follows it. If you don't follow it, then you will never get your code working.

As you can see, the proper reference is primarily dependent on 4 things:
1.Where (form or subform) the code is being executed from.
2.Using the correct name for the object being referenced.
3.Using the bang ! or period operator in the context required by the syntax.
4.whether you are referencing the object, or a method or property of the object.

Your decision to eliminate the space in the object names is a good decision and is recommended as a standard practice under current naming conventions adopted for Access. Brackets are optional, except when there is a space in the object name…then they are required. While you have some leeway with brackets and spaces, the bang and period operators must be used within the context prescribed in the link.

http://www.mvps.org/access/forms/frm0031.htm

If you use the syntax prescribed in the link with your object names and get an error, it generally means either of the following:
1.the object name is incorrect (e.g. using the name of the subform instead of the subform control in the reference).
2.you are using the wrong syntax in the context of what you want to reference (e.g. you using the object context, instead of the method or property context).