Cancel Print Action if Sub form has No Records

[DJRhino1175]

When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -

If subfrmLotInfo Is Null Then
    MsgBox "You are required to enter your Material Lot Info", vbCritical, "No Material Lot Info"

Here is the code as a whole...

Private Sub CMBPrint_Click()
If Me.Dirty Then
      Me.Dirty = False
End If
 
If subfrmLotInfo Is Null Then
    MsgBox "You are required to enter your Material Lot Info", vbCritical, "No Material Lot Info"
 
Else
 
If Me.NewRecord Then
        MsgBox "Select a record to print", vbOKOnly, "No Record To Print"
Else
 
    DoCmd.OpenReport "OEE-RPT", acViewPreview, , "LineID =" & [LineID], acWindowNormal
    DoCmd.PrintOut acSelection, , , acHigh
    DoCmd.Close acReport, "OEE-RPT", acSaveYes
    DoCmd.GoToRecord acDataForm, "FrmLine", acNewRec
End If
End If
End Sub

My question is how do I make this work?

Thanks in advance for the help. I hope I asked the question correctly so NeoPa doesn't scold me...lol - I'm kidding my friend...

[NeoPa]

Hi DJ.

I suspect that friendly guidance from me in the past, far from disadvantaging you, has enabled more of your threads to garner positive & helpful responses ;-) As a friend, however, you're more than welcome to throw little digs my way & can expect them to bounce off my gnarly old hide with nothing but amused acceptance :-)

Let me start by pointing out that lines #2 through #4 actually ensure that any changed data currently on the Form (so necessarily a bound Form) is saved to disk. I suspect that is your intent so just making sure we're on the same page.

Moving on to the code in question - lines #6 through #9 - I would assume that you have a SubForm control on your Form which is called [subfrmLotInfo]. Please clarify at any point if any of my assumptions are invalid. Of course I understand you do your best to provide all relevant information, but that isn't always easy and you develop better skills as you proceed. So, where I need to I'll have to make assumptions ;-)

If, then, that is a reference to your SubForm, we know that can never be Null. However, and this is important, your reported error message indicates, not a failed check, but a failure even to recognise [subfrmLotInfo] as an object. So, assuming you do have a SubForm control, and we know that while Me.subfrmLotInfo may be a more appropriate way to reference it your reference would also be recognised if it were valid, it seems the name is not correct/valid in some way.

It may be that the name [subfrmLotInfo] is actually the Form object you have designed to be used within your SubForm control - rather than the name of the SubForm itself. I don't know but it's worth considering.

When you have this resolved appropriately then we still have a problem because, as stated earlier, a reference to an existing control will never be Null. We need to change that to a check of the records within the Form that is held in the SubForm control. This may be a good time to check out Referring to Items on a Sub-Form.

Unfortunately, some Forms have Recordsets that are too big to load all at once and when that happens the .RecordCount property returns 0. This means you cannot simply rely on checking that value and must be more creative to determine if there are any records.

To know which approach to use I would need some more details as to what you expect in that SubForm. The OnCurrent event of the Form itself (The one used in the SubForm I mean.) could prove fruitful. Sometimes you can check a Bound Control on that same Form if you have any which cannot be Null except for an empty record.

Perhaps you can reply back with what approach suits you best. Anyway, good luck with your project :-)

[DJRhino1175]

NeoPa:
Let me start by pointing out that lines #2 through #4 actually ensure that any changed data currently on the Form (so necessarily a bound Form) is saved to disk.
I suspect that is your intent so just making sure we're on the same page.

This is intentional, the form is bound.

NeoPa:
Moving on to the code in question - lines #6 through #9 - I would assume that you have a SubForm control on your Form which is called [subfrmLotInfo].

No this is the name of the subform...Controls are "Section", "Material" and "LotNo" - I'm guessing this is where I went wrong. I need to add a control to the code, like "subformlotinfo.section"....

NeoPa:
Please clarify at any point if any of my assumptions are invalid.

OK

NeoPa:
If, then, that is a reference to your SubForm, we know that can never be Null. However, and this is important, your reported error message indicates, not a failed check, but a failure even to recognise [subfrmLotInfo] as an object. So, assuming you do have a SubForm control, and we know that while Me.subfrmLotInfo may be a more appropriate way to reference it your reference would also be recognised if it were valid, it seems the name is not correct/valid in some way.

Most likely because of no control in the code...

NeoPa:
When you have this resolved appropriately then we still have a problem because, as stated earlier, a reference to an existing control will never be Null. We need to change that to a check of the records within the Form that is held in the SubForm control. This may be a good time to check out Referring to Items on a Sub-Form.

I just need to check if there is data in the subform linked to the main form. If there isn't I need the msgbox to pop up and force them to put the info in...

I hope this clarifies what I'm looking to do. It sounded way easier in my head until I started writing the code.

[NeoPa]

Hi DJ.

Thank you for responding directly to all my questions :-) I've edited your post simply to make the quoting clearer to readers.

DJRhino:

NeoPa:
Moving on to the code in question - lines #6 through #9 - I would assume that you have a SubForm control on your Form which is called [subfrmLotInfo].

No this is the name of the subform...Controls are "Section", "Material" and "LotNo" - I'm guessing this is where I went wrong. I need to add a control to the code, like "subformlotinfo.section"....

If you read my linked article (Referring to Items on a Sub-Form) you'll know that simply referring to 'subform's is almost guaranteed to be confusing. Actually, the only item that can properly be referred to as a SubForm is the SubForm control itself. This is a control on your main form that may contain a separate Form (Such controls can & do exist without any separate Form being specified at both run-time & design time.) but is not a Form itself, but a Control obviously.

My assumption at this point is that [subfrmLotInfo] is actually a Form object that happens to be designed into use by a SubForm control on your main Form whose name we have not yet shared. It would be helpful to determine this name & share it in order to be able to continue reliably.

DJRhino:

NeoPa:
... it seems the name is not correct/valid in some way.

Most likely because of no control in the code...

Not remotely likely.
Yes, you do need to format the reference either to include a Control or otherwise determine if the Form is empty of data.
No, that is not the point the compiler error message & I were both trying to bring to your attention. The name of the SubForm control must be something other than [subfrmLotInfo]. This is important as, until you get that right, nothing else will even get to being tested.

DJRhino:
I just need to check if there is data in the subform linked to the main form. If there isn't I need the msgbox to pop up and force them to put the info in...

No kidding ;-) This is basically a re-statement of the original requirement. Not wrong, but nor does it really help at all.

We do now have the names of the bound Controls. To reference them in code we will need to know the name of the SubForm control. We will also need to select a Control, from those you've listed, that can never be Null unless in an empty record. Obviously (I hope), we also need to be clear that no navigation of records occurs before we run the test. I suspect that's pretty safe but it makes sense to raise the point for a fuller understanding.

I hope this helps you :

1. To solve your current issue.
2. To better understand what's going on so future situations are clearer in your head & thus easier to deal with.

[DJRhino1175]

NeoPa,

When I go into design mode and look at the "Name" of the control[sub form] it says "subfrmlotinfo"

I did read the article link you gave and based on my under standing I did the following:

If me.subfrmLotInfo![SectionID] Is Null Then
    MsgBox "You are required to enter your Material Lot Info", vbCritical, "No Material Lot Info"

It compiles just fine but still gives me the same runtime error. So my guess is my understanding of the issue isn't quite right.

[NeoPa]

Hi DJ.

Let's see if we can build this up from what we have then. I will assume, as this was not clarified or confirmed in your latest reply, that :

1. The Control on the Form you use within your SubForm is called [LotNo] and that if a record exists then the value of this Control can NOT be Null.
2. At the point when you are running this code the SubForm and its Form are running normally and no record navigation has occurred.

We now know that the SubForm control is named [subfrmLotNo] so checking the value of the [LotNo] control for Null should work reliably. The code you'd need for that woul be :

If Me.subfrmLotInfo.Form.Controls("LotNo") Is Null Then

Obviously, if the names of any objects are not as we've been led to believe then all you need to do is amend that part in the code to match your actual names. For instance, if the Field is [LotNo] but your actual control, reported in post #3 as that, is actually [txtLotNo], then simply replace one with the other. Ultimately, make sure the names in the code exactly match those actually in your project.

You can even abbreviate the code by using bang (!) references in place of the fully-specified dot (.) notation I've used, but I suggest you only try that if/when you understand what the changes mean.

I hope you enjoy your weekend :-)

[DJRhino1175]

NeoPa,

I updated the code to the following:

If Me.subfrmLotInfo.Form.Controls("LotNo") Is Null Then

It compiles just fine, but when I run it I still get the same "Runtime Error of 424: Object Required". I go to debug and it highlights the line of code above.

I verified on the subform there is a text box with the name of "LotNo"

[NeoPa]

Hi DJ.

Ah. Silly me. An object reference can, itself, be checked for Null. This means the code will assume that is the test, when really we only want to test the .Value. Try this instead :

If Me.subfrmLotInfo.Form.Controls("LotNo").Value Is Null Then

It's a bit harder when you don't have the code in front of you to test, but hopefully that's the right solution this time :-)

[DJRhino]

NeoPa,

Nope same Runtime error of 424...

[NeoPa]

Hi DJ.

Now I've really hit that Doh! moment. Is Null is SQL syntax - not VBA. When working so heavily in both it's easy to get them confused :-(

The Is Operator in VBA is for comparing/checking one object against another. Using it with Null is not valid. Instead, if we want to check for Null, we use the IsNull() Function. See if this works for you :

If IsNull(Me.subfrmLotInfo.Form.Controls("LotNo").Value) Then

You may even get away with dropping the .Value part at the end, but check first.

Sorry I misled you along the way. Hopefully this just works for you :-)

[DJRhino]

NeoPa,

This worked perfectly. No worries about any misleads...It does help me learn things that way to....

I so appreciate your help in this matter.

DJ Rhino

[DJRhino]

NeoPa,

There is one more step I need to add to this. I would like to set focus to SectionID on this subform.

I tried:

Me.subfrmLotInfo.Form.Controls("SectionID").SetFocus

[NeoPa]

Hi DJ.

Let's try to keep this to one question per thread if we can.

In this case your question doesn't make clear exactly what happened when you tried that so I'll guess it didn't work and if more detail is required then I'll let you continue by creating a new thread for the question.

I would start by making the SubForm control itself active. If you further need to select a Control on the Form contained within the SubForm then what you tried earlier could be run after that.

To make the SubForm active try :

Call Me.subfrmLotInfo.SetFocus()

[DJRhino]

NeoPa,

Worked perfectly. Thanks.

[NeoPa]

I'm pleased to hear that DJ.

Onwards & upwards.