After update event not working properly

[Kalmed2018]

Hi,



I have a database with four tables and the following relationships:

• Patients - Op Notes: One to many
• Op Notes - Histology: One to One
• Op notes - Complications: One to Many

Although it is not recommended there are a few fields that i wanted to be in all three tables: Op Notes, Histology, and Complications

Among other forms that I use to update the tables i have "Histology" and "Complication" forms. I have written a simple code to auto populate the fields in the form Histology and Complication with the data from the table Op Notes that i have already recorded. The firing event is After update of a form field "Operation ID". When i first created the forms they were both working perfectly well. However suddenly the form “Histology” does not work as expected: When I enter a new record the required fields will not autopopulate with the code. If I close the form and then reopen it to a record and then I update the field ”Operation ID” then the code works and populate the fields. Of note the two forms are very similar with the exact same properties and the “Complication” form still works very well as expected. The problem persists when I change the property “Data Entry” to yes or no. The code that I use looks like that:

Option Compare Database

Private Sub IDOPERATION_AfterUpdate()
Me.AGE.Value = Me.AGE_Operation_Note.Value
Me.CONSULTANT = Value = Me.Cons.Value
Me.Procedure.Value = Me.Procedure_Operation_Note.Value
Me.BreastProcedure.Value = Me.Breastprocedure_Operation_Note.Value
Me.operationcategory.Value = Me.operationcategory_Operation_Note.Value
Me.AxillaryProcedure.Value = Me.AxillaryProcedure_Operation_Note.Value
Me.Laterality.Value = Me.Laterality_Operation_Note.Value
Me.Presentation_Hist.Value = Me.Presentation.Value
Me.NeoAdjChemoTx.Value = Me.neoadjuvantchemotherapy.Value
Me.NeoAdjETx.Value = Me.neoadjuvantendocrine.Value
Me.CavityShavings.Value = Me.CavityShavings_Operation_Note.Value

End Sub


Do you have any idea why this may be happening?

Many thanks, for your time and expertise!

[Bob Fitz]

You have

• Op Notes - Histology: One to One

May I ask why you don' just have one table with all the data from both of those tables?

[Kalmed2018]

I guess it is the high number of fields in each of these tables. I currently have 66 fields in the first and 52 fields in the second table. Even counting the duplicates, a single table would have over 100 fields and in the past I had problems with such large number of fields. The honest answer is that I never thought about this and I thought keep them separate as they have very different kind of data. I am not sure what are your views on the high number of fields. Is there any way to make it work in two different tables as they are already data and it would be a lot of extra work (for me) to make it one table?

Thanks

[Bob Fitz]

I guess it is the high number of fields in each of these tables. I currently have 66 fields in the first and 52 fields in the second table. Even counting the duplicates, a single table would have over 100 fields and in the past I had problems with such large number of fields. The honest answer is that I never thought about this and I thought keep them separate as they have very different kind of data. I am not sure what are your views on the high number of fields. Is there any way to make it work in two different tables as they are already data and it would be a lot of extra work (for me) to make it one table?

Thanks

I'm not sure that I can be of much help to you here. The reason I asked was really because of my own curiosity. I know that you can have a 1 to 1 relationship set but I've never had need of one and can't imagine when I might need to. I believe the maximum number of fields that Access can handle is supposed to be 255 so I would have thought it could handle around 100.
Just my curiosity, once again, but I can't imagine a properly normalised database needing a table with anything like that number of fields. I suppose they must exist, otherwise MS wouldn't have made Access with the ability to have 255 but I've certainly never got anywhere near that number of fields in a table. Are you sure you've normalised the database correctly.
Perhaps you could post a copy (even one with no records).

[Kalmed2018]

Sorry I cannot answer your question. I am obviously an amateur in Access without much experience in proper databases so I don't understand what you mean by "Properly normalised database". Happy to give you more info if you could explain further?
I guess the number of the fields is dictated by the need of the data you want to capture? So I tried rather than having a long text where you can write all the details of an operation, it is better to brake it in small pieces/fields so you can retrieve any relevant data and analyse them accordingly but perhaps I am wrong?

[Welshgasman]

I think you need that code in the Current event of your form and also only when a NewRecord.?

However I do not think holding the data elsewhere is such a great idea. Data can get out of sync?, then which do you believe?
This is expecially pertinent if you are inexperienced with Access.?

Data Entry property to Yes, will only allow new records? About the worse named property you can think of. :-)

Good luck anyway.

[Kalmed2018]

Hi Thank you for your answer. What do you mean by current event? TRhe options I have is On Click, Before update, After update etc but there isn't a "Current event"?

Thanks

[Kalmed2018]

I think you need that code in the Current even of your form and also only when a NewRecord.?

However I do not think holding the data elsewhere is such a great idea. Data can get out of sync?, then which do you believe?
This is expecially pertinent if you are inexperienced with Access.?

Data Entry property to Yes, will only allow new records? About the worse named property you can think of. :-)

Good luck anyway.

O I found the On Current event (I was looking the field rather than the form properties!). Not sure what you suggest but the way it works is that I have to manually enter the "Operation ID" then it retrieves the data form the Op Note table and populates the relevant fields. What kind of code I would write to On Current event to achieve the same?

Thanks

[Welshgasman]

The Current event is on the Form, not a control, hence

in the Current event of your form

You say you want those controls populated when you add a new record?

When I enter a new record the required fields will not autopopulate with the code

[Kalmed2018]

The Current event is on the Form, not a control, hence
You say you want those controls populated when you add a new record?

Yes that is correct

[Welshgasman]

That code is not retrieving anything?, merely setting one form control from another form control.?

Now would be a good time to learn some basic debugging? See the link in my signature. Ensure the code you have written is actually being used.?

If you enter a Operation ID into a new record, then I would have thought that would work PROVIDING you were retrieving the data at that point. That code is not doing that.?

Take a step back and think of each step, one by one. In Debug, set a breakpoint and look to see what the code is actually doing.?

The Current event will not work with your method, as there will be no OperationID to work from.? My understanding was you already had those values for those controls and so would set them when you have a new record.?

[Kalmed2018]

The Debug does not return any errors. Although it may look unorthodox to an expert like you, this method works well for my other form ("Complications"). Even for this form it works if I update the relevant field in an existing record but not in a new record when I need it the most.

[Welshgasman]

Believe me I am not an expert, and consequently I used the debug option A LOT when I was using Access in my job to get to the bottom of an issue.?
IF you have a new record and enter an OperationID and tab out then I would expect the AfterUpdate even to to fire.? Debug would confirm that.?

Then you could see what is actually in those controls.?

I do not see where you 'retrieve' the data to populate those controls.?

You are only showing half a picture, which makes it very had to offer any help that will work.?
It is nor good saying 'it works on my other forms'. I have heard that so many times and when we get to the bottom of the problem, we find there is a subtle difference between them.?

That is what is great about computers, they do not change their mind. If you give the correct command they do what is asked. Make a mistype and they will say they do not understand. You and I would see the mistype and recognise it for what it is, computers will not (well at least at the moment )

I do not expect it to return errors, else you would have been presented with them.? I am trying to find out what actually runs?

Edit: I have just added a simple Debug.print of a control in its AfterUpdate event, and on a new record and I enter a value, that value is shown from the AfterUpdate event (which I expected), so now figure why yours does not work as it should?

[Kalmed2018]

The form has a subform that pulls data from the table Op Notes with link and master fields to correlate the right record and identify the key that connects the two tables. The code will fire the right event for an existing record but not for a new one. If that helps i could send you some screenshots or a video capture?

[Welshgasman]

The form has a subform that pulls data from the table Op Notes with link and master fields to correlate the right record and identify the key that connects the two tables. The code will fire the right event for an existing record but not for a new one. If that helps i could send you some screenshots or a video capture?

No, that is no good.
I would need to use the form and do exactly what I am asking you to do, to get to the bottom of the problem. What you say happens, does not make sense, at least to me.
I have just confirmed in a test form of mine that the event fires even on a new record, so there is something else amiss here, but if you are not prepared to even try with Debug, there is not much more I can offer. Sorry.
Let's hope an expert comes along soon.