how to set focus to specific record on continuous subform 1

[knot22]

Hello,
I have a problem that is very similar to xicana's. (Refer to xicana's original posting:

http://www.tek-tips.com/viewthread.cfm?qid=1443498&page=9)

The only difference is, there are a combination of 3 fields that make a record unique in my database rather than just one field holding a unique record ID. These 3 fields are all character data types. I attempted to put all 3 fields in the hldID variable as follows:

hldID = propertyID&scenariotype&scenarioID

but got a Compile error Expected end of statement message. Does anyone have any recommendations on how to use a combination of 3 fields as a unique record identifier to get the form to return to that record after requerying the data?

Thanks in advance.

 

[TheAceMan1]

How are ya knot22 . . .

[blue]   hldID = propertyID & scenariotype & scenarioID[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[knot22]

Ah...it liked that much better. I am trying to write this as part of some code to build a function in a module so it can be called by a macro. Below is all the code from the module so far:

Function refreshdata()

Dim hldID As String, recordtoview As String

hldID = Me!propertyID & scenariotype & scenarioID

Me.Requery

recordtoview = "propertyID & scenariotype & scenarioID = '" & hldID & "'"
Me.Recordset.FindFirst recordtoview

End Function

It isn't working though because I get a compile error that says Invalid use of Me. It looks like the word Me is what a form can use to refer to itself. I'm guessing it's not working because Me has to be used in the form, not in a module...?

A little background on the reason for doing the Requery from a macro...
This database uses SQL tables that're linked into Access. On the subform, when the user changes numbers they can click a button called "Refresh Calculations". This button is hooked up to a macro which runs a query that executes a SQL stored procedure which recalculates the numbers. Then I would like it to requery the information, so the recalculated numbers appear, and stay in the active record that the user was editing. How should I approach this?

Thanks for your help.

 

[Pampers]

I dont think you can use Me in a module function...

Maybe you can write a function that takes the form name as a variable

Function RefreshDate (MyFormName as Form)


Pampers
Keeping it simple can be complicated

 

[MajP]

Pampers has me a little confused because he said pass the name, but he actually passes the form. Either way is ok, just makes a difference how you call it. The other thing is you call it a function but do not return anything. I would make it a sub

Public sub RefreshDate (MyForm as access.Form)
now you can use myForm instead of Me
end sub

and call the procedure like this from the form
RefreshDate Me

or pass the name instead of the form object

Public sub RefreshDate2 (strMyFormName as string)
dim frm as access.form
set frm = forms(strMyFormName)
now you can use frm instead of me
end function

and call the procedure like this from the form
RefreshDate me.name

Me refers to the current instantiated object within a class module. Since a form or report's module is a class module you can use the Me keyword. Excel worksheets, word documents, power point presentations are all objects that have class modules associated with them. If you build a custom class or use any class module you can also use the Me keyword. A standard module is just a place to hold code, it does not instantiate an object.

 

[Pampers]

Hi MajP,
You are absolute right (2x).

Pampers
Keeping it simple can be complicated

 

[TheAceMan1]

knot22 . . .

Perhaps the following will do:

[blue]Function refreshdata()
   Dim hldPI As String, hldST As String, hldSI As String, Cri As String
   
   If CurrentProject.AllForms("[purple][b]FormName[/b][/purple]").IsLoaded Then
      hldPI = Me!propertyID
      hldST = Me!scenarioType
      hldSI = Me!scenarioID
      Me.Requery
      
      Cri = "[PropertyID] = '" & hldPI & "' AND " & _
            "[scenarioID] = '" & hldSI & "' AND " & _
            "[scenarioType] = '" & hldST
      Me.Recordset.FindFirst Cri
   End If

End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[knot22]

Dear TheAceMan1,
I tried the code you provided above but got a Compile error: Invalid use of Me keyword. Here are the steps I used, in case I went astray somewhere:

1. cut/pasted your code from above into the module
2. went to the Macro called CalculationsForIncomeAndExpenses, removed Requery and replaced it with RunCode [Function Name: refreshdata()]
side note: this entire Macro consists of 3 steps
SetWarnings - Warnings On: No
OpenQuery - Query Name: incomeexpensecalculations

   RunCode - Function Name: refreshdata()
3.  opened form
4.  for a property with 5 income/expense scenarios, went to scenario 3 and changed a number value (which requires SQL stored procedure to recalculate answers when user clicks Refresh Calculations button)
5.  clicked on Refresh Calculations button  [this button calls the CalculationsForIncomeAndExpenses macro when the user clicks on it]
...this is when the compile error message appeared.  I am hoping you may have some ideas about how to fix...

Thanks, Knot22.

 

[TheAceMan1]

knot22 . . .

[blue]Me[/blue] only works in form/report modules (the module behind form/report), not standard modules in the modules window.

Since your [blue]function appears to be specific to a particular form[/blue], the module behind this form is where the code should go. So (at least for testing) transfer the function to that form module and [blue]change the function to a Sub![/blue] Don't forget to edit [purple]FormName[/purple] properly.

You should also call the sub directly from the button (no need for the macro) with:

[blue]   Call refreshdata[/blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[knot22]

TheAceMan1,

It is much closer to working now. Here's where it's at...

I modified the "Refresh Calculations" button so that when it's clicked it no longer calls a macro but runs this private sub instead:

Option Compare Database

Private Sub button_IncExpCalcns_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "IncomeExpenseCalculations"
    Call refreshdataforIncExpscreen()
    DoCmd.SetWarnings True
End Sub

Function refreshdataforIncExpscreen()
   Dim hldPI As String, hldST As String, hldSI As String, Cri As String
   If CurrentProject.AllForms("dbo_PropertyScenarioIndex-incomeandexpenses subform").IsLoaded Then
      hldPI = Me!propertyID
      hldST = Me!scenariotype
      hldSI = Me!scenarioID
      
      Me.Requery

      Cri = "[propertyID] = '" & hldPI & "' AND " & _
            "[scenariotype] = '" & hldST & "' AND " & _
            "[scenarioID] = '" & hldSI

      Me.Recordset.FindFirst Cri
   End If
End Function

Now, when I test it with this approach, by changing a number in an existing record, here is how it responds:
- it stays on the current record that I just edited (good)
- the calculated numbers do not change (not good)
- if I click one record ahead and then click back to the record I edited the calculated numbers are updated (I think this means the query that executes the SQL stored procedures in the SQL database did actually run since the math is correct once I return to the edited record)

What am I missing to get the calculated values to be displayed automatically while still on the edited record?

Thanks, Knot22

 

[knot22]

Great news! The following code works.

Option Compare Database
Private Sub button_IncExpCalcns_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "IncomeExpenseCalculations"
    'MsgBox "calcn query just ran"
    Call refreshdataforIncExpscreen
    DoCmd.SetWarnings True
End Sub
Function refreshdataforIncExpscreen()
   Dim hldPI As String, hldST As String, hldSI As String, Cri As String
      hldPI = Me!propertyID
      hldST = Me!scenariotype
      hldSI = Me!scenarioID
      
      Me.Requery
      'MsgBox "requery just ran"
      
      Cri = "[propertyID] = '" & hldPI & "' AND " & _
            "[scenariotype] = '" & hldST & "' AND " & _
            "[scenarioID] = '" & hldSI & "'"

      Me.Recordset.FindFirst Cri
        'MsgBox "record finder just ran"
   
End Function

It was giving a run-time error '3307' on the Me.Recordset.FindFirst Cri line but after adding the & "'" after hldSI then it was happy. Note the commented out message boxes...I found it helpful to use those so I could be sure what steps had completed in the code after I clicked the calculate button. I just commented them out but left them there in case I should ever have to go back and step through it again.

Thanks very much for your help with this everyone!