Forcing All records in a Continuous form to be populated

[Joe Coulter]

Hi

Can anyone help me with this.

I have a Sub form "Frm_Receipt_Ser" which opens from a main data input form
to input serial numbers of the Pieces of the order, which may vary in
ammounts.

When the quantity_pieces is input on the Main Form, the system creates that
amount of records in another table to house the serial numbers and opens the
Frm_Receipt_Ser to allow the user to input these, what I need to do is ensure
that the user inserts data in all records, I have used the following code on
the After Update Event of Field "Ser_Num"

If IsNull(Me.Ser_Num) Then
MsgBox "Please input all serial numbers"
Else
DoCmd.Close
End If
End Sub

This only works if the cursor is on a null field, if the first record is
inputted and others are null, the form closes.

How can I ensure that the user must input all records??

Thanks in advance

Joe

 

[Ofer Cohen]

Check if the value was enterd as the user move from one record to another
using the before update event of the form

If IsNull(Me.Ser_Num) Then
MsgBox "Please input all serial numbers"
Cancel = True
End Sub

That way when you get to the end all the data is properly inserted, and you
don't need to search all the records

========
If you still want to check all the records, try something like

Dim I As Integer
' Move to the first record
DoCmd.GoToRecord , , acFirst
' Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
' Your code
' Move to the next record
If IsNull(Me.Ser_Num) Then
MsgBox "Please input all serial numbers"
Exit Sub
End If
DoCmd.GoToRecord , , acNext
Next I
DoCmd.Close

 

[Marshall Barton]

I have a Sub form "Frm_Receipt_Ser" which opens from a main data input form
to input serial numbers of the Pieces of the order, which may vary in
ammounts.

When the quantity_pieces is input on the Main Form, the system creates that
amount of records in another table to house the serial numbers and opens the
Frm_Receipt_Ser to allow the user to input these, what I need to do is ensure
that the user inserts data in all records, I have used the following code on
the After Update Event of Field "Ser_Num"

If IsNull(Me.Ser_Num) Then
MsgBox "Please input all serial numbers"
Else
DoCmd.Close
End If
End Sub

This only works if the cursor is on a null field, if the first record is
inputted and others are null, the form closes.

How can I ensure that the user must input all records??

Use the form header or footer section in the subform with
two text boxes:

txtRecords =Count(*)
txtFilled =Count(Ser_Num)

Then you can use something like this air code in the
subform's Unload event:

Me.Requery
If Me.txtFilled < Me.txtRecords Then
MsgBox "Please input all serial numbers"
Cancel = True
Me.Recordset.FindFirst "Ser_Num Is Null"
Me.Ser_Num.SetFocus
End If

 

[Joe Coulter]

Hi Marshall, thanks for your reply, this is the results.

I think I must be missing something (probably knowledge lol)

I have a Command Button "Close" which the user will use to close the form
after inputting the serial numbers.
When I enter data in all the Ser_Num records and click the button, the form
closes, but If one of the Records is Null, then the message "Please Input All
the Serial Numbers" is displayed as expected, but then a Visual Basic Error
is displayed and the debug window shows the DoCmd.Close line on the Command
Button Code

 

[Joe Coulter]

Thanks Cohen for your suggestions, I have tried both but am getting an error
message when using them:

First Suggestion:-
If IsNull(Me.Ser_Num) Then
MsgBox "Please Input All Serial Numbers"
Cancel = True
End If

This works to an extend, if the first field is null, I cant move to the
second field and if I try to, I get the message, however, if i populate the
first field, I can move to the first field, then exit the form without
populating this.

Second Suggetsion:-
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim I As Integer
DoCmd.GoToRecord , , acFirst
For I = 1 To Me.RecordsetClone.RecordCount
If IsNull(Me.Ser_Num) Then
MsgBox "Please input all serial numbers"
Exit Sub
End If
DoCmd.GoToRecord , , acNext
Next I
DoCmd.Close
End Sub

Again I having a problem with this,
I can close the form without inputting any data,
Also when I populate data into one of the records "Cant move to the
specified record, with the debug highlighting the acNext Line.

Sorry for causing so much bother.

 

[Marshall Barton]

It's difficult to guess at a problem without knowing what
the problem is. What, exactly, was the error?

If the error is 2501 - operation was canceled, then just
ignore it in your button's procedure:

On Error GoTo ErrHandler
DoCmd.Close acForm, Me.Name, acSaveNo
ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume ExitHere
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Select
End Sub

 

[Joe Coulter]

Yahoo

Thanks Marshall, worked like a treat

The Error Message was
"Run-time error '3219"
The |action was canceled.

I put your last into the Close Button, and it works great, again many thanks.

Joe

It's difficult to guess at a problem without knowing what
the problem is. What, exactly, was the error?

If the error is 2501 - operation was canceled, then just
ignore it in your button's procedure:

On Error GoTo ErrHandler
DoCmd.Close acForm, Me.Name, acSaveNo
ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume ExitHere
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Select
End Sub
--
Marsh
MVP [MS Access]

Hi Marshall, thanks for your reply, this is the results.

I think I must be missing something (probably knowledge lol)

I have a Command Button "Close" which the user will use to close the form
after inputting the serial numbers.
When I enter data in all the Ser_Num records and click the button, the form
closes, but If one of the Records is Null, then the message "Please Input All
the Serial Numbers" is displayed as expected, but then a Visual Basic Error
is displayed and the debug window shows the DoCmd.Close line on the Command
Button Code