Need Help with a Chemotherapy Database

[wolfbrother]

Hi Everyone, I have a form thats going to be used for scheduling chemotherapy appointments. I have a Datedue control, a freqency control( that is how often the patient will be getting treatment; entered in X many days) and a cycle control, how many times the patient will receive this particular treatment. What I'd like to be able to do is take the input from the user add the frequency of days to it and repeat that for the number of cycles making the code do all this automatically creating a new record for each entry that it enters, but I can't seem to get this to work at all.
Here's what I've tried

Public Function CalculateAppointments(datinidate As Date, intfrequency As Integer) As Date

CalculateAppointments = datinidate + intfrequency

End Function


then the function is fired off a command button which the user clicks to schedule the appointments based off the information he/she has entered.

Private Sub cmdscheduleapts_Click()

Dim datinidate As Date
Dim inifrequency As Integer

Set datinidate = Me![DATEDUE]
Set intfrequency = Me![frequency]

result = CalculateAppointments

Me!DATEDUE.Value = result

End Sub

As you can see I haven't even tried to get the repetion to work yet because I can't even get this to work. I know I'm probably way off on this, this is my first real attempt at using variables, Please Help!!!
Dave

 

[jjob]

Hi Dave,

A few errors here,

a) first SET is used to refer to "Objects" not ordinary variables so I removed the SETS

b) You Dim iniFrequency but refer to intFrequency - use Option Explicit at the top of your code to trap these errors, as they can be insidious!

c) You define your function with parameters, but provided no arguments in the Call to fill these parameters.

below is code that I would hope works in your database, simply copy it in to replace the original stuff.

Hope this is OK - have Fun

regards

John


************* Fixed Code to copy into your application


Public Function CalculateAppointments(datinidate As Date, intfrequency As Integer) As Date

CalculateAppointments = DateAdd("d", intfrequency, datinidate)

End Function



Private Sub cmdscheduleapts_Click()

Dim datinidate As Date
Dim intfrequency As Integer

datinidate = Me![DATEDUE]
intfrequency = Me![frequency]

Result = CalculateAppointments(datinidate, intfrequency)


End Sub

 

[wolfbrother]

Thanks SOO very much John, do you have any idea how i can make this loop to the number of times specified in the users entry field and add a new record for each loop? the field is designated as txtcycles and will be an integer, but as to where I go from there I'm still guessing at this point and the Doctors would like to have this program up and running as soon as possible. Thanks again John you're a life saver!

 

[jjob]

Hi Dave,

Below is a suggestion to show the loop for adding multiple records - but as I'm not sure quite what you have and want to add, I've only shown the place where you would ADD the records, NOT how to add them.

I would tend to build up a SQL string and use SQL and DAO to insert the data, but this may not be appropriate for you - partly because it's a developer's method, requires DAO objects to be installed on your PC and, given my lack of expertise in Access, may not be the best method.

I'm about to log off now, but will look again and see if this is of interest or if anyone has better suggestions for adding.

I've left out any error handling.


Private Sub cmdscheduleapts_Click()

Dim datinidate As Date
Dim intfrequency As Integer
dim intCount as integer '** Counter for adding records

datinidate = Me![DATEDUE]
intfrequency = Me![frequency]

'** below is the loop counter
For intCount = 1 to Me![CycleControl]

'*** below keeps updating the date variable by adding the frequency days
datinidate = CalculateAppointments(datinidate, intfrequency)

'** ADD CODE SHOULD GO HERE

Next

End Sub

regards

John

 

[wolfbrother]

Thanks again John, after I posted that last post, I managed to sit down in between making IV's for patients, answering the phone, inputting new medication orders and other such ilk, and figure out a way to do what I needed with some code. I'll show you what I did, let me know what you think. It's not exactly pretty, haven't had time to go through and make it look right;it's hard enough to think about the code amongst doing everything else, much less make it pretty. HA HA

Private Sub cmdscheduleapts_Click()
Dim intcycle As Integer
Dim datinidate As Date
Dim intfrequency As Integer
Dim result As Date
Dim intloop As Integer
Dim TIMEDUE As String
Dim drug As String
Dim LOCATION As String
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


TIMEDUE = Me![TIMEDUE]
drug = Me![IVMED1]
LOCATION = Me![infusionlocation]
intfrequency = Me![frequency]
intcycle = Me!txtcycles 'set intcycle equal to users input of cycle'
Do Until intloop = intcycle

intloop = intloop + 1
datinidate = Me![DATEDUE]


result = CalculateAppointments(datinidate, intfrequency)

DoCmd.GoToRecord , , acNewRec
Me!DATEDUE.Value = result
Me!TIMEDUE.Value = TIMEDUE
Me!IVMED1.Value = drug
Me!infusionlocation.Value = LOCATION

Loop

End Sub

 

[jjob]

Hi Dave,

I'm impressed, I built a table and form and then pasted your code in and ran it, and it worked.

Being a developer used to rolling my own code, not an Access man, I'd never have thought of using the Docmd stuff to insert in this manner, I'd write some complex SQL using DAO etc etc.

The code works, providing you remember to fill in all the fields - if you're testing this, try not filling in the frequency etc fields then click the command button and see the errors produced - you will probably get

"Invalid use of Null" errors.

I guess you'll need to trap those, so a bit of validation to ensure that fields that MUST be filled are filled would help, I suspect you would come up with a more access friendly way than I would, but a really crude example would be something like.

If isNull(Me![frequency]) then
msgbox "Must give a frequency", vbInformation + vbOKOnly
exit sub
end if

placed in the code prior to assigning the control value to the variable.

If you are using this method you will soon get fed up of multiple message boxes if you keep forgetting to fill in the values and that will provide an incentive to improve on the simple validation!!

You may also find that you need to ensure the date is entered in a correct format , if not you'll get date errors - isDate() is a function useful for checking dates.

Hope this helps. I'm assuming you are in the USA, if so I guess our times are way out, I'm off to bed now and tomorrow is a working day - so if you have any other questions I may not be quite so prompt in getting back to you, as I've a busy day!

regards

John