Queires on continous forms ?

[GaryPanic]

Hi Guys - having a a minor moment ..

ok I have a continous form and on each "record" is a country
Germany/France/ Ireland/USA states .50 of them ...) and i select one from a drop down list (no problem)


OK I want to have a list (showing on the record /form) of all the other records that have the same country on them

so if i have say 50 records of which 25 are "Germany" and I select Germany on my new record I want a list on the current record to show all of the Germany records (I only need 2 -3 bits of information rather than the whole dataset

and on the next record say France (and 5 records) it to show just those 5...

I have a qry that once I change the country changes all of the records so if I select germany i get 25 and then if I select france the germany record set then lists just the french ones ..
any ideas

 

[MajP]

It can be done the way you describe, but that user interface would be very confusing as you explain it.
The user selects a country in a new record and while entering in a new record it then filters the list. That can be done but a little more complicated to use and be user friendly.
I would have a combobox in the header. Select a country for that and it filters the record to that country and sets the default value of the new record to that country. Seems more user friendly to me, and easy to code. If a country does not exist in the combo then have a means to add to the list either using a popup or a not in list event.

 

[GaryPanic]

thanks - I knew about putting it into the header/footer route (List box) so that the current record would show the list - but I was looking more along the lines of .. on the germany record list all of the germany things ) more on a recordset basis rather than current record /form I will have more coffee and see if I can wrap my head around it

but your response is gratefully recieved

 

[MajP]

I believe I interpreted your question wrong. I will do a quick demo to see if this is what you want.

 

[MajP]

See if this is what you meant.

 

[GaryPanic]

hello - sorry I justed kick myself out of the network ..
I see what you have done - and it wasn't what I had in mind - I have approach already I am trying to move forward/improve on this - (your approach is a lot neater than mine!! )


so looking at how you have this and looking at the first line - on my continous form i have lots of information so the form for this record is say 3 inch's high (8cm) and I had in mind on the current record "Germany" - it would list all Germany things - and underneath this record would be say France and this would list all the France items - so I can see two lines of information and see two lots of germany thing and french just by looking at it without having to jump to the record ..

 

[MajP]

it would list all Germany things

Is each record information about a country and then the "things" are child records?

You cannot have a continuous form with a continuous subform on each records. You can have a continuos form with a concatenated textbox.
You can also do a subdatasheet, but somewhat limited on what you can do

Opinions on form design

I see. Thank looks soo much simpler than messing with a tree view. Thanks MajP!

www.access-programmers.co.uk

 

[GaryPanic]

You cannot have a continuous form with a continuous child records. You can have a continuos form with a listbox of the child records

this is what I think i am asking
using Germany as the example
I have a set of data in a continous form where I have selected Germany as the country in another table I have say 25 recordss with Germany
so on my German current record its to look at those 25 and list them in a listbox/textbox while also showing the next record (In my example I ahve used France) so I would look at two records and see that Germany has 25 and France has 5 - (It coudl be any country and any amount of course)

 

[MajP]

The problem will be that if you concatenate it into a textbox will be a set size. So although you can scroll the textbox sometimes it will be too big and other times too small. It is doable, but probably not very nice looking
If this is just for viewing then simply do it in a report. A report allows sorting and grouping. You can then neatly have country details and then detail records grouped within.

 

[MajP]

Disregard what I previously said. You cannot put an unbound listbox in a continuous form and filter it. You could make a concatenated textbox. Again that would be a set size, if that is OK.

 

[GaryPanic]

I think that might work

 

[MajP]

This is what can be done doing a concatenation. Each Customer has many orders. In some of the textboxes you will have to scroll, but you cannot change the size of the textbox per record.

 

[GaryPanic]

that is excatly what i am trying to achive ..lol

 

[MajP]

To do this you need to make a concatenate function that does the concatenation of the child records. You pass in the parentID to the function. Then in the forms query you build a calculated control using this function.
I find it is just easier to write my own function each time, but you can find some generic concatenations. I think they are more complicated to use then building your own.

Here is mine for this example, but the are all about the same. Just modify the strSql and the strOut line.

Public Function ConcatOrders(CustomerID As Variant) As String
  Dim rs As DAO.Recordset
  Dim strOut As String
  Dim strsql As String
 
  If Not IsNull(CustomerID) Then
    strsql = "Select OrderID, OrderDate from Orders where CustomerID = '" & CustomerID & "' order by OrderDate"
    Set rs = CurrentDb.OpenRecordset(strsql)
    Do While Not rs.EOF
      strOut = strOut & "OrderID: " & rs!OrderID & " OderDate: " & rs!orderDate & vbCrLf
      rs.MoveNext
    Loop
    ConcatOrders = strOut
  End If
End Function

Then in the form's query I would make a calculated column

Orders: ConcatOrders([CustomerID])

This column can be use a control source same as any other query column.

 

[GaryPanic]

That will do it ...but i need coffee before I attempt it -

Many thanks - if there is an issue i'll shout..
have a great weekend and if you have a bank holiday enjoy it .

 

[MajP]

Here is a generic function, but you can get more flexibility and ease of use if you roll your own

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

If I use one it is this one.

Code Snippets