I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will

Possible? Yes. That having been said Excel is not really the best vehicle for
doing this. What you really want is a database. Using Excel will be
combersome, slow and problematic. If you can feed the data into an access
databse then you are laughing. You can still use Excel for the front end if
you wish (assuming nurses do not have Access). That's how I would do it... If
you need some help with the implimentaion of something like this, then this
forum can give you a hand. You sound like you are generally proficient with
code...

My Two Cents

"WillRn" wrote:

I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will

I will probably print off your response and show it to my boss. I really wish
I could do this in Access. Unfortunately, all I have is Excel for the moment.

As far as my writing code, . . . well, . . . .

Let's just say that I am familiar with, not proficient at VB. : )

I know just enough to be dangerous.

"Jim Thomlinson" wrote:

Possible? Yes. That having been said Excel is not really the best vehicle for
doing this. What you really want is a database. Using Excel will be
combersome, slow and problematic. If you can feed the data into an access
databse then you are laughing. You can still use Excel for the front end if
you wish (assuming nurses do not have Access). That's how I would do it... If
you need some help with the implimentaion of something like this, then this
forum can give you a hand. You sound like you are generally proficient with
code...

My Two Cents

"WillRn" wrote:

I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will

You could try the following:

Account Number Admission Date Key SearchKey
0001 23/01/2005 000138375 000138428
0001 17/03/2005 000138428
0003 13/02/2005 000338396
0001 25/02/2005 000138408


Set up a KEY field as text field formed by concatenating Account Number and
Admission Date; the latter will be converted to Excel Date Serial Number .
Then use Find to get the record you want:

sKey = Range("SearchKey") ' Contains search key

Set rnga = Range("C1:C100") ' Range containing key values

Set c = rnga.Find(sKey, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox c.Row ' Row of match
' assign data in row to Userform controls
Userform1.textbox1=Cells(c.row,1) ' Account Number ....
Else
MsgBox "No match found"
End If

This should work reasonable efficiently. How many record have you got?

HTH

"WillRn" wrote:

I will probably print off your response and show it to my boss. I really wish
I could do this in Access. Unfortunately, all I have is Excel for the moment.

As far as my writing code, . . . well, . . . .

Let's just say that I am familiar with, not proficient at VB. : )

I know just enough to be dangerous.

"Jim Thomlinson" wrote:

Possible? Yes. That having been said Excel is not really the best vehicle for
doing this. What you really want is a database. Using Excel will be
combersome, slow and problematic. If you can feed the data into an access
databse then you are laughing. You can still use Excel for the front end if
you wish (assuming nurses do not have Access). That's how I would do it... If
you need some help with the implimentaion of something like this, then this
forum can give you a hand. You sound like you are generally proficient with
code...

My Two Cents

"WillRn" wrote:

I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will

I will probalbly end up with about 500 or so records at the end of the year.
But I do like the search key idea.

"Toppers" wrote:

You could try the following:

Account Number Admission Date Key SearchKey
0001 23/01/2005 000138375 000138428
0001 17/03/2005 000138428
0003 13/02/2005 000338396
0001 25/02/2005 000138408


Set up a KEY field as text field formed by concatenating Account Number and
Admission Date; the latter will be converted to Excel Date Serial Number .
Then use Find to get the record you want:

sKey = Range("SearchKey") ' Contains search key

Set rnga = Range("C1:C100") ' Range containing key values

Set c = rnga.Find(sKey, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox c.Row ' Row of match
' assign data in row to Userform controls
Userform1.textbox1=Cells(c.row,1) ' Account Number ....
Else
MsgBox "No match found"
End If

This should work reasonable efficiently. How many record have you got?

HTH

"WillRn" wrote:

I will probably print off your response and show it to my boss. I really wish
I could do this in Access. Unfortunately, all I have is Excel for the moment.

As far as my writing code, . . . well, . . . .

Let's just say that I am familiar with, not proficient at VB. : )

I know just enough to be dangerous.

"Jim Thomlinson" wrote:

Possible? Yes. That having been said Excel is not really the best vehicle for
doing this. What you really want is a database. Using Excel will be
combersome, slow and problematic. If you can feed the data into an access
databse then you are laughing. You can still use Excel for the front end if
you wish (assuming nurses do not have Access). That's how I would do it... If
you need some help with the implimentaion of something like this, then this
forum can give you a hand. You sound like you are generally proficient with
code...

My Two Cents

"WillRn" wrote:

I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will

For 500 records Excel will be no problem. I have tried a test with 1200+
randomly generated records and it works fine.

"WillRn" wrote:

I will probalbly end up with about 500 or so records at the end of the year.
But I do like the search key idea.

"Toppers" wrote:

You could try the following:

Account Number Admission Date Key SearchKey
0001 23/01/2005 000138375 000138428
0001 17/03/2005 000138428
0003 13/02/2005 000338396
0001 25/02/2005 000138408


Set up a KEY field as text field formed by concatenating Account Number and
Admission Date; the latter will be converted to Excel Date Serial Number .
Then use Find to get the record you want:

sKey = Range("SearchKey") ' Contains search key

Set rnga = Range("C1:C100") ' Range containing key values

Set c = rnga.Find(sKey, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox c.Row ' Row of match
' assign data in row to Userform controls
Userform1.textbox1=Cells(c.row,1) ' Account Number ....
Else
MsgBox "No match found"
End If

This should work reasonable efficiently. How many record have you got?

HTH

"WillRn" wrote:

I will probably print off your response and show it to my boss. I really wish
I could do this in Access. Unfortunately, all I have is Excel for the moment.

As far as my writing code, . . . well, . . . .

Let's just say that I am familiar with, not proficient at VB. : )

I know just enough to be dangerous.

"Jim Thomlinson" wrote:

Possible? Yes. That having been said Excel is not really the best vehicle for
doing this. What you really want is a database. Using Excel will be
combersome, slow and problematic. If you can feed the data into an access
databse then you are laughing. You can still use Excel for the front end if
you wish (assuming nurses do not have Access). That's how I would do it... If
you need some help with the implimentaion of something like this, then this
forum can give you a hand. You sound like you are generally proficient with
code...

My Two Cents

"WillRn" wrote:

I am asking a general question just to see if the following could be possible.

I have a spreadsheet that contains answers to nurse's chart audits that they
perform on a weekly basis. Each row of the spreadsheet is referenced uniquely
by two values: an account number and the patient's admission date.

If a nurse answers one of the questions on my userform that is out of
compliance and requires followup, I have a separate userform that pop's up
automatically based on the response and gathers data on how the issue was
followed up on. This information is posted to a separate worksheet, but pulls
in the account number and admission date from the original chart audit. So
far so good.

I would like to figure a way for the nurse to come back later and enter
additional data on the same entry if needed. I was thinking of a separate
followup user form that would allow the nurse to pull in the same patient by
account number and admission date. I would like this form to pull in the
previous answers if possible so that the nurse can update the responses and
save over the original entries.

Is there a programmatic way for the auditor to:

First, look up the previous group of answers, based on account number and
admission date.

Second, pull the original answers into a userform so that they can be edited.

Third, save the new answers to the followup spreadsheet and erase/overwrite
the old answers.

Any advice or code would be welcome,

Thanks,

Will