Search results

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!

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

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

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!

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

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

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.

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 " &...

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

Updated code to test: 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...

Are you suggesting that I do that in the form load event? currently there is no event for the form load for that form.

Unfortunately, I cant share the db as it has lots of confidental data for me to mask.

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

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. Values from the intermediate...

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

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.

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

Hello, Below is the code where I want to make it using pass through query. Can anyone guide me how can i achieve the below logic and implement using PTQ? Dim rs1 As New ADODB.Recordset Dim strSQL As String strSQL = "SELECT * FROM SQLTABLE;" rs1.Open strSQL, DC.ASSIGNMENT_DATA, adOpenKeyset...

Hello, I have the below code that I am trying to delete the records from access table (listtable) and then adding the records from SQL (SQL table) DoCmd.SetWarnings False DoCmd.RunSQL "Delete * From LISTTABLE;" DoCmd.SetWarnings True strSQL = "SELECT * FROM LISTTABLE;" r1.Open...