IF statement

D

dcjones

Dereck
Local time
Today, 00:19
Joined
Mar 10, 2004
Messages
108
Hi all,

I have the following code: -

Private Sub Command58_Click()
' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo54 is set to "yes" or "No" if No'
Else
If (Comb054) = "No" Then
DoCmd.OpenReport "rptInvoice"
DoCmd.Close
Else
'COMMENT if Yes'
If (Comb054) = "Yes" Then
DoCmd.OpenForm "frmDifferentShippingAdd", , , , acFormAdd
End If
End If
End If

End Sub

What I am trying to do is test if the "completion_date" has data, If no then open the "frmCompletion_date" form.

If yes the move on and check the "Combo54", if it is set to "No" the open "rptInvoice" report but if set to "Yes" do not open "rptInvoice" but move to and open the "frmDifferentShippingAdd" form.

I have been playing with this for hours.

Can anyone advise please.

Kind regards
 
dcjones said:
Hi all,

I have the following code: -

Private Sub Command58_Click()
' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo54 is set to "yes" or "No" if No'
Else
If (Comb054) = "No" Then
DoCmd.OpenReport "rptInvoice"
DoCmd.Close
Else
'COMMENT if Yes'
If (Comb054) = "Yes" Then
DoCmd.OpenForm "frmDifferentShippingAdd", , , , acFormAdd
End If
End If
End If

End Sub

What I am trying to do is test if the "completion_date" has data, If no then open the "frmCompletion_date" form.

If yes the move on and check the "Combo54", if it is set to "No" the open "rptInvoice" report but if set to "Yes" do not open "rptInvoice" but move to and open the "frmDifferentShippingAdd" form.

I have been playing with this for hours.

Can anyone advise please.

Kind regards
Click to expand...

Code: 
Private Sub Command58_Click()
' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
    DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo54 is set to "yes" or "No" if No'
[B]Else[/B] ----> I[COLOR="Red"] would remove this.  I don't think you need that. It should go straight into the nested if statement. [/COLOR]
If [B](Comb054)[/B] = "No" Then
    DoCmd.OpenReport "rptInvoice"
    DoCmd.Close
Else
'COMMENT if Yes'
If [B](Comb054)[/B] = "Yes" Then
    DoCmd.OpenForm "frmDifferentShippingAdd", , , , acFormAdd
End If
End If
End If

End Sub

Why are your combo boxes in parentheses. Why not call them me.Comb054 and me.Comb054.

Secondly, did you mean for the 'o' in combo to be a '0' instead of a 'o'. Just a thought.

Also, I don't think you need the 3rd if statment for the yes. If it is no, then yes is the only other solution. Try this.

Code: 
Private Sub Command58_Click()
' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
    DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo54 is set to "yes" or "No" if No'

    If me.Comb054 = "No" Then
    DoCmd.OpenReport "rptInvoice"
    DoCmd.Close
  Else
'COMMENT if Yes'
     DoCmd.OpenForm "frmDifferentShippingAdd", , , , acFormAdd
    
   End If
End If


End Sub

I could be completely nuts, but try that and see if that helps any.
 
Hi

Thanks for your reply, another pair of eyes works wonders.
I made changes to your points and now I have: -

Private Sub Command58_Click()
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
Exit Sub
End If
If (Combo54) = "No" Then
DoCmd.OpenReport "rptInvoice"
DoCmd.Close
Else
DoCmd.OpenForm "frmDifferentShippingAdd", , , , acFormAdd
End If
End Sub

Work fine, thank you.
 
Glad you figured it out, and posted your firnal code.

I would have kept it as nested IF statements, but each of us has our own styles.

Good luck to you,
 
Hi Again. back with the same problem.

Hi

Thanks for your reply, another pair of eyes works wonders.
I made changes to your points and now I have: -

All I am trying to do is check to see if the "frmCompletion_date" field has data, if so check if "combo104" is "No" or "Yes".
If "No" print the report "DoCmd.OpenReport "rptInvoice" but if "Yes" open another form "DoCmd.OpenForm "frmAlternativeShippingAddress"


Private Sub Command58_Click()

' COMMENT If the "completion_date" is null open form "frmCompletion_date, This part works'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo104 is set to "yes" or "No" if No, this part does not work'
Else
If Me.Combo104 = "No" Then
DoCmd.OpenReport "rptInvoice"
DoCmd.Close
Else
'COMMENT if Yes'
If Me.Combo104 = "Yes" Then
DoCmd.OpenForm "frmAlternativeShippingAddress", , , , acFormAdd
End If
End If
End If
End Sub

Can anyone see why this does not work, many thanks
 
Last edited:
dcjones said:
Hi Again. back with the same problem.

Hi

Thanks for your reply, another pair of eyes works wonders.
I made changes to your points and now I have: -

All I am trying to do is check to see if the "frmCompletion_date" field has data, if so check if "combo104" is "No" or "Yes".
If "No" print the report "DoCmd.OpenReport "rptInvoice" but if "Yes" open another form "DoCmd.OpenForm "frmAlternativeShippingAddress"


Private Sub Command58_Click()

' COMMENT If the "completion_date" is null open form "frmCompletion_date, This part works'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo104 is set to "yes" or "No" if No, this part does not work'
Else
If Me.Combo104 = "No" Then
DoCmd.OpenReport "rptInvoice"
DoCmd.Close
Else
'COMMENT if Yes'
If Me.Combo104 = "Yes" Then
DoCmd.OpenForm "frmAlternativeShippingAddress", , , , acFormAdd
End If
End If
End If
End Sub

Can anyone see why this does not work, many thanks
Click to expand...

Break the code on the If statement and then walk through it. That way you can see what it is showing when select 'No'.

Also in your combo box, are you binding the yes and no? If you are, are you actually binding the No or a primary key number. If you are, make sure it is reading it as no and not as the primary key number.

That is why it is good to go into break mode and watch your code run.
 
Hi selena, ,

Thanks for coming back to me so quickly.

I am new to this has you can tell from my basic questions, but a I am learning fast (I think).

I was woundering how you can follow what is happening in the code when it is run. How do I break into the code,

Many thanks for your time.

Kind regards
 
dcjones said:
Hi selena, ,

Thanks for coming back to me so quickly.

I am new to this has you can tell from my basic questions, but a I am learning fast (I think).

I was woundering how you can follow what is happening in the code when it is run. How do I break into the code,

Many thanks for your time.

Kind regards
Click to expand...


Go into the VBA code for that form. On the left side of the coding you click on it. It will place a little red stop sign looking thing there. That is called a break point.

Once you break it there, go back and run your form. When you run your form it will stop at the if statement and take you to your coding page. You then F8 through the code. It will step into the code. If you hover over each part as it is high lighted it will show you what is being stored there.

Like the if statment for your first combo box, hover over it when your mouse over the 'No". It will either pop up No or Yes, if it doesn't you know that is the problem.

You can also F1 in the VBA screen and search for break points and stepping through code.

play with it, you seem like smart bloke, you'll get it.

I'm attaching a word doc that shows that a break point looks like.

HTH
selena
 

Attachments

Hi selena,

Your a star. Now I can follow and check the code as I create it. I knew there must be some way of doing this.

What can I say, only many thanks.

Kind regards
 
dcjones said:
Hi selena,

Your a star. Now I can follow and check the code as I create it. I knew there must be some way of doing this.

What can I say, only many thanks.

Kind regards
Click to expand...


Very welcome, and let me know what comes of it. Sorry I couldn't be of more help by telling you exactly what is wrong. But i promise you, you will get more out of what I just showed you than if I have done it for you.

Good luck!!
;) :)
 
Hi,

This is great. all fixed and working and 100% down to you for giving up your time to point me in the right direction.

I followed what you told me and I found that the combo box was enpty (Null)
so I changed the code to :-

Private Sub Command58_Click()

' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo104 is set to "yes" or "No" if No'
Else
'It's here where the problem was, I was testing for the string "Yes" or "No" when I should have been testing for "1" or "2". Now it works.
If Me.Combo104 = "2" Then

DoCmd.OpenReport "rptInvoice"
DoCmd.Close

'COMMENT if Yes'
Else

DoCmd.OpenForm "frmAlternativeShippingAddress", , , , acFormAdd
End If
End If
End Sub

Thanks again, The internet is a non human place but if your male or female it's people like you that make the world a better place.

Many thanks agian from England.

Kind regards
 
dcjones said:
Hi,

This is great. all fixed and working and 100% down to you for giving up your time to point me in the right direction.

I followed what you told me and I found that the combo box was enpty (Null)
so I changed the code to :-

Private Sub Command58_Click()

' COMMENT If the "completion_date" is null open form "frmCompletion_date'
If IsNull(completion_date) Then
DoCmd.OpenForm "frmCompletedDate"
'COMMENT if the "completion_date" has date then check if "Combo104 is set to "yes" or "No" if No'
Else
'It's here where the problem was, I was testing for the string "Yes" or "No" when I should have been testing for "1" or "2". Now it works.
If Me.Combo104 = "2" Then

DoCmd.OpenReport "rptInvoice"
DoCmd.Close

'COMMENT if Yes'
Else

DoCmd.OpenForm "frmAlternativeShippingAddress", , , , acFormAdd
End If
End If
End Sub

Thanks again, The internet is a non human place but if your male or female it's people like you that make the world a better place.

Many thanks agian from England.

Kind regards
Click to expand...

You are very welcome.

I don't know bout you, but I LOVE to watch the code run in the back ground. I learn alot about coding by breaking other peoples code and watching it run. Yeah, I'm crazy, but you have to be, to be a female in this field. I'm quit proud to be apart of the small female group in the IT field, along with Pat, Tess, Cindy and Lisa. :D

I am so glad you figured out the problem, and fixed it. If I can help you, I'll do it again in the future.

BTW, you keeping talking all nice to me like that you're gonna make me blush.:o
 
You must log in or register to reply here.

Similar threads

Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
604
Jason Lee Hayes
Jason Lee Hayes
A
Filter Records
Replies
1
Views
227
June7
June7
D
Solved Unbound form required using VBA
Replies
9
Views
637
Pat Hartman
P
C
Checking if records exist
Replies
12
Views
529
cheekybuddha
cheekybuddha
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
306
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom