Two word form coding problem

[Timoty]

This is a stupid question but one I can't remember the answer two. How do I fix this line due to the fact that my form name is two words. It won't recognize the "officer comments" form because it is two words.

Tried officer_comments
tried (officer comments)
tried [officer comments]
tried "offcier comments"

Forgive me...I'm self taught and if I could start over again I would but this is a very large databse and if I rename the form it will effect code in a 100 other pklaces.

 

[Timoty]

da forgot the line

If Response = vbYes And (Forms!Officer Comments![Expire]) Is Not Null Then

 

[adam_fleck]

Does this work?

If Response = vbYes And Not IsNull(Forms("officer comments")("expire")) Then

This should work, I think. I've never really used the "Bang" syntax (Forms![o c] ...) although there should be a way of getting it to work. It could also be that [officer comments] was OK and the problem was with IS NOT NULL. I've never tried that in code, and have always used the isNull() function instead. I'll check that out sometime.

 

[Timoty]

unfortunately no

I have never used the is not null before either.

I want one thing to happen if the field is not null and something else if it is null.

 

[Rich]

Where are you running this code from?

 

[adam_fleck]

Not isNull()

Hi Timoty,

I guess the line I suggested didn't work for you. Sorry about that.

I think something along the following lines definitely should work:

If Response = vbYes Then

    If IsNull(Forms("officer comments")("expire")) Then
         
        'Put code here for what to do if "expire" is Null
        
    Else

        'Put code here for what to do if "expire" isn't Null

    End If

End If

 

[Timoty]

Would work but

That would work but I have three options...here is the code
The user inputs an expiry date, selects a tool from the inventory and after so a messsage pops up confirming the issuance of the tool to that person.

If the expiry date is missing, it will prompt you for an expiry date

if you select no, nothing changes and the form is reset.

This is just one small part of a larger form

This is my most recent failed attempt

Private Sub ocpick_AfterUpdate()

Dim msg, Style, Title, Response, MyString
Style = vbYesNo + vbDefaultButton1
Title = "Import OC Number?"
msg = "By Pressing YES, You will import this OC serial number and mark it as taken in the inventory. Continue?"
Response = MsgBox(msg, Style, Title)

If Response = vbYes And IsNull(Me.Expire) Then
MsgBox "You must Enter an Expiry Date First", vbOKOnly, "No Changes Made"
[Forms]![Officer Comments]![ocpick] = Null
Forms![Officer Comments]![Expire].SetFocus
End If

If Response = vbYes And (#" & me.expire & "#") Is Not Null Then
[Forms]![Officer Comments]![Spray] = [Forms]![Officer Comments]![ocpick]
[Forms]![Officer Comments]![OCSub]![Taken] = [Forms]![Officer Comments]![Officer Name]
[Forms]![Officer Comments]![OCSub]![Expire] = [Forms]![Officer Comments]![Expire]
[Forms]![Officer Comments]![ocpick] = Null
End If

If Response = vbNo Then
MsgBox "You have chosen to cancel this OC serial number import", vbOKOnly, "No Changes Made"
[Forms]![Officer Comments]![ocpick] = Null
[Forms]![Officer Comments]![Expire] = Null
Forms![Officer Comments]![Officer Name].SetFocus
Cancel = True
End If


End Sub

 

[adam_fleck]

how about this?

Maybe this would be worth a try:

Private Sub ocpick_AfterUpdate()

    Dim msg, Style, Title, Response, MyString
    
    Style = vbYesNo + vbDefaultButton1
    Title = "Import OC Number?"
    msg = "By Pressing YES, You will import this OC serial number and mark it as taken in the inventory. Continue?"
    Response = MsgBox(msg, Style, Title)

    If Response = vbYes Then
        If Not IsDate(Me.expire) Then
        'Repsonse is YES but Me.expire doesn't contain a valid date so,
            MsgBox "You must Enter an Expiry Date First", vbOKOnly, "No Changes Made"
            [Forms]![Officer Comments]![ocPick] = Null
            Forms![Officer Comments]![expire].SetFocus
        Else
        'Response is YES and Me.expire does contain a valid date so,
            [Forms]![Officer Comments]![Spray] = [Forms]![Officer Comments]![ocPick]
            [Forms]![Officer Comments]![OCSub]![Taken] = [Forms]![Officer Comments]![Officer Name]
            [Forms]![Officer Comments]![OCSub]![expire] = [Forms]![Officer Comments]![expire]
            [Forms]![Officer Comments]![ocPick] = Null
        End If
    Else
    'Repsonse is NO
        MsgBox "You have chosen to cancel this OC serial number import", vbOKOnly, "No Changes Made"
        [Forms]![Officer Comments]![ocPick] = Null
        [Forms]![Officer Comments]![expire] = Null
        Forms![Officer Comments]![Officer Name].SetFocus
    End If

End Sub

The isDate() function checks to see that "expire" contains a valid date. The function doesn't consider that Null is a valid date.

 

[Timoty]

This place is awesome

Thanks Adam Fleck....that worked like a charm.
One of these days I am going to take a bonofide VB course.
Unfortunately, even if I did take the course, I would not have the time to go back and fix all the "bad code," working as it is, that I have already created over four years.

I have learned many things from the very helpfull people here....Thanks.