mandatory field on subform - possible?

[MontyBurns]

Hi,

I have a main form, with a subform. There is only one field on the subform. When you add a new record, I need to make the field in the subform mandatory. I.e. when I add a new main record, I want the user to have to enter something in the field in the subform.

Seems like this should be easy, but I can't see an easy way of doing this.

Anybody have any ideas?

Thanks,
Burns

 

[Bastien]

what about setting the required property to true in the table design view? Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[MontyBurns]

done that anyway, but it doesn't solve my problem. I have a feeling I haven't explained myself very well - i'll try again:

When the main form record is added, all the fields which are on the main form, and therefore from the main table, are filled in by the user. But if the user doesn't actually stick something in the field on the subform, the .AddNew method will not get called for the sub form table, so the Required property doesn't even come into it.

I suppose what i'm going to have to do is write some code so that when .AddNew is called on the main form, .AddNew is called on the subform.

Any tips anyone? Or even code you've already got working?

Thanks,
Burns

 

[easyedd]

Couldnt u just trap whether the field on the subform is NULL before you apply the .AddNew code?

 

[jsolutions]

Monty - trap the AfterInsert event of your main form. Then test the value of the textbox on the subform. If it is null, prompt the user to enter a value. You might have to navigate the main form back to the prior record.

OR- you could trap the AfterUpdate of the last field on the main form (assuming it is required) and do the same things.

These are just some ideas. Try them and let me know if you have any questions or need to go another direction.

Jay

 

[MontyBurns]

yeah, reckon i'll do that. I was really hoping to not have to do any coding as this is one instance of a generic DB, used in about 20 depts. Looks like i'm just going to get stuck in and code it though!

Thanks,
Burns

 

[jsolutions]

Yes, there is no way (without coding) to do this. The main reason, as you stated earlier is that this is on a subform, so the required property is not affected until you enter the subform.

Let me know if you have any problems trapping the error.

Also, I am a little confused about the generic db used in 20 departments comment. Do you mean there are 20 different dbs floating around that will have to have this code added? Do all of the dbs do the same thing?

 

[MontyBurns]

apologies, I had to leave this alone for a while.

I'm having trouble stopping the user from navigating back to the previous record. I'm using the main forms AfterInsert event but it still saves the rec and moves to the prev rec. Here's my code:

Private Sub Form_AfterInsert()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Purpose: Makes record in frmPracticeSubGroup mandatory
' by catching the new record insert in this main
' form and checking for the sub record value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim intSubPractice As Integer

intSubPractice = Me.Form![frmPracticeSubGroup]![cbSubpractice]

If intSubPractice = 0 Then

MsgBox ("You must enter a Practice Sub Group&quot
DoCmd.CancelEvent

Else
MsgBox ("Fine&quot
End If

End Sub

It does cancel the AfterInsert event i'm assuming, but i'm not sure what exactly is happening after that. How do I stop the user from moving record until he/she has entered a record in the subform?

Thanks in advance,
Burns

ps - to answer your qs about the numerous instances of this DB: each dept has one to hold what we call 'KnowHow' on particular areas of law. We're currently moving them all into one system (either in house, custom SQL DB with web front end, or a outsourced package). The number of them has grown from an initial 5 or so, and this code will only have to be added to the Finance dept one, which I look after, because it's at their request. The basic schema etc doesn't change in doing this, so it remains largely a generic design.

 

[jsolutions]

Hey Monty - I told you the wrong place to trap. Use the Before Update event - this fires before your record is saved (new or updated). If you use the CancelEvent the way you have here, it will do what you want.

Try it and let me know.

Jay

 

[MontyBurns]

thanks Jay.

It seems it's not so simple though. There are loads of subforms on the main form, and each time you try to enter any of them, the main forms BeforeUpdate event fires and cancels the action.

Not sure where to go now - any ideas anyone?

Thanks,
Burns

 

[MontyBurns]

don't know if I made that clear - you can't actually get into any of the subforms if I put this code behind the main form:


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intSubPractice As Integer

intSubPractice = Me.Form![frmPracticeSubGroup]![cbSubpractice]

If intSubPractice = 0 Then
MsgBox ("You must enter a Practice Sub Group&quot
DoCmd.CancelEvent
End If
End Sub

 

[jsolutions]

Monty - this is a hard one. Basically, you need to trap the event for moving from one record to another on the main form. I looked through MSDN and could not find the events that are associated with the record navigator on a form.

I came up with a solution that is relatively easy and will do what you want but don't know if they will fit in to your design.

Basically, create your own navigation buttons on the main form. With a little time and effort, you can actually make them look like the real navigation buttons.

Once you do this, you can easily trap the On Click (or any other event) for the buttons. Then you will simply test the control on the subform as you did before :

Dim intSubPractice As Integer
intSubPractice = Me.Form![frmPracticeSubGroup]![cbSubpractice]
If intSubPractice = 0 Then
   
    MsgBox ("You must enter a Practice Sub Group")
    DoCmd.CancelEvent
Else
    MsgBox ("Fine")
End If

And use the DoCmd.CancelEvent for the navigation button. This will acheive the results you are looking for. Note If you choose to go this route, you will need to turn off the form's navigation buttons.

I will keep looking in MSDN, but let me know if this solution is possible for you to do.

Also, another possiblity is to use the ActiveX control for record navigation - I have never used it, but in looking at it, it has trappable events that you could use. It also looks very similar to the regular navigation buttons. The only issue would be ActiveX control registration that can be a pain sometimes.

Let me know your thoughts on this solution.

Jay

 

[MontyBurns]

thanks for all your suggestions Jay, I really appreciate your time.

Unfortunately, due to the generic nature of the DB etc, neither of these solutions will be possible. I did think this was going to be difficult, so 'they' do know that it may not be possible.

Just had a thought though: if I put some code in the OnCurrent event of the main form, checking for Me.NewRecord (think that's right off hand), can I programatically call the .AddNew method on the subform? This way I could make the field required in the sub table design and just let Access itself take care of making sure the field is entered.

Any thoughts on this?

Cheers,
Burns