Solved Creating a New Record issue.

[mloucel]

Hello Gurus:

I have an issue [at least it is for me]..

This is my Form:

The Fields [Name, Last Name, DOB and Phone], are taken from the Search form is coming (I search for the Patient first) then I move the data to the txt Fields to be displayed, this is the code I use on the SEARCH Form:

        PatID = PatientID
        
        DoCmd.OpenForm "AddAuthorizationsF", acNormal, OpenArgs:=1
        
        With Forms("AddAuthorizationsF")
            .PatID = PatientID
            .PatID1 = PatientID
            .FN = PFirstName
            .LN = PLastName
            .PatDOB = PDOB
            .PatPhone = PPhone
        End With
        DoCmd.Close acForm, Me.Name

This works perfectly, my problem is:
The program should be able to ADD a new Authorization to the same Patient or more, the ONLY thing I need is to be able to reopen the above form with the same PatientID, and display the same fields [Name, Last Name, DOB and Phone] all others should be initialized [meaning NEW].
I've tried a few things but I still can't figure out how to do it.

Any help will be appreciated.

 

[CarlettoFed]

If you can attach a sample file, to also check the database structure, it would be much easier to understand and help you.

 

[theDBguy]

The program should be able to ADD a new Authorization to the same Patient or more, the ONLY thing I need is to be able to reopen the above form with the same PatientID, and display the same fields [Name, Last Name, DOB and Phone] all others should be initialized [meaning NEW].
I've tried a few things but I still can't figure out how to do it.

What exactly is the problem? Are you not able to add new authorizations? If not, are you getting any error messages?

 

[mike60smart]

Hello Gurus:

I have an issue [at least it is for me]..

This is my Form:
View attachment 118211

The Fields [Name, Last Name, DOB and Phone], are taken from the Search form is coming (I search for the Patient first) then I move the data to the txt Fields to be displayed, this is the code I use on the SEARCH Form:

        PatID = PatientID
       
        DoCmd.OpenForm "AddAuthorizationsF", acNormal, OpenArgs:=1
       
        With Forms("AddAuthorizationsF")
            .PatID = PatientID
            .PatID1 = PatientID
            .FN = PFirstName
            .LN = PLastName
            .PatDOB = PDOB
            .PatPhone = PPhone
        End With
        DoCmd.Close acForm, Me.Name

This works perfectly, my problem is:
The program should be able to ADD a new Authorization to the same Patient or more, the ONLY thing I need is to be able to reopen the above form with the same PatientID, and display the same fields [Name, Last Name, DOB and Phone] all others should be initialized [meaning NEW].
I've tried a few things but I still can't figure out how to do it.

Any help will be appreciated.

Hi
Are you trying to add New Referral details for the Patient Details obtained via your Search Form ?

 

[MajP]

To me the problem is you are making it hard by using an unbound form.

The main form should be bound to Patient information.
Then you should have a list of authorizations for that patient as a subform in continuous view linked by patient ID
How would you know what authorizations exist for that patient, to know you need to add another?

From that form showing Patient info and a list of authorizations you can hit add new and it pops open the above form. The add form. This form should pop only in dialog IMO. Again main form bound to patient info and you could then have a subform in single form view where you can add an authorization. Here you validate the entry in the forms before update. You close it and it takes you back. You can cancel the before update if you decide not to add an authorization. If you want to add another one you repeat the process.

IMO this idea of a add new single form view that pops open another add new single form view is confusing and not the most user friendly.
I personally want to see the patient info and a list of authorizations. Click to add new and return back. See the new list and click new again to add another.
Maybe other people would do it the way you want, but I would not.

If you want to do it the way you are asking it is still going to be far easier to bind the main form to patient and have a subform for an authorization. Then to add new you go to a new record in the authorization subform.

 

[Pat Hartman]

@mloucel You are very far into this database so you probably don't want to hear this but here goes anyway.

You programed in COBOL for a long time so you are not afraid of writing code. What this has done is prevent you from actually understanding Access which is a RAD (Rapid Application Development) tool which does a lot of stuff for you if only you would let it. That means you start with bound forms. You use querydefs rather than embedded SQL. You use property settings. You use VBA functions. And when you can't get it done with a query or a VBA function or a property setting, then and ONLY then do you write code. You don't start with unbound forms and try to code all the necessary behavior yourself.

You need to gradually, one at a time, convert ALL your unbound forms to be bound and learn to take advantage of Form and Control level events to place any necessary non-generic code. Think of all the hidden code behind a form that was written by the Microsoft people and which you cannot ever see as the "mainline". In that mainline, the form's code recognizes events (things that the user does or that happen as part of navigation through a recordset bound to a form. These events become "hooks" which are exposed to you so that you can add custom code when one of these events happens. Remember writing COBOL? You create an outline of what the program will do with "stubs" which do nothing except return to the mainline. These "stubs" translate to Events in Access, either Control level or Form level. You always need code but not for the stuff you are using it for. You need code to do the things that are not generic and so Access cannot do for you. So, you always need validation code and your validation code determines whether or not records get saved to the tables. When a form is bound you can open it using the OpenForm Method and pass in an ID. If the ID is found, the record is displayed which is exactly what you want and you need NO CODE to populate the form's data fields. If the ID is not found, the form opens ready to add a new patient. It works sooooooo smoothly and logically, it is almost magical. But you really need to let Access be Access or you will never be at ease with creating applications using it.

Your current methods are causing you to build multiple forms for every table to handle add/view/edit separately. All you ever need is ONE form. Occasionally, your validation code will need to distinguish between add/update but most of it will be the same.

I can probably count on one hand the number of times I have made the decision to have two forms update the same record. In those cases, the validation code was in a separate module and called from the BeforeUpdate event of each form so that if something changed, I only needed to modify ONE procedure. An example of this might be if a child record has too many fields to display nicely in a one line list. In that case, I display all required fields and he most important other fields so the user doesn't have to open a second form if he wants to change something. But the subform has a double click event that will open a single record data edit form that shows ALL fields should the user need to get to those less important fields to view or update them.

 

[mloucel]

@mloucel You are very far into this database so you probably don't want to hear this but here goes anyway.

You programed in COBOL for a long time so you are not afraid of writing code. What this has done is prevent you from actually understanding Access which is a RAD (Rapid Application Development) tool which does a lot of stuff for you if only you would let it. That means you start with bound forms. You use querydefs rather than embedded SQL. You use property settings. You use VBA functions. And when you can't get it done with a query or a VBA function or a property setting, then and ONLY then do you write code. You don't start with unbound forms and try to code all the necessary behavior yourself.

You need to gradually, one at a time, convert ALL your unbound forms to be bound and learn to take advantage of Form and Control level events to place any necessary non-generic code. Think of all the hidden code behind a form that was written by the Microsoft people and which you cannot ever see as the "mainline". In that mainline, the form's code recognizes events (things that the user does or that happen as part of navigation through a recordset bound to a form. These events become "hooks" which are exposed to you so that you can add custom code when one of these events happens. Remember writing COBOL? You create an outline of what the program will do with "stubs" which do nothing except return to the mainline. These "stubs" translate to Events in Access, either Control level or Form level. You always need code but not for the stuff you are using it for. You need code to do the things that are not generic and so Access cannot do for you. So, you always need validation code and your validation code determines whether or not records get saved to the tables. When a form is bound you can open it using the OpenForm Method and pass in an ID. If the ID is found, the record is displayed which is exactly what you want and you need NO CODE to populate the form's data fields. If the ID is not found, the form opens ready to add a new patient. It works sooooooo smoothly and logically, it is almost magical. But you really need to let Access be Access or you will never be at ease with creating applications using it.

Your current methods are causing you to build multiple forms for every table to handle add/view/edit separately. All you ever need is ONE form. Occasionally, your validation code will need to distinguish between add/update but most of it will be the same.

I can probably count on one hand the number of times I have made the decision to have two forms update the same record. In those cases, the validation code was in a separate module and called from the BeforeUpdate event of each form so that if something changed, I only needed to modify ONE procedure. An example of this might be if a child record has too many fields to display nicely in a one line list. In that case, I display all required fields and he most important other fields so the user doesn't have to open a second form if he wants to change something. But the subform has a double click event that will open a single record data edit form that shows ALL fields should the user need to get to those less important fields to view or update them.

Hello
@mike60smart , @theDBguy , @MajP and of course @Pat Hartman

I'll try to answer all of you here.
Maybe this will explain a bit better the issue..
This is the Table where the info is SAVED the first time, once I have found the Patient:

This is how I do it:
I need first to find the patient so I use this form, based on a query, to find the patient, I am only interested in the record ID, but since I need to display the name, DOB and phone, [in the Form I have the issue].
This is the SEARCH form and Query:

This Form Passes the PatientID to AddAuthorizationsF [as mentioned by you @Pat Hartman ]
I need to move this data to the ADDAuthorizationF, [Displayed in #1] remember I ONLY need the ID saved to the above Table, see second FIELD [PatientID] so the Name, DOB and Phone are for DISPLAY purposes only.
ALL other fields in #1 are INITIALIZED, so the EU will pick whatever the Authorization needs, so that FORM IS BOUND to the AuthorizationsT, but this table, only saves ID's, from the rest of the other tables:

Now, that's when the problem comes:

The EU or mostly the patient may have different Authorizations, GI, Optometry and Dietitian, or more.

FOR NOW, once I finish one authorization the EU goes out to the main menu, and starts the whole process again:
- Main Menu
- Search for the Patient
- once found Add a new authorization
Go Back to Main Menu..

That is why I need to ADD that feature to the AddNewAuthorizationF so that the EU doesn't have to go back over and over again.

So as you can see the LastName / FirstName / DOB and Phone are sort of transferred just display only not important [when I pick a Patient, only the ID is transferred to the bound form, as you can see, I am following (I THINK) the rules you set me (and I appreciate), since ALL other fields are going to start ALWAYS from DEFAULT values; except for the PatientID, that will be always the same, and once I get to a new record in that form, I need to display ONLY the LN, FN, DOB and Phone again, but all other fields start from defaults.

Every time I try to start the form for a new record, I lose everything.

I hope this is better explanation.

just need to create a new record but keep the PatientID and somehow, get the FN, LN, DOB and Phone from the PatientT display them and start a new Authorization for that patient, as many times as he/she has authorizations.

Sorry for the long explanation but as you can see, the form is bound as it should be.

Any help will be appreciated.

Maurice.

 

[mloucel]

Hello ALL: @mike60smart , @theDBguy , @MajP and of course @Pat Hartman

My ideas were not so lost, and after a weekend of calming down, and today, with a fresh BRAIN, I had a EUREKA moment, and the answer to my problem, WAY easier that what I thought and it works very well should I say myself, so I deserve a "Pat" , [Not a pun intended] on the back and a good Lunch.
Here is the Code I Needed and yes I did it myself.

'------------------------------------------------------------
' AnotherAuthBtn_Click
'
'------------------------------------------------------------

Private Sub AnotherAuthBtn_Click()
'
' First Let's get the TEMP data
'
    FN = DLookup("[PFirstName]", "PatientT", "[PatientID] = " & [PatientID])
    LN = DLookup("[PLastName]", "PatientT", "[PatientID] = " & [PatientID])
    PatDOB = DLookup("[PDOB]", "PatientT", "[PatientID] = " & [PatientID])
    PatPhone = DLookup("[PPhone]", "PatientT", "[PatientID] = " & [PatientID])
 
 ' Now let's enable the fields again.
 
    Call fncEnableCtrl(True) ' Enables all controls to allow changes
    
' This command allows me to start the form in new record mode
    
    RunCommand acCmdRecordsGoToNew

' Now let's go back to load the form and start all over again.

    Form_Load
    
End Sub

 

[Mark_]

I would suggest re-reading MajP's post #5.

Using bound forms and a normal form-subform structure would have accomplished this in an hour, at most. If you have invested more than an hour in "solving" this, you will want to learn how Access is designed to work. It will save you an immense amount of time and effort going forward.

 

[mloucel]

I would suggest re-reading MajP's post #5.

Using bound forms and a normal form-subform structure would have accomplished this in an hour, at most. If you have invested more than an hour in "solving" this, you will want to learn how Access is designed to work. It will save you an immense amount of time and effort going forward.

I did, and I am reconsidering re-creating the form, but for now time is of the essence, and I have no choice but to continue as is.
On the other side, those authorizations follow a protocol internally [Paper Trail] so it is impossible to re-create the same authorization twice, this system is implemented actually to get rid of EXCEL, yes as you see Excel, and the system is so perfect that duplicate authorizations will be detected in 2 or 3 places: The IPA has a record, as well as The Plan and the Insurance, if we try to create a new authorization any of those systems will reject the Pre-Authorization and the Authorization will never made it to either Excel or my program.

But nevertheless, the idea is great and gave me a perspective unto something else that can be done, [not related to duplicates] but related to make it easy for the EU to find any Pending authorizations.

Thanks.

 

[mloucel]

Just a following note:

Dear: @mike60smart , @theDBguy , @MajP and of course @Pat Hartman and @Mark_

I understand your concerns and advise, and truly appreciate your input, but nevertheless I have to clear something:

I am learning Access, on a let me see what next basis is.

I have followed Richard Rost and been using his teachings for quite a while, He is an extremely knowledgeable programmer in VBS and access, but after seeing your words of advice I can see that surely I am making mistakes, but they are not intentional, that is the way I have learned Access and VBS, I don't know how to use all those features that access already have, I have never been taught the correct way, I know what I've been told to do.
Your guidance then is important to me, but I wish @Pat Hartman can follow her idea with SQL and not only that, but teach all of us the correct way to use access and add the power of VBS and SQL to the teachings, I don't mind paying again to re-learn if that is going to guide me in the right way to Access and VBA.

Thank you all for your patience and guidance.

Maurice.

 

[MajP]

I have followed Richard Rost and been using his teachings for quite a while, He is an extremely knowledgeable programmer in VBS and access, but after seeing your words of advice I can see that surely I am making mistakes, but they are not intentional, that is the way I have learned Access and VBS, I don't know how to use all those features that access already have, I have never been taught the correct way, I know what I've been told to do.

It is not really a mistake, but you just may be making it hard on yourself.

I find it a little bewildering when people are struggling doing things with unbound forms when the out of the box bound form solution is trivial. People choose these unbound solutions because they have misconceptions or saw someone on-line saying that is the way to do it. I am sure I can figure out how to operate my computer without a mouse and keyboard only, but God gave me a mouse for a reason so I am going to use it
Access's power is bound forms and controls. Sure you can by-pass this, but Access IMO sucks for designing unbound solutions. Sometimes it seems that people feel that they are being clever by "out smarting" Access with a bunch of unnecessary code and form designs.

My argument is if someone is not going to design an Access application using bound forms and controls, you are wasting your time using Access. Not that it cannot be done, but because there are way better things to design in. You can design in something like visual studio far better and far easier. In something like VB you have datagrids that would blow away any unbound solution you can do in Access.

So it is not that you cannot make it work, but it is just that you could have done it easier and not have to "fix" what you have.

 

[mloucel]

It is not really a mistake, but you just may be making it hard on yourself.

I find it a little bewildering when people are struggling doing things with unbound forms when the out of the box bound form solution is trivial. People choose these unbound solutions because they have misconceptions or saw someone on-line saying that is the way to do it. I am sure I can figure out how to operate my computer without a mouse and keyboard only, but God gave me a mouse for a reason so I am going to use it
Access's power is bound forms and controls. Sure you can by-pass this, but Access IMO sucks for designing unbound solutions. Sometimes it seems that people feel that they are being clever by "out smarting" Access with a bunch of unnecessary code and form designs.

My argument is if someone is not going to design an Access application using bound forms and controls, you are wasting your time using Access. Not that it cannot be done, but because there are way better things to design in. You can design in something like visual studio far better and far easier. In something like VB you have datagrids that would blow away anything you can do in Access.

So it is not that you cannot make it work, but it is just that you could have done it easier and not have to "fix" what you have.

Yes good friend, but I was never shown the correct way, when I learn what I know so far, is what I know how to do.

I understand your concept, but I am simply following what I've learn, I wish someone can actually create a learning video series on how to correctly program in Access instead of simply learn how to program in access, the concept of all teachers out there is correct, they all teach you how to program in access, the problem is the methodology is wrong, that is my problem, I know a little bit how to program [what I have learn so far] the problem is that my teacher is teaching me how to program, not how to program correctly using all the resources that Access has, just as you say.

How am I going to do something I don't know how to do it, if no one has shown me the way????

I pick hints here and there from you guys, see I used to have some boxes in my code as CLICK events now I changed them to ON FOCUS, because it makes more sense than click and I can do way better specific validations with BEFORE UPDATE and AFTER UPDATE, before I didn't even know, until someone gave me some code and asked me to apply different code in those areas, yes the program worked, but I didn't know until someone taught me, now I try that route, but my classes are still wrong, they are still teaching me VBA not how to apply that VBA in the correct area first, just how to apply the code by not going the correct way.

I got already a couple of slaps in the hand by not doing what I told to do, just because I know it was wrong, and I knew the right way, but the assignment was not to do it correctly, it was to follow the example and do it that way, the wrong way, no matter what.

I would love to know all you and the rest of people here knows, but I don't, I am simply trying my best, and being grateful for all of your help.

Maurice.

 

[MajP]

Yes good friend, but I was never shown the correct way

Again I would not say it is "incorrect." I would just say you are using a harder approach than necessary in certain cases.
I just recommend where possible to use a bound main form and bound sub forms. This is not always possible to get the best results, but I would look at that as the starting point.
People often argue they need unbound solution so they can "control" data entry. They want an add/update/cancel. You can do all of this with bound and easier if you understand the BeforeUpdate event of the form. All data can be validated and "saved", or "canceled" in this event.
Again you will have not problem accomplishing what you want to do, you just could have done it easier with less code.

 

[Pat Hartman]

@mloucel If you have time, you might want to look at the videos I created regarding form and control events.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

There is a database to download so you can create your own forms (or modify mine) and watch events as they fire. I don't have any code in the mouse events because those will drive you crazy. So, leave those alone unless you are trying to use them to control something. You need to watch at least one video before trying to use the database so you understand how the logging and viewing form works.