CmdButtons and DLookUp Issue

[d9pierce1]

Hi all,


Hope everyone had a fantastic thanksgiving holiday!


I have my frmAccount which has subforms on it that are controlled by CmdButtons, Inserts different subform by
clicking buttons to change the source object....


Parent Form = frmAccount
SubForm Control = sfrmAccount
SourceObject = sfrmAccountDetail (CmdAccountDetail)


On the sfrmAccountDetail I have a CmdButton, (CmdPayment) and with that I am seeking to do a DLookUp to see if
tblReceiveBill has any records per this Account or if IsPaid = True, then.... (See Code Below)


Basicall If I click this CmdPayment, I want it to first look up to see if a record is present and if its paid, if yes
then msgbox (See below) to open up a form to add a new bill. 'Cant pay a bill that is not recorded or is paid.


If a record exist in the tbl and is not paid, then i want it to "Switch Tabs on Parent Form" to CmdViewPayBill and set focus to
to the unpaid record.


Thats It!


I have seeked different google and have worked on this past few hours with little to no success. At this point i am not even
sure if i can do this. So, before going further i would like to see if i can get some help on this....Please

Private Sub CmdPayment_Click()
    Dim x As Integer
    
 If DLookup("BillID", "tblReceiveBill", "AccountID= " & AccountID & " AND IsPaid=True") Then


    x = MsgBox("You must first Enter Bill First, Would you like to add now?", vbYesNo)
    If x = vbYes Then


    DoCmd.OpenForm "frmTransBill"
    DoCmd.GoToRecord , , acNewRec


 End If
    
 Else
    DoCmd.GoToControl "CmdViewPayBill"
    Call CmdViewPayBill_Click


  End If
End Sub

Thanks
Dave

[June7]

There is an extra End If.

Need to do 2-stage check on conditions. This means nested If Then blocks.

First, does record exist. If it does, then check for paid status.

Either do two DLookups or open a recordset object.

[Welshgasman]

As well as that, don't you have to compare the result of DLookup with something?
Could use Dcount() > 0 ?

[Micron]

There is an extra End If.

Well, I'm not seeing that. Proper indentation always helps.

Private Sub CmdPayment_Click()
Dim x As Integer
    
If DLookup("BillID", "tblReceiveBill", "AccountID= " & AccountID & " AND IsPaid=True") Then
   x = MsgBox("You must first Enter Bill First, Would you like to add now?", vbYesNo)
   If x = vbYes Then
     DoCmd.OpenForm "frmTransBill"
     DoCmd.GoToRecord , , acNewRec
   End If
Else
   DoCmd.GoToControl "CmdViewPayBill"
   Call CmdViewPayBill_Click
End If

End Sub

There's no indication in the post as to what is happening with that code. Probably errors if BillId returns text, or form switch doesn't happen if BillId is Null. That's because IF...Then is a T/F evaluation. Text is not = to T/F (thus likely type mismatch) and Null is not = to anything, so result will be false. As WGM noted, best to write that so as to get a result that is either T or F.

If the lookup is returning ID as a number data type and it cannot return Null, simply adding > 0 to the lookup should work:
...AND IsPaid=True") > 0 Then

[June7]

Yep, my bad. Missed the nested If.

[d9pierce1]

Hi all,
I have been playing with this all morning with just msgboxes and this does work. What I cant figure out is to set focas on the Primary Form to that the subform changes to CmdviewPayBill?

Basically If it doesnt meet the If criteria, then I want it to click the cmd button "CmdViewPayBill" on the primary form
Which would change it to a different subform (sourceObject in this case)
I cant figure out how to do that?

Here is what I have so far, I ' out some so it didnt open the form up all the time.

Private Sub CmdPayment_Click()
    'Dim Control As Control
    
If DCount("BillID", "qryReceiveBill", "AccountID= " & Me.AccountID & "") <= 0 Then
    MsgBox ("You must first Enter Bill First, Would you like to add now?")
ElseIf DCount("BillID", "qryReceiveBill", "AccountID= " & Me.AccountID & "AND IsPaid = True") Then
    MsgBox ("You must first Enter Bill First")
    'DoCmd.OpenForm "frmTransBill"
    'DoCmd.GoToRecord , , acNewRec
Else
    MsgBox ("PayNow")
    'Forms!frmAcctount!CmdViewPayBill.SetFocus
End If


End Sub

Thanks

[d9pierce1]

Still Testing,
It does set focus to the ViewPayBill button!
How on earth do i get it to click that button?

[Welshgasman]

Still Testing,
It does set focus to the ViewPayBill button!
How on earth do i get it to click that button?

You do not. You call the code for the click event of the button ?

[Micron]

Not really following that well, but if the code in question and the button are on the same form, just use cmdPayment_Click. If the code resides on a different form, change the button event from Private to Public and use syntax of

Form_FormNameHere.ControlNameHere_Click

Note that Form_ is what you see in the vbe objects pane. Pretty sure you need that preface.

[d9pierce1]

Thank you, Thank you, Thank you to all!
Micron, that worked line a charm!!!!!!
Bless you all for assistance on this
Dave

[June7]

Don't see how this works correctly. Second If condition is missing the =0 criteria. Also need a space in front of AND

ElseIf DCount("BillID", "qryReceiveBill", "AccountID= " & Me.AccountID & " AND IsPaid = True") = 0 Then

Suggest not annoying user with so many message boxes. I think only the first one is useful. Required action should be obvious for the other two situations without message boxes.

However, what should happen with user response to first messssage? Code does not deal with that.

If MsgBox("You must first enter Bill, would you like to add now?", vbDefaultButton1 + vbYesNo) = vbYes Then do something here

If you want user just to acknowledge message and not make a decision, don't use parentheses and they will get only an Okay button.

MsgBox "Must enter a new bill record."

Might want to correct spelling of Acctount to Account.

[Micron]

Dave, review post 4 wrt If ... Then being a T/F test. If you didn't understand it, you need to ask because it applies to any such test - e.g. ElseIf or Else included. If there is a use of the test that is something other than True or False I cannot think of it at present. So June7 is correct. If it works, it is a fluke. A zero (0) result from an expression/test can pass for False which will might work in some cases. Null result should simply act as if the test is false. Either of these issues can cause unexpected results. You really should form logical tests as being true or false in all cases.

EDIT - as for not being able to think of a test, this sort of thing does not apply:

If Me.someControlName.Visible Then

because AFAIK, If tests default to testing for True when not specified. This is the same as the above
If Me.someControlName.Visible = True Then

[June7]

Actually, on second thought, =0 might not be necessary. DCount will return 0 if there is no match (DLookup will return Null). Zero is seen as False and all other numeric returns will be treated as True. This is often why DCount is preferable to DLookup because DCount does not ever return Null and DLookup can which then must be handled.

[Micron]

Maybe one can get away with it, but I think it's bad practice. If there is a zero result, the test becomes

ElseIf 0 Then

The action to be taken is then entirely dependent upon zero not being True, which of course, it isn't. I suspect we've seen multiple threads where less than perfect code from older versions stopped working in newer versions of Access, and it wasn't because of references, dropped features, Trusted Locations and so on.
All that is just my opinion for sure.