Global variable not working

D

D. Stacy

Have a form that allows the user to select a value from a combo box. The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " & lCriteria &
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function
 
V

vanderghast

The selection from the combo box, assuming it is bound to a field ID in the
table tblGPCI will be SAVED in the TABLE only after the record is updated,
which is not the same as after the combo box is updated. To update the combo
box, you have to move out of it, to update the record, you have to either
move to another record, either to force a save:

Me.Dirty = false


As long as the record is only dirty but not saved, reading the table would
return the actual value in the table, a value which does not see the update
from the combo box.


Vanderghast, Access MVP
 
J

Jeanette Cunningham

There is a line missing from the function.
Add this just under the msgbox line:

FindGPCI_Work = CurrentWork_GPCI

You are not taking any action for when the DLookup returns a Null value.
When ever the DLookup returns a null, you will get an error.
Include the Nz function as I have shown below.


------------------------
Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria &
""),0)

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
FindGPCI_Work = CurrentWork_GPCI
End Function
-------------------------


If the DLookup returns a null, FindGPCI_Work will be equal to zero.
You can test for this in the sub as shown below:

----------------------
Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
Dim lngReturn as Long

lCriteria = Me!txtLocality_ID
lngReturn = FindGPCI_Work (lCriteria)
If lngReturn >0 Then
'code to do something here
Else
'ignore
End If

MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub
 
D

D. Stacy

The selection of the value from the cbo does not update any records in a
table. The cbo has the user selecting the geographic region name AND a
separate txt box displying the ID field that is used in the criteria.

Does this information make a difference in your answer?

Also, where would the Me.Dirty = false code go? I tried it in the Sub
Combo0_AfterUpdate() but in errored out.



vanderghast said:
The selection from the combo box, assuming it is bound to a field ID in the
table tblGPCI will be SAVED in the TABLE only after the record is updated,
which is not the same as after the combo box is updated. To update the combo
box, you have to move out of it, to update the record, you have to either
move to another record, either to force a save:

Me.Dirty = false


As long as the record is only dirty but not saved, reading the table would
return the actual value in the table, a value which does not see the update
from the combo box.


Vanderghast, Access MVP


D. Stacy said:
Have a form that allows the user to select a value from a combo box. The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " & lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function
Click to expand...
Click to expand...
 
D

D. Stacy

I added back the Nz () function ( I had removed it thinking it was causing
errors)

The value of CurrentWork_GPCI is always 1, no matter the selection.
For what it's worth the number 1 does not appear in the table/field anywhere!

Present state of the code:

Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function



Jeanette Cunningham said:
There is a line missing from the function.
Add this just under the msgbox line:

FindGPCI_Work = CurrentWork_GPCI

You are not taking any action for when the DLookup returns a Null value.
When ever the DLookup returns a null, you will get an error.
Include the Nz function as I have shown below.


------------------------
Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria &
""),0)

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
FindGPCI_Work = CurrentWork_GPCI
End Function
-------------------------


If the DLookup returns a null, FindGPCI_Work will be equal to zero.
You can test for this in the sub as shown below:

----------------------
Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
Dim lngReturn as Long

lCriteria = Me!txtLocality_ID
lngReturn = FindGPCI_Work (lCriteria)
If lngReturn >0 Then
'code to do something here
Else
'ignore
End If

MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

----------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

D. Stacy said:
Have a form that allows the user to select a value from a combo box. The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " & lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

Do some debugging like this:


Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

Debug.Print "lCriteria:", lCriteria

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function


Now run the code and when it's finished, open the immediate window to see
what access put for lCriteria.

Now look at the tblGPCI and see what value you would get when lCriteria is
the same as the value from the immediate window.

Sounds as if there is a problem with the values in the combobox, or perhaps
you need more than one criteria to locate the correct GPCI in the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

D. Stacy said:
I added back the Nz () function ( I had removed it thinking it was causing
errors)

The value of CurrentWork_GPCI is always 1, no matter the selection.
For what it's worth the number 1 does not appear in the table/field
anywhere!

Present state of the code:

Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function



Jeanette Cunningham said:
There is a line missing from the function.
Add this just under the msgbox line:

FindGPCI_Work = CurrentWork_GPCI

You are not taking any action for when the DLookup returns a Null value.
When ever the DLookup returns a null, you will get an error.
Include the Nz function as I have shown below.


------------------------
Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria &
""),0)

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
FindGPCI_Work = CurrentWork_GPCI
End Function
-------------------------


If the DLookup returns a null, FindGPCI_Work will be equal to zero.
You can test for this in the sub as shown below:

----------------------
Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
Dim lngReturn as Long

lCriteria = Me!txtLocality_ID
lngReturn = FindGPCI_Work (lCriteria)
If lngReturn >0 Then
'code to do something here
Else
'ignore
End If

MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

----------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

D. Stacy said:
Have a form that allows the user to select a value from a combo box.
The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function
Click to expand...


.
Click to expand...
Click to expand...
 
V

vanderghast

The Me.Dirty is only to save the current record, but if it is not involved,
it should not be used.

I find strange that you use the Combo0 after update event handler to get
the txtLocality_ID value for your filter. If there is no other code
involved, the combo0 value may change but would leave your parameter,
txtLocality_ID, unchanged?

Also, you may have more than one variable called txtLocality_ID (one at the
application level, defined in a standard module, one at the form level in
the form declaration, and one at a procedure level). Clearly, txtLocality_ID
is not defined in any of the procedures you use, but if txtLocality_ID is
defined in the form declaration, it would hide the variable defined at the
application level, a little bit like a variable define locally in a
procedure hides any variable of the same name at an outer scope:


Option Explicit
Dim i As long
Public Sub toto( )
Dim i as long
...
End sub

the variable i under toto( ) hides the variable i at the module/class level
and the i, at the module/class level cannot be reached from inside toto. So,
in the same way, ***if*** txtLocality_ID is defined at the form level, it
would hide txtLocality_ID defined at the application level.

But again, maybe it is something missing between changing Combo0 and not
changing txtLocality_ID used as criteria.



Vanderghast, Access MVP


D. Stacy said:
The selection of the value from the cbo does not update any records in a
table. The cbo has the user selecting the geographic region name AND a
separate txt box displying the ID field that is used in the criteria.

Does this information make a difference in your answer?

Also, where would the Me.Dirty = false code go? I tried it in the Sub
Combo0_AfterUpdate() but in errored out.



vanderghast said:
The selection from the combo box, assuming it is bound to a field ID in
the
table tblGPCI will be SAVED in the TABLE only after the record is
updated,
which is not the same as after the combo box is updated. To update the
combo
box, you have to move out of it, to update the record, you have to either
move to another record, either to force a save:

Me.Dirty = false


As long as the record is only dirty but not saved, reading the table
would
return the actual value in the table, a value which does not see the
update
from the combo box.


Vanderghast, Access MVP


D. Stacy said:
Have a form that allows the user to select a value from a combo box.
The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function
Click to expand...
Click to expand...
Click to expand...
 
D

D. Stacy

The value in the immediate window for lCriteria shows the correct value. The
function is still returning 1.

It's strange that when the function is typed directly into the Immediate
window, replacing the variable "lCriteria" with "74" it works correctly.

Immediate Window Results.

lCriteria: 74



CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " & 74 & ""), 0)
debug.Print CurrentWork_GPCI
0.992




Jeanette Cunningham said:
Do some debugging like this:


Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

Debug.Print "lCriteria:", lCriteria

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function


Now run the code and when it's finished, open the immediate window to see
what access put for lCriteria.

Now look at the tblGPCI and see what value you would get when lCriteria is
the same as the value from the immediate window.

Sounds as if there is a problem with the values in the combobox, or perhaps
you need more than one criteria to locate the correct GPCI in the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

D. Stacy said:
I added back the Nz () function ( I had removed it thinking it was causing
errors)

The value of CurrentWork_GPCI is always 1, no matter the selection.
For what it's worth the number 1 does not appear in the table/field
anywhere!

Present state of the code:

Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function



Jeanette Cunningham said:
There is a line missing from the function.
Add this just under the msgbox line:

FindGPCI_Work = CurrentWork_GPCI

You are not taking any action for when the DLookup returns a Null value.
When ever the DLookup returns a null, you will get an error.
Include the Nz function as I have shown below.


------------------------
Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria &
""),0)

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
FindGPCI_Work = CurrentWork_GPCI
End Function
-------------------------


If the DLookup returns a null, FindGPCI_Work will be equal to zero.
You can test for this in the sub as shown below:

----------------------
Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
Dim lngReturn as Long

lCriteria = Me!txtLocality_ID
lngReturn = FindGPCI_Work (lCriteria)
If lngReturn >0 Then
'code to do something here
Else
'ignore
End If

MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

----------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Have a form that allows the user to select a value from a combo box.
The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function












.
Click to expand...
Click to expand...


.
Click to expand...
 
D

D. Stacy

Break Thru!

I think the function is rounding.

Look at the immediate window results
lCriteria: 78
CurrentWork_GPCI: 1
lCriteria: 25
CurrentWork_GPCI: 1
lCriteria: 56
CurrentWork_GPCI: 1
lCriteria: 40
CurrentWork_GPCI: 1
lCriteria: 89
CurrentWork_GPCI: 0
lCriteria: 83
CurrentWork_GPCI: 0

the answers should have been
78 = 1.110
25 = .546
56 = 1.010
40 = 1.029
89 = .409
83 = .489

Any idea how to stop the rounding?

D. Stacy said:
The value in the immediate window for lCriteria shows the correct value. The
function is still returning 1.

It's strange that when the function is typed directly into the Immediate
window, replacing the variable "lCriteria" with "74" it works correctly.

Immediate Window Results.

lCriteria: 74



CurrentWork_GPCI = Nz(DLookup("Work_GPCI", "tblGPCI", "[ID] = " & 74 & ""), 0)
debug.Print CurrentWork_GPCI
0.992




Jeanette Cunningham said:
Do some debugging like this:


Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

Debug.Print "lCriteria:", lCriteria

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function


Now run the code and when it's finished, open the immediate window to see
what access put for lCriteria.

Now look at the tblGPCI and see what value you would get when lCriteria is
the same as the value from the immediate window.

Sounds as if there is a problem with the values in the combobox, or perhaps
you need more than one criteria to locate the correct GPCI in the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

D. Stacy said:
I added back the Nz () function ( I had removed it thinking it was causing
errors)

The value of CurrentWork_GPCI is always 1, no matter the selection.
For what it's worth the number 1 does not appear in the table/field
anywhere!

Present state of the code:

Public Function FindGPCI_Work(lCriteria As Long) As Long
' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria & ""), 0)
FindGPCI_Work = CurrentWork_GPCI

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function



:

There is a line missing from the function.
Add this just under the msgbox line:

FindGPCI_Work = CurrentWork_GPCI

You are not taking any action for when the DLookup returns a Null value.
When ever the DLookup returns a null, you will get an error.
Include the Nz function as I have shown below.


------------------------
Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = Nz(DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria &
""),0)

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
FindGPCI_Work = CurrentWork_GPCI
End Function
-------------------------


If the DLookup returns a null, FindGPCI_Work will be equal to zero.
You can test for this in the sub as shown below:

----------------------
Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
Dim lngReturn as Long

lCriteria = Me!txtLocality_ID
lngReturn = FindGPCI_Work (lCriteria)
If lngReturn >0 Then
'code to do something here
Else
'ignore
End If

MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

----------------------

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Have a form that allows the user to select a value from a combo box.
The
combo box result is used as the criteria of a dlookup function.

The value created by the public function will be used in a series of
calculations throughout the application.

The function is returning the value of 1 every time, regardless of the
user
selection.

Need help fixing this situation!

Current Code:

Private Sub Combo0_AfterUpdate()
Dim lCriteria As Long
lCriteria = Me!txtLocality_ID
FindGPCI_Work (lCriteria)
MsgBox "lCriteria equal to " & lCriteria, vbOKOnly
'MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly
End Sub

and.....

Public Function FindGPCI_Work(lCriteria As Long) As Long

' take tblGPCI.ID value from Form frmLocalitySelect
' lookup integer value in tblGPCI.Work_GPCI
Dim CurrentWork_GPCI As Long

CurrentWork_GPCI = DLookup("PE_GPCI", "tblGPCI", "[ID] = " &
lCriteria
&
"")

MsgBox "CurrentWork_GPCI equal to " & CurrentWork_GPCI, vbOKOnly

End Function












.
Click to expand...


.
Click to expand...
Click to expand...
 
B

BruceM via AccessMonster.com

Long is an integer, and therefore has no decimal places. Try double:

lCriteria As Double

D. Stacy said:
Break Thru!

I think the function is rounding.

Look at the immediate window results
lCriteria: 78
CurrentWork_GPCI: 1
lCriteria: 25
CurrentWork_GPCI: 1
lCriteria: 56
CurrentWork_GPCI: 1
lCriteria: 40
CurrentWork_GPCI: 1
lCriteria: 89
CurrentWork_GPCI: 0
lCriteria: 83
CurrentWork_GPCI: 0

the answers should have been
78 = 1.110
25 = .546
56 = 1.010
40 = 1.029
89 = .409
83 = .489

Any idea how to stop the rounding?
The value in the immediate window for lCriteria shows the correct value. The
function is still returning 1.
Click to expand...
[quoted text clipped - 152 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function w/ Dlookup 2
User Defining Problem 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Dialog in MsgBox 4
Public Function to display in text box 8
copy data to table 0
Transfer data 14
OpenRecordSet Error 3

Top