Form showing #Name on all fields

[usertest]

Hi,
I have a form that is connected to a vba code that populates the data using SQL tables.

VBA Code:

Private Sub Form_Load()
Dim r As New ADODB.Recordset
Dim DC As New DataConnection
Dim strSQL As String


Debug.Print "Startvol" & Now
strSQL = "SELECT A.ID, P.FULL_NAME, Count(A.ID) AS CountOf_ID," & _
         "FROM table1 AS A INNER JOIN table2 AS P ON A.ID = P.ID "
         
         r.Open strSQL, DC.ConnectionName, adOpenKeyset, adLockOptimistic
         
Set Me.Recordset = r



Debug.Print "endvol" & Now
End Sub

The query works fine in the SQL and populates the data, but always shows #Name error on the form.

Form details:
3 text boxes
Each one has the Name and control source as ID, FULL_NAME, CountOf_ID.

Can anyone help me on this?

Thanks!

[ranman256]

Me.Recordset = r

the SET is only for assigning variables, but this is a property.

[CJ_London]

Don’t agree - you use set to assign a recordset

however the OP has commented it out

also doesn’t appear to have assigned any parameters to the connection

[usertest]

The Set Me.Recordset = r code is NOT COMMENTED. I accidently pasted it. Fixed on the post as well.

The connections are already set in a function list.

SOmehow for my other forms, the respective query in that form works fine. Only for this one it shows the error.

Don’t agree - you use set to assign a recordset

however the OP has commented it out

also doesn’t appear to have assigned any parameters to the connection

[June7]

Asking again - please post code between CODE tags.

You are connecting to SQLServer tables? SQL is a language, not a database.

You are setting form Recordset property to a virtual dataset (something I've never tried) - there is no table or query or table link in the local frontend as source for data. If I understand correctly, it should work.

Looked at SQL again. Replace comma after CountOf_ID with a space.

[CJ_London]

the #name error occurs when the control source cannot be identified from the recordsource

so you are confirming that r is returning records? (not the same as 'query works fine in sql')

i.e. debug.print r.recordcount

is the form a single or continuous form?

and just to confirm, you are getting your data from sql tables in sql server or another rdbms and not sql tables in access

other basic checks

- do you have 'Option Explicit' at the top of every module?
- have you suppressed error messages?

Reason this matters is the sql is not valid in access, but might be in another rdbms, didn't think it was valid for sql server either but could be wrong

[usertest]

Hi @CJ_London,

so you are confirming that r is returning records? (not the same as 'query works fine in sql') --> Yes, when I copy the query and run on MS SQL SERVER mgmt studio, it works fine.

i.e. debug.print r.recordcount --> This gives an count of 15. Which is correct. there are 15 records in my output

is the form a single or continuous form?--> Continuous form

and just to confirm, you are getting your data from sql tables in sql server or another rdbms and not sql tables in access --> Yes, correct.


As this is a file that is given to me, I have not changed anything. It works fine on the other user's machine.
I also tried using a pass-through query to check and it works that way. But down the line I cant use the pass through option.

Thanks!

the #name error occurs when the control source cannot be identified from the recordsource

so you are confirming that r is returning records? (not the same as 'query works fine in sql')

i.e. debug.print r.recordcount

is the form a single or continuous form?

and just to confirm, you are getting your data from sql tables in sql server or another rdbms and not sql tables in access

other basic checks

- do you have 'Option Explicit' at the top of every module?
- have you suppressed error messages?

Reason this matters is the sql is not valid in access, but might be in another rdbms, didn't think it was valid for sql server either but could be wrong

[June7]

The SQL in posted code cannot work because it shows an extraneous comma after CountOf_ID - replace it with a space.

[usertest]

In the post, there seems to be an additional comma. apologies for the confusion
I do not have that in the main code.
Below is the actual code without the comma that is there in my VBA

Private Sub Form_Load()
Dim r As New ADODB.Recordset
Dim DC As New DataConnection
Dim strSQL As String


Debug.Print "Startvol" & Now
strSQL = "SELECT A.ID, P.FULL_NAME, Count(A.ID) AS CountOf_ID" & _
         "FROM table1 AS A INNER JOIN table2 AS P ON A.ID = P.ID "
         
         r.Open strSQL, DC.ConnectionName, adOpenKeyset, adLockOptimistic
         
Set Me.Recordset = r



Debug.Print "endvol" & Now
End Sub

The SQL in posted code cannot work because it shows an extraneous comma after CountOf_ID - replace it with a space.

[June7]

Well, now it can't work because there is no space where the comma was. The SQL will run together when executed and be nonsensical.
SQL will not understand CountOf_IDFROM.

[CJ_London]

Yes, when I copy the query and run on MS SQL SERVER mgmt studio, it works fine.

where are you copying from? Clearly not the sql code you are generating in vba.

use debug.print strsql

and use that

I note you haven’t answered the other questions

[usertest]

Oh, thanks for pointing that out.
I fixed that.
still, it doesn't show the form values.
when I do debug.print r.recordcount. It displays 15 as an output in the intermediate window.

Attaching pic of the QUERY



Form:





Property:

[June7]

I would try testing this but not understanding the DC.ConnectionName reference. Where is CPS_Assignment_Data defined?

I get compile error on Dim DC As New DataConnection.

So I tested by opening recordset of linked SQLServer table and setting Recordset property. That does work. However, data is not editable. Same with a local table as source.

[usertest]

I checked the debug.print srtsql. I copied that code and ran as is it on the server. It ran and generated the output


I replied to all the other questions.

Let me copy paste that again for you. Highlighted in bold.

so you are confirming that r is returning records? (not the same as 'query works fine in sql') --> Yes, when I copy the query and run on MS SQL SERVER mgmt studio, it works fine.

i.e. debug.print r.recordcount --> This gives an count of 15. Which is correct. there are 15 records in my output

is the form a single or continuous form?--> Continuous form

and just to confirm, you are getting your data from sql tables in sql server or another rdbms and not sql tables in access --> Yes, correct.


As this is a file that is given to me, I have not changed anything. It works fine on the other user's machine.
I also tried using a pass-through query to check and it works that way. But down the line I cant use the pass through option.

Thanks!

[CJ_London]

Where are the answers to these questions?

do you have 'Option Explicit' at the top of every module?
- have you suppressed error messages?

Reason this matters is the sql is not valid in access, but might be in another rdbms, didn't think it was valid for sql server either but could be wrong