Force users to enter data into mainform before into subform

[StefanScheidegger]

Hello there

I have a mainform with a subform.

I would like to prevent users from entering data into the subform before they enter data into the mainform (mainform fields must be completed first!).

Let's say the main form is called "FormA" and the subform is called "SubFormB".

How can I achieve that?

Thanks very much.

Stefan

 

[KenReay]

Hi

In the oncurrent event of the main form:

loop through all of the controls which can contain data, if any are null, lock the subform

Dim i as Integer
Dim blnLock as Boolean
'
blnLock = False
For i = 0 to Me.Controls.Count -1
Select case Me.Controls(i).Type
Case acLabel, acSubForm, acCommandButton
' do Nothing
Case Else
If IsNull(Me.Controls(i).Value) Then
blnLock = True
i = Me.Controls.Count
end if
End Select
Next i
Me.SubFormControlName.Locked = blnLock

Note the above is not tested Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StefanScheidegger]

Hi Ken

Many thanks for your helpful Idea. I tried to implement your code to my database. Unfortuantely, it did not work.
Do I have to change anything in your code to implement it into my database (subform names, etc.). If yes, can you please indicate what I have to change?

Thanks and regards,

Stefan

 

[KenReay]

Hi

Dim i as Integer
Dim blnLock as Boolean
'
blnLock = False
For i = 0 to Me.Controls.Count -1
Select case Me.Controls(i).Type
Case acLabel, acSubForm, acCommandButton
' do Nothing
Case Else
If IsNull(Me.Controls(i).Value) Then
blnLock = True
i = Me.Controls.Count
end if
End Select
Next i
Me.SubFormControlName.Locked = blnLock <-- you need to put the name of your subform CONTROL here in place of SubFormControlName
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StefanScheidegger]

Thanks Ken for your reponse

When I run the code. It brings up the following error:

Compile error:
method or data member not found


The line which is marked yellow is:

Me.Produkt.Locked = blnLock

I don't understand why there should be an error. The subform field is called &quot;Produkt&quot;.

Do you know what could be wrong?

Stefan

 

[KenReay]

Hi Stefan

&quot;I don't understand why there should be an error. The subform field is called &quot;Produkt&quot;.&quot;

Maybe it is just choice of words, but it is the Subform CONTROL on the MAIN form whose locked proerty you should be setting, not a field on the actual subform

Can you just confirm this is so?

Or an odd ball I always forget . or ! maybe it should be Me!Produkt.Locked = False Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StefanScheidegger]

Hi Ken

I can confirm to you that &quot;Produkt&quot; is a subform field on the actual main form.

I changed the last line of your code to &quot;Me!Produkt.Locked = blnLock&quot; and there is no more error message appearing. However, I am still able to fill in data into the subform field &quot;Produkt&quot; before entering data into the main form.

This seems to be tricky, isn't it?

Any ideas?

Stefan

 

[StefanScheidegger]

Hi Ken

MS Access is showing an error in the line:

Select Case Me.Controls(i).Type

It is marked yellow when I open the main form.

Is there something wrong? Do I have to define something more in the code?

regards,

Stefan

 

[StefanScheidegger]

HI Ken

For your information (forgot to mention it): the error message is run-time error 438 (object does not support these properties or method). and wen clicking at debug, Visual basic marks the above mentioned line yellow (Select Case Me.Controls(i).Type).

I hope this helps.

Stefan

 

[KenReay]

Hi

Sorry Stefan, I did say not tested

should be

Select case Me.Controls(i).ControlType
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StefanScheidegger]

Hi Ken

Thanks for your hint. this line seems to be correct now.

However, MS Access brings error messages on the following other lines:

- If IsNull(Me.Controls(i).Value) Then
- Me!Produkt.Locked = blnLock

The error message is again run-time error 438 (object does not support these properties or method).

Thank you for your competent support.

Stefan

 

[KenReay]

Hi

If you look at the code you can see what it is doing, ie loop through each control on the report, ignore those which would not have data entered,(eg Label, Command Button and SubFormControl), and for the rest check if any data has been entered (ie the .value property is not null).

I cannot see you form, so I do not know which controls are on it, how about:

putting a breakpoint in the code and stepping through it to see which control is giving the error, this may give you a clue as to why it fails.

OR

at the point when it fails, in debug mode in the immediate window do ? Me.Controls(i).Name to see which control it is stopping on


Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[StefanScheidegger]

Hi Ken

I have only two field on the main form which must be filled out by the user (there are also two command buttons on the main form). Is it possible to amend the code so that the code just checks whether those two main form fields are filled out and if one is not filled out it would lock the subform?

Let's say main form field 1 is called &quot;field1&quot; and main form field 2 is called &quot;field2&quot;.

Thanks Ken.

Stefan

 

[KenReay]

Hi

In that case

If IsNull(Field1) Or IsNull(Field2) Then
Produkt.Locked = True
Else
Produkt.Locked = False
End If

You would need to execute this code in the oncurrent event of teh main form, and in the after update event of Field1 and Field2.

You can obviously do this by repeating the code, but the correct way to do it would be to put the code in a subroutine and call the subroutine from each place. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK