I'm having a problem that is inconsistent. This database was written for a

health center, and tracks patients who get counseling services. There is a

Clients table, a Program table (which lists an intake date and the funder

who is paying for the client - such as Medi-Cal, and a discharge date once

they have been discharged) and a Services table (into which each counseling

session is entered). I wrote some code to make sure that services can only

be entered for a client if that client has a current intake date and funder

in the Program table. Since clients can go through the program and be

discharged, but then return later with a new intake date, I set up the form

to check the last DischargeDate field to see if it's empty. If it's not

empty (that is, they aren't enrolled in the program currently), it won't

allow the user to enter any services. The code looks like this:



	If Not IsNull(Me![ProgramHistorySubform].Form![txtDischargeDate])

Then

		MsgBox "Client has been discharged!"

	End If



txtDischargeDate is an invisible text box on the ProgramHistorySubform that

reads =Last([DischargeDate]), that shows the contents of the last

DischargeDate field on the subform.



So here's the problem: the users at the health center called me to say that

in certain cases only, it won't let them enter services for clients who have

been discharged previously, even though a new program intake date has been

entered. I can't get the problem to happen on my copy, and even for them, it

only happens for certain clients. I had someone make the txtDischargeDate

text box visible and it is working properly, even on the records where the

message box comes up erroneously. (That is, it appears to be null, but even

so, the message box keeps popping up and won't let them enter services.)



Thoughts on this? 



Thanks in advance.



Miriam Engelberg







-----Original Message-----

From: Preethi [mailto:preethi@s...]

Sent: Wednesday, October 17, 2001 7:57 PM

To: Access

Subject: [access] Re: Common Dialog boxes





I have done this in VB using Common Dialog Control. Hope that may work with

Access too.

----- Original Message -----

From: "Paul Ansell" <paul.ansell@t...>

To: "Access" <access@p...>

Sent: Wednesday, October 17, 2001 02:01 PM

Subject: [access] Common Dialog boxes





> I want to somehow use a open/common dialog box to allow the user to browse

> throught files on there machine and select a certain file. I then want to

> be able to store the path and filename of that file in a field in the

> database.

>

> I've tried several things, but with no success as yet. Can anyone help me?

> has anyone done anything like this before??

>

> Thanks

> Paul

>

I believe that DLast() gives you the last record as entered.  Try changing

your invisible text box to call DMax() instead & see if that doesn't take

care of the problem.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Miriam Engelberg [mailto:MiriamE@c...]

Sent: Monday, October 22, 2001 2:44 PM

To: Access

Subject: [access] MsgBox problem





I'm having a problem that is inconsistent. This database was written for a

health center, and tracks patients who get counseling services. There is a

Clients table, a Program table (which lists an intake date and the funder

who is paying for the client - such as Medi-Cal, and a discharge date once

they have been discharged) and a Services table (into which each counseling

session is entered). I wrote some code to make sure that services can only

be entered for a client if that client has a current intake date and funder

in the Program table. Since clients can go through the program and be

discharged, but then return later with a new intake date, I set up the form

to check the last DischargeDate field to see if it's empty. If it's not

empty (that is, they aren't enrolled in the program currently), it won't

allow the user to enter any services. The code looks like this:



	If Not IsNull(Me![ProgramHistorySubform].Form![txtDischargeDate])

Then

		MsgBox "Client has been discharged!"

	End If



txtDischargeDate is an invisible text box on the ProgramHistorySubform that

reads =Last([DischargeDate]), that shows the contents of the last

DischargeDate field on the subform.



So here's the problem: the users at the health center called me to say that

in certain cases only, it won't let them enter services for clients who have

been discharged previously, even though a new program intake date has been

entered. I can't get the problem to happen on my copy, and even for them, it

only happens for certain clients. I had someone make the txtDischargeDate

text box visible and it is working properly, even on the records where the

message box comes up erroneously. (That is, it appears to be null, but even

so, the message box keeps popping up and won't let them enter services.)



Thoughts on this? 



Thanks in advance.



Miriam Engelberg

Miriam,

I am wondering how the records are sorted. You need to be very careful

when using Last because the order of the recordset will determine which

record is last. Two options here. Either make sure your sort order is

returning the records in the order in which the patient's most recent

admission is last or better yet (I think), using the admission date, use

the Max function. That assures you of getting the most recent date

regardless of sort order.



>So here's the problem: the users at the health center called me to say

that in certain cases only, it won't let them enter services for clients

who have been discharged previously, even though a new program intake

date has been entered. I can't get the problem to happen on my copy, and

even for them, it only happens for certain clients. I had someone make

the txtDischargeDate text box visible and it is working properly, even

on the records where the message box comes up erroneously. (That is, it

appears to be null, but even so, the message box keeps popping up and

won't let them enter services.)<



Good luck,

Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

Thanks, Roy and Richard! I'll try out the DMax function tomorrow when I'm

back at work. (And I'll also check how I set up the sort in the form.) I'll

let you know if it fixes the problem.



Miriam Engelberg



-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: Monday, October 22, 2001 3:01 PM

To: Access

Subject: [access] RE: MsgBox problem





I believe that DLast() gives you the last record as entered.  Try changing

your invisible text box to call DMax() instead & see if that doesn't take

care of the problem.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: Miriam Engelberg [mailto:MiriamE@c...]

Sent: Monday, October 22, 2001 2:44 PM

To: Access

Subject: [access] MsgBox problem





I'm having a problem that is inconsistent. This database was written for a

health center, and tracks patients who get counseling services. There is a

Clients table, a Program table (which lists an intake date and the funder

who is paying for the client - such as Medi-Cal, and a discharge date once

they have been discharged) and a Services table (into which each counseling

session is entered). I wrote some code to make sure that services can only

be entered for a client if that client has a current intake date and funder

in the Program table. Since clients can go through the program and be

discharged, but then return later with a new intake date, I set up the form

to check the last DischargeDate field to see if it's empty. If it's not

empty (that is, they aren't enrolled in the program currently), it won't

allow the user to enter any services. The code looks like this:



	If Not IsNull(Me![ProgramHistorySubform].Form![txtDischargeDate])

Then

		MsgBox "Client has been discharged!"

	End If



txtDischargeDate is an invisible text box on the ProgramHistorySubform that

reads =Last([DischargeDate]), that shows the contents of the last

DischargeDate field on the subform.



So here's the problem: the users at the health center called me to say that

in certain cases only, it won't let them enter services for clients who have

been discharged previously, even though a new program intake date has been

entered. I can't get the problem to happen on my copy, and even for them, it

only happens for certain clients. I had someone make the txtDischargeDate

text box visible and it is working properly, even on the records where the

message box comes up erroneously. (That is, it appears to be null, but even

so, the message box keeps popping up and won't let them enter services.)



Thoughts on this? 



Thanks in advance.



Miriam Engelberg