NewBie Problem (1 Viewer)

[rushB]

Hello there,

I am making my first database and running into a small problem.
I created two tables which are linked via a 1:n relationship. In a form I took the main table and created a combo box with the values of the second table. Now I want that when the enduser picks a value from the combobox, Access calculates the next date. So for example when I chose "High Risk" Access will take the date ented in the box " Last Review Date" and put this value + 1 year in the field " Next Review Date"

for this I created a after update procedure

if Me.CRRB_ID_F = " High Risk"
Me.Basic_NRDate = Me.Basic_LRDate + 365
End if

if Me.CRRB_ID_F = " Medium Risk"
Me.Basic_NRDate = Me.Basic_LRDate + 720
End if

I hope someone could help me there
Stay safe

 

[arnelgp]

If Len(Trim(Me.Basic_LRDate & ""))>0 then
if Me.CRRB_ID_F = " High Risk"
Me.Basic_NRDate = DateAdd("yyyy", 1, CDate(Me.Basic_LRDate))
End if

if Me.CRRB_ID_F = " Medium Risk"
Me.Basic_NRDate = DateAdd("yyyy", 2, CDate(Me.Basic_LRDate))
End if
End If

 

[rushB]

If Len(Trim(Me.Basic_LRDate & ""))>0 then
if Me.CRRB_ID_F = " High Risk"
Me.Basic_NRDate = DateAdd("yyyy", 1, CDate(Me.Basic_LRDate))
End if

if Me.CRRB_ID_F = " Medium Risk"
Me.Basic_NRDate = DateAdd("yyyy", 2, CDate(Me.Basic_LRDate))
End if
End If

Thanks a lot for the code, I put it into the event procedure, but it doesn't seems to be working.
Any suggestions

 

[arnelgp]

did you put it on the combo's AfterUpdate event?

 

[rushB]

did you put it on the combo's AfterUpdate event?

I put it into the Last Review Date after Update Event

 

[bob fitz]

Should there be a space as the first character in:
" Medium Risk" and
" High Risk"

or should they be:
"Medium Risk" and
"High Risk"

 

[rushB]

Should there be a space as the first character in:
" Medium Risk" and
" High Risk"

or should they be:
"Medium Risk" and
"High Risk"

No there shouldn't. Just "Medium Risk" or "High Risk"

Thanks in advance

 

[arnelgp]

create a function in your Form:

Public function fncRiskDate()
If Len(Trim(Me.Basic_LRDate & ""))>0 then
    if Me.CRRB_ID_F = "High Risk"
        Me.Basic_NRDate = DateAdd("yyyy", 1, CDate(Me.Basic_LRDate))
    Elseif Me.CRRB_ID_F = "Medium Risk" Then
        Me.Basic_NRDate = DateAdd("yyyy", 2, CDate(Me.Basic_LRDate))
    End if
End If
End Function

on Last Review Date after Update Event:

Call fncRiskDate

also on combos afterupdate:

Call fncRiskDate

 

[rushB]

create a function in your Form:

Public function fncRiskDate()
If Len(Trim(Me.Basic_LRDate & ""))>0 then
    if Me.CRRB_ID_F = "High Risk"
        Me.Basic_NRDate = DateAdd("yyyy", 1, CDate(Me.Basic_LRDate))
    Elseif Me.CRRB_ID_F = "Medium Risk" Then
        Me.Basic_NRDate = DateAdd("yyyy", 2, CDate(Me.Basic_LRDate))
    End if
End If
End Function

on Last Review Date after Update Event:

Call fncRiskDate

also on combos afterupdate:

Call fncRiskDate

I created a function, but I keep getting the error " Invalid use of Me. "

 

[rushB]

 

[bob fitz]

Cross posted: https://www.utteraccess.com/forum/index.php?act=Post&CODE=02&f=53&t=2058824&qpid=2759200

Please read: https://www.excelguru.ca/content.php?184

 

[arnelgp]

you put it on the Form's module not on separate module.

 

[rushB]

you put it on the Form's module not on separate module.

would you mind telling me how to do that?
I created a new Module and the put into the different AfterUpdate Events the Call codes

Best regards

 

[arnelgp]

no, that's not the way.
open your form in design view.
you should see the Property window on the Right side.
on Property sheet, Event, click on ..., and choose, Code Builder.
if it create a Private Sub, delete the whole sub
and paste the function i gave you.
you then need to delete the sub on the Standard Module.

 

[rushB]

@bob fitz I updated the describtion on the other one

 

[rushB]

no, that's not the way.
open your form in design view.
you should see the Property window on the Right side.
on Property sheet, Event, click on ..., and choose, Code Builder.
if it create a Private Sub, delete the whole sub
and paste the function i gave you.
you then need to delete the sub on the Standard Module.

When I am on the Event Sheet, I can chose between several options ( On Current, On Load, On Click, After Update, Before Update )
Which one should I take?

 

[arnelgp]

any, and delete it afterward.
the paste the code i gave you.

 

[rushB]

any, and delete it afterward.
the paste the code i gave you.

Oh okay thanks a lot

 

[rushB]

any, and delete it afterward.
the paste the code i gave you.

I put the code as you told me to, but it doesn't seems to work. I am not getting any longer some error messages but it seems like access is not willing to do what you want to do

 

[arnelgp]

bring you form in design view.
click on the combobox.
on it's Event, AfterUpdate, add some code

private sub combo_name_AfterUpdate()
Call fncRiskDate
end sub

again, click on the Last Review Date textbox, add code
to its AfterUpdate event:

private textboxName_AfterUpdate()
Call fncRiskDate
end sub