Thank you in advance for any help! (1 Viewer)

Hello everyone, long time lurker, first time poster!

I am re-building a database due to the fact that I took a few shortcuts around normalization the first go around. I've just about gotten my database and FE application going, but have run into a problem that I can't seem to get around.

I have a Form (Vancomycin_monitoring). The form has the main demographic information in two tables, Patient_Demographics (PD) and Vanco_Demographics (VD). The PK for PD is Patient_ID, which is the foreign key for VD.

When entering data on this form I get an error message, "You must enter a value in the 'Vanco_Demographics.Patient ID' field." Pretty easy to figure out what's going on here, so I built an append query to add the Patient ID from the PD table into the VD table after it is input. Problem solved....if only...:banghead:

This works, as long as I close the form, then re-open it to the same record. I am then able to update the rest of the information on the form as expected. I've built a pop-up message into the form advising the user to do this, but obviously that is not a user friendly way to do this. I know there is probably an easy solution, but I've worn out my internets trying to find a way! I ran through about a billion different macros to get this to work (okay, more like 30), but have totally struck out!

Thank you in advance for any advice you can provide,
Kyle

This should NOT require an INSERT action query. Sounds like you need a form/subform arrangement or a combobox to select patient.

I agree with June, but you are talking about a specific issue. Can you describe the application in its entirety in simple terms? There must be more that Patient Demographics and Vancomycin, but you know the business and readers do not.

Thank you June7, that is exactly the problem. I don't know why I didn't think of that, I have 6 other subforms on this form. Maybe just because it is a one-to-one instead of one-many relationship. I was stuck in my previous track where all of this information was in a single table and not appropriately normalized.

Now I just need to figure out how to get the formatting to where I like it!

Thank you for your reply jdraw! My application is being used to track renally dosed medications (primarily vancomycin) at our client facilities. There are different tables to track different lab values, calculations, dose adjustments, and general comments. There are also separate tables to track patient demographics and vanco demographics.

Changing from 1 big table to 1-to-1 related tables is not necessarily more 'normalized', mostly just more complicated because of requiring table joins and form/subform arrangements. Personally, I can and do tolerate a bunch of empty fields in table to simplify data entry/output.

I had to add a new function to the form which necessitated splitting all of the information into separate tables. Instead of monitoring just vancomycin, we have to monitor all renally dosed drugs. It definitely complicated everything! I ended up rebuilding the entire database, which probably wasn't necessary, but I think I have a much cleaner product now.

Sent from my SM-G965U using Tapatalk

The saga continues! Now I'm getting an error that says "The LinkMasterFields property setting has produced this error: 'A problem occurred while Re-Build Pharmacokinetic Database was communicating with the OLE server or ActiveX control'

I've double checked the LinkMasterFields control, and it looks correct to me. I've also deleted the subform and re-added it to the main form, no luck.

Aaargghhh!!!

Any ideas what I might have done?

Doubt can help without viewing db. If you want to provide db for analysis, follow instructions at bottom of my post.

Thank you again for your assistance June7! I'll need to remove all of the data from the tables as there is PHI (protected health information) in the database.

I'll upload it as soon as I can figure out how to do that!

Here you go! I tried to test the error after removing info, but got a totally different error...

'Return without GoSub'

Why does every problem fixed create two new problems!

Hi

You need to look at each of your tables and remove any spaces and special characters.

You should also NOT have a Lookup at the table Level, Google "The Evils of Lookups in Tables" for an explanation of Why.

I would also recommend that you use an Autonumber as the Primary Key in All of your tables.

You then have a Foreign Key with the Same Name in any related tables.

If you look at the modified Database attached. I have shown an example of a Main Form / Subform based on your renamed tables :-

tblPatientDemograhics
tblPatientVancomycinDose

You will note that the Subform is automatically populated with the correct PatientID record number.

Come back with any questions

View attachment Pharmacokinetic Monitoring - Blank.zip

Thank you kindly for your response Mike, I'll get started on your recommended changes right away.
Are these formatting issues the reason for these errors popping up? Why am I getting them?

Hi

The error message is related to not having proper Referential Integrity set between tables