Solved How to reinitialize a form with a fresh data-entry view?

[cheekybuddha]

The form opens and we have to wait a long time for the form to pull all the data.

Actually this is not the case.

If you do a trace on what is received by the SQLServer you will see that Access is clever enough to request first just the records that will be displayed, then continue to fetch the rest

 

[cheekybuddha]

@KitaYama

See this post

 

[KitaYama]

Actually this is not the case.

If you do a trace on what is received by the SQLServer you will see that Access is clever enough to request first just the records that will be displayed, then continue to fetch the rest

While I'm reading your link, then why we have to wait for ever for the form to be opened? (when the record source is set to a table with a large amount of records)

 

[KitaYama]

Could you elaborate on what specifically makes your form unclear, forcing you to clear it? Is it based on a temporary table, or does it hold data in memory?

The form is not based on temp table or memory. It's based on a table. My terminology may be wrong when I used "Clear".
When a form with "Data Entry" set to yes is opened, the recordsetclone.recordcount is equal to zero. When we add records, the recordcount is changed to 1->2->3->4->
By "clearing recordsetclone", I meant set the recordsetClone to nothing, or setting its recordcount to zero. Or as I explained in #1, make the form exactly like it's opened.

Can you elaborate on why flagging a record as emailed wouldn't be a better fit?

The records are flagged as Emailed. (with a datatime field and UserFK field in a junction table)
But there are more IFs as I explained.
I'd appreciate if you have a better suggestion in the following situation.

User starts entering data.
She saves 5 records for two suppliers, clicks "Send Mail". Two mails are sent to suppliers. (The records are flagged as mail sent)
She continues to add more records. She adds 3 more records for the same supplier (or another one). But doesn't click the "Send Mail". Because for some specific reasons, these records won't be emailed.
Still she continues to add more records. This time 10 records. These last 10 records should be emailed.
Now how does your program can recognize the second set of data (3 records that should not be mailed) with the last set of records (10 that should be mailed)?
13 records in recordsetclone exists that has not flagged as MailSent. But it's not clear which should be sent and which should not.

If we can "clear" (again bad terminology) the form's recordset after the records that should not be mailed, the recordsetclone contains only the last 10 records.
That was my main question. How to make the form forget about the already entered data, and start fresh.

You want to store what has been sent in that hidden textbox? Why not keep a record of such an important thing as an interaction with a supplier?

If I was successful to explain my situation above, I think this is already answered. The interaction is saved and the record is set as emailed. The problem is not all records need to be emailed.

As I said, there are a lot more of conditions and IFs that we're facing. And it's really hard to explain all. I hope the above, at least shows a part of it.

Thanks for jumping in. I really appreciate your help, and am open to any kind of suggestion.

 

[cheekybuddha]

It will be slow if you try and scroll directly to the last record because then it needs to load all the records.

I agree that you should only load the records you need using a filter.

I was just trying to show that it is not true to say that it will load all the records on opening (unless you use a stored procedure as the RecordSource)

 

[KitaYama]

It will be slow if you try and scroll directly to the last record because then it needs to load all the records.

I agree that you should only load the records you need using a filter.

I was just trying to show that it is not true to say that it will load all the records on opening (unless you use a stored procedure as the RecordSource)

Just a correction.
I just added a continouse form bound to a table with 1,277,585 records.
Opening the form took less than a second.
Going to the last record took around 4 seconds.

Seems you're correct. (as always.)
I don't know what made me think it would take a long time for the form to be opened. I stand corrected.
Everyday has a new lesson. No matter how old I get.

 

[GaP42]

User starts entering data.
She saves 5 records for two suppliers, clicks "Send Mail". Two mails are sent to suppliers. (The records are flagged as mail sent)
She continues to add more records. She adds 3 more records for the same supplier (or another one). But doesn't click the "Send Mail". Because for some specific reasons, these records won't be emailed.
Still she continues to add more records. This time 10 records. These last 10 records should be emailed.
Now how does your program can recognize the second set of data (3 records that should not be mailed) with the last set of records (10 that should be mailed)?
13 records in recordsetclone exists that has not flagged as MailSent. But it's not clear which should be sent and which should not.

So you could have two flags set on the record: One for suppliers / or orders to the supplier, for which an email is not to be sent (emailRequired) and another for email issued (emailIssued). The person doing data entry must "know" form the flow you describe which are to be processed and issued an email and which are not. Can that be determined automatically from other values/settings for the supplier? That would then mean that the process does not require the recordset to be cleared ... maybe

 

[KitaYama]

The person doing data entry must "know" form the flow you describe which are to be processed and issued an email and which are not.

Yes, they do.

Can that be determined automatically from other values/settings for the supplier?

No.
Of course there are rules around. For example the total price of the order, the total quantity of the order, the delivery date, .....
But it's hard to program something that not even a human can decide. Even users at times have to consult with others to decide.

The records that are not emailed, will be discussed in a closed meeting between the person in charge of the customer we've received our orders from, a supervisor, the head of manufacturing section and someone from shipping section. They finally decide on a date and build the schedule needed. When the parts we order should be delivered, when our production starts, when it should end.
After all is scheduled, the mail with the final delivery date will be sent to the supplier(s).

So you could have two flags set on the record: One for suppliers / or orders to the supplier, for which an email is not to be sent (emailRequired) and another for email issued (emailIssued).

This can be a good idea, but requires a change in the table design in sql server. I will discuss this suggestion with my colleagues.
Thanks.

 

[KitaYama]

I will discuss this suggestion with my colleagues.

@GaP42 The result came out sooner than I expected.
We decided on if @The_Doc_Man's Me.Requery or @arnelgp's "Select * From tbl" can do the job, we prefer not to change the table's design at this point.
Of course, the suggestion was added to the to-do-list and will be thought over again if it can help for our future problems.

Thanks for the advice.

 

[GaP42]

Happy that it was considered as potential solution- and the immediate resolution was as expected, as the potential solution should be packaged with other fixes/improvements that may involve workflow changes that require possible changes to training.

 

[KitaYama]

It will be slow if you try and scroll directly to the last record because then it needs to load all the records.

Ok. I think I understand where my wrong beliefs had came from.
As I explained, a new form with a large dataset was very fast (as you explained), but when I changed our search result form's recordsource to tblOrders, it's drastically slower than the numbers I gave in #26.

I think Conditional Formatting, and On_Current event has a good part in slowing down a form. (Just a guess).

 

[Edgar_]

The form opens and we have to wait a long time for the form to pull all the data.

You could implement pagination in your class, but I understand: you have lots of data that shouldn't be brought at once. It does not even have to be pagination though, you could simply limit it to a few records that meet certain criteria. But it's a choice you've made, I also get that.

Users have been given a way to open search result forms directly from ribbon.

If I understood this correctly, you're opening instances of forms where the search results from multiple queries can persist. Is that the case?

Going back and forth between search form and result form makes me crazy.

Not sure I understand what is going on here. Can you elaborate on why you had to separate the search form and the result form? I understand that you have solved this by putting the search input in the header, but what did it look like "going back and forth"? was it really two separate windows doing it?

Now how does your program can recognize the second set of data (3 records that should not be mailed) with the last set of records (10 that should be mailed)?

Well, in the first place, why are those 3 records in the email list if they won't be emailed? wouldn't it make sense if your user deleted them or soft deleted them instead of having them there? I understand if they still need to be there for a completely different reason, but doesn't that mean that this form is doing two things and it should only be doing one (emailing)?

That was my main question. How to make the form forget about the already entered data, and start fresh.

Deletion is suggested, as mentioned above.

OK. The destiny of the 3 not emailed records will be discussed. That means 3 records belong in a workflow and 10 records belong in another workflow. Maybe this form should let your user flag what belongs where to email only what needs no further discussion. In my ranch, we would send the 13 records to a "requisition items" table where a superior will authorize them. This superior will mark 10 records as authorized and your user would then be able to email them, not before. The other 3 records will stay in the requisition phase until their fate is decided. But it can be simpler: mark what should be emailed and persist that decision somewhere.

 

[Minty]

I think Conditional Formatting, and On_Current event has a good part in slowing down a form. (Just a guess).

Just to chuck my 2p worth in here, in my experience CF definitely works more slowly on Azure SQL linked tables than Access ones.
I have improved this sometimes by setting display "flags" in the views I connect to and using those to control things less intrusively than CF.