Inconsistent results using with and user defined properties

[hbeer444]

(first time post so hopefully I am explaining it clearly)
Hi I am getting two different values for the same user defined property while using the with statement in two different procedures
I expect that since its all in the same database, no matter where I call currentdb.properties!ProgState, after it is created, it would return the same value.
How can I make it do that? Thanks in advance.

Situation:



In a non-class module I have:

Public gFuncResult
  Public gdaoDB As DAO.Database
  Public gdaoPR As DAO.Properties

In a Form Module I have this:

Private Sub Form_Open(Cancel As Integer)

Call Test3

End Sub

Private Sub Test3()


Set gdaoDB = CurrentDb
Set gdaoPR = gdaoDB.Properties


With gdaoPR
    Debug.Print "1:" & !ProgState
    gFuncResult = ShowThesePropVals()
    Debug.Print "3:" & !ProgState
End With


End Sub

In a non-class module I have

Public Function ShowThesePropVals()


Set gdaoDB = CurrentDb
Set gdaoPR = gdaoDB.Properties


With gdaoPR
    Debug.Print "2:" & !ProgState
    Debug.Print "2a:" & CurrentDb.Properties!ProgState
    CurrentDb.Properties!ProgState = "X"
    Debug.Print "2c:" & !ProgState
    Debug.Print "2d:" & CurrentDb.Properties!ProgState
End With
ShowThesePropVals = True  'for now, will change this later


End Function

I have stripped code and rerun exactly as above and still have a problem. When I open the form this is what I get in the debug window:

1:r
2:r
2a:r
2c:r
2d:X
3:r

I would expect 2c to be "X" as well as 3.
I want everything after the value change to reflect the change, why don't they and how can I be sure the changes are made to the property (is the 'with' statement not recomended?)
Thanks in advance

[CJ_London]

please use the code tags to identify your code and preserve indentation - highlight your code and click the # button

Think what I am seeing is you are setting a global variant called gFuncResult but debug.printing gdaoPR

Perhaps also clarify what you are trying to do

[kd2017]

I'd also suggest posting all of your code instead of snippits.

[hbeer444]

I reworded the question for clarity - hope it helps

[CJ_London]

you are setting your global variables twice and I have assigned X to gdaoPR rather than currentdb

Public Function ShowThesePropVals()


'Set gdaoDB = CurrentDb
'Set gdaoPR = gdaoDB.Properties


With gdaoPR
    Debug.Print "2:" & !ProgState
    Debug.Print "2a:" & CurrentDb.Properties!ProgState
    !ProgState = "X"
    Debug.Print "2c:" & !ProgState
    Debug.Print "2d:" & CurrentDb.Properties!ProgState
End With
ShowThesePropVals = True  'for now, will change this later


End Function

every time you call currentdb, it causes a refresh of the object - but not the previously assigned objects (gdao..).

If you want to modify the currentdb property per your post#1, refresh the gdaoPR immediately after

CurrentDb.Properties!ProgState = "X"
.refresh

[hbeer444]

you are setting your global variables twice and I have assigned X to gdaoPR rather than currentdb

every time you call currentdb, it causes a refresh of the object - but not the previously assigned objects (gdao..).

If you want to modify the currentdb property per your post#1, refresh the gdaoPR immediately after

CurrentDb.Properties!ProgState = "X"
.refresh

Ok Thanks so much
I'll just use .refresh every time just after I change a property and go to another procedure and when I return from another procedure that changed it.

Just wondering, what really is the advantage of this

Set gdaoDB = CurrentDb
Set gdaoPR = gdaoDB.Properties
x=gdaoDB!CustProp

when I could just use

x=currentDB.Properties!CustProp

and never worry about need to refresh?

[CJ_London]

you did not answer my question about what you are trying to do (to which I would probably then ask why?), so can't answer that

if it is about getting currentdb properties, I would just use

x=currentDB.Properties!CustProp

The benefit of assigning currentdb to a variable (Set gdaoDB = CurrentDb) is to more to do a) with recordset performance issues, particularly around calling currentdb in a loop (there is a small delay as a new object is created when called) and b) with being able to use the .recordsaffected property for action queries.

You might find this link of interest https://sourcedaddy.com/ms-access/th...-function.html

[hbeer444]

Thanks Ajax, that was definitely helpful