How to Display Corresponding Recordsets in Form?

[gohym]

Hi,

I'm a newbie to programming, appreciate any help given here... thx in advance!

For each record, there are fields txtV1 and txtV2. I displayed them in a form, frmMain. For each record I also need to display 2 corresponding recordsets (rsV1 and rsV2), each corresponding to txtV1 and txtV2 respectively. rsV1 and rsV2 are created based on a Union Query.

After much struggle, I am able to create rsV1 and rsV2 using DAO's openrecordset method, but I do not know how to display them in the form... I have thought of using subform but it is not so straight forward due to the union query and that the criteria of the union queries being dependent on txtV1 and txtV2.

I hope this is clear to you guys... thx!

 

[KenReay]

Hi

Where to begin.

If theer is a relationship between the recordset you are using for frmMain, and the recordsets rsV1 and rsV2, perhaps you can make a query which includes all three recordsets (tables?) and then use that as the recordsource of frmMAin, all of the columns of the query will then be available for display (and possibly update, but that depends if it is an updatable query)

If the two recordsets rsV1 and RsV2 are 'child' recordsets of the first one, then a sub form (or subforms) would seems to be the answer using the facilities within Access (Master Child Link fields property) to have the relevant records displayed.

However, if you will forgive me for saying so, from your explanation, I think you need to take a step back. In my humble opinion the key thing to using Access successfully is to really understand table design, structure and relationships, and it does not sound to me like you do understand that. Why not describe the data you are trying to store and see if someone can help you structure it sensibly and correctly. Once you have that the queries, forms, reports just fall into place

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PeteJohnston]

Sorry, but "clear as mud"! How many tables are involved? What are the two recordsets? Do they relate different tables or perhaps different records in the same table?

If you go into Relationships (from the toolbar) and add your tables (right-click and Add Tables) then define the relationships between them by clicking on a field and dragging it to the related field. If both are on the same table you need to add the table twice to the relationship diagram. Then create a query (either in design view or by using the wizard) to represent the recordsets. This is usually straight forward even if it is a complicated query. You can then add a subform to frmMain for each and let the wizard take you through the steps - it should prompt you for the datasource (ie. the query you have created), then let you define the fields which link the main form to each subform.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[gohym]

Guess I did a terrible job for the description part... sorry! To be frank I am not too sure if I'm getting the whole thing right too... well let me give one last try, if it seems like rubbish to you tell me and I will just have to do more first before I come back to you guys.

In order to cut things short I will just desctibe the crux of my problem... hopefully I can do this properly...

Basically I created a DAO recordset using the following (I can't seem to get it any neater... sorry):

SQLline = "SELECT [Job Step SS].[Value 1] AS [Input], [Job Step SS].[Value 2] AS [Stored], " & _
"[Job Step SS].[Similarity] FROM [Job Step SS] WHERE ([Job Step SS].[Value 1] ='" & V1 & "') " & _
"UNION SELECT [Job Step SS].[Value 2] AS [Input], [Job Step SS].[Value 1] AS [Stored], " & _
"[Job Step SS].[Similarity] FROM [Job Step SS] " & _
"WHERE ([Job Step SS].[Value 2] ='" & V1 & "'); "
Set rsV1 = dbs.OpenRecordset(SQLline)
rsV1.Sort = "Input, Stored"
rsV1.Filter = &quot;Stored <>'&quot; & V2 & &quot;'&quot;
Set rsV11 = rsV1.OpenRecordset()

There is only one table [Job Step SS] and I need to capture the set of records with either Value 1 or Value 2 being equal to V1... these 2 sets of values are unioned into 1 recordset (rsV11) with fields {Input, Stored, Similarity}

Now having the recordset rsV11, I intend to display the whole recordset in a single form (together with other stuff). Thus the key qn is, how do I display such a recordset? there are some other issues, but I think I should try to do things step by step... Thanks!

 

[KenReay]

Hi

If you are a beginner why are you making life more difficult for yourself, you could build the query in the query builder, and use that saved query as the recordsource of the form

I see no reason to build two recordsets and then join them together, just have your query criteria as somthing like [Job Step SS].[Value 2] = V1 OR [Job Step SS].[Value 2] = V2



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[gohym]

Hi,

But the problem (at least for me) is V1 is a variable... can you help me here... I now about parameter query, but can we base a form on a parameter query? Or is parameter not relevant here?

Still... regardless of the relevance... I'm also interested in knowing whether is it possible to display rsV11... thx!!

 

[gohym]

Thought I should give more info... don't want to confuse everyone again...

I am hoping to cycle through [Job Step SS] using a form, displaying [Value 1] (among other info), which is also V1... and every time I come to a new record (i.e. new V1), a corresponding rsV11 is displayed in the form... note that rsV11 uses V1 as the criteria.

Thanks!