Data no show in subform

[rickyfong]

I am trying to do a simple Do-while loop to display master and relevant transactions in form/subform environment under Access 2003.

Below are some of my statements:

Dim dbs As DAO.Database
Dim TRANS1, TRANS2 As DAO.Recordset
Set TRANS1 = CurrentDb.OpenRecordset("Master", dbOpenDynaset)
Set TRANS2 = CurrentDb.OpenRecordset("Transaction", dbOpenDynaset)

...
TRANS1.MoveFirst
Do While Not TRANS1.EOF
Me.TEXT456 = TRANS1![TRANSCODE]
Me.Requery
MsgBox "Press anykey to next record!!"
TRANS1.MoveNext
Loop


then I ha e a main and subform creation with at least the variable "text456" to ensure the master transaction code (i.e. the transcode) has been located and are in the main form. In the subform that I have created, I have a condition as [forms]![mainform].[text456] under the "transcode" field in relevant query file. I have also checked that in the property of subform, both the child and parent linkages are stored the "transcode" variable.

But when I run the program, it only show the "transcode" in main form but no data showing in the subform. Any idea, is that the loop with property such as trans1 and trans2 would'nt work in this way!!?? Thanks!!

 

[pr2-eugin]

rickyfong, I am quiet unsure why are you using Code here? Is the Main form and Subform not related? Why has this not been defined in the Form design set up? Are you creating dynamic Subform? Even if that is the case, just assign the Recordset to the subform; why are you looping through?

 

[rickyfong]

rickyfong, I am quiet unsure why are you using Code here? Is the Main form and Subform not related? Why has this not been defined in the Form design set up? Are you creating dynamic Subform? Even if that is the case, just assign the Recordset to the subform; why are you looping through?

Hi, The main and subform are related with transcode. (one master record with a few transactions relationship).
I have open 2 files, one main and one transaction with different property to control , the trans1 and trans2.
I don't think I have created a dynamic subform. The subform is used to display the relevant transaction records based on the main form transcodye.
How to assing the recordset to the subform, the subform is a query which should enquery the relevant information based on the condition
forms![main].text456 whereas text 456 is the variable in the main form to hold the transcode of the master record.
In fact, each succesfully "transaction" contain a master record and a few transaction records. The looping is to display all "transaction" one by one!

I have also try the advises from the following thread, the it still not working yet!

http://www.access-programmers.co.uk/forums/showthread.php?t=111250&highlight=RecordSource+query​

Thanks for your help!!

 

[JHB]

Show the recordsource for the main and sub-form.
Have you set the "Link Master Fields" and "Link Child Fields"?

 

[rickyfong]

DO I miss something!! How to show the recordsource for the main and subform?? IN fact, I have the transcode in both link master and child fields!! Thanks!!

 

[rickyfong]

Me.[Main] Form.RecordSource = "select * from MASTER where MASTER.Transcode =" & SCODE & ""
Me.[TRANS SUBFORM].Form.RecordSource = "select * from transaction where transaction.transcode =" & SCODE & ""

given SCODE is the current transcode which is using to control which set of master and transactions to be displayed!!

I have included the above 2 statements, but it is still not working!!

 

[rickyfong]

Solved! The select statement should be as follows:

Me.[Main] Form.RecordSource = "select * from MASTER where MASTER.Transcode = '" & SCODE & "'"
Me.[TRANS SUBFORM].Form.RecordSource = "select * from transaction where transaction.transcode = '" & SCODE & "'"

 

[pr2-eugin]

That is exactly what I asked you to do in my first post..

just assign the Recordset to the subform; why are you looping through?

Sorry was away for the weekend.. But I am glad you have it solved..