change code

[awake2424]

 If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# Then
        Me.Date_Received = Me.Date_Received + 1
        End If
If Me.Test = "LR" And Me.Time_Received >= #12:00:00 PM# And Me.Day_Received = "Friday" Then
        Me.Date_Received = Me.Date_Received + 3
        End If
If Me.Test = "HR" And Me.Time_Received >= #12:00:00 PM# Then
        Me.Date_Received = Me.Date_Received + 1
        End If
If Me.Test = "HR" And Me.Time_Received >= #12:00:00 PM# And Me.Day_Received = "Friday" Then
        Me.Date_Received = Me.Date_Received + 3
        End If

The above code works great, but if the user changes the test from LR to HR the Date_Received increments by 1. How do I prevent that from happening. So if the user changes the Test from LR to HR the Date_received in unchanged. Thanks.

 

[vbaInet]

This code never ends does it awake2424.

I don't think I follow what you're saying. Can explain differently.

 

[awake2424]

It seems whenever I think I have it, a new issue arises or something changes that wasn't accounted for. I really appreciate your help and patience .

So, on the attached database when you enter some text into the HLA ID field most of the fields in blue will populate. The Date Received is one on them. Then once the Test is selected from the dropdown the Due Date will calculate. But lets say the user accidentally chose the wrong test, when the test is changed the Date Received increments by one day, instead of remaining unchanged.

If the Test LR is selected and the Date Received is 8/6/2014, if the user accidentally selected the wrong test, when the correct it the date received now increments by 1 instead of staying the same. Thank you.

 

[vbaInet]

The form is linked to a Sharepoint linked list so you need to upload a local copy.

Also explain in bullet points how to replicate the problem. I didn't understand what you said in your last post.

 

[awake2424]

I attached a local copy of the database.

Step 1: in the HLA ID field enter text or a number
a bunch of fields will populate and one of them is Date Received which will be 8/7/2014.

Step 2: in the Test select "LR" from the dropdown and that will calculate a Due Date (which is a field).

The problem is that there is a condition where if a sample is received after 12:00 PM then 1 day is added to the Date Received. The line of code that controls that is in post 1. If the user accidentally selects the wrong test and then changes it and it is after 12:00PM then 1 day gets added to the Date Received. If you perform the two steps above before 11:59AM then everything works fine. But if it is after 12:00PM 1 day is added and that shouldn't happen. I hope this helps.

It may be more clear if you run a record through before 11:59AM, then another one after 12:00PM to illustrate the issue. Thanks.

 

[awake2424]

Are you able to see the issue? Thanks.

 

[JHB]

By changing the receiving date you create more or less false information/ data.
Instead, you should create a field that indicates when the test has to start, calculated from the date of receiving.

 

[vbaInet]

If the user accidentally selects the wrong test and then changes it and it is after 12:00PM then 1 day gets added to the Date Received. If you perform the two steps above before 11:59AM then everything works fine. But if it is after 12:00PM 1 day is added and that shouldn't happen.

It's doing exactly what you coded it to do.

If you want a fixed date from the very first point the record was created then fix the date once. Set the date once and test using IsNull(), if it's Null, don't run the code, otherwise run the entire code.

By the way, I have to say that your code writing style is odd. I wonder why you indent code in such a way?

 

[awake2424]

I am not sure I follow, can you please provide an example? I am not a programmer, rather a scientist who is trying to learn. Thanks a lot .

 

[vbaInet]

Maybe we could trade some code for some experiments?

See attached, not tested but it should do what you want. And please note my comments within and the code structure.

 

[awake2424]

If the Time_received is after 12:00PM the Date_Received doesn't seem to increment by 1 day.

For example a sample received at 1:25PM on 8/11/2014 should be changed to 8/12/2014 because the time is >= 12:00PM. Thanks.