I have a basequery named qryFghEkonomi and a global variable, dim dak as long and a module shown below.
I then use this code in a form, it all works fine until the moment when I want to save the value to the variable and then display it in a textbox. According to the msgbox the value of dak i 0
and when trying toi assign the textbox a value i get the message can not assign that objekt a value. The funny thing is if I pull up the question manually and run it with the form open after clicking the button ( so the qry is changed according to GetDAK() ) i get the correct value but it will not be saved into the variable.

Option Compare Database
Option Explicit

Public Function GetDAK() As Integer

Dim qry As QueryDef
Dim DB As Database
Dim sSql As String

Set DB = CurrentDb()
Set qry = DB.QueryDefs("qryFghEkonomi")

qry.SQL = "SELECT intDAK FROM [tblFghEkonomi] " & _
"WHERE [intFastighetsnummer]=[forms].[frmNyAlgh].[intFastighetsnummer];"
dak = DMax("[intDak]", "qryFghEkonomi")

Set qry = Nothing
End Function

In the form:

Private Sub cmdGetDAK_Click()
On Error GoTo Err_cmdGetDAK_Click


Call GetDAK
Me.txtDAK = dak

MsgBox dak

Exit_cmdGetDAK_Click:
Exit Sub

Err_cmdGetDAK_Click:
MsgBox Err.Description
Resume Exit_cmdGetDAK_Click

End Sub

I don't follow what you are doing here. You define the SQL for a query but don't appear to be using it anywhere. Also, if you are using a value in a textbox as criteria, you need to concatenate it in:

"WHERE [intFastighetsnummer]=" & [forms].[frmNyAlgh].[intFastighetsnummer] & ";"

If qryFghEkonomi is a named query as it appears, then you shouldn't need GetDAK at all. Just use:

Me.txtDAK = DMax("[intDak]", "qryFghEkonomi")

Also a function returns a value so you don't use a CALL with a function. You just assign it. So if you really needed a GetDAK function you would just use:

Me.txtDAK = GetDAK()

I define the query and then that is the query that is updated with the sql in qry.sql = ....
When i run the querry manually i get the correct value but i cant get that value saved in a text box or variable

Me.txtDAK = DMax("[intDak]", "qryFghEkonomi") doesn't work allready tried it.

I wanna use the function because i will use the same procedure in many different forms.

the really strange thing is that when i display this in a msg box: MsgBox DMax("[intDak]", "qryFghEkonomi")

the correct value is shown, but i cant put it into a textbox.

Edited by: oso on Fri Feb 3 8:01:17 EST 2006.

Ahh, I see what you mean about updating the query by setting the SQL property of the QueryDe, I missed that. But again, that doesn't work because you need to concatenate the value from the form.

But frankly I don't understand why just assigning the DMax to the control doesn't work. What happens when you try it?

I don't see you defining the variable "dak" anywhere. You said it is defined as a global variable; where?

Global variables are defined in code modules (not form modules) and should be defined as:

Public dak As Long

Depending on how/where you are defining it, it may not have scope in your form's module, and may thus not be keeping its value between your two different procedures. Since this is a function, let's use the return value, rather than depending on a global. There may also be some issues with the way you are modifying the query; the change may not be saved when you run your DMAX function.

Try this code:

Just a note; here is another way to write your GetDAK function, that does not do any query modifications: