Full Version: help with subform recordsource
UtterAccess Forums > Microsoft® Access > Access Forms
ishabir
I have a subform and unbound textboxes. I want the textboxes to be populated with the data that is retured from the code below. How can i set the subform recordsource to be the data from the code below?
tSQL = "select SFDC_Z4_RP_STOP_LOGS.[STOP_START_DATE], SFDC_Z4_RP_STOP_LOGS.[STOP_END_DATE], SFDC_Z4_RP_STOP_LOGS.[STOP_DURATION], SFDC_Z4_RP_STOP_LOGS.REMARK, SFDC_SFDC_STOP_TEXTS.[STOP_REASON]"
stSQL = stSQL & " FROM SFDC_Z4_RP_STOP_LOGS INNER JOIN SFDC_SFDC_STOP_TEXTS ON "
stSQL = stSQL & " SFDC_Z4_RP_STOP_LOGS.STOP_CODE = SFDC_SFDC_STOP_TEXTS.STOP_CODE"
stSQL = stSQL & " WHERE SFDC_Z4_RP_STOP_LOGS.STOP_CODE =" & [Forms]![frmDelaysAnalysis]![lstReasonCode]
Thanks very much
ishabir
danishani
Try this:
YourSubFormName].Form.Recordsource = stSQL
HTH
Daniel
ishabir
thanks very much for that Daniel. It seems to be running in that when i select different criteria it returns different number of records in the subform but all are blank. So it does not seem to be populating the controls on the subform. I have 5 textboxes on the subform...how do i get to populate these with the recordsource of stSQL
Thanks very much.
ishabir
danishani
Yes u need to set the fields to the textfields which are unbound i guess...
can do that by open the recordset and set the record fields to the unbound texfields.
rst(0) = the first field in your SQL
For example...
Set dbs As DAO.Database
Set rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordSet(stSQL)
Me.TextField1 = rst(0)
Me.TextField2 = rst(1)
Me.TextField3 = rst(2)
Me.TextField4 = rst(3)
Me.TextField5 = rst(4)
rst.Close
dbs.Close
Set rst = Nothing
set dbs = Nothing
HTH
Daniel
ishabir
thanks again Daniel. Slight problem in that everytime you run it it only ever returns one record when I know there are more than 1 result.
o you know what is casuing this?
Thanks
ishabir
danishani
Hmmm difficult to tell...
can check the SQL in a query, to see if it return more?
Something to do with the Where clause ?
For example, whats the datatype of the Stop_Code?
Daniel
ishabir
I have just checked the SQL in a query and get 3 records returned but the subform only shows 1 record returned. The datatype of STOP_CODE is a number/integer field. very puzzling!
Any ideas?
thanks
ishabir
ishabir
just to add...only the first record gets returned in the subform and the rest are not displayed.
Thanks
ishabir
danishani
Is the Subform linked to the Mainform by an ID?
aniel
ishabir
no links and no bound recordsource for the subform.
shabir
danishani
Can u post a sample of the subform and form, query and table, without the confential data....zipped and compacted < 500kb. So i can take a look? Its weird...
aniel
ishabir
here it is dan.
Cheers
ishabir
ishabir
sorry forgot attachment
danishani
I miss one table SFDC_Z4_RP_STOP_LOGS, can u send the db again with that table?
Thanks! frown.gif
Daniel
ishabir
my mistake Daniel. The SFDC_SFDC_STOP_LOGS table is the SFDC_Z4_RP_STOP_LOGS table I just named it wrong when i cut it down. Could you please rename it.
Thanks
ishabir
ishabir
any look Daniel with what the problem is?
Thanks
ishabir
danishani
Hi shabir,
Yes, i know what the problem is, but almost there with the solution...
If i am done, I will post the database back to you...
Daniel
ishabir
thanks very much Daniel. I look forward to the solution.
shabir
danishani
Hi Ishabir,
I have done it... see attachment, sorry it took so long, I just wake up this morning smirk.gif
Hope this is what you after...
Osetup the recordsource of the subform, when opening the mainform, and when u select a Stop_Code then the recordsource will be changed according your selection. Let me know this ok...
Cheers,
Daniel
ishabir
Thanks Daniel, that's working great. Sorry for rushing your morning. Hope you have a good day. Your help is very much appreciated.
One other thing...how would it be possible to add a textbox control in the footer section of the main form that sums all the STOP_DURATION in the subform. So that I can get some summary totals.
Thanks very much for your help.
ishabir
danishani
Hi Ishabir,
Take a look at the attachment...
Created another SQL where the Stop_Duration is summed based on the Selection Stop_Code.
HTH
Daniel
ishabir
Thanks Again Daniel. You are a star thumbup.gif
danishani
No problem glad to help frown.gif
aniel
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.