How to resolve #Name error?

[alexsmith91]

Hey,


I have a continuous form that should populate the output from a SQL query.

Private Sub Form_Load()
Dim A As New ADODB.Recordset
Dim DC As New DataConnection


Dim strSQL As String




strSQL = "SELECT FULL_NAME as NAME, PHONE as DIAL, LOCATION as ADDRESS from table;"
         
         A.Open strSQL, DC.ConnectionProperties, adOpenKeyset, adLockOptimistic
         
         Set Me.Recordset = r


End Sub

The DC.ConnectionProperties is the connection string that is already defined and it working as expected, since I use the same connection for other queries as well.

Below is the debugging that I have already performed and are producing the output as expected in the Intermediate window

• SQL query is correct.
• debug.print field
• debug.print value
• debug.print recordcount
• Control source has not been changed and they are as-is.

What other debugging that I can do to check to see the issue?

[Minty]

Name is a reserved word (and a poor choice of Alias, Name of what?) so if you continue to use it try surrounding it with square brackets [Name]

In addition to the above I've just re-read your post

You aren't setting the recordset to your SQL.

Edit2:

Why bother with all the ADODB stuff?
Just set the forms recordsource to the SQL, assuming the table is linked?

[alexsmith91]

Thanks for your quick reply.

1: I changed the reference of NAME to other text and it still doesnt populate. I also modified the control source to match the alias.

2: Oh got it, I fixed that as well. Set Me.Recordset = A. But still doesnt populate the form

3: the table is not linked. and I dont have that option to do it.

Name is a reserved word (and a poor choice of Alias, Name of what?) so if you continue to use it try surrounding it with square brackets [Name]

In addition to the above I've just re-read your post

You aren't setting the recordset to your SQL.

Edit2:

Why bother with all the ADODB stuff?
Just set the forms recordsource to the SQL, assuming the table is linked?

[Minty]

Have you tried using

Me.RecordSource = A

I'm not sure setting the forms recordset has the same effect?

[alexsmith91]

Got a Compile Error : Type mismatch error on that line you suggested.

So the reason I had put set me.recordset=A is because, I have a button against each row and then once you click the button, it will take the Name, Dial, Address for that record and open a new form and populate information on that.

Have you tried using

Me.RecordSource = A

I'm not sure setting the forms recordset has the same effect?

[Minty]

Okay create a pass through query with the same SQL. Make sure you set the connection properties as per you existing connection string.
Save it as pt_MyData
Set the forms Recordsource to the query in the form, note it won't be editable.

That will allow you to open the query and see that it is bringing in the correct data, and lets you mess about with the syntax easily if it's not quite right.

Still not sure why you can't link to it if you can create a ADODB connection to it?

[alexsmith91]

I did that.
I also checked the query and it was populating the correct data with no issues on syntax.
Now when I set the rs to query, it is now popultaing the table.
Weird and strange.
Thanks.

Okay create a pass through query with the same SQL. Make sure you set the connection properties as per you existing connection string.
Save it as pt_MyData
Set the forms Recordsource to the query in the form, note it won't be editable.

That will allow you to open the query and see that it is bringing in the correct data, and lets you mess about with the syntax easily if it's not quite right.

Still not sure why you can't link to it if you can create a ADODB connection to it?