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

[KitaYama]

Ok, I know. Strange request. But it can help my specific situation.

I have a form bound to a table.
The "Data Entry" property of the form is set to Yes.

After adding several records, Is there any way (VBA) to reset the form to it's original "blank" data-entry state? Just like closing and opening it again.

In another word, after adding several records, is there any way to truly replicate the state of the form when it first opens, with no records visible?
Something like resetting its recordset and forcing it to reinitialize with a fresh data-entry view.

I tried
Set Me.Recordset = Nothing
but it causes the form loose its link to the source table.

I can simply set echo to false, close it, then use hidden parameter in OpenForm and then visible to true, but I couldn't get the OK from those who use the form.

Thanks for any kind of suggestion.

 

[xavier.batlle]

Ok, I know. Strange request. But it can help my specific situation.

Perhaps something like this:

Dim strRecordSource As String

strRecordSource = Me.RecordSource
Me.RecordSource = "Select * From  [name of the table] Where 1=2"
Me.Recordset.Requery
Me.RecordSource = strRecordSource

 

[arnelgp]

you can try to use the Recordsource:

SELECT * FROM yourTable Where (1=0);

this will return No Record recordset.

 

[KitaYama]

Million thanks to both.
Re-setting the RecordSource did the job.

 

[KitaYama]

Just a further test shows the following will do the same.
There was no need to Where clause.
I wonder why I didn't think of it at all.

Me.RecordSource = "SELECT * FROM myTable"

Again thanks to both for your help.

 

[The_Doc_Man]

Perhaps even simpler - try Me.Requery, since you have Data Entry = True.

The reason I suggest that is because if you reset the .RecordSource to what it already was, that still implies a Requery. So just cut to the chase. Trigger the Requery for the main form and be done with it.

 

[KitaYama]

Perhaps even simpler - try Me.Requery, since you have Data Entry = True.

The reason I suggest that is because if you reset the .RecordSource to what it already was, that still implies a Requery. So just cut to the chase. Trigger the Requery for the main form and be done with it.

Thanks Doc.
Just tested and it works.

 

[KitaYama]

The more I receive your suggestions, the more I hate myself.
How didn't I think of that?

 

[The_Doc_Man]

How didn't I think of that?

Because you were immersed in a different level of the problem? I can see the simple answers 'cause I'm such a simple guy!

 

[arnelgp]

the Form is Data Entry, why bother to Requery or change recordsource? after saving the record you are always in new record.

 

[KitaYama]

the Form is Data Entry, why bother to Requery or change recordsource? after saving the record you are always in new record.

It's a long story, with a lot of conditons and problems. The purpose is not to go to a new record. The purpose is to clear the form.

It's a purchase form, the users start entering data and then click a button to send the orderd items to the suppliers.
The "Send Mail" button loops through form's recordsetclone and create the mails and sends them.
Once the mails are sent, users continue entering new items, but they can not send the new records mails. Because if they do, the recordsetclone still holds the previous records, and the same mails are sent again over and over.

I know what you think. You may ask why they don't send the mails when all the records are entered.
As I said, there are a lot of business rules and conditions that prevents us to do so, and even if I explain it here, it may be hard to understand the situation..

 

[arnelgp]

why not use dictionary object.
check first if the autonumber is in the dictionary.
if not in dictionary send it to mail and save to dictionary.

 

[KitaYama]

why not use dictionary object.
check first if the autonumber is in the dictionary.
if not in dictionary send it to mail and save to dictionary.

That's how I'm managing it now. Not a dictionary, but a hidden unbound textbox.
I was looking for a simpler way.

Assigning new PKs to a dictionary, and then checking if the PK exists in the dictionary
Vs
Me.requery

I'm sure you understand which method is simpler.

Thanks again for your concern.

 

[Edgar_]

The purpose is not to go to a new record. The purpose is to clear the form.

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?

he recordsetclone still holds the previous records, and the same mails are sent again over and over

Can you elaborate on why flagging a record as emailed wouldn't be a better fit? Is there no record about the email being sent so that your users know what to track? Have you tried modifying the recordset with a column that holds that?

Not a dictionary, but a hidden unbound textbox.
I was looking for a simpler way.

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?

By the way.
I legitimately want to know why setting the Where clause to an impossible match has been suggested and validated twice ITT. Very interesting thread, by the way.

 

[cheekybuddha]

I legitimately want to know why setting the Where clause to an impossible match has been suggested and validated twice

Try it, and see how many records are returned!

(Easiest way to open a recordset from a populated table but without any records in it.)

You can also use:

SELECT
  *
FROM YourTable
WHERE FALSE
;

 

[Edgar_]

Try it, and see how many records are returned!

(Easiest way to open a recordset from a populated table but without any records in it.)

Appreciate the clarification, but I still fail to see the justification for opening a recordset without records. While I understand the intention behind it, and thank you for that, I'm still trying to think of a valid scenario where this makes sense.

Since others seem to use this approach, I assume there must be legitimate cases for it. If you or anyone are willing to shed some light on them, I'd be interested to learn more. I'm not saying it's wrong, it's just that my design process hasn't yet led me to encounter this necessity, so I think it's useful to understand the reasoning behind it.

 

[cheekybuddha]

I'm still trying to think of a valid scenario where this makes sense.

Sometimes it can make more sense to open a recordset to add new records in code rather than using a query.

So then it's better not to pull any records down the pipe that you aren't going to use.
eg

With CurrentDb.OpenRecordset("SELECT fld1, fld2, fld3 FROM YourTable WHERE 1=0;")
  .AddNew
    .Fields("fld1") = Me.txt1
    .Fields("fld2") = Me.txt2
    .Fields("fld3") = Me.txt3
  .Update
  .Close
End With

Just as fast as a query for a small amount of inserts and you don't have to fanny around worrying about delimiters etc.

 

[Edgar_]

.AddNew


Just as fast as a query for a small amount of inserts and you don't have to fanny around worrying about delimiters etc.

Excellent example, that is indeed a valid reason. Thanks. I'm still open to knowing other reasons, but I can see the use in that.

 

[KitaYama]

I'm still open to knowing other reasons,

Most of our tables in sql server has more than a million records.

During developing process, what happens if a developer double click a form in navigation pane when its recordsource is set to someTable or Somequery?
The form opens and we have to wait a long time for the form to pull all the data.

In our case, each form's recordsource is set to:

SELECT * FROM SomeQuery WHERE False;

And, each form at least has the following two properties:

Public Property Let fltr(str As String)
    m_Filter = str
End Property
Public Property Get fltr() As String
    fltr = m_Filter
End Property

Public Property Get src() As String
    src = "SomeQuery"
End Property

Then we open the form:

Docmd.OpenForm "MyForm"
Forms("MyForm").fltr= "Some Filter"
SetFormRS Forms("MyForm")

Public Function SetFormRs(frm As Form)
    ' Do some tasks here '
    frm.Recordsource="SELECT * FROM " & frm.src & "WHERE " & frm.fltr
    ' do some more tasks here '
End Function

 

[KitaYama]

Another benefit to above:

Searching a record and norrowing down the result is a husstle. Going back and forth between search form and result form makes me crazy.

All of our continuous forms, have several textboxes, combo boxes, checkboxes etc. in the header.
Users have been given a way to open search result forms directly from ribbon.
When a user opens search result, because of WHERE False in their record source, the form is opened empty.
Adding search keyword using controls in the header, sets fltr property of the form and calls SetFormRs function.
This way they can narrow down the result, instead of going back and forth between search form and result form.