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

U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
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
1698680435767.png



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

Code: 
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
1698680531426.png



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

1698680835197.png


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!
 
Solution
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.

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"
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"...
Sort by date Sort by votes
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
I have replied to all the answers I could and still havent found a reply.
I thought this was a seperate wesbite and hence cross posted.
I wanted a fresh set of feedback on the issue. Maybe I'm missing a minor thing in the code and posted here as well.
 
Upvote 0 Downvote
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,301
It is a separate website.
However crossposting is frowned upon when it is not mentioned. That way you can get the same advice given twice and just waste responders time. Several members exist on both this site and the other site, not all with the same username.
At least now both sites are aware.
You have also done this on previous posts and that was commented as being crossposted also.
 
Upvote 0 Downvote
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
Gasman said:
It is a separate website.
However crossposting is frowned upon when it is not mentioned. That way you can get the same advice given twice and just waste responders time. Several members exist on both this site and the other site, not all with the same username.
At least now both sites are aware.
You have also done this on previous posts and that was commented as being crossposted also.
Click to expand...
But since the feedback from other members of the forum were somehow not working for my scenario, I created a fresh post and not tagged the cross post as that feedback were unfortunately not working for me.

I genuinely didn't want to go back and make the members read the previous comments to confuse them.
I intend to keep this thread and if I get another feedback and then tag both those threads together for future references.

Let me know if I should delete this thread to make sure the sanity of the forum remains intact and there is no issues from the site moderation perspective.
Thanks!
 
Upvote 0 Downvote
A

adhoustonj

Member
Local time
Today, 05:34
Joined
Sep 23, 2022
Messages
150
Do you need to make it the forms recordsource, and not recordset?
It looks like you are loading a recordset but doing what with it? If form fields are bound to the recordset, I would think it would need to be Me.RecordSource = strSql, but I would probably move the code to the frm_CPS_EDIT load event and get rid of the requery - i don't think that would be needed.
 
Upvote 0 Downvote
June7

June7

AWF VIP
Local time
Today, 01:34
Joined
Mar 9, 2014
Messages
5,472
And I apparently made efforts to test code from other thread. I think we need to see the db.
 
Upvote 0 Downvote
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:34
Joined
May 21, 2018
Messages
8,529
I tried making sure that the Me.CPS_REP_ID is in quotes as it is a varchar value.
Click to expand...
If I understand correctly your ADODB recordset is returning the correct records, but you are unable to bind the fields.

The #name is using when you have a Contolsource set but that controlsource is not found. Example I have a control source of "LastName" but my recordsource has "Last_Name".
First thing is make sure you are binding your recordset correctly. Easiest is to ensure you navigation control shows records.

In this case it could be a timing issue. Before you hit the button there is no Recordset but the controls have Control Sources, and thus #name.

I would remove the control sources and then set them at the same time you return your records.
Code: 
dim frm as access.form
set frm = forms("frm_CPS_Edit")
Set frm.Recordset = r
frm.requery
'Verify the navigation controls shows the correct amount of records
'Set control sources
frm.controls("TxtCps_rep_ID").controlsource = "Cps_rep_ID"
... other controls
 
Last edited:
Upvote 0 Downvote
June7

June7

AWF VIP
Local time
Today, 01:34
Joined
Mar 9, 2014
Messages
5,472
I have tested setup of form with bound controls but no RecordSource. RecordSource is set in form Load event. It works. No #Name? error.
 
Upvote 0 Downvote
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
MajP said:
If I understand correctly your ADODB recordset is returning the correct records, but you are unable to bind the fields.

The #name is using when you have a Contolsource set but that controlsource is not found. Example I have a control source of "LastName" but my recordsource has "Last_Name".
First thing is make sure you are binding your recordset correctly. Easiest is to ensure you navigation control shows records.

In this case it could be a timing issue. Before you hit the button there is no Recordset but the controls have Control Sources, and thus #name.

I would remove the control sources and then set them at the same time you return your records.
Code: 
dim frm as access.form
set frm = forms("frm_CPS_Edit")
Set frm.Recordset = r
frm.requery
'Verify the navigation controls shows the correct amount of records
'Set control sources
frm.controls("TxtCps_rep_ID") = "Cps_rep_ID"
... other controls
Click to expand...
The below screenshot is the property sheet for 1 of the text boxes. Same structure for other text boxes as well. So i think my binding is correct.
Do you think since the name and control source have the same name, that could be an issue?
I can test that as well.

1698689517378.png

1698689538015.png


Values from the intermediate window for debug print

1698690261821.png



Do you want me to update the code post my SQL statement?
Something like below:

Code: 
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 frm = Forms("frm_CPS_EDIT")
Set frm.Recordset = r
frm.Requery

frm.controls("CPS_REP_ID") = "CPS_REP_ID"

If I do that, it shows me an runtime error 2448. You cant assign a value to this object on the frm.controls("CPS_REP_ID") = "CPS_REP_ID"
 
Last edited:
Upvote 0 Downvote
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
adhoustonj said:
Do you need to make it the forms recordsource, and not recordset?
It looks like you are loading a recordset but doing what with it? If form fields are bound to the recordset, I would think it would need to be Me.RecordSource = strSql, but I would probably move the code to the frm_CPS_EDIT load event and get rid of the requery - i don't think that would be needed.
Click to expand...
The form A just shows a summarized view while when you click on the edit emp info for that row, it will open a new form with all detailed info.
So i am storing all the detailed info in recordset (r) and then once the frm_edit form opens, it will display the values in those fields.

That is the premise.

Wouldnt Me.RecordSource = strSql command only show the value in the form A. Will it take the values and display in form B as well?
 
Upvote 0 Downvote
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
June7 said:
I have tested setup of form with bound controls but no RecordSource. RecordSource is set in form Load event. It works. No #Name? error.
Click to expand...
Are you suggesting that I do that in the form load event?
currently there is no event for the form load for that form.
 
Upvote 0 Downvote
GPGeorge

GPGeorge

Grover Park George
Local time
Today, 02:34
Joined
Nov 25, 2004
Messages
1,867
usertest said:
But since the feedback from other members of the forum were somehow not working for my scenario, I created a fresh post and not tagged the cross post as that feedback were unfortunately not working for me.

I genuinely didn't want to go back and make the members read the previous comments to confuse them.
I intend to keep this thread and if I get another feedback and then tag both those threads together for future references.

Let me know if I should delete this thread to make sure the sanity of the forum remains intact and there is no issues from the site moderation perspective.
Thanks!
Click to expand...
The point is that we appreciate the courtesy of posters noting, "I also posted this question at XYZ site" It's not that complicated. Just be courteous with others' time.
 
Upvote 0 Downvote
A

adhoustonj

Member
Local time
Today, 05:34
Joined
Sep 23, 2022
Messages
150
usertest said:
The form A just shows a summarized view while when you click on the edit emp info for that row, it will open a new form with all detailed info.
So i am storing all the detailed info in recordset (r) and then once the frm_edit form opens, it will display the values in those fields.

That is the premise.

Wouldnt Me.RecordSource = strSql command only show the value in the form A. Will it take the values and display in form B as well?
Click to expand...
The Me.RecordSource = strSQL would show you whatever the SQL below returns, and then your controls/form fields will get the data from Form_B's recordsource.


Code: 
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 & "' ;"

But you will need to change the WHERE P.CPS_REP_ID = '" & Me.CPS_REP_ID & " to WHERE P.CPS_REP_ID = '" & [Forms]![Form_A]![CPS_REP_ID] & "
 
Upvote 0 Downvote
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,301
usertest said:
Unfortunately, I cant share the db as it has lots of confidental data for me to mask.
Click to expand...
We only need enough to see the issue?
On that other site, there is a db to mask confidential data. I am on my phone, so no link to share, but a quick search should find it. It was created by Micron and adapted by Moke123
 
Upvote 0 Downvote
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:34
Joined
May 21, 2018
Messages
8,529
If I do that, it shows me an runtime error 2448. You cant assign a value to this object on the frm.controls("CPS_REP_ID") = "CPS_REP_ID"
Click to expand...
I apologize for the typo. I wanted you to set the control source not the value. So it should read
frm.controls("CPS_REP_ID").cotrolsource = "CPS_REP_ID"

I still want your to verify your are in fact binding the form to the recordset. This can be done by simply looking at the navigation control. The reason I say this is that if your controls already have a control source and your code is not setting the recordset correctly then you will get the #name error. Since there is no recordset the controls would reference non existing fields.
 
Upvote 0 Downvote
U

usertest

New member
Local time
Today, 05:34
Joined
Oct 17, 2023
Messages
19
MajP said:
I apologize for the typo. I wanted you to set the control source not the value. So it should read
frm.controls("CPS_REP_ID").cotrolsource = "CPS_REP_ID"

I still want your to verify your are in fact binding the form to the recordset. This can be done by simply looking at the navigation control. The reason I say this is that if your controls already have a control source and your code is not setting the recordset correctly then you will get the #name error. Since there is no recordset the controls would reference non existing fields.
Click to expand...
1698694239445.png

1698694248958.png


Updated code to test:

Code: 
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("CPS_FIRST_NAME").ControlSource = "CPS_FIRST_NAME"

Debug.Print CPS_REP_ID
Debug.Print CPS_FIRST_NAME

If CurrentProject.AllForms("frm_CPS_EDIT").IsLoaded Then
MsgBox "yes"
Else
MsgBox "No"
End If
In the output, I get the value of CPS_REP_ID in the intermediate window, but not for CPS_FIRST_NAME. I also get the msgbox as YES

I'm new to Access and still trying to understand what you mean by "This can be done by simply looking at the navigation control".
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

D
Referencing an unbound form control in a continuous form using recordsetclone
2
Replies
20
Views
1,613
dobseh
D
4
Populating a new form with fields from another table
Replies
3
Views
996
theDBguy
theDBguy
4
Populating a new form with fields from another table
Replies
8
Views
793
theDBguy
theDBguy
F
Combo Box Column not populated (Run Time Error 424)
Replies
7
Views
1,119
fabio
F
K
How do I populate a listbox in Excel with Access Data?
Replies
7
Views
4,896
Cronk
Cronk

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top Bottom