Restricting subforms entry before mainform

[Herman_KLF]

Hi There

I just created a PurchaseOrder form with a PurchaseOrderDetail subform. How
do I restrict someone from first entering data into the subform, otherwise
the transaction would have no P_OrdNr.

Thank you

 

[Mr. B]

Hi, Herman_KLF.

On way you can do this is to make your sub form to be disabled (set the
enabled property of the subform to No) until the desired controls in the main
form have had an entry and then use VBA code to enable the sub form.

You could actaually make the sub form to be invisible until the user
provides the appropriate data, but this might leave them thinking that they
are not going to have the ability to enter all information that they are
needed to enter. I really think the enabled property is the best option.

 

[Joan Wild]

You could make the Detail information a separate form. Put a button on
the first form to check that required information has been filled in,
and then open the detail form. If something is missing, you toss up a
message to the user and not allow the detail to open.

Alternatively, you could set the subform control as hidden and only set
its visible property to true once key main form information has been
filled in.



Joan Wild
MS Access MVP

 

[Herman_KLF]

Thank you guys for the feedback

I am not a very experienced Access user and have no VBA experience
whatsoever. Can I do any of this without VBA. Otherwise could you help me wih
the coding or point me in the right direction.

Regards

 

[Mr. B]

Herman,

It is kinda difficult to write code to do anything without knowing what the
objects are that you are working with.

What field or fields are you wanting to have populated in the main form
before allowing data entry in the subform? What are the actual names of the
controls that are bound to these fields. What is the name of your subform?

If you can provide some details, I am sure that we can help.

Mr. B
askdoctoraccess dot com

 

[Herman_KLF]

Hi there,

A quick description of the structure.

The parent form (Purchase Orders) is based on the table Purchase_Orders with
the following fields:
POID (Autonumber)
Employee (Number) – Lookup
Supplier (Number) – Lookup
Creationdate (Date)
Submitteddate (Date)

The child form (Transactions) comes from a table called Transactions, which
is linked to Purchase_Orders.POID . Transactions looks like this:
TransID (Autonumber)
TransType (Number) – Lookup
POID (Number) – Lookup
Quantity (Number)

Thus I want the subform only to become available when a number is given in
the Purchase_Orders.POID field.

Thanx.

Kind regards
Herman

 

[Mr. B]

Herman,

Actually in the situation that you describe, any entry in any field will
cause the AutoNumber field to be populated. So you would most likely need to
check from any available control except the POID field.

Here is my suggestion:

First, display the properties dialog box for your form. Locate the Enabled
property and set this property to No.

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Next, in the property dialog box for the selected control, with the Events
tab visible, locate the On Current event of your form.

Click the down arrow at the end of the On current event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the On Current event of the form. I will
appear like this:

Private Sub Form_Current()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

The On Current event should then look like this:

Private Sub Form_Current()
CheckPOID
End Sub

Next, indivicually select each of the controls for the Employee, Supplier,
CreateDate, and Submitteddate fields and complete the process described below
for the After Update event of each control.

Click the down arrow at the end of the After Update event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the After Update event of the form. I
will appear like this except that it will have the actual name of your
control instead of "NameOfControl" in its starting line.

Private Sub NameOfControl_AfterUpdate()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

When you finish, there should be an entry like the one below for each of the
controls on your form except the AutoNumber field. Each entry will have the
actual name of the control instead of the "NameOfControl" in its starting
line.

Private Sub NameOfControl_AfterUpdate()
CheckPOID
End Sub

 

[Herman_KLF]

Hi Mr.B

On the following part of your advice

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Should this be in my Parent (Purchase Orders) form or the subform
(Transactions)?

At this stage this is the content of my VBA screen on the Purchase Order form


Option Compare Database
_______
Function CheckPOID()
If Me.NameOfPoidControl > 0 Then
Me.NameOfSubForm.Enabled = True
Else
Me.NameOfSubForm.Enabled = False
End If
End Function
______
Private Sub Employee_AfterUpdate()
CheckPOID
End Sub
_____
Private Sub PO_Creation_Date_AfterUpdate()
CheckPOID
End Sub
_____

Private Sub Supplier_AfterUpdate()
CheckPOID
End Sub
_____

And this is the content of my VBA screen on the Transactions subform

Private Sub Form_Current()
CheckPOID
End Sub


When i try to run it like this i get an error

COMPILE ERROR:
Sub or Function not defined.

and the following lines are highlighted on the Transactions subform VBA screen

Private Sub Form_Current() [yellow]
CheckPOID [light blue]
End Sub



Regards
H.

Herman,

Actually in the situation that you describe, any entry in any field will
cause the AutoNumber field to be populated. So you would most likely need to
check from any available control except the POID field.

Here is my suggestion:

First, display the properties dialog box for your form. Locate the Enabled
property and set this property to No.

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Next, in the property dialog box for the selected control, with the Events
tab visible, locate the On Current event of your form.

Click the down arrow at the end of the On current event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the On Current event of the form. I will
appear like this:

Private Sub Form_Current()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

The On Current event should then look like this:

Private Sub Form_Current()
CheckPOID
End Sub

Next, indivicually select each of the controls for the Employee, Supplier,
CreateDate, and Submitteddate fields and complete the process described below
for the After Update event of each control.

Click the down arrow at the end of the After Update event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the After Update event of the form. I
will appear like this except that it will have the actual name of your
control instead of "NameOfControl" in its starting line.

Private Sub NameOfControl_AfterUpdate()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

When you finish, there should be an entry like the one below for each of the
controls on your form except the AutoNumber field. Each entry will have the
actual name of the control instead of the "NameOfControl" in its starting
line.

Private Sub NameOfControl_AfterUpdate()
CheckPOID
End Sub

-----
HTH
Mr. B
askdoctoraccess dot com

Hi there,

A quick description of the structure.

The parent form (Purchase Orders) is based on the table Purchase_Orders with
the following fields:
POID (Autonumber)
Employee (Number) – Lookup
Supplier (Number) – Lookup
Creationdate (Date)
Submitteddate (Date)

The child form (Transactions) comes from a table called Transactions, which
is linked to Purchase_Orders.POID . Transactions looks like this:
TransID (Autonumber)
TransType (Number) – Lookup
POID (Number) – Lookup
Quantity (Number)

Thus I want the subform only to become available when a number is given in
the Purchase_Orders.POID field.

Thanx.

Kind regards
Herman

 

[Mr. B]

Herman,

Remove the code in the sub form:

Private Sub Form_Current()
CheckPOID
End Sub

You do not need it.

-----
HTH
Mr. B
askdoctoraccess dot com

Herman,

Actually in the situation that you describe, any entry in any field will
cause the AutoNumber field to be populated. So you would most likely need to
check from any available control except the POID field.

Here is my suggestion:

First, display the properties dialog box for your form. Locate the Enabled
property and set this property to No.

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Next, in the property dialog box for the selected control, with the Events
tab visible, locate the On Current event of your form.

Click the down arrow at the end of the On current event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the On Current event of the form. I will
appear like this:

Private Sub Form_Current()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

The On Current event should then look like this:

Private Sub Form_Current()
CheckPOID
End Sub

Next, indivicually select each of the controls for the Employee, Supplier,
CreateDate, and Submitteddate fields and complete the process described below
for the After Update event of each control.

Click the down arrow at the end of the After Update event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the After Update event of the form. I
will appear like this except that it will have the actual name of your
control instead of "NameOfControl" in its starting line.

Private Sub NameOfControl_AfterUpdate()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

When you finish, there should be an entry like the one below for each of the
controls on your form except the AutoNumber field. Each entry will have the
actual name of the control instead of the "NameOfControl" in its starting
line.

Private Sub NameOfControl_AfterUpdate()
CheckPOID
End Sub

-----
HTH
Mr. B
askdoctoraccess dot com

Hi there,

A quick description of the structure.

The parent form (Purchase Orders) is based on the table Purchase_Orders with
the following fields:
POID (Autonumber)
Employee (Number) – Lookup
Supplier (Number) – Lookup
Creationdate (Date)
Submitteddate (Date)

The child form (Transactions) comes from a table called Transactions, which
is linked to Purchase_Orders.POID . Transactions looks like this:
TransID (Autonumber)
TransType (Number) – Lookup
POID (Number) – Lookup
Quantity (Number)

Thus I want the subform only to become available when a number is given in
the Purchase_Orders.POID field.

Thanx.

Kind regards
Herman

 

[Herman_KLF]

WELL DONE! I am truely greatful.

Regards
Herman

Herman,

Remove the code in the sub form:

Private Sub Form_Current()
CheckPOID
End Sub

You do not need it.

-----
HTH
Mr. B
askdoctoraccess dot com

Herman,

Actually in the situation that you describe, any entry in any field will
cause the AutoNumber field to be populated. So you would most likely need to
check from any available control except the POID field.

Here is my suggestion:

First, display the properties dialog box for your form. Locate the Enabled
property and set this property to No.

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Next, in the property dialog box for the selected control, with the Events
tab visible, locate the On Current event of your form.

Click the down arrow at the end of the On current event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the On Current event of the form. I will
appear like this:

Private Sub Form_Current()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

The On Current event should then look like this:

Private Sub Form_Current()
CheckPOID
End Sub

Next, indivicually select each of the controls for the Employee, Supplier,
CreateDate, and Submitteddate fields and complete the process described below
for the After Update event of each control.

Click the down arrow at the end of the After Update event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the After Update event of the form. I
will appear like this except that it will have the actual name of your
control instead of "NameOfControl" in its starting line.

Private Sub NameOfControl_AfterUpdate()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

When you finish, there should be an entry like the one below for each of the
controls on your form except the AutoNumber field. Each entry will have the
actual name of the control instead of the "NameOfControl" in its starting
line.

Private Sub NameOfControl_AfterUpdate()
CheckPOID
End Sub

-----
HTH
Mr. B
askdoctoraccess dot com

Hi there,

A quick description of the structure.

The parent form (Purchase Orders) is based on the table Purchase_Orders with
the following fields:
POID (Autonumber)
Employee (Number) – Lookup
Supplier (Number) – Lookup
Creationdate (Date)
Submitteddate (Date)

The child form (Transactions) comes from a table called Transactions, which
is linked to Purchase_Orders.POID . Transactions looks like this:
TransID (Autonumber)
TransType (Number) – Lookup
POID (Number) – Lookup
Quantity (Number)

Thus I want the subform only to become available when a number is given in
the Purchase_Orders.POID field.

Thanx.

Kind regards
Herman


:

Herman,

It is kinda difficult to write code to do anything without knowing what the
objects are that you are working with.

What field or fields are you wanting to have populated in the main form
before allowing data entry in the subform? What are the actual names of the
controls that are bound to these fields. What is the name of your subform?

If you can provide some details, I am sure that we can help.

Mr. B
askdoctoraccess dot com


:

Thank you guys for the feedback

I am not a very experienced Access user and have no VBA experience
whatsoever. Can I do any of this without VBA. Otherwise could you help me wih
the coding or point me in the right direction.

Regards


:

You could make the Detail information a separate form. Put a button on
the first form to check that required information has been filled in,
and then open the detail form. If something is missing, you toss up a
message to the user and not allow the detail to open.

Alternatively, you could set the subform control as hidden and only set
its visible property to true once key main form information has been
filled in.



Joan Wild
MS Access MVP


Herman_KLF wrote:
Hi There

I just created a PurchaseOrder form with a PurchaseOrderDetail subform. How
do I restrict someone from first entering data into the subform, otherwise
the transaction would have no P_OrdNr.

Thank you

 

[Mr. B]

Glad to help.

Mr. B
askdoctoraccess dot com

WELL DONE! I am truely greatful.

Regards
Herman

Herman,

Remove the code in the sub form:

Private Sub Form_Current()
CheckPOID
End Sub

You do not need it.

-----
HTH
Mr. B
askdoctoraccess dot com

Herman,

Actually in the situation that you describe, any entry in any field will
cause the AutoNumber field to be populated. So you would most likely need to
check from any available control except the POID field.

Here is my suggestion:

First, display the properties dialog box for your form. Locate the Enabled
property and set this property to No.

Copy and paste the function below in the VBA window of your form.

Function CheckPOID()
If me.NameOfPoidControl > 0 then
Me.NameOfSubForm.Enabled = true
Else
Me.NameOfSubForm.Enabled = False
End If
End Function

Next, in the property dialog box for the selected control, with the Events
tab visible, locate the On Current event of your form.

Click the down arrow at the end of the On current event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the On Current event of the form. I will
appear like this:

Private Sub Form_Current()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

The On Current event should then look like this:

Private Sub Form_Current()
CheckPOID
End Sub

Next, indivicually select each of the controls for the Employee, Supplier,
CreateDate, and Submitteddate fields and complete the process described below
for the After Update event of each control.

Click the down arrow at the end of the After Update event row and select the
[Event Procedure] option. Then click the button to the right of the down
arrow to display the VBA window and the After Update event of the form. I
will appear like this except that it will have the actual name of your
control instead of "NameOfControl" in its starting line.

Private Sub NameOfControl_AfterUpdate()

End Sub

Copy and paste the follwing line in between the two statements:

CheckPOID

When you finish, there should be an entry like the one below for each of the
controls on your form except the AutoNumber field. Each entry will have the
actual name of the control instead of the "NameOfControl" in its starting
line.

Private Sub NameOfControl_AfterUpdate()
CheckPOID
End Sub

-----
HTH
Mr. B
askdoctoraccess dot com


:

Hi there,

A quick description of the structure.

The parent form (Purchase Orders) is based on the table Purchase_Orders with
the following fields:
POID (Autonumber)
Employee (Number) – Lookup
Supplier (Number) – Lookup
Creationdate (Date)
Submitteddate (Date)

The child form (Transactions) comes from a table called Transactions, which
is linked to Purchase_Orders.POID . Transactions looks like this:
TransID (Autonumber)
TransType (Number) – Lookup
POID (Number) – Lookup
Quantity (Number)

Thus I want the subform only to become available when a number is given in
the Purchase_Orders.POID field.

Thanx.

Kind regards
Herman


:

Herman,

It is kinda difficult to write code to do anything without knowing what the
objects are that you are working with.

What field or fields are you wanting to have populated in the main form
before allowing data entry in the subform? What are the actual names of the
controls that are bound to these fields. What is the name of your subform?

If you can provide some details, I am sure that we can help.

Mr. B
askdoctoraccess dot com


:

Thank you guys for the feedback

I am not a very experienced Access user and have no VBA experience
whatsoever. Can I do any of this without VBA. Otherwise could you help me wih
the coding or point me in the right direction.

Regards


:

You could make the Detail information a separate form. Put a button on
the first form to check that required information has been filled in,
and then open the detail form. If something is missing, you toss up a
message to the user and not allow the detail to open.

Alternatively, you could set the subform control as hidden and only set
its visible property to true once key main form information has been
filled in.



Joan Wild
MS Access MVP


Herman_KLF wrote:
Hi There

I just created a PurchaseOrder form with a PurchaseOrderDetail subform. How
do I restrict someone from first entering data into the subform, otherwise
the transaction would have no P_OrdNr.

Thank you