UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Please Help With Using A Combo Box Selection To Calculate A Date Field In A Form, Access 2010    
 
   
halltribe
post Aug 15 2019, 10:52 AM
Post#1



Posts: 19
Joined: 6-January 04



Hi all,

I'm a little rusty with MS Access these days. My query relates to MS Access 2010.

I have created a form based on a single table "Patient_Reviews" which contains a couple of combo box selectors and date fields. I would like to do the following:

Field Name: RvwDate (short date field default set to Now()
ComboBox: RvwOutcome (has several values and I have used a PK - not sure if I need to for such a small table but I tend to add one to most of my tables)
FieldName: NextRvwDate (short date field)

What I would like to do is update the NextRvwDate field automatically based on the RvwOutcome combo box selection.

I have pasted the Review_Outcome table below (I have added Response days to give an idea of the "no of days" I would like to be added to the Rvwdate field to populate the NextRvwDate)

RvwOutcomeID Rvw_Outcome Response_Days

1 Careplan Required Standard (14)
2 Careplan Required Urgent (7)
3 Careplan Not Required (180)
4 Not Applicable (180)
5 Careplan Continues (180)

i.e.
Review Date = 15/08/2019, a "Standard" selection would return 29/08/2019
Review Date = 15/08/2019, an "Urgent" selection would return 22/08/2019

I hope that this makes sense and thank anyone in advance for taking the time to read this post and view my database.


Attached File(s)
Attached File  UtterAccess_DBase_Query.zip ( 213.82K )Number of downloads: 4
 
Go to the top of the page
 
theDBguy
post Aug 15 2019, 10:59 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,317
Joined: 19-June 07
From: SunnySandyEggo


Hi. Sounds like you'll need to add some code in the AfterUpdate event of the combo and use the DateAdd() function to calculate the date to use for your field. I would probably recommend using a Select Case statement to determine what value was selected from the dropdown to get the correct date calculation.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
halltribe
post Aug 15 2019, 02:29 PM
Post#3



Posts: 19
Joined: 6-January 04



Sorry, but I couldn't work out how to use the Select Case function involving 2 other conditions and also struggled to use the DateAdd as it involved looking up the combo box value and then looking at te review date to calculate the next review date.

I have tried the following, but had no joy.

Private Sub RvwOutcome_AfterUpdate()
If Me.RvwOutcome = "Careplan Required Standard" Then Me.NextRvwDate = Me.RvwDate + 7

End If
End Sub

If anyone can provide the correct code or supply an alternative way to achieve what I am trying to do I would be extremely grateful. The database will be heavily used and I promised the users I would automate as much of the data entry as possible.
Go to the top of the page
 
theDBguy
post Aug 15 2019, 02:32 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,317
Joined: 19-June 07
From: SunnySandyEggo


Hi. Can you post the SQL statement for the Row Source of the RvwOutcome combobox? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Aug 15 2019, 02:37 PM
Post#5


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but ...

QUOTE
I have tried the following, but had no joy.

We don't really know what that means. Did it produce the wrong results? Did the Sub error? What happened?

CODE
Private Sub RvwOutcome_AfterUpdate()
If Me.RvwOutcome = "Careplan Required Standard" Then Me.NextRvwDate = Me.RvwDate + 7

End If
End Sub

If this is exactly the code you used, you have an unnecessary "End If". Once you put the "Then Me.NextRvwDate = Me.RvwDate + 7" on the same line as the "If", the End If is no longer required and will no doubt error the sub.

Try ...

CODE
Private Sub RvwOutcome_AfterUpdate()
If Me.RvwOutcome = "Careplan Required Standard" Then Me.NextRvwDate = Me.RvwDate + 7
End Sub

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
halltribe
post Aug 15 2019, 02:55 PM
Post#6



Posts: 19
Joined: 6-January 04



My code is now working:

Private Sub RvwOutcome_AfterUpdate()

If Me.RvwOutcome = "1" Then Me.NextRvwDate = Me.RvwDate + 14
If Me.RvwOutcome = "2" Then Me.NextRvwDate = Me.RvwDate + 7
If Me.RvwOutcome = "3" Then Me.NextRvwDate = Me.RvwDate + 180
If Me.RvwOutcome = "4" Then Me.NextRvwDate = Me.RvwDate + 180
If Me.RvwOutcome = "5" Then Me.NextRvwDate = Me.RvwDate + 180


End Sub

Once again, Thank you. I appreciate the help.

I've still got lots of work to do, so am sure I'll be posting a lot in the next few weeks.
This post has been edited by halltribe: Aug 15 2019, 03:05 PM
Go to the top of the page
 
theDBguy
post Aug 15 2019, 03:11 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,317
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Aug 15 2019, 04:38 PM
Post#8



Posts: 6,076
Joined: 11-November 10
From: SoCal, USA


the code you posted works, good job, but it's inefficient in that every If statement will be executed each time the event fires. in this situation i'm sure the additional time is negligible, but it never hurts to add something new to your skill set. there are a few other ways this could be handled, but someone mentioned SELECT CASE earlier in the thread, so follows an example using it:

CODE
Private Sub RvwOutcome_AfterUpdate()

    Dim i As Integer

    Select Case Me.RvwOutcome
        Case "1"
            i = 14
        Case "2"
            i = 7
        Case "3", "4", "5"
            i = 180
    End Select

    Me.NextRvwDate = DateAdd("d", i, Me.RvwDate)

End Sub

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
halltribe
post Aug 16 2019, 03:50 PM
Post#9



Posts: 19
Joined: 6-January 04



Thank you for this alternative method.

I'll try the code you suggested. My database is on a pretty slow network so anything that speeds things up will be welcome.

The last 15 years, I've spent more time using Excel than Access (and my design experience was mainly in Access 2000 and 2003 so lots of new functions to learn). I tried to replicate a nested if function.

I found Iif but couldn't get that to work. Case select looks good!!
This post has been edited by halltribe: Aug 16 2019, 03:50 PM
Go to the top of the page
 
tina t
post Aug 16 2019, 04:02 PM
Post#10



Posts: 6,076
Joined: 11-November 10
From: SoCal, USA


you're welcome. come on back if questions. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 08:42 PM