Full Version: Notinlist Take The Value And Place It On The Form That Opens
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
chrisemtpa
Nov 3 2011, 11:29 AM
I know I saw this somewhere on this forum but I can't find it. I have a notinlist event, which does what it is supposed to, asks the user to add the new information they click yes, the patient info form opens to a new blank record for them to add the info. What I want to happen is the form to open and take the Lnam,Fnam that typed in the combobox and set the values of the Patientinfo form Lnam Fname to those values.

CODE
Private Sub PatientName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String

' Confirm that the user wants to add the new POC.
msg = " Patient not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a updater, set the Response argument to suppress an error message and undo changes.'
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new POC, open a recordset using the PatientData table.'
Set db = CurrentDb
DoCmd.OpenForm "frmPatientData", , , , acFormAdd, acDialog

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If

Exit_PatientName_NotInList:
Exit Sub
Err_PatientName_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub

Jeff B.
Nov 3 2011, 11:33 AM
There's something I'm not clear on ...

If you have a table for patient info, and a form in which to add a new patient/new patient info, why are you trying to display that patient's name in your starting form?

... and is that starting form's controls "bound" to another table? That is, are you trying to store the patient's name in two tables?

More info, please...
Trivial
Nov 3 2011, 03:33 PM
Sounds familiar to something I've worked with. Let me know if this is wrong:

You have a form which currently shows some thing which is done either by or to patients. Therefore in this form you have a combo box which has all the names in your patients table as its' rowsource. You should consider closely whether or not a patient can do or have this thing done to her more than once, if so the proper model is many to many rather than one to many: This thing is done to/by many patients and each patient can do/have this action done to her more than once.

In any case

You can either store the values in variables then open the form, set focus to each control and set the value of the control to the variable for both name controls in sequence.

Or you can add the data directly to the table using your favorite method of doing so, then open the form and DoCmd.GoToRecord , , acLast.

I personally favor the latter because in my experience SetFocus is of dubious worth and you can't populate a field without it having the focus.
chrisemtpa
Nov 4 2011, 07:41 AM
Responding to both posts

The PatientData table has a one patient to many dispatches relationship. However the patient may not be in the list, when you start a new dispatch. So when you get to the patient combo you begin typing in the lastname and then first name if the patient is in the list nothing happens you just tab off the field. If the patient is not in the list then it opens a separate form that allows you to input the patient data, when you close the second form it reloads the combo box and the patient is in the list. What currently happens is you must retype the patients name in the new blank patient record, i want to have it take what was typed in the combo box and carry it through to the new form to save on typing, since we have to be able to input quickly.

Unfortunately this gets beyond my MS Access skills.

I attached the main form for some visualization
Jeff B.
Nov 4 2011, 07:49 AM
I may not be fully understanding your description...

A more common approach might be to use a combobox (unbound) in the header of the form. This holds all existing patients. If you start typing a name that isn't in the patient table, the NotInList event (of the combobox) fires, takes you to the New Patient data entry screen. You fill it in and Save/Close it.

The code takes you back to the combobox, reloaded, now with the new patient. You select that new patient (note: no need to retype the name), and that patient's ?dispatch? records come up. If you want to see the patient's name, you can either leave it in the combobox, or use unbound textboxes on the form that are filled by the combobox's AfterUpdate event.

There's never a need to (re-)type the patient name, and you don't have to store it a second time.

Good luck!
Trivial
Nov 4 2011, 11:02 AM
I understand your problem. It is exactly identical to one I wrestled with when doing my forms.

If you can train your staff to reliably enter the data in the format LastName, FirstName you can extract it into two separate fields. If you cannot, you're going to need substantial amounts of conditional logic to accommodate errors.

CODE
Dim LstNm As String
Dim FstNm As String
NewData = Trim(NewData) /Removes leading and ending spaces.
LstNm = Left(NewData,Instr(",")-1) /Gets everything to the left of the first comma
FstNm = Right(NewData,(Len(NewData)-InStr(",")-2))/Gets everything to the right of the first comma and space.


I know of two methods for doing this. I'm not sure which one is going to work better for you.

You can pass the entire string (NewData) in OpenArgs of OpenForm
DoCmd.OpenForm "frmName", OpenArgs:= NewData

Then you can handle assigning values in the Patient form's Load event, using a conditional like
CODE
If Len(Me.OpenArgs) > 0 Then

then put the code above in the conditional, replacing every instance of NewData with OpenArgs.

Or you can put the code in the NotInList event of the combo box on your Dispatch form.

In either case you need to assign the values of LstNm and FstNm to your actual controls on the patient form. For that to work you have to SetFocus to the control then set the Control's value to the variable.

CODE
Control.SetFocus
Control.Value = LstNm
Control2.SetFocus
Control2.Value = FstNm


Let me know if you have issues implementing.

Does every dispatch really only have one patient? If not, typing out the duplicate data for the dispatch for every patient is going to consume significantly more time than typing a name twice.
chrisemtpa
Mar 13 2012, 10:45 AM
About to try the above code, each dispatch only has one patient these are prescheduled ambulance transports, not 911 Emergency calls.

Will let you know what happens

Thanks!
chrisemtpa
Mar 13 2012, 11:44 AM
Ok Here is what I got code wise I am not a real strong VBA Coder

This is the code on the dispatch form:

Problem: Lstnm and Fstnm come up red in VB code also when you tab off the Patient LookUp Combo it asks for the patientID, so it also needs to create new record ID in the PatientID field in the underlying table.

CODE
Private Sub PatientName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
Dim newid As String
' Confirm that the user wants to add the new POC.
msg = " Patient not in the list: " & NewData & ""
msg = msg & " Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a updater, set the Response argument to suppress an error message and undo changes.'
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new POC, open a recordset using the PatientData table.'
Set db = CurrentDb
DoCmd.OpenForm "frmPatientDataNotInList", , , , acFormAdd, acDialog, NewData

Dim LstNm As String
Dim FstNm As String
NewData = Trim(NewData) 'Removes leading and ending spaces.
LstNm = Left(NewData,Instr(",")-1) 'Gets everything to the left of the first comma
FstNm = Right(NewData,(Len(NewData)-InStr(",")-2)) 'Gets everything to the right of the first comma and space
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If

Exit_PatientName_NotInList:
Exit Sub
Err_PatientName_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue

End Sub


This is the code on the patient form:

CODE
Private Sub Form_Load(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
Control.SetFocus
LName.Value = LstNm
Control.SetFocus
FName.Value = FstNm
End If

End Sub
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.