Recordset not populating data in a new form (#Name?)

[usertest]

Hello All,
I am unable to populate the records fetched from a recordset (r) in Form A into Form B.

This is Form A. In this form, I have 3 text boxes which are populated using a sql query that is stored in a recordset (r).
Once the user click on EDIT EMP INFO, it should use the CPS_REP_ID and populate additional data into Form B

Below is the code for the button click. The debug.print produces the desired output

Private Sub btn_EDIT_EMPLOYEE_INFO_Click()

Dim strSQL As String
Dim r As New ADODB.Recordset
Dim DC As New DataConnection

strSQL = "SELECT P.CPS_REP_ID, P.CPS_FIRST_NAME, P.CPS_LAST_NAME, P.CURRENT_MEMBER, P.DATE_ADDED, " & _
         "P.DATE_REMOVED, P.PREFERRED_FULL_NAME, P.EMAIL_ADDRESS, A.REASON_ADDED, R.REASON_REMOVED " & _
         "FROM (Table A AS P LEFT JOIN  TABLE REASONS_ADDED AS A ON P.REASON_ADDED = A.ID) LEFT JOIN " & _
         "TABLE REASONS_REMOVED AS R ON P.REASON_REMOVED = R.ID WHERE P.CPS_REP_ID = '" & Me.CPS_REP_ID & "' ;"
        
r.Open strSQL, DC.CPS_DATA, adOpenKeyset, adLockOptimistic


Debug.Print r.Fields(0).Name
Debug.Print r.Fields(1).Name
Debug.Print r.Fields(2).Name
Debug.Print r.Fields(0).Value
Debug.Print r.Fields(1).Value
Debug.Print r.Fields(2).Value



DoCmd.OpenForm "frm_CPS_EDIT", acNormal

Set Forms("frm_CPS_EDIT").Recordset = r

Forms("frm_CPS_EDIT").Requery



DoCmd.Close acForm, "DASHBOARD", acSaveNo
End Sub

This is form B

But whenever I click on the button, it always shows me #Name? on my new form.

I tried making sure that the Me.CPS_REP_ID is in quotes as it is a varchar value.
Is it a datatype issue or a control name issue?

Any guidance or help would be appreciated.

Thanks!

 

[usertest]

You are saying I should be able to do this if my table name is "Table Reason". Never seen that work and tried it both in code and in qbe and it does not work.

SELECT A.* FROM Table Reason as A

I tried this and get a syntax error as I would expect. But this works as expected.

SELECT A.* FROM [Table Reason] as A

So there is no way that query works. So how is the debug working. Something else is going on.

Apologies for the confusion.
What I meant in posting that initial query was that there is a table called REASON which is aliased as A.
just for the sake of easier reading of the code.
that is how the developer had aliased the tables so I just wanted to make it clear from a reading perspective.
But overall the query runs fine as the table names are the way it should be..

 

[Gasman]

Apologies for the confusion.
What I meant in posting that initial query was that there is a table called REASON which is aliased as A.
just for the sake of easier reading of the code.
that is how the developer had aliased the tables so I just wanted to make it clear from a reading perspective.
But overall the query runs fine as the table names are the way it should be..

Show us the ACTUAL code, not code you have changed to make it easier?
As CJ_London and now MajP have pointed out, the sql code as posted does not work. So everything after that is moot.

 

[MajP]

overall the query runs fine as the table names are the way it should be

NO it does not run, if that code is real. You cannot just throw the word Table before the actual table name and say it works. So we have no idea what your true query is.
If your table name is truly "TABLE REASONS_ADDED" the SQL might work if you drop it into the query editor, because it is smart and will do this
[TABLE REASONS_ADDED]
But if you do that in code it will fail. There is NO debate.

 

[MajP]

At this point this is really just an intellectual exercise. You do know this can be done in one line of code using the where criteria of the OPENFORM method?

Docmd.openform "Frm_CPS_EDIT",,,"CPS_REP_ID = '" & me.CPS_REP_ID & "'"

 

[usertest]

NO it does not run, if that code is real. You cannot just throw the word Table before the actual table name and say it works. So we have no idea what your true query is.
If your table name is truly "TABLE REASONS_ADDED" the SQL might work if you drop it into the query editor, because it is smart and will do this
[TABLE REASONS_ADDED]
But if you do that in code it will fail. There is NO debate.

This is the query

strSQL = "SELECT P.CPS_REP_ID, P.CPS_FIRST_NAME, P.CPS_LAST_NAME, P.CURRENT_MEMBER, P.DATE_ADDED, " & _
         "P.DATE_REMOVED, P.PREFERRED_FULL_NAME, P.EMAIL_ADDRESS, A.REASON_ADDED, R.REASON_REMOVED " & _
         "FROM (CPS_PERSONNEL AS P LEFT JOIN REASONS_ADDED AS A ON P.REASON_ADDED = A.ID) LEFT JOIN " & _
         "REASONS_REMOVED AS R ON P.REASON_REMOVED = R.ID WHERE P.CPS_REP_ID = '" & Me.CPS_REP_ID & "' ;"

r.Open strSQL, DC.CPS_DATA, adOpenKeyset, adLockOptimistic

I think someone mentioned that it is a local access table. These tables are NOT local access tables, but directly connected to SQL tables and fetching up the data from the source itself.
Hence the DC.CPS_DATA data connection that is established.

The developer has not linked the tables and it can't be done as per the requirement.

If I have to create a query just like how you provided in the demo file, I will have to create a pass-through query and then pass this query and then set the recordset, which I think would 100% work and I'm WIP on that part to test it out.

 

[MajP]

I think someone mentioned that it is a local access table. These tables are NOT local access tables, but directly connected to SQL tables and fetching up the data from the source itself.

That is because in thread 30 I asked this question specifically, but since you did not answer I assumed local.

I tested using the cusrosr location.
Got the error for the cursorlocation line.

The error says that you cannot assign cursor location once the RS opened. Pretty obvious error statement. And pretty obvious solution is to do what I demonstrated and set that cursor location like I do prior to opening the RS.

Recordset not populating data in a new form (#Name?)

Hello All, I am unable to populate the records fetched from a recordset (r) in Form A into Form B. This is Form A. In this form, I have 3 text boxes which are populated using a sql query that is stored in a recordset (r). Once the user click on EDIT EMP INFO, it should use the CPS_REP_ID and...

www.access-programmers.co.uk

 

[usertest]

So I finally had to create a pass-through query to bring in those data tables from SQL and then created a local query and it is working now.

Thank you all for your help. much appreciated and being patient during the confusions!

 

[MajP]

You can simply update the sql of the query definition of your pass through. Something like this

Dim strSql As String
  Dim qdf As QueryDef
  Set qdf = CurrentDb.QueryDefs("qryDummyPass")
 
  If IsNull(Me.cmboFamily) Then
   strSql = "SELECT NATALNAME, FullName FROM vw_Population ORDER BY FullName"
  Else
    strSql = "SELECT NATALNAME, FullName FROM vw_Population "
    strSql = strSql & " WHERE CURRENTFAM = " & CSql(Me.cmboFamily) & " ORDER BY FullName"
  End If
  qdf.Sql = strSql

 

[CJ_London]

It is normal SQL query. It is an alias to a table. It will work

I wish I could believe you but it is not a normal access query and you provide no evidence that it does work such as a screenshot of the results from your debug.prints and in previous threads you provided different versions of what you say you have

 

[Gasman]

40

You can simply update the sql of the query definition of your pass through. Something like this

Dim strSql As String
  Dim qdf As QueryDef
  Set qdf = CurrentDb.QueryDefs("qryDummyPass")

  If IsNull(Me.cmboFamily) Then
   strSql = "SELECT NATALNAME, FullName FROM vw_Population ORDER BY FullName"
  Else
    strSql = "SELECT NATALNAME, FullName FROM vw_Population "
    strSql = strSql & " WHERE CURRENTFAM = " & CSql(Me.cmboFamily) & " ORDER BY FullName"
  End If
  qdf.Sql = strSql

@MajP
You would need to supply your CSql() function as well?

 

[MajP]

You would need to supply your CSql() function as well?

Probably overkill in this case since the cboFamily should just return a string name, but that function delimits the string 'Name' and if the name has a single ', it doubles it up.
However it is a very helpful function and handles strings, numbers, nulls, dates, and special characters.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[spaLOGICng]

Hello All,
I am unable to populate the records fetched from a recordset (r) in Form A into Form B.

This is Form A. In this form, I have 3 text boxes which are populated using a sql query that is stored in a recordset (r).
Once the user click on EDIT EMP INFO, it should use the CPS_REP_ID and populate additional data into Form B
View attachment 110659


Below is the code for the button click. The debug.print produces the desired output

Private Sub btn_EDIT_EMPLOYEE_INFO_Click()

Dim strSQL As String
Dim r As New ADODB.Recordset
Dim DC As New DataConnection

strSQL = "SELECT P.CPS_REP_ID, P.CPS_FIRST_NAME, P.CPS_LAST_NAME, P.CURRENT_MEMBER, P.DATE_ADDED, " & _
         "P.DATE_REMOVED, P.PREFERRED_FULL_NAME, P.EMAIL_ADDRESS, A.REASON_ADDED, R.REASON_REMOVED " & _
         "FROM (Table A AS P LEFT JOIN  TABLE REASONS_ADDED AS A ON P.REASON_ADDED = A.ID) LEFT JOIN " & _
         "TABLE REASONS_REMOVED AS R ON P.REASON_REMOVED = R.ID WHERE P.CPS_REP_ID = '" & Me.CPS_REP_ID & "' ;"
      
r.Open strSQL, DC.CPS_DATA, adOpenKeyset, adLockOptimistic


Debug.Print r.Fields(0).Name
Debug.Print r.Fields(1).Name
Debug.Print r.Fields(2).Name
Debug.Print r.Fields(0).Value
Debug.Print r.Fields(1).Value
Debug.Print r.Fields(2).Value



DoCmd.OpenForm "frm_CPS_EDIT", acNormal

Set Forms("frm_CPS_EDIT").Recordset = r

Forms("frm_CPS_EDIT").Requery



DoCmd.Close acForm, "DASHBOARD", acSaveNo
End Sub

This is form B
View attachment 110660


But whenever I click on the button, it always shows me #Name? on my new form.

View attachment 110661

I tried making sure that the Me.CPS_REP_ID is in quotes as it is a varchar value.
Is it a datatype issue or a control name issue?

Any guidance or help would be appreciated.

Thanks!

I don't see where you are defining the Data Connection.

The SQL Statement does not know where the data lives. Define the DC and I think you will be good,