Looking for better coding advice 3

[MHadden]

Hello All:
I need to evaluate 2 fields to add a certain # of days to a date due field. The code I am using works very well, but I will have to do this for several scenarios & thought there may be someone out there with a shorter / more efficient way of doing this. The code is below & thanks in advance for any help!
- Michael

Private Sub Command23_Click()
           
If Insurance = "Medicare" And [Orders]!HCPC = "A7030" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 3, [Orders]![Date_Last_Rec])
    
        Else

If Insurance = "medicare" And [Orders]!HCPC = "A7031" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 1, [Orders]![Date_Last_Rec])
    
        Else
    
If Insurance = "medicare" And [Orders]!HCPC = "A7032" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 1, [Orders]![Date_Last_Rec])
    
        Else

If Insurance = "medicare" And [Orders]!HCPC = "A7033" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 1, [Orders]![Date_Last_Rec])
    
        Else
    
If Insurance = "medicare" And [Orders]!HCPC = "A7034" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 3, [Orders]![Date_Last_Rec])
    
        Else
        
If Insurance = "medicare" And [Orders]!HCPC = "A7035" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 6, [Orders]![Date_Last_Rec])
    
        Else
        
If Insurance = "medicare" And [Orders]!HCPC = "A7036" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 6, [Orders]![Date_Last_Rec])
    
        Else
        
If Insurance = "medicare" And [Orders]!HCPC = "A7037" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 1, [Orders]![Date_Last_Rec])
    
        Else
        
If Insurance = "medicare" And [Orders]!HCPC = "A7038" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 1, [Orders]![Date_Last_Rec])
    
        Else
        
        
If Insurance = "medicare" And [Orders]!HCPC = "A7039" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 6, [Orders]![Date_Last_Rec])
    
        Else
        
If Insurance = "medicare" And [Orders]!HCPC = "A7046" Then
    
    [Orders]![Date_Due_Refill] = DateAdd("m", 6, [Orders]![Date_Last_Rec])
    
        Else
        

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[mharroff]

Give This A Try

Private Sub Command23_Click()
Dim MonthsToAdd As Integer
Dim SetReOrderDate As Boolean

           
Select Case [Orders]!HCPC
  Case "A7030", "A7034"
      SetReOrderDate = True
      MonthsToAdd = 3
      
  Case "A7031", "A7032", "A7033", "A7037", "A7038"
      SetReOrderDate = True
      MonthsToAdd = 1
  Case "A7035", "A7036", "A7039", "A7046"
      SetReOrderDate = True
      MonthsToAdd = 6
  Case Else
      SetReOrderDate = False
      MonthsToAdd = 0
End Select

If Insurance = "medicare" And SetReOrderDate Then
  [Orders]![Date_Due_Refill] = DateAdd("m", 6, [Orders]![Date_Last_Rec])
End If
End Sub

 

[MHadden]

Thanks for responding so quickly, I have tried it, but it keeps adding 6 months, no matter what the HCPC case is.

To further clarify what I am trying to do, I need to evaluate whether the customer has medicare and receives a product with a certain HCPC code. Medicare has certain time limits on how often certain supplies can be obtained.
I will later have to do the same scenario with BC/BS, then Medicaid, etc.

I will work with this code to see if it sparks any ideas. I really appreciate you helping get me on the right track.
- Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[Eupher]

Hi, MHadden,

In principle, mharroff's suggestion should work (the boolean seems extraneous, but Select Case is a good way to handle this), I think we just need to sort out some syntax.

What is [blue][Orders][/blue]? The form on which your code is running? Another (open) form? Have you tried setting a break point in your code and stepping through it to see where it fails?

Ken S.

 

[MHadden]

[Orders] is the subform that has the HCPC code & dates in it. The mainform has the customer information, including insurance info.

Thanks for looking at this. I originally wanted to use a Select Case, but since I need to evaluate two criteria, I didn't think it was possible - then again, maybe I just don't know how to do it???

As far as stepping through the code, I've heard about this for years, but have no clue how to do it, so the answer to you question would be no.

Anyway, Thanks again I really do appreciate it,
Michael


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[MHadden]

I got it!

I changed the 6 to MonthsToAdd.

The last line now reads

[Orders]![Date_Due_Refill] = DateAdd("m", MonthsToAdd, [Orders]![Date_Last_Rec])

And it works perfectly!

Thanks to both of you for taking the time to help!

- Michael


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[Eupher]

MHadden,

Glad you got it sorted out. For future reference, you can set a break point in your code by simply left-clicking in the little gray left margin of your VBA code window at the desired line of code. A reddish-purple dot will appear and the whole line of code will be highlighted in the same color. Another way is to place the cursor where you want to break, then click Debug->Toggle Breakpoint (or just hit F9). Then when the code runs, it will stop at this point, and you can step through the code one line at a time with the F8 key. An absolute must-use tool for debugging code!

Another useful aspect of this - while the code is paused at a break point, you can hover your mouse over variables, objects, and such, to see their current value.

HTH,

Ken S.

 

[MHadden]

Eupher,
This will most likely prove to be the most helpful advice ever!
Thanks so much for explaining this method. I will use it often, I'm sure.

Thanks Lots!
Michael


MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[ogri]

Hi

Possibly being a bit picky here, but would it not be better to have a table of months to add for insurance / hcpc, so you can easily update them in the future without having to delve into the code.

It is easy to call a function to check the data on the table via a query and return a value.

For example

Public Function GetMonthsToAdd(strInsuranceToCheck as string, strDrugToCheck As String) As Integer
Dim db As Database
Dim qry As QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set qry = db.QueryDefs("qryMonthsToAdd")
qry.Parameters("pInsurance") = strInsuranceToCheck 
qry.Parameters("pDrug") = strDrugToCheck 
Set rs = qry.OpenRecordset()  ' Open recordset on the query
If rs.EOF Then
    GetMonthsToAdd= 0
Else
    GetMonthsToAdd= rs!txtValue
End If
rs.Close
qry.Close
db.Close
End Function

With a query called of qryMonthsToAdd

SELECT Months
FROM tblInsMonth
WHERE tblInsMonth.Insurance = pInsurance
and tblInsMonth.Drug = pDrug

Table called tblInsMonth

Insurance[tab]Text[tab]Length 20
Drug[tab]Text[tab]Length 5
Months[tab]Integer

Instead of using the If statements or the case statement you could then do

[Orders]![Date_Due_Refill] = DateAdd("m", GetMonthsToAdd(Insurance , [Orders]!HCPC), [Orders]![Date_Last_Rec])

Hope that makes sense.

All the best

Keith

 

[MHadden]

Keith,
Thanks for the idea. It looks like a great idea, but I can't figure out how to make it work.
I have the function, the table & the query.
What I can't figure is how to call the function, or where to place the last lines of code you provided:

[Orders]![Date_Due_Refill] = DateAdd("m", GetMonthsToAdd(Insurance , [Orders]!HCPC), [Orders]![Date_Last_Rec])

Any thoughts about where I am going wrong?

Thanks again, Michael

MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!

 

[ogri]

Hi

Basically those 3 lines (actually one line of code, it has just overflowed onto three lines when displayed here) would replace the entire contents of the subroutine.

Private Sub Command23_Click()
[Orders]![Date_Due_Refill] = DateAdd("m", GetMonthsToAdd(Insurance , [Orders]!HCPC), [Orders]![Date_Last_Rec])
End Sub

Basically what is happening is that you are calling the function "GetMonthsToAdd" passing the type of insurance and the drug code to return the number of months to add. This should match against entries on the table, but if not found it returns zero (so you only need entries for any non standard ones). However it is doing this call as part of the assignment to "[Orders]![Date_Due_Refill]".

If you set a break point and single step through it in the code you should see how it works (and any typos I have made should be obvious!).

All the best

Keith

 

[MichaelRed]

ogri's concept is, conceptually, a better approach. You do need to however provide the mechanisims for the update of the table (assuming you are actually a professional programmer - you then should NOLT be in hte data maintenance business?). This presents almnost as many complications to overcome as just doing it. The only other issue I have with this module is the embedding of the function call within the function. While this usually does work, my 'style' guide emphasizes the readability and maintenance issues as more important than the minimization of a few lines of code. Adding a varialble, assigning the varialble to the resulta of the function call and using the variable in the DateAdd function seems,to me, to be a small price to pay for the clarity of the results. Going the next step and actually commenting the code would be an additional bonus.




MichaelRed