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!

 

[Gasman]

This is the DB to disguise your data.
Run it on a COPY of your DB

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[MajP]

If a form has controls with controlsources but the form's recordset is not set then you would see something like

Notice the #name? and the 1 of 1 with everything greyed out.

If the recordset is bound to the form I would see something more like

 

[usertest]

If a form has controls with controlsources but the form's recordset is not set then you would see something like
View attachment 110668
Notice the #name? and the 1 of 1 with everything greyed out.

If the recordset is bound to the form I would see something more like
View attachment 110669

I see the the 1st option.
no recordset bound. Let me check the recordsource

 

[Gasman]

I have tested setup of form with bound controls but no RecordSource. RecordSource is set in form Load event. It works. No #Name? error.

O/P is using form.recordset not recordsource though?

This link states you cannot use recordset?

How to set a RecordSet as Form RecordSource

groups.google.com

 

[usertest]

If a form has controls with controlsources but the form's recordset is not set then you would see something like
View attachment 110668
Notice the #name? and the 1 of 1 with everything greyed out.

If the recordset is bound to the form I would see something more like
View attachment 110669

Ok, so i checked the recordset, and it has the query that is initially part of my _click() event


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

I ran the query in the SQL builder and it shows all the records.

 

[MajP]

I have not demoed this in ADODB (is there any reason for not using DAO? Does the data reside in something beside Access?), but you need to ensure the RS cursor location is adUseClient. That may be your issue.
R.CursorLocation = adUseClient

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[MajP]

So I tested it

Private Sub Form_Load()
  Dim rsClone As adodb.Recordset
  Dim strSql As String
 
  Set RS = New adodb.Recordset
  strSql = "SELECT * from Employees"
  RS.CursorLocation = adUseClient
  RS.Open strSql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  Set Me.Recordset = RS
  Set RS = Nothing

End Sub

If I comment out the cursorlocation this code fails
"The object you entered is not a valid recordset property"

 

[MajP]

This link states you cannot use recordset?

That is just simply wrong. You can bind a DAO or ADO recordset to a form, combo, listbox or anything that is bind-able.
Now I am not sure the OP has a need and this may be more complicated than needed, but they should be able to do it and it is worth understanding why it is not working.

 

[Gasman]

That is just simply wrong. You can bind a DAO or ADO recordset to a form, combo, listbox or anything that is bind-able.
Now I am not sure the OP has a need and this may be more complicated than needed, but they should be able to do it and it is worth understanding why it is not working.

I think they meant that you have to use the recordsource property and not the recordset property?
Having set the recordsource, you then have a recordset. ?

 

[usertest]

I have not demoed this in ADODB (is there any reason for not using DAO? Does the data reside in something beside Access?), but you need to ensure the RS cursor location is adUseClient. That may be your issue.
R.CursorLocation = adUseClient

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

No such reason.
Actually I have inherited this file from someone and my job is to fix the issue.
I dont know why DAO wasnt used.
If you think DAO would be better to use, then I can try that as well.

 

[MajP]

If you think DAO would be better to use, then I can try that as well.

If that data is in access then use DAO it is more tailored for Access. ADODB is more robust and flexible for external data sources. However, it is a little more complex to use. By default an access form's recordset is returned as a DAO recordset. Regardless, this should work. You can create an ADO recordset and then set another form's recordset to that recordset.
Did setting the cursorlocation make a difference.

Again, I do not see the error, but the symptoms appear to me as if the form is not being bound to the recordset.

 

[CJ_London]

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

I ran the query in the SQL builder and it shows all the records.

I’m surprised that works since you have spaces in your table names

Table A AS P
TABLE REASONS_REMOVED AS R
TABLE REASONS_ADDED AS A

 

[Gasman]

Both of these work for me.
Seems that link was incorrect as well, though I have always used recordsource for the form?
In fact when I had RecordSource in the first snippet of code, Access gave me an error saying it could not be set.

Sub Test_RST()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strSQL = "Select * from TestTransactions"
Set db = CurrentDb()

Set rst = db.OpenRecordset(strSQL)

DoCmd.OpenForm "frmTransactions"
Set Forms("frmTransactions").Recordset = rst


End Sub

[code]
Sub Test_RST()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strSQL = "Select * from TestTransactions"
Set db = CurrentDb()

Set rst = db.OpenRecordset(strSQL)

DoCmd.OpenForm "frmTransactions"
Forms("frmTransactions").RecordSource = strSQL


End Sub

 

[CJ_London]

No point testing on sql you know works - use the same principle as the op with spaces in the table names and no square brackets

 

[Gasman]

No point testing on sql you know works - use the same principle as the op with spaces in the table names and no square brackets

That is why a copy of the DB would be better?
We are taking the O/P's word on everything else, so I am curious now, as to how that sql works, now you have pointed it out.

He has said in the past that the debug.print of recordset fields names and values work?

I was concentrating on the code.

I do not have any objects with spaces in their names either, and I am not going to create any now.

 

[MajP]

I have always used recordsource for the form?

99% of the time one should probably use the recordsource, but there are times to do the recordset. The most obvious one is when you build an in memory ADO recordset.
Examples here

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Solved - Unbound Combo box need multiple value

Hi, I have a searcher form. It is worked. But now like to put multiple value to search. CODE : Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.fltCompanyName) Then strWhere = strWhere & "([CompanyName] = """ &...

www.access-programmers.co.uk

But other times you may dynamically sort and filter a form and may be very difficult to pass the recordsource. You may even want to link the pop to the mainform. There can also be efficiency gains because it is a pointer to an already loaded recordset.

This example shows both ADO and DAO setting of another forms recordset.

In this thread I do not see the need since it is a basic query and the backend is access. This thread might have been solved by simply passing the sql string recordsource.

 

[usertest]

If that data is in access then use DAO it is more tailored for Access. ADODB is more robust and flexible for external data sources. However, it is a little more complex to use. By default an access form's recordset is returned as a DAO recordset. Regardless, this should work. You can create an ADO recordset and then set another form's recordset to that recordset.
Did setting the cursorlocation make a difference.

Again, I do not see the error, but the symptoms appear to me as if the form is not being bound to the recordset.

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

Code that I tested:

r.Open strSQL, DC.CPS_ASSIGNMENT_DATA, adOpenKeyset, adLockOptimistic
 
 r.CursorLocation = adUseClient


DoCmd.OpenForm "frm_CPS_EDIT", acNormal


Set frm = Forms("frm_CPS_EDIT")
Set frm.Recordset = r
frm.Requery

'frm.Controls("CPS_REP_ID").ControlSource = "CPS_REP_ID"
frm.Controls("PREFERRED_FULL_NAME").ControlSource = "PREFERRED_FULL_NAME"

Debug.Print CPS_REP_ID
Debug.Print PREFERRED_FULL_NAME
Debug.Print CountOfOSJ_ID

If CurrentProject.AllForms("frm_CPS_EDIT").IsLoaded Then
MsgBox "yes"
Else
MsgBox "No"
End If






'Forms("frm_CPS_EDIT").Requery
Forms!frm_CPS_EDIT!CPS_REP_ID.ControlSource = CPS_REP_ID
'Forms!frm_CPS_EDIT!CPS_FIRST_NAME.ControlSource = r.Fields(1).Value



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

 

[usertest]

I’m surprised that works since you have spaces in your table names

Table A AS P
TABLE REASONS_REMOVED AS R
TABLE REASONS_ADDED AS A

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

 

[usertest]

99% of the time one should probably use the recordsource, but there are times to do the recordset. The most obvious one is when you build an in memory ADO recordset.
Examples here

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Solved - Unbound Combo box need multiple value

Hi, I have a searcher form. It is worked. But now like to put multiple value to search. CODE : Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.fltCompanyName) Then strWhere = strWhere & "([CompanyName] = """ &...

www.access-programmers.co.uk

But other times you may dynamically sort and filter a form and may be very difficult to pass the recordsource. You may even want to link the pop to the mainform. There can also be efficiency gains because it is a pointer to an already loaded recordset.

This example shows both ADO and DAO setting of another forms recordset.

In this thread I do not see the need since it is a basic query and the backend is access. This thread might have been solved by simply passing the sql string recordsource.

Let me use this file and copy the code and check if I can replicate the same or not.
WIll get to you all.
Thanks!

 

[MajP]

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

You are saying I should be able to do this if my table name is "Table Reason". You have the word Table that is not supposed to be there. 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.