Solved Need help optimizing my query after SQL Server migration

[renQ]

The normal route would be a pop up single form that is opened modally/dialog on top the of the main list, and then returns you to where you where after is opened.
I have lots of them but they are are proprietary to the clients app, and difficult to take pictures of without munging a ton of the data.

I normally use a double click event on the Job_Number/Contract/Client_ID and pass it to the pop up with the OpenArgs property, then set the pop form to the appropriate single record.

Something like this:
View attachment 116209

Thats all fine thanks for the help, I think I can find a way to implement this. But I was wondering after updating that single record have you been able to reflect that change on the original table instantly. Or does the original table have the old info?

 

[GPGeorge]

Thats all fine thanks for the help, I think I can find a way to implement this. But I was wondering after updating that single record have you been able to reflect that change on the original table instantly. Or does the original table have the old info?

Once you have saved the change(s) to a record, they are in the table. To see the change(s) in a form, though, you need to requery or refresh that form's recordsource.

 

[renQ]

Once you have saved the change(s) to a record, they are in the table. To see the change(s) in a form, though, you need to requery or refresh that form's recordsource.

Is there a way to do this without losing your position in the table/form?

 

[GPGeorge]

Is there a way to do this without losing your position in the table/form?

Yes, but it is VBA that I don't have at hand at the moment. I'm sure one of the less forgetful members here will have a suggestion handy, though.

 

[Minty]

If you requery the Recordset not the form it will normally stay put where it was. (An often overlooked method)

If not simply store the unique ID before opening the edit form, then requery, then use the rs.bookmark and find first method to get you back where you were.

 

[GPGeorge]

If you requery the Recordset not the form it will normally stay put where it was. (An often overlooked method)

If not simply store the unique ID before opening the edit form, then requery, then use the rs.bookmark and find first method to get you back where you were.

I've not had success requerying the form's recordset directly. Can you provide a working example for me to learn from?

 

[GPGeorge]

Well, that's frustrating. The first form i tested raised an error, but others didn't. Now I have to figure out what's different about that form.

 

[Minty]

@GPGeorge I'll have to find an example. I'm sure I've made it work somewhere.
Probably be tomorrow as I need to sign off shortly

 

[Gasman]

@GPGeorge
Where you have a Me.Requery or any form requery, just insert Recordset eg: Me.Recordset.Requery

Hard to give you a working example, but here is code from my old SSAFA DB
In fact I just added the recordset, as it was not there before.

Private Sub cmdRequery_Click()
    Dim lngID As Long
    Dim rst As DAO.Recordset
    Dim strCriteria As String
   
    ' Save record so requery on subform will pick up all last record written
    If Me.Dirty Then Me.Dirty = False
    lngID = Me.ID
    Me.Recordset.Requery
    strCriteria = "ID=" & lngID
    Set rst = Me.sfrmEmails.Form.Recordset

    ' Requery the subform
    Me.sfrmEmails.Form.Recordset.Requery
    ' Go back to record we were on
    Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    rst.FindFirst strCriteria
    Set rst = Nothing
End Sub

so really the FindFirst is no longer needed.

 ' Go back to record we were on
    'Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    'rst.FindFirst strCriteria
    Set rst = Nothing

 

[NauticalGent]

Requery an MS Access Form While Remaining on the Same Record

Have you ever wanted to requery a form after a user inserts a new record or modifies an existing record and wanted to stay on the same record?

www.devhut.net

 

[GPGeorge]

@GPGeorge
Where you have a Me.Requery or any form requery, just insert RecordSouce eg: Me.Recordset.Requery

Hard to give you a working example, but here is code from my old SSAFA DB
In fact I just added the recordset, as it was not there before.

Private Sub cmdRequery_Click()
    Dim lngID As Long
    Dim rst As DAO.Recordset
    Dim strCriteria As String
  
    ' Save record so requery on subform will pick up all last record written
    If Me.Dirty Then Me.Dirty = False
    lngID = Me.ID
    Me.Recordset.Requery
    strCriteria = "ID=" & lngID
    Set rst = Me.sfrmEmails.Form.Recordset

    ' Requery the subform
    Me.sfrmEmails.Form.Recordset.Requery
    ' Go back to record we were on
    Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    rst.FindFirst strCriteria
    Set rst = Nothing
End Sub

so really the FindFirst is no longer needed.

 ' Go back to record we were on
    'Me.Recordset.FindFirst strCriteria
    ' Now the sub form
    'rst.FindFirst strCriteria
    Set rst = Nothing

Thanks, as noted, the problem only occurred in one subform, for reasons.

I hope to go back at some point and try to identify what's different about that subform.

Of course, it was the first one I tried....

 

[GPGeorge]

Thanks, as noted, the problem only occurred in one subform, for reasons.

I hope to go back at some point and try to identify what's different about that subform.

Of course, it was the first one I tried....

The subform's recordsource is a passthru query, not a local query.

 

[Gasman]

Only ever used Access tables/queries, sorry

 

[GPGeorge]

Only ever used Access tables/queries, sorry

That was my bad. I just randomly picked one subform to try it on; turns out I made a bad choice.

 

[GPGeorge]

I don't have a link to the thread but I discovered a while back that hidden indexes created by Access on FK's do not get upsized. Only indexes that are manually created get upsized. No one was ever able to tell me whether or not having two indexes on the same field cause a problem for ACE, so since Access always creates hidden indexes and those don't get upsized, I'm not sure I want to create duplicate indexes in the ACE BE's. So, I don't crete the manual indexes until I'm ready to convert the BE. Then I create them in ACE and SSMA upsizes my indexes correctly.

I think that SSMA probably doesn't upsize the indexes created on foreign key fields when referential integrity is enforced because they are not required in SQL Server or created by default for Foreign Key fields in SQL Server tables.

I think SSMA should have an option to include them anyway, or omit them at the preference of the user.

 

[renQ]

I am having trouble keeping the position I was at after requerying the form. I feel this is because I am using a pass through query, it does not allow me to access the record set property of the form.

 

[renQ]

The view isn't very efficient when it comes to navigating my form. I'm not entirely sure why but it might be because of how Access is handling the view. Specifically for the main use that my company has which is using the find feature to better hop around the different records a passthrough query works much better.

 

[cheekybuddha]

I don't have a link to the thread but I discovered a while back that hidden indexes created by Access on FK's do not get upsized.

See post #10