Solved What the *&$!!@ am i doing wrong! Preventing empty textbox in a subform

[Sam Summers]

Seeing as my dementia is getting worse i just cannot solve this despite trying endless things, referring to past projects and trying numerous examples online!!!

All i am trying to do is to ensure that at textbox 'Quantity' on my 'PartsSubform' (Which is a continuous form) which is a subform on the main form 'OrderMachineParts' is not empty!

Thats all!!!!

I currently have this code (Which of course isn't working)

If IsNull Forms("OrderMachineParts").PartsSubform.Form.Quantity Then

 

[CJ_London]

Simplistically you need brackets

If IsNull(Forms("OrderMachineParts").PartsSubform.Form.Quantity) Then

But suspect how you are referencing the form is also not right depends on where your code is

If the code is on the main form, you just need

If IsNull(PartsSubform.Form.Quantity) Then

and if on the subform

If IsNull(Quantity) Then

finally this will only work on the current record of the subform, not all the records. Would need to know the context of the requirement to advise further - it may be you just need to use formatting or set a default value for example, or maybe a validation rule or perhaps you need some code in the subform form before update event to make sure the record isn't saved with no value

 

[Sam Summers]

Simplistically you need brackets

If IsNull(Forms("OrderMachineParts").PartsSubform.Form.Quantity) Then

But suspect how you are referencing the form is also not right depends on where your code is

If the code is on the main form, you just need

If IsNull(PartsSubform.Form.Quantity) Then

and if on the subform

If IsNull(Quantity) Then

finally this will only work on the current record of the subform, not all the records. Would need to know the context of the requirement to advise further - it may be you just need to use formatting or set a default value for example, or maybe a validation rule or perhaps you need some code in the subform form before update event to make sure the record isn't saved with no value

Thank you very much for your help!

Here is the full code of what i am trying to do. Its all from a 'Save' button on the subform

Private Sub SavePartsBtn_Click()
On Error GoTo SavePartsBtn_Click_Err

If IsNull Forms("OrderMachineParts").PartsSubform.Form.Quantity Then
        Dim Msg, Style, Title
        DoCmd.Beep
        Msg = "You must enter a quantity"
        Style = vbOKOnly + vbExclamation
        Title = "Tanera Asset Maintenance System"  ' Define title.
Me.Quantity.SetFocus
End
End If
    On Error Resume Next
        Dim Response, MyString
        DoCmd.Beep
        Msg = "Are these Details you entered correct?"
        Style = vbYesNo + vbQuestion + vbDefaultButton1
        Title = "Confirm Details"  ' Define title.
        Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then       ' User chose Yes.
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "OrderedPartsHistoryQry", acViewNormal, acEdit
    DoCmd.Close acForm, "OrderMachineParts"
    Forms.ViewMachine.Form.ViewPartsSubform.Requery
    Else   ' User chose No.
    End If

SavePartsBtn_Click_Exit:
    Exit Sub

SavePartsBtn_Click_Err:
    MsgBox Error$
    Resume SavePartsBtn_Click_Exit

End Sub

 

[CJ_London]

ok then all your should need is

If IsNull(Quantity) Then

but might be better to have

If nz(Quantity,0)=0 Then

since I presume a 0 value would also not be acceptable?

 

[Sam Summers]

ok then all your should need is

If IsNull(Quantity) Then

but might be better to have

If nz(Quantity,0)=0 Then

since I presume a 0 value would also not be acceptable?

I dont know whats going on but whatever i try it is not preventing empty text and also just bypasses it and jumps to the next section of code to confirm? I stepped through it all and cannot see what the problem is?!
I have to go soon but will have to come back to it later. I need a drink!

 

[Gasman]

I dont know whats going on but whatever i try it is not preventing empty text and also just bypasses it and jumps to the next section of code to confirm? I stepped through it all and cannot see what the problem is?!
I have to go soon but will have to come back to it later. I need a drink!

Well you are creating a message but not displaying it?

 

[Sam Summers]

Well you are creating a message but not displaying it?

? I don't know but i'll have a look. I just think i am too stupid to do this sort of thing

 

[Gasman]

? I don't know but i'll have a look. I just think i am too stupid to do this sort of thing

Compare first block of code to your second block of code?
For problems like this I would ALWAYS walk through the code with F8 ?

 

[CJ_London]

also looks to me like you have a superfluous End

Me.Quantity.SetFocus
End
End If

 

[Gasman]

also looks to me like you have a superfluous End

Me.Quantity.SetFocus
End
End If

I thought that as well, as I have never used that statement, but it just stops the code, so thought that was part of the 'debugging'

 

[arnelgp]

you need to do it in the Subform's BeforeUpdate Event, so you can Cancel if there is no Quantity.

 

[LarryE]

Seeing as my dementia is getting worse i just cannot solve this despite trying endless things, referring to past projects and trying numerous examples online!!!

All i am trying to do is to ensure that at textbox 'Quantity' on my 'PartsSubform' (Which is a continuous form) which is a subform on the main form 'OrderMachineParts' is not empty!

Thats all!!!!

I currently have this code (Which of course isn't working)

If IsNull Forms("OrderMachineParts").PartsSubform.Form.Quantity Then

Try:
If IsNull(Forms![OrderMachineParts]![PartsSubform]![Quantity]) Then

 

[CJ_London]

indentation is a bit messy, but think

Me.Quantity.SetFocus
End
End If
On Error Resume Next

the End If should be Else

and probably need to remove an end if further down

 

[Pat Hartman]

Almost all validation should be performed in the FORM's BeforeUpdate event. That way it doesn't matter what prompted the record to be saved. This event ALWAYS runs and it is the last event to run before the record gets saved so you can always prevent bad data from being saved if you simply just use the correct event for validation. Think of the form's BeforeUpdate event as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If it is closed, it doesn't and YOU control the flapper.

 

[mjdemaris]

After putting this code in the BeforeUpdate event...
Gasman mentioned comparing the two main blocks of code. The first IF block does not call the Message Box, so you won't get a pop-up. And, as CJ pointed out, that extra "End" will stop your code from working properly.

As for referencing... you have a direct reference "Me.Quantity.SetFocus", but above that you are trying to reference the same control in a different manner.

So, which form is the button on? From there you can determine, as mentioned in the above replies, how you will reference the "Quantity" text box.

Just my two cents...

 

[Sam Summers]

Ok finally back at work and the penny dropped in the end.
Sorry for my stupidity but thank you to all of you and your patience!

In the end i now have this:

If IsNull(Quantity) Then
        DoCmd.Beep
        MsgBox "You must enter a quantity", vbExclamation, "Tanera Asset Maintenance System"
Me.Quantity.SetFocus
Else
    On Error Resume Next
        Dim Response, MyString, Msg, Style, Title
        DoCmd.Beep
        Msg = "Are these Details you entered correct?"
        Style = vbYesNo + vbQuestion + vbDefaultButton1
        Title = "Confirm Details"  ' Define title.
        Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then       ' User chose Yes.
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "OrderedPartsHistoryQry", acViewNormal, acEdit
    DoCmd.Close acForm, "OrderMachineParts"
    Forms.ViewMachine.Form.ViewPartsSubform.Requery
    Else   ' User chose No.
    End If
End If

 

[Gasman]

Indent your code properly. It will be much easier to see errors in logic.?
Else for second If not really needed, is it?

 

[Pat Hartman]

OK but is your code now in the FORM's BeforeUpdate event?