# Annoying warning message and search functionality

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.

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

Customers sample.odb

edit retag close merge delete

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?

( 2017-03-03 16:54:09 +0200 )edit
1

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

( 2017-03-03 17:12:20 +0200 )edit

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

( 2017-03-03 17:15:43 +0200 )edit

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?

( 2017-03-03 17:18:09 +0200 )edit
1

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.)

( 2017-03-03 17:43:16 +0200 )edit
1

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?

( 2017-03-03 17:57:15 +0200 )edit

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.

( 2017-03-03 18:45:16 +0200 )edit

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?

( 2017-03-03 18:47:52 +0200 )edit

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

( 2017-03-03 18:54:38 +0200 )edit
1

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.)

( 2017-03-04 20:30:05 +0200 )edit

Sort by » oldest newest most voted

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:

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

more

Ratslinger, you are genius.It worked as a charm indeed!

Haha, the option button is there on purpose. I put it as I could not find another way to stretch my bottom margin, so I put a non working tiny option button. :P

As for the macro, it was there forgotten, so I deleted it.

But now, how can I control if something is changed on my record before refreshing the form? The only solution is putting a macro? And if yes, is there a macro that has worked for you? And in which SubForm should I put it?

( 2017-03-04 01:15:51 +0200 )edit
1

Macros are not for you - at least until you learn a great deal more. Form=Master; the other two are People & Visits. On the Master, add a button with an action of Save record. If you make changes to Master click save record before any NEW selection with refresh button. For People & Visits (not all fields on these) any changes will be saved anytime you got to a different line in the grid. No button necessary for these. You can even add new entries for these two; going to a new line saves.

( 2017-03-04 02:29:41 +0200 )edit
1

( 2017-03-04 02:31:00 +0200 )edit

Well, I understood your solution but my problem is the opposite.. Everything that is written, even by mistake, (or maybe someone erases data by mistake) is saved automatically on the record change. So I just would like to put a reminder before the record change to remind the user that somewhere he made changes. If he did on purpose he will click Yes and jump to next record. If not, he will click No, the changes will revert and he will jump to next record. This can only done with macro?

( 2017-03-04 11:41:24 +0200 )edit
1

@preks The forum is set up to ask a question and (hopefully) get an answer which satisfies the question. Then people can search for their problem and possibly find an answer to their question. If different questions are presented and answered after the initial question, it gets buried. For no other reason, please ask this as a new question. It is actually a pretty good question.

( 2017-03-04 15:42:39 +0200 )edit

That's what I wrote in my initial question: "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."

We found the way to make it disappear totally (by giving it the correct structure), but that's not what I asked in my first question.

( 2017-03-04 15:55:21 +0200 )edit
1

Sorry I didn't read it that way. Let me give it a bit of thought.

( 2017-03-04 15:58:30 +0200 )edit

No, wait.. I create a new question... ;)

( 2017-03-04 16:00:49 +0200 )edit

Thank you very much. Let's close this one. You can find my next question on my profile!

( 2017-03-04 16:08:02 +0200 )edit
1

@preks FYI - the message referred to in you question was actually related to a change made to the "Filter" table because the listbox had changed the value but the record was not updated yet when the refresh button was selected. Of course, when the button was moved to the correct position the message stopped. So the approach now must take on a whole different aspect. I write this only so you understand what was happening.

( 2017-03-04 17:55:34 +0200 )edit

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.

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.

more

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.

more