Require subform to have an entry

[kbrown]

I have searched the forums and tried several suggestions already posted to try to solve my issue, but have been unsuccessful. What I would like to do is require a subform to have an entry.

My main form (frmInfantFeedingGuide) has a subform (sbfrmIFGLiquids). The subform has one control (Liquids). I used this setup to allow users to enter multiple liquids (milk, water, soda, etc.) for each Infant Feeding Guide. There are also options for none and no response. I would like to make sure that at least one of these item has been selected.

I tried using the IsNull function on the BeforeUpdate event of the subform. But I am able to tab into the subform and back out again without triggering my message box.

If IsNull(Liquids) Then
Cancel = True
Me.Liquids.SetFocus
MsgBox "Liquids is blank. Please make an entry", vbOKOnly
End If

I am wondering if the problem (or at least part of it) is that no entry at all has been made yet? I did try replacing the IsNull function with the IsEmpty function, but couldn't make that work either.

Am I on the wrong path completely? Can I do this with the way my forms are set up? Would using a multi-select listbox make checking for nulls easier (given that I have a very basic level understanding of VBA and am not 100% sure how to create a multi-select listbox although I have seen instructions)? Any suggestions would be appreciated!
Thank you
Kristen

 

[RichO]

I think I came up with a solution:

First, go into your main module and set up a global variable

Public gFlag As Boolean

Then in design mode, click once on your subform and set up the On Enter event with:

gFlag = False

And in the On Exit event:

If gFlag = False Then
Cancel = True
MsgBox "Liquids is blank. Please make an entry", vbOKOnly
End If

Then click on the upper left corner of the subform and in the On Dirty event:

gFlag = True

Now when a user enters the subform, the flag is set to false and until something is typed, the flag remains false. The only problem is that a user can type something, then backspace over it, and be able to exit the subform with an empty control.

I'm sure there is a better/easier way to do this but since you've had no repsonses up to now, I thought I'd give it a shot. Hope it works for you.

 

[kbrown]

Thank you for the suggestion, I wouldn't have ever thought of that.

I tried it this morning and it sort of works. There are two issues. One, when I have a new record, the code doesn't seem to be triggered by tabbing through the subform. Two, when I have an existing record with an entry, the code is executed when I tab through the subform. This is leading me to believe that the OnEntry event for the control on the subform doesn't happen unless there is an entry made into the control, i.e. that tabbing through the subform doesn't "enter" into the control.

I did try the same code, but with On Activate/Deactivate of Subform and also with GotFocus/LostFocus. Neither of these worked either.

I am wondering if part of the problem is that the control is in continuous datasheet view?

Any ideas?

 

[RichO]

When I referred to the On Enter and On Exit events, are these the only two events that show in the event properties box?

If you click only once on the subform, you will get only these 2, which are actually part of the main form. If you click more than once, you'll get all of the actual form properties for the subform which also has an On Enter and On Exit, but they are different events.

Is your subform continuous, and is it bound to a table?

 

[kbrown]

I did have the code in the wrong place. Now it's in the correct place. And it does work!

The only thing that I am worried aobut is if someone enters the subform after the data has been entered. They will be forced to make another entry before they can leave that field. I guess I could change the error message to allow for OK and Cancel and count on training the users to always make sure an entry is made. Around here that might be playing with fire, though.

My subform is continous and it is bound to a table.

Thank you for all your help!

 

[RichO]

Is your subform's table linked to a table in the main form in such a way that you require each record in the main form to have at least one entry in the subform?

If this is the case, you could always check for a subform entry by looking directly at the table with DLookup or DCount.

 

[kbrown]

I tried to set the tables up so that the main form required at least one entry in the subform. But maybe I missed something? Here is my table structure (this portion, anyway):

DDLiquids
LiqID
Liquids

tblIFGLiquids
LiqID
IFGID

tblInfantFeedingGuide
IFGID
lots of other unrelated fields

I spent a couple hours yesterday trying to understand DCount and how to apply it to my situation, but couldn't get it to work. I'm not sure if it is a structure issue in my tables or syntax, or something else all together. I didn't get any error messages, just didn't get the response I was looking for.

This is along the lines of the code that I was trying:

If DCount ("LiqID", "tblIFGLiquids") = 0 Then
Cancel = True
MsgBox (insert message)
End If

I can't find where I wrote down the actual code and I've since replaced it with another method I was trying (I may have even saved it somewhere, but given the state of my pea brain yesterday, it's doubtful).

In my DCount research, I also came upon a different method, that I believe you proposed: Using Count of LiqID in the footer of the subform and referencing that text box in the footer of the mainform:
=IIf(IsNumeric([sbfrmIFGLiquids].Form!IFGLiqCount),[sbfrmIFGLiquids].Form!IFGLiqCount,0)

Then I used the following code:

Dim Msg, Style, Title, Response
Msg = "Liquids is blank. Make a selection." ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Missing Information: Liquids" ' Define title.

Me.IFGLiqCount.Requery
If Me.IFGLiqCount = 0 Then
Response = MsgBox(Msg, Style, Title)
End If

And I think this would work great, if I could get the Requery to work. Once I make an entry the count is still 0 (I think because I can't get past my error message for Access to actually add the entry to the table).

So now I'm stuck. Maybe DCount would be better, but I'm a little lost with how it should work.

Sorry this is so long....I appreciate any more help you can give!

 

[RichO]

You would need to specify a criteria in the DCount, which would be the unique identifier that links the subform to your main form. I assume this is LiqID.

If DCount ("LiqID", "tblIFGLiquids", "[LiqID] = " & Me.txtLiqID) = 0 Then

I'm referring to the name of the control in the MAIN form that holds the LiqID field as txtLiqID. Not the name of the field, but the name of the text box control. The form wizard always names them the same but it's not recommended. If you do not have an actual text box set up for this field on the main form, insert a non-visible text box bound to that field.

This checks for the existence of LiqID in the subform's underlying table, that matches LiqID from the main form and that would determine if an entry has been made to the subform for the existing record on the main form.

See if that helps.

 

[kbrown]

I'm sorry, but I'm not sure that I understand. I don't have a txtLiqID on my Main form, because I need the control to reflect multiple entries. When I do put a txtLiqID control on my Main form with the control source =[tblIFGLiquids]![LiqID], I get #Name? Is that what you meant to do? I'm feeling really dense on this subject. It's probably something really simple, but I'm just not getting it.

I am also getting this error when the code is run: run-time error 2447: invalid use of the .(dot) or ! operator or invalid parentheses. If have this code set up OnExit of the Subform:

If DCount("LiqID", "tblIFGLiquids", "[LiqID] =" & Me.txtLiqID) = 0 Then

I have been able to use our discussion to successfully use DCount on a report, so it has been helpful. I appreciate your time, can you see where I am going wrong? I'm not sure what other information might be helpful in pointing out my error(s).

Thanks

 

[le888]

I think the simple way to do this is :

1) created a count field in the subform i.e created an independent field. Give this field a name say " txtcountsubLiq" and in the control source : =Count("fieldName?") (that you want to count)

2) goto the main form, created the an independent field. Give this field a name say "txtcountLiq" and in the source control : click on the gerenator code ( i.e. three little point beside the source control) this will give you the path way that you want to referer to "txtcountsubLiq"

3) in the condition stament : if (txtcountLiq) = 0 then
MsgBox "Liquids is blank. Please make an entry", vbOKOnly ......

4) to hide the count fields, put in the visible = No

 

[kbrown]

Thanks, I did try the Count in the footer method. The only problem is that I couldn't get the counter to update when I make an entry into the subform, so I got stuck in an endless loop of "you must make an entry". Where would you put the code? I had it in the Exit event of the subform, but I think that may have prevented the entry from being generated.

 

[le888]

Try this

Put on the subform ;

in activation control : Me.Recalc

Le

 

[kbrown]

Yeah, it's working! Thank you for all the help! The code I am using is below. The first one is in the main form in the exit event for the subform. The second set of code is in the subform in the after update event for the form.

Private Sub sbfrmIFGLiquids_Exit(Cancel As Integer)

Dim Msg, Style, Title, Response
Msg = "Liquids is blank. Make a selection." ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Missing Information: Liquids" ' Define title.

If Me.IFGLiqCount = 0 Then
Cancel = True
Response = MsgBox(Msg, Style, Title)
End If

End Sub


Private Sub Form_AfterUpdate()
Me.Recalc
End Sub