Form showing #Name on all fields

[usertest]

DC is the data connection that I have created a function that gets called.

[usertest]

do you have 'Option Explicit' at the top of every module? --> No
- have you suppressed error messages? --> No. Did you mean create a error handling function to catch the error?

This SQL is the only one that is not working. My other 10+ similar sql are working and populating results as expected

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

[June7]

Still can't get past compile error.

[usertest]

do you have 'Option Explicit' at the top of every module? -- NO
- have you suppressed error messages? -- NO (do you mean having error handling to catch the error?

Also, only this sql is not working. Rest all similiar structure sql are working in access smoothly.

I tried to just edit the propery of 1 of the text box as NOW() to display the date. That works fine for that textbox when I generate the result

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

[usertest]

How did you achieve this part? Can you guide me for that and I can test.


"So I tested by opening recordset of linked SQLServer table and setting Recordset property."

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.

[June7]

I simply opened a recordset as I normally would with a table or table link as source.

Finally got it with:

Private Sub Form_Load()
Dim r As New ADODB.Recordset
Dim DC As New ADODB.Connection
DC.Open "DRIVER=SQL Server;SERVER=localhost\SQLEXPRESS01;DATABASE=Test;Trusted_Connection=True"
r.Open "SELECT * FROM Umpires;", DC, adOpenKeyset, adLockOptimistic
Set Me.Recordset = r
End Sub

Data still not editable.

[usertest]

Do you think there might be an issue with how I am assigning the control source to those text boxes?
Since I am creating a reference to the column name using "AS". Could that be an issue?

Because I firmly believe that my VBA code is correct that
1: no compile error
2: I can print the count of recordset
3: I can print the srtsql as well and run it on the server with the exact expected result.

I simply opened a recordset as I normally would with a table or table link as source.

Finally got it with:

Private Sub Form_Load()
Dim r As New ADODB.Recordset
Dim DC As New ADODB.Connection
DC.Open "DRIVER=SQL Server;SERVER=localhost\SQLEXPRESS01;DATABASE=Test;Trusted_Connection=True"
r.Open "SELECT * FROM Umpires;", DC, adOpenKeyset, adLockOptimistic
Set Me.Recordset = r
End Sub

Data still not editable.

[CJ_London]

@june7 - just to confirm your query that works on sql server does not require a group by clause?

also the op has now aliased the file to ‘name’ which is a reserved word and can cause unexpected errors

[usertest]

I checked and aliased the file to a different name as well. It still doesnt work

@june7 - just to confirm your query that works on sql server does not require a group by clause?

also the op has now aliased the file to ‘name’ which is a reserved word and can cause unexpected errors

[June7]

Then form controls need to reference alias field names in ControlSource.

[usertest]

Tested that as well.
Below are the details of the property section


Name: CPS_REP_ID
Label Name:
Control Source: ID


Still doesnt work. I even tried to only take 1 column and try fixing the issue. still doesnt work.

Then form controls need to reference alias field names in ControlSource.

[June7]

@CJ, Why would I need a GROUP BY clause?

For grins, I tested JOIN with and without GROUP BY.

All work.

[CJ_London]

@usertest

you really make it difficult to understand what you have.

Original post had code commented out - subsequently corrected but you said happened in error
you say query worked fine but clearly could not with the extra comma and no space - but you said it worked
it's taken you nearly 20 posts before you answered some of the questions
in post #7 you say r.recordcount returns a value of 15 - which I struggle to believe since the sql code you have provided simply would not work
in the same post you say 'As this is a file that is given to me, I have not changed anything. It works fine on the other user's machine.'
in post #9 you show 'the actual code' - and r.recordcount would still not work because the sql is still wrong
in post #12 you show a different 'actual code', with some redaction for an unknown reason - but at least now the sql looks viable to run
in post#16 you show what looks like made up code, unless you really have a server called ABC and a db called xyz?
You keep saying it works on other machines - but I suspect you mean something similar since so far nothing you have shown is viable except perhaps post #12, with a question mark over the connection

I strongly recommend you use Option Explicit at the top of every module because without it, this statement

Because I firmly believe that my VBA code is correct that

1: no compile error

is meaningless. Without it, compile errors will occur at runtime - and based on what you have shown so far I can pretty much guarantee you will get compile errors since I can see at least one bit of code that won't compile


this line

Dim DC As New DataConnection

implies DataConnection is a class library - so per you post #16 - are they the correct server and db names?

To answer your question

- have you suppressed error messages? -- NO (do you mean having error handling to catch the error?

No, tho' always a good idea for any sub of function that could generate errors. What I mean was have you usedDoCmd.SetWarnings Trueanywhere in your code, not just the bits you are showing.


If you have, then all error messages are suppressed until you turn it off again.

recommend you do some more debug.printing

dc.cps_assignment_date
r.fields(0).name
r.fields(1).name
r.fields(2).name
r.fields(0).value
r.fields(1).value
r.fields(2).value

[CJ_London]

@June

@CJ, Why would I need a GROUP BY clause?

The OP claimed they are using

SELECT A.ID, P.FULL_NAME, Count(A.ID) AS CountOf_ID

If there is a count with other fields, the other fields need to be grouped, at least in Access. Aside from all the typo's, the lack of grouping would be something I believe would result in invalid sql.

I speculated in post #6 that if the BE was sql server, perhaps grouping was not required. Since you were testing in sql server, just wondered if that is the case.

And in case the OP reads this. If warnings have been turned off, this would not present as an error

[usertest]

I added the

Docmd.setwarnings True

still nothing.