Forms recalling previous data

[dchayka]

Hi, I'm in desperate need of help. I'm a Pharmacy student currently in a Medicare program where we use the Access database for data collection. I've inherited the database along with all its faults. I've spent countless hours attempting to fix issues and make it better, but I'm self-taught by YouTube and countless hours of playing with it, so my experience is limited. I'm about ready to give up, and I'm hoping someone here may be able to offer guidance for the sake of my sanity. Our data collection involves many questions forcing us to use many forms. Everything is hunky-dory when I enter the first patient (and sometimes the second patient); however, as soon as patient number 3 or 4 goes in, forms S10b - d start to glitch out and recall previously entered patients forcing me to overwrite. I suspected it was a macro issue, but compared macros from other forms that aren't doing the same thing and found them to be identical. I'm out of ideas and need help. Please. Any suggestions would be greatly appreciated. Thank you ahead of time.

[aytee111]

Most people don't go to external links, you need to attach it to your post.

We need more information, what are you talking about? Remember, we aren't in your business so have no idea what you mean when you talk about "forms" - to us a form is an object within the database. How do you enter paitients? What is the table structure? What does "glitch out" mean? Please explain using Access terminology.

[ranman256]

no macros need be used to enter data.
If you enter data into a form, then move to the next new record, the previous data is saved.
The new record will not edit the previous record unless you move back to it.

this could ONLY be a problem if you changed the settings on the form property, OTHER tab,
CYCLE = All Records (the default)

if this got changed to any other setting then you could overwrite exising records.

is DATAENTRY = TRUE?
its possible you only entered 1 new record with DATAENTRY=false
then you could overwrite an existing rec when you move to the next one.

[dchayka]

Thank you for your responses, aytee111 and ranman256. I apologize for posting a link. I've uploaded the file in a zip format as the .acccdb file extension kept failing for some reason. I'll do my best to explain in access terminology as I myself am not fluent in the language.

It's not a business, but more of a class that puts on health fairs for the elderly to help them best choose their prescription drug benefit through Medicare and use our clinical expertise to educate them about their medications. Way before my time, a student was kind enough to build the access database in order to make it easier for the parties collecting the demographic/health/and insurance data to populate a single sheet in a neat fashion. In order to make it manageable for the students to fill it out quickly, the creator made multiple forms as you can see on the left of the picture below. In order to move about those forms, the creator built macros into the "Save and Next" button which are supposed to save the previous form information and move onto the next one. I don't know much about macros because when I try to mess with them, everything crashes. The primary key field is what is tied to the "unique patient's ID" which decides to change on its own on random forms. This is my original problem. From what I've been told after completing the final steps on "Medicare D BOS FORM S10D," the save button is supposed to take you right back to form "S01" where the "New Beneficiary" button is supposed to create a new field allowing for a new "Unique Patient ID" to be created, and the process to be started all over again till the next patient. What I was attempting to communicate in a poor fashion with my "glitch out" is that after I enter the first patient and start going on my merry way filling out the fields for the second patient, somewhere around form S10a to S10b, the previous beneficiaries information would start to appear. I was almost tempted to scrap the whole multiple forms, and just input all of that information onto a single form without any skipping in between to help avoid this issue. This would be a whole lot of work, but it would solve this nightmare, and my suffering will help the future inheritors of this database. I apologize if this is not clear or helpful. Please let me know what other information I can provide to better help narrow down my issue.

[pbaldy]

Post 4 moderated, posting to trigger email notifications.

[aytee111]

This is an Excel spreadsheet put into Access! At the very least you need a Patient table which contains their information, so that next time it already exists. Also, the naming conventions makes it quite confusing - remove all spaces from object names and change the form names to be easier to read. But that is a lot of work and not necessary to get the database working.

As Ranman says, data is saved automatically, although a Save button can assist to save as you are typing, thereby not having to re-enter everything in case of crash. To make it easier to enter the information, I would add a tab control to a main form. On each of the pages have one (Access) form pertaining to one (Medicare) form. As stated, you could have a save button on each of them in order to be safe, but there would be no Next button. After all the data has been entered to one patient the New Beneficiary would take you to the next record, or a search for an existing patient.

[dchayka]

I'm so sorry for this ignorant question. I understand the concept of autosave as the information is entered as this is how most of how OneNote operates as well as other office software. I've also just looked up "tab control," and it looks like I would have to rework some things but the concept is something I haven't come across yet in access. Looks like a refreshing solution. I'll look into that further to see the extent of work I would need to put in. If I'm understanding what you're saying, the current access forms that are currently dubbed "Medicare D BOS Form S***" would now be tabs, and all of the tabs would be confined to one "Form" with said tabs with one common heading for the primary key of "Unique pt ID"? Would this require me to basically rebuild the whole thing from scratch, or is there any way to salvage some time? Also, may I ask how you instantly knew that this whole database was imported from Excell? I honestly have no idea whether this was the case or not. Thank you again for taking the time to address my issue.

[aytee111]

First, my reference to Excel - Excel is one spreadsheet holding all fields, Access is multiple tables, each one holding its own fields and linked thru the use of keys. Hence my suggestion to create a separate table for patients which contains the patient info (health ID, etc) which never changes. Then to link that table to your medicare table so that this information isn't repeated. As it is, all information on one table, is how Excel is structured. It makes it very cumbersome and difficult to use any of the advantages of Access.

You are on the right track - each tab would contain the form for that "form". No need for major rework, these forms exist already. Just remove the buttons and unique patient ID entry, this field would be used as the "link" field between the main form and each of these subforms (a subform is just a form that isn't opened by itself).

Another way of doing it would be more like a website, with a list of the "forms" down the left hand side, each one would be a command button, and you could make the subforms visible as the button is pressed. This looks nicer but is harder to develop as all the subforms would then be one on top of the other.

As all the information would now be entered on one form, I suggested a Save button for "saving as you go along", but is not necessary.

(No need to apologize!)

[dchayka]

thank you so much for this information. Would it be okay if you could reference a YouTube link to kinda briefly visualize what you're describing, or would just looking up "access tab control" and watching videos till my eyes bleed would answer a lot of my problems?

[aytee111]

Sorry, don't do videos. I created two forms, showing what the options I mentioned would look like. There are many other ways to do this, you decide on which way to go and we will assist.

[dchayka]

Thank you. I see what you mean. There are about 40 different people that have to use this database, so I'll have to get creative on how to make it self-explanatory. Thank you so much for your time. You were super helpful in helping me with suggestions towards a resolution.