Nedd to requery form with out losing place 2

[dbar10]

I have a Form that has quite a few subforms in it that update based on entries in the [Units Worked Billing Query subform] I am including the code that I have now just to give you an idea of the prameters. From the [Units Worked Billing Query subform] Hours Worked field AfterUpdate I need to requery the [Billing Work Form] and have it return to the same record I was working on and SetFocus on the last record and in the Units field of the [Units Worked Billing Query subform]. I hope this is clear. I am stuck and frustrated. Any help you could give would be very appreciated.

Private Sub Hours_Worked_AfterUpdate()
Forms![Billing Work Form].Requery
Me.[Units Worked Billing Query subform].SetFocus
DoCmd.GoToRecord acActiveDataObject, , acLast

Forms![Billing Work Form].[Units Worked HC Month].Requery
Forms![Billing Work Form].[Units Worked PC Month].Requery
Forms![Billing Work Form].[Units Worked Respite Month].Requery
Forms![Billing Work Form].[Units Worked APC Month].Requery
Forms![Billing Work Form].[Units Worked LPN Daily Month].Requery
Forms![Billing Work Form].[Units Worked Nurse Visit].Requery
Forms![Billing Work Form].[Units Worked Nurse Superv MO].Requery




End Sub

This obviously resets me to the first record in the main form. I need it to do what I've described above Thanks

 

[missinglinq]

To be honest, your explanation left me completely at sea as to what you're doing, but the code for doing this in a single form is:

Where [UniqueField] is a field unique to only one record.

Where [UniqueField] is Text

Dim RecUF as String

RecUF = Me!UniqueField
Me.Requery
Me.Recordset.FindFirst "[UniqueField] = '" & RecUF & "'"

Where [UniqueField]is Numeric

Dim RecUF as (Fill in number type here)

RecUF = Me!UniqueField
Me.Requery
Me.Recordset.FindFirst "[UniqueField] = " & RecUF



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dbar10]

I'm sorry for the confusion. Thanks for your response.
I hope I can clear this up.

My [Billing Work Form] has a subform called [Units worked Billing Query subform] after entering the hours worked in the subform I need to use the AfterUpdate command to requery the [Billing Work Form]. After the requery I need the [Billing Work Form] to return to the record I was working on and the Focus to go to the last record of the [Units Worked Billing qquery subform] at the [Units] field. The UniqueID is the [ClientID] and it is numeric.

Thanks for your help. Doug

 

[dbar10]

OK, I incorporated your ideas and it gets most of the job done. But cursor is landing in the first record of the subform in the first field. I need it to land in the last record of the subform in the units field. Can you help me figure that out? Thanks

Private Sub Hours_Worked_AfterUpdate()
Dim RecUF As String

RecUF = Me!ClientID
Me.Requery
Me.Recordset.FindFirst "[ClientID] = " & RecUF & ""


Forms![billing Work Form].[Units Worked HC Month].Requery
Forms![billing Work Form].[Units Worked PC Month].Requery
Forms![billing Work Form].[Units Worked Respite Month].Requery
Forms![billing Work Form].[Units Worked APC Month].Requery
Forms![billing Work Form].[Units Worked LPN Daily Month].Requery
Forms![billing Work Form].[Units Worked Nurse Visit].Requery
Forms![billing Work Form].[Units Worked Nurse Superv MO].Requery




End Sub

 

[ProgramError]

Try findlast instead of findfirst


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[dbar10]

That worked great. All is good except I still need the cursor to land in the fourth field [Units] in the last record. It is landing in the last record but the first field. Is this possible? I really appreciate your help.

 

[TheAceMan1]

How are ya dbar10 . . .

I assume from your post origination your individually requering subforms. Be aware ... if you requery the manform you automatically requery the subforms! The trick is to hold the primarykeys of the form/subforms in queston so you can return to those records proper! Here's an example (copy/paste the following tot he AfterUpdate of [Units Worked Billing Query subform]! Hours Worked :

[blue]   Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As Long
   
   Set frmMain = Forms![Billing Work Form]
   Set sfrm = frmMain![Units Worked Billing Query subform].Form
   hldMainID = frmMain![PrimarykeyName]
   hldsfrmID = sfrm![PrimarykeyName]
   
   frmMain.Requery
   frmMain.Recordset.findFist "[PKname] = " & hldMainID
   sfrm.Recordset.FindFirst "[PKname] = " & hldsfrmID
   
   Set sfrm = Nothing
   Set frmMain = Nothing[/blue]

Note: If any PK is text ... changs will have to be made!

Give it a go and let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dbar10]

Well, I tried your code and it did work about the same as what I already had but, when it finished running I had focus set on the first field in the Main {Billing Work Form] and in the first field of the last record in my subform.
Can that be fixed? And is it possible to have the cursor set focus on the [Hours Worked] or [Units] (this is in the subform) field when its done?

Private Sub Hours_Worked_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As Long

Set frmMain = Forms![Billing Work Form]
Set sfrm = frmMain![Units Worked Billing Query subform].Form
hldMainID = frmMain![ClientID]
hldsfrmID = sfrm![UnitsID]

frmMain.Requery
frmMain.Recordset.FindFirst "[ClientID] = " & hldMainID
sfrm.Recordset.FindFirst "[UnitsID] = " & hldsfrmID

Set sfrm = Nothing
Set frmMain = Nothing



End Sub

 

[PHV]

...
sfrm.Recordset.FindFirst "[UnitsID] = " & hldsfrmID
frmMain![Units Worked Billing Query subform].SetFocus
sfrm![Units].SetFocus
Set sfrm = Nothing
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

Hey PHV, Thanks for your input. I tried your code, but I don't know if did it right. I hammered away trying different things but I still can't get it to work. the focus alway comes back to the first tab stop in the datasheet subform. The [Units] field is the fourth field in the record. Do you have any idea what I'm doing wrong?

With the following code I get the error message:
runtime error '438':
Object doesn't support this property or method

This is the code as it stands:

Private Sub Hours_Worked_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As Long

Set frmMain = Forms![Billing Work Form]
Set sfrm = frmMain![Units Worked Billing Query subform].Form
hldMainID = frmMain![ClientID]
hldsfrmID = sfrm![UnitsID]

frmMain.Requery
sfrm.Recordset.FindFirst "[UnitsID] = " & hldsfrmID
frmMain![Units Worked Billing Query subform].SetFocus
sfrm![Units].SetFocus
Set sfrm = Nothing


End Sub

When I debug the following line is highlighted:

sfrm![Units].SetFocus


I really appreciate any help that you might offer. Thanks

 

[PHV]

sfrm![real name of Units control].SetFocus

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dbar10]

You were right I was using the wrong field name. I corrected. Then the cursor was landing in the [UnitConv] field, but it was in the first record in the datasheet subform. I changed the FindFirst to FindLast and it landed in the record I wanted. The problem now is that after the code runs the main form goes back to the first record instead of the one I was working on. Any ideas?

This is what the code looks like now:

Private Sub Hours_Worked_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As Long

Set frmMain = Forms![Billing Work Form]
Set sfrm = frmMain![Units Worked Billing Query subform].Form
hldMainID = frmMain![ClientID]
hldsfrmID = sfrm![UnitsID]

frmMain.Requery
sfrm.Recordset.FindLast "[UnitsID] = " & hldsfrmID
frmMain![Units Worked Billing Query subform].SetFocus
sfrm![UnitConv].SetFocus
Set sfrm = Nothing


End Sub

 

[dbar10]

PHV, I figured it out!!!!! Of course with your help.. Thanks!

Private Sub Hours_Worked_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As Long

Set frmMain = Forms![Billing Work Form]
Set sfrm = frmMain![Units Worked Billing Query subform].Form
hldMainID = frmMain![ClientID]
hldsfrmID = sfrm![ClientID]

frmMain.Requery
frmMain.Recordset.FindFirst "[ClientID] = " & hldMainID
sfrm.Recordset.FindLast "[UnitsID] = " & hldsfrmID
frmMain![Units Worked Billing Query subform].SetFocus
sfrm![UnitConv].SetFocus

Set frmMain = Nothing
Set sfrm = Nothing


End Sub

This did it. I appreciate your help. dbar10

 

[PHV]

You no longer use hldMainID, why ?

 

[dbar10]

This was a tough problem and you really helped a lot. I don't know what I would have done with out this resource. thank you very much!!!!

 

[TheAceMan1]

dbar10 . . .

Sorry I couldn't be here to complete the thread (a family emergency came up that turned out to be a loss ). Happy to read [blue]all is resolved[/blue].

[blue]PHV[/blue] ... thanks for the backup!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]