VB-wanna-b

I defined a string value (dim as string) that contains an sql string that filters out a specifik set of records.

code:


Dim UsrStr As String
Dim sSql As String
Dim comp As Long
UsrStr = Me.MyFieldFilter 'the filter value that is used as identifier to
'what records should be counted
'dim comp as DAO.RecordSet


sSql = "SELECT * FROM TBL_1 WHERE TBL_1.A1r= " & UsrStr & ""
'Set comp = CurrentDb.OpenRecordset(sSql)
'Me.MyField = comp.RecordCount (sSql)
'comp = DCount("*", "TBL_1", ["A1"] = " & UsrStr & ")
comp = DCount("[A1]", sSql)
Me.MyField= comp

as is shows I´ve tried a few ways...cant get it to work..
hope you can help me out..

MKoslof

Do something like this:

VB-wanna-b

when I debug it, I get total_Count out as empty...but in fact it should say "3"

made an msgbox total_count
...blank

and I get en "Object requiered" error when I run it

but running the sql in query mode., gives me "3" as the value og total_Count

MKoslof

post your code...did you do what I said?

You can not do msgbox total_count. Total count is now an aliased field created by the sql

msgbox rs.fields("total_Count").value

VB-wanna-b

Dim UsrStr As String
Dim sSql As String
Dim comp As String
UsrStr = Me.AfdNr

'the sql is one long line - not split up.

sSql = "SELECT COUNT (*) as total_Count FROM TBL_bruger WHERE AfdNr= " & UsrStr & "" 'when run in querymode it gives me "3" which is correct


'Set comp = CurrentDb.OpenRecordset(sSql)
'MsgBox comp
'comp = DCount("*", "TBL_bruger", ["AfdNr"] = " & UsrStr & ")
'comp = DCount("[AfdNr]", sSql)
'Me.Bruger_antal = comp
'comp = rs.Fields("total_Count").Value
'MsgBox "& total_Count &"
MsgBox rs.Fields("total_Count").Value 'gets me "Object required"

comp = rs.Fields("total_Count").Value

Me.Bruger_antal = comp
compDoCmd.SetWarnings True
'MsgBox (comp)
'Me.Bruger_antal = comp.RecordCount

MKoslof

yes, the SELECT statement will work, as you described, look at what I am doing here.

comp = rs.Fields("total_Count").Value


this reason this doesn't work is, you have NOT declared rs (or the recordset) in your code. I am saying here the variable comp is equal to the RECORDSET field "total_count". You have not declare the rs as recordset, and you have not opened it.

VB-wanna-b

woops..slipped my mind... I changed it a bit, but I now get into an infinate loop of such..."Action can not be completed while object is closed"
I do close the recordset again... but I AM supposed to do that..cant leave open at all time..

Private Sub Bruger_antal_GotFocus()
On Error GoTo Bruger_antal_GotFocus_Err


Dim UsrStr As String
Dim sSql As String
Dim comp As String
Dim rs As ADODB.Recordset
UsrStr = Me.AfdNr

Set rs = New ADODB.Recordset


rs.Open "SELECT COUNT (*) as total_Count FROM TBL_bruger WHERE AfdNr= " & UsrStr & ""
'Set comp = CurrentDb.OpenRecordset(sSql)
'MsgBox comp
'comp = DCount("*", "TBL_bruger", ["AfdNr"] = " & UsrStr & ")
'comp = DCount("[AfdNr]", sSql)
'Me.Bruger_antal = comp
'comp = rs.Fields("total_Count").Value
'MsgBox "& total_Count &"
MsgBox rs.Fields("total_Count").Value

comp = rs.Fields("total_Count").Value

Me.Bruger_antal = comp
compDoCmd.SetWarnings True
'MsgBox (comp)
'Me.Bruger_antal = comp.RecordCount
Bruger_antal_GotFocus_exit:
rs.Close
DoCmd.SetWarnings True
Exit Sub

Bruger_antal_GotFocus_Err:
MsgBox Err.Description
Resume Bruger_antal_GotFocus_exit

End Sub

MKoslof

All you should be doing is:

Open your recordset and run your query
get the total_Count value
Close the recordset, if that is what you need to do.

Then everytime this "procedure" or "event" is run, the events occur again without issue. If you open a recordset, do an event, and then close it..there is no reason to get an error regarding it being closed. You are opening and closing the valid recordset with every event trigger...make sure your recordset is being opened and closed properly.

VB-wanna-b

Dim UsrStr As String
Dim sSql As String
Dim comp As String
Dim rs As ADODB.Recordset
UsrStr = Me.AfdNr

Set rs = New ADODB.Recordset '1 Define recordset

rs.Open "SELECT COUNT (*) as total_Count FROM TBL_bruger WHERE AfdNr= " & UsrStr & "" 'Open recordset

comp = rs.Fields("total_Count").Value 'getting value of total_Count

Me.Bruger_antal = comp 'Assigning value to a field (not an unbound)
rs.Close 'Close connection
Bruger_antal_GotFocus_exit:

DoCmd.SetWarnings True
Exit Sub

Bruger_antal_GotFocus_Err:
MsgBox Err.Description
Resume Bruger_antal_GotFocus_exit

End Sub


If I follow what you wrote, then this should work....
But it cannot run on a illegal connection or on a closed object it says...
what Im I missing

MKoslof

First of all where is this event coded? Do not use the Got_focus event. Use the Changed event or something else. As an initial test, just put it in a command button for now.

VB-wanna-b

I put it in a click event procedure...now... :-)
looks like this.. the Error I get now is
"[Microsoft][ODBC Driver Manager] Datasourcename not found, and no default driver selected.



Private Sub Bruger_antal_Click()
On Error GoTo Bruger_antal_Click_Err

Dim UsrStr As String
Dim sSql As String
Dim comp As Integer

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection


UsrStr = Me.AfdNr


sSql = "SELECT COUNT (*) as total_Count FROM TBL_bruger WHERE AfdNr= " & UsrStr & ""
Set rs = New ADODB.Recordset
Set cn = New ADOB.Connection


'Establishing ADOB connection

cn.ConnectionString = "CurrentProject.Connection "
cn.Open

'Open recordset based on sSql

rs.Open sSql, cn, adOpenKeyset, adLockOptimistic, adCmdText

'retrieve value of total_Count which is number of records with usrStr as key value

comp = rs.Fields("total_Count").Value

MsgBox comp


rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing