Solved Restricting recordsets to improve performance (1 Viewer)

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
After some testing yesterday I decided to change the way my DE form loads:

https://www.access-programmers.co.u...pecific-value-in-text-box.309738/post-1671935

This means using

Code:
DoCmd.OpenForm "frmInspection", acNormal, , , acFormAdd, acDialog

Which results in a blank form with only 1 record in the navigation bar. This is ideal.

I then started to wonder about some of my other forms. Specifically, the popup forms that are accessed from that DE form, for example to enter Comments:

Where.png


I open this form with a WHERE clause so that the ID matches the ID on the main form. You get a filtered popup form that, if you undo the filter, you can cycle the comments for all the records.

I can not use the acFormAdd data mode here since it needs to work with the current record. Does anyone know how I can get a similar result to acFormAdd, where the ONLY record available is the one the user is working with, yet is still tied to the correct record? To be clear, I always hide the navigation bar - this is for performance optimization.
 

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
Hmm, not sure why you can't use acFormAdd as I do it all the time...

SQL:
    If DCount("tSourceID", "tblTips", "[tSourceID]=" & Nz(Me.txtCustomerID, 0)) = 0 Then "Checks to see if there are comments entered and if none
        DoCmd.OpenForm "sfrTips", , , , acFormAdd  'Opens to allow additions but with the line below puts in your PK
        DoEvents
        Forms![sfrTips]![txtSourceID] = Me.txtCustomerID
    Else
        DoCmd.OpenForm "sfrTips", , , "[tSourceID]=" & Me![txtCustomerID] 'Open form to comments already entered if there
    End If

The Form is set as a pop-up. What am I missing?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:44
Joined
May 7, 2009
Messages
19,169
same effect, when on design view of the form you set it's DataEntry property to Yes.
 

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
Hmm, not sure why you can't use acFormAdd as I do it all the time...

SQL:
    If DCount("tSourceID", "tblTips", "[tSourceID]=" & Nz(Me.txtCustomerID, 0)) = 0 Then "Checks to see if there are comments entered and if none
        DoCmd.OpenForm "sfrTips", , , , acFormAdd  'Opens to allow additions but with the line below puts in your PK
        DoEvents
        Forms![sfrTips]![txtSourceID] = Me.txtCustomerID
    Else
        DoCmd.OpenForm "sfrTips", , , "[tSourceID]=" & Me![txtCustomerID] 'Open form to comments already entered if there
    End If

The Form is set as a pop-up. What am I missing?
I'm getting errors saying I must enter a value in other fields on the main form. It's like the Comment box is not related to the record because I didn't use a WHERE statement on opening the Comment form.

Now your code is interesting because you are adding in the PK manually after opening the form? Does your Tips form have a field for the primary key? My Comment form is just a text box and a close button and normally works with a WHERE on load. I'm seeing something interesting in your approach but I need a little more help on how it works. Thanks.
 

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
I am not adding the PK, I am adding the FK (Foreign Key) which is the Customer ID. That field is what connects it to that Customer's notes.

It has a control for the PK and the Customer ID that is hidden. No other way to get the Customer ID in the field without it being on the Form.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
Now your code is interesting because you are adding in the PK manually after opening the form? Does your Tips form have a field for the primary key?
And if you are not doing that then how are you doing it now? How do you create a new record and relate it to the parent.
 

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
And if you are not doing that then how are you doing it now? How do you create a new record and relate it to the parent.
As I explained, I open the Comments form with a WHERE clause. Comments is a field in the parent table - I just open a popup form for it because it needs more space.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Pardon me for jumping in... I hope I am not adding any more confusion to this topic. One other approach you could try, I guess, is to use an unbound form and bind it at runtime to a record source with only one record in it. Just a thought...
 

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
I am not adding the PK, I am adding the FK (Foreign Key) which is the Customer ID. That field is what connects it to that Customer's notes.

It has a control for the PK and the Customer ID that is hidden. No other way to get the Customer ID in the field without it being on the Form.
Ok, I was just reading your code which states

'Opens to allow additions but with the line below puts in your PK

Hidden controls with the PK is what I figured you were doing. I might try that - thanks.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
Comments is a field in the parent table - I just open a popup form for it because it needs more space.
So in truth you are not adding any comments, you are building a zoom box. No records are created.

Here is one I built that I think works very well and super easy to use for your case. Basically what you are doing is a zoom form. In fact you can use the native zoom feature instead. However, with the custom zoom I can tailor the form as I want.
To use it is one line the code does it all.

Private Sub Comment_DblClick(Cancel As Integer)
openUnboundZoom "Enter Comment for customer: " & Me.CustomerID
End Sub

This is one feature that gets imported to all my DBs.
 

Attachments

  • AddCommentUnbound.zip
    55 KB · Views: 101

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
Ok, I was just reading your code which states

'Opens to allow additions but with the line below puts in your PK

Hidden controls with the PK is what I figured you were doing. I might try that - thanks.
Yep, that comment is WRONG. That's what happens when you type to fast! :)
 

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
So in truth you are not adding any comments, you are building a zoom box. No records are created.

Here is one I built that I think works very well and super easy to use for your case. Basically what you are doing is a zoom form. In fact you can use the native zoom feature instead. However, with the custom zoom I can tailor the form as I want.
To use it is one line the code does it all.

Private Sub Comment_DblClick(Cancel As Integer)
openUnboundZoom "Enter Comment for customer: " & Me.CustomerID
End Sub

This is one feature that gets imported to all my DBs.
You are exactly right and thank you for sharing. It looks like a lot of code compared to other methods - should I be concerned with efficiency? I know local code is probably better than telling the back end to send you ALL the comments, then filtering them on your local machine. That is what I'm trying to avoid - just send me the one comment that applies. Maybe I can figure out a way to use criteria in the record source query...I know, more than one way to do things. I'm just drawn to simplicity.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
This is the most efficient of any possible solution, but that may not mean anything in real terms. It is definitely easy to use though. This uses a completely blank form. There is no call to the database. Again, like my class modules it is a black box, being concerned about the "complexity" of the code is not really your issue. Unless I made a mistake and you have to fix it. You are just using the watch to tell time and not building it.
 

zeroaccess

Active member
Local time
Today, 12:44
Joined
Jan 30, 2020
Messages
671
I just wanted to report back what I ended up doing.

MajP's method looks brilliant; however my Comment isn't displayed as a text box, but a Comment command button. I would recommend trying his method if you are displaying a truncated portion of the text and want a zoom box to enlarge.

I modified the Comment form's record source query to have the following criteria:

Comment.png


Then, On Click of the Comment button, I am doing the following:

NGINX:
    TempVars.Add "CurrentRecordID", InspectionID

Then it's simply DoCmd.OpenForm as normal and let the Comment form open with a recordset of 1.

I have also added the same criteria to my main Inspection form so it also opens with a recordset of 1, using the value of the TempVar that was set when you clicked to open the record. When it comes to adding a NEW record, the recordset returns no records, so you get an empty form. To open that Inspection form when hitting NEW I am using the
NGINX:
    DoCmd.OpenForm "frmInspection", acNormal, , , acFormAdd, acDialog
method but it may be redundant with the recordset as 1 via TempVar.

The result is that you are only going to ask the back end (on network drive, SQL server, etc) to send a single record - the one you need to work with. I am replacing my opening of forms using WHERE statements, which ask the server to send potentially thousands of records to then be filtered by your local machine, with this new method.

So here's one for program optimization - Cheers!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
@zeroaccess
The zoom idea could be easily modigied to work off of a cmd button by passing in a reference to the parent form and the name of the field. This code would be far easier than the existing code. The existing code had a lot of bells and whistles to handle non updateable things. Then you simply update the recordset. I will demo if you care. In that method you still do not have to go back to the server at all because you already have a populated recordset. Are you binding your form to an ADO recordset or DAO? Again there is no real efficiency, but you seem pretty interested in not having to pull anything.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:44
Joined
May 21, 2018
Messages
8,463
You may find this approach interesting. You are having two forms use the same recordset.
Code in the module
Code:
Public Function openBoundZoom(FormName As String, FieldName As String, Optional WindowMode As AcWindowMode = acDialog)
On Error GoTo errlbl
Dim frm As Access.Form
Set frm = Forms(FormName)
'MsgBox "Boundzoon"
DoCmd.OpenForm "frmBoundZoom", , , , , WindowMode, FormName & ";" & FieldName
Exit Function
errlbl:
MsgBox Err.Number & " " & Err.Description & " In openBoundZoom"
End Function
code in the popup form
Code:
Private Sub cmdCancel_Click()
Me.Undo
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdOK_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Load()
'On Error Resume Next
Dim frmName As String
If Not IsNull(Me.OpenArgs) Then
frmName = Split(Me.OpenArgs, ";")(0)
Set Me.Recordset = Forms(frmName).Recordset
Me.txtZoom.ControlSource = Split(Me.OpenArgs, ";")(1)
Me.txtZoom.SetFocus
Me.txtZoom.SelStart = 0
End If
End Sub
Code to use this
Code:
Private Sub cmdComment_Click()
openBoundZoom Me.Name, "Comment"
End Sub
Private Sub cmdCountry_Click()
openBoundZoom Me.Name, "Country", acWindowNormal
End Sub
So all you are doing is creating a pointer to the forms recordset. Anything you do in the popup occurs in the mainform. What you will find interesting is open the Country. This is not opened dialog. Now navigate to new records in the main form and watch the pop up change.
 

Attachments

  • AddCommentBound.zip
    50.8 KB · Views: 89

Guus2005

AWF VIP
Local time
Today, 18:44
Joined
Jun 26, 2007
Messages
2,645
Instead of passing arguments in the openform statement, like MajP suggested, you could use TempVars to do the same.
Code:
Private Sub Form_Load()
'On Error Resume Next
  Dim frmName As String
  If Not IsNull(Me.OpenArgs) Then
    frmName = TempVars!WhatEverYouWant
    Set Me.Recordset = Forms(frmName).Recordset
    Me.txtZoom.ControlSource = Split(Me.OpenArgs, ";")(1)
    Me.txtZoom.SetFocus
    Me.txtZoom.SelStart = 0
  End If
End Sub
But i would follow MajP's example and pass arguments
 

Guus2005

AWF VIP
Local time
Today, 18:44
Joined
Jun 26, 2007
Messages
2,645
Another solution to your 1 record problem is to use an unbound zoom form.
Show the value the user can edit and build an update sql statement to save the changed value.

It just shows the one value and nothing more. Tab brings you to the other control and you won't cycle through your recordset because there isn't one.

HTH:D
 

Users who are viewing this thread

Top Bottom