Annoying warning message and search functionality

English
, , , , ,
#1

Hello to all,

I have created a CRM database to store my customers’ company details, personnel, visits on their companies etc. based on DACAM’s helpful instructions and I have faced an issue:

I have made a form in which I have included some subforms and a filter table which I use to jump from record to record. Although, after changing the drop down value and refreshing my form an annoying message appears, saying “The content of the current form has been modified. Do you want to save your changes?”. The weird thing is that it appears even if I haven’t made any changes. So clicking “No” or “Cancel” does not make an action (and it stays in the same record), while if I press Yes the current record is saved and it jumps to the chosen one successfully. Of course I want this message to appear if I do some changes, but only when that happens, else it is irritating to appear all the time.

Also is anyone able to help me how to import a text box to search for people names within my SubForm? I have to use again a filter table for this job? Because using the default search in navigation bar returns me no results when not searching the main form. The filter table for the main form was a bit advanced for me but I managed to do it, but thinking of having to put a second filter table for search purpose makes me a bit nervous :shock:

Sorry for my bad English, I hope that it makes sense.

Thank you in advance.

I finally got the rights to upload my file. :slight_smile:

Customers sample.odb

#2

So far, the solution to stop this message from appearing is to put the button in the SubForm within and not within the MainForm (as Frofa said), but then it refreshes only this SubForm (without appearing a message) and the other two SubForms keep showing old records. Is there any way to make all SubForms refresh at once without the use of a macro?

#3

Without a macro you need a separate refresh button for each subForm.

#4

Oh thank you Ratslinger. No, that’s not efficient. Could you please help me with the correct macro syntax for my case?

#5

Also, the macro that I will enable should have the control for the possible change of record’s data or that is another macro’s job?

#6

I think it might have been better if you had split this into two separate questions. It would be more simple and clear for Q and As, and I think also more useful for others to learn from, which is the greater reason for having an Ask style support web site. (Having said that there is another good alternative to using a filter, and I will give you help with that shortly.)

#7

I’m going to somewhat retract my previous statement. Your structure seems vague. Are these SubForms related to each other? Sounding more like a relationship problem and the way you created your form. Can you provide a brief explanation of the tables & how they relate to each other if they do?

#8

You are right EasyTrieve. I’ll try to separate the 2 questions when I have some time.
Well my structure is the following:
4 tables → 1 form, 3 subforms
People, Visits, Companies → 3 subforms containing personnel details (name,surname), visit details (date, notes) and company details (address, tel…) respectively.
The main form data tab is connected to the filter table which I have connected to the listbox.
Although, it seems like I’m obliged to put 3 different buttons to refresh all subforms.

#9

I wish I was able to upload my database file here, but I think I miss something with the karma stuff, because no upload button appears. The 4 tables (People, Visits, Companies and Filters) all have common the Company ID column. That is their relationship. What do you think I have done wrong?

#10

Oh, I managed to upload my database in the main question for your reference.

#11

Ok, good to look at your database. 1) Companies table primary key is not AutoValue=Yes. This can cause issues. Set to yes. 2) Filter table primary key is Boolean, well, ok, that’s sort of interesting. What’s going on with that? (Side tip: Suggest you change it’s name to FilterID from ID.) 3) List Box 1 (poor name choice) is in Main Form, not Form. (Suggest revise all form names, to: Form, SubForm, SubSubForm1, SubSubForm2, or possibly: Filter, Form, Sub1, Sub2.)

#12

Also Form Properties | Events | Before record action is pointing to Standard.Confirm.BeforeRecordAction but there is no code in Standard.Confirm. Suggest removing BeforeRecordAction event from Form Properites.

#13

Note, to change Companies CompanyID to autoinc, you must first get rid of the CompanyID=0 value in the first record, as 0 is a special autoinc value used in SQL to mean set to next auto-inc value. Because you have constraint based relationships setup these need to be unhooked first, and then re-hooked after the IDs are manually edited, and the auto-inc is set to yes. You have to hand edit the Companies, People and Visits tables and change CompanyID from 0 to 4. Then re-hook relations.

#14

At First, thank you very much for your suggestions!
1)Before I read your last comment, I changed the AutoValue by a more stupid way. I created a copy of my database to a different file, then deleted People and Visits tables. I changed to AutoValue and then reinserted the People and Visits from my backup database. I hope the result is the same.
2)As for the Boolean I changed it to Integer. Whatever the type it works normally. I had placed the Boolean because I saw it in an example.

#15

Also I changed the name from ID to FilterID. I revised all form names to Filter, Form, Sub1, Sub2 and Renamed the List Box 1 to Companies List Box. Fianally, I deleted the macro from the events tab.

Now, as for the List Box, it’s in the right place. It must be to Main Form (Filter) in order to work.

#16

Let’s close this one. Again, thank you very much for your suggestions.

#19

UPDATED: Check this out. It’s my new improved version of this solution that I promised you.

@preks, if you think this is cool, please check the (approved answer) check mark to the left of the top of this answer to turn it green. Thanks.

image description
image description704×749 80.6 KB

Caveat: This method is however, for now, limited to small to reasonable sized tables only. Large tables, like those with well over 1000 records might not work very well as a brute force method is used to seek the selector’s record#. This is because LO is missing a function to use an index for record lookups. I’m hoping that this missing functionality can be added to LO at some point.

#17

Make sure your REFRESH button is contained WITHIN the sub-form as shown in the FORM NAVIGATOR window (and NOT within the MainForm). That’s how it is in my setup, and I don’t get any message (such as “The content of the current form has been modified. Do you want to save your changes?”) when clicking the confirm/refresh button.

#18

Thank you for your prompt reply. You are right, my refresh button is within the MainForm, one step higher of all SubForms. But I did this in order to refresh all 3 subforms when clicking the button. If I put it in one SubForm it will only refresh this SubForm (and the other two SubForms will keep showing previous records). How can I get over this?

#20

My suspicion was correct. It is how your Forms/subforms are set up.

In MainForm properties you have a macro (supposedly) pointed to in Events -> Before record action. Delete this.

Next, the form (subform if you will) Form is correctly under MainForm. However, Subform and Subform2 should actually be subsubForms in the subform Form.

Here is what it should look like in Form Navigator:

image description
image description235×652 17.1 KB

With that change all the entries update with the one button clicked. Also see there is a dangling option button out there.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.