Solved Weird behavior of subform while navigatin trough query records (1 Viewer)

[Romio_1968]

Hello,

I have an Continuous subform linked to the following Query

SELECT Titles.Title_ID, 
       Titles.Title, 
       SQLConcatenate("SELECT Author.Author_Name 
                        FROM Author 
                        INNER JOIN TAJunction ON Author.Author_ID = TAJunction.Author_IDFK
                        WHERE TAJunction.Title_IDFK = " & [Titles].[Title_ID],", ") AS AuthorNames, 
       Titles.Timestamp, 
       Titles.Publisher, 
       Titles.PublishPlace, 
       Titles.PrintYear, 
       Titles.Media, 
       SQLConcatenate("SELECT ClassCode 
                        FROM TDJunction 
                        WHERE Title_IDFK = " & [Titles].[Title_ID],", ") AS ClassCodes, 
       SQLConcatenate("SELECT Inventory_No 
                        FROM Inventory 
                        WHERE Title_IDFK = " & [Titles].[Title_ID],", ") AS InventoryNumbers, 
       [ClassCode1] AS Cota, 
       IIf(InStr([ClassCodes],";")=0,Trim([ClassCodes]),Trim(Left(Trim([ClassCodes]),InStr(Trim([ClassCodes]),";")-1))) AS ClassCode1, 
       IIf(InStr([ClassCodes],";")=0,"",IIf(InStr(Mid([ClassCodes],InStr([ClassCodes],";")+1),";")=0,Trim(Mid([ClassCodes],InStr([ClassCodes],";")+1)),Trim(Left(Trim(Mid([ClassCodes],InStr([ClassCodes],";")+1)),InStr(Trim(Mid([ClassCodes],InStr([ClassCodes],";")+1)),";")-1)))) AS ClassCode2, 
       Trim(IIf(InStr([ClassCodes],";")=0,"",IIf(InStr(Mid([ClassCodes],InStr([ClassCodes],";")+1),";")=0,"",Mid(Mid([ClassCodes],InStr([ClassCodes],";")+1),InStr(Mid([ClassCodes],InStr([ClassCodes],";")+1),";")+1)))) AS ClassCode3
FROM Titles;

The controls on subform are linked to the above query fields.
Loading fine, works fine, all except 3 unbound textbox controls
There are 3 controls that are taking value from correspondin query fields, called ClassCode1, ClassCode2, ClassCode3.
All three are calculated fields in the Query
The 3 unbound textbox (Domain1, Domain2, Domain3) controls are suppose to take value based on ClassCode1, ClassCode2, ClassCode3.
I used the Form_OnLoad event to do this, using the following code:

Private Sub Form_Load()
' ClassCode1 - Domain1
Me.Domain1 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode1 & "'")
' ClassCode2 - Domain2
Me.Domain2 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode2 & "'")
' ClassCode3 - Domain3
Me.Domain3 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode3 & "'")
End Sub

Usin this, the first record was ok, including the Domain1-3 controls, but when navigating form the subform Nav.Bar to the next record, the values inside Domain1-3 controls are not updated, shwing the values for the first record, up to the end.

I tryed to use the same code not in Form_OnLoad event, but in Form_Current.
Now looks quite good. The values of the 3 textboxes are updated for each record, but... if I try to navigate trough records using the vertical scrol bar, again I see no updated values.

Any Ideea why?

I may have a workarround using the query itself, by creating an agregated field and then breaking it apart, but I think that maybe there is something simpler out there.

Thank You

 

[arnelgp]

use Current event and not Load event:

Private Sub Current_Load()
' ClassCode1 - Domain1
Me.Domain1 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode1 & "'")
' ClassCode2 - Domain2
Me.Domain2 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode2 & "'")
' ClassCode3 - Domain3
Me.Domain3 = DLookup("Domain", "Domains", "ClassCode = '" & Me.ClassCode3 & "'")
End Sub

 

[Romio_1968]

arnelgp, you mean Form_Current()?

Ity is in Form_Current()

First record

Second record by clicking the navigation button:

The same second record, but on scrollbar
On screen is the 2nd record data, except the mess from lower three rows.
The numbers are ok, as in query reuslt, but the Description fields are not updates.
And the record number still shows Record 1 of 6

 

[ebs17]

If you don't have problems, make them yourself.
It is merciless nonsense to combine the contents of table fields into an overall string in a query and then immediately split this string back into individual elements. Two steps forward, two steps back - how do you get ahead? At least the processor doesn't get cold, at least it has to work.
Note: Such composite strings are for display only, for those who explicitly need it. This does not do any further processing.

You have visible options via linking and thus via a query to get from Titles.Title_ID to Domains.Domain , see the schema of your relationships. With the link to Title_ID you have in (another) subform all the values you need, simply and securely. But how to represent and provide value is another matter. There are also queries that can do what you want.

 

[Romio_1968]

If you don't have problems, make them yourself.
It is merciless nonsense to combine the contents of table fields into an overall string in a query and then immediately split this string back into individual elements. Two steps forward, two steps back - how do you get ahead? At least the processor doesn't get cold, at least it has to work.
Note: Such composite strings are for display only, for those who explicitly need it. This does not do any further processing.

You have visible options via linking and thus via a query to get from Titles.Title_ID to Domains.Domain , see the schema of your relationships. With the link to Title_ID you have in (another) subform all the values you need, simply and securely. But how to represent and provide value is another matter. There are also queries that can do what you want.

Yeah... Doesn't make too much sense, I know. Yet, that subform should "present" data in a compact and recogizable way and I didn.t found a way to "Group" the record in a query and doing text opperatins. I know that the meaning of form is not for presentation, but for working on data, yet I cannot see how the user will be confortable to recongnise and to address, while shearcing, the same data set (1 Tithe, many authors, many domains and many inv. no. split in multiple records).
"If you don't have problems, make them yourself". We are saying otherwise: When it has nothing to do, the cat licks its ass until it hurts.
I'm starting to feel some pain...

Anywy, getting back on topic, sometimes doing weird things you may get weird results. It seems that this is a known behavior of using mouse wheel on forms. What i found out until now is that is not reccommended to navigate trough records this way because sometimes it can mess the data handled. It looks that i didn't wait for that "sometimes", but stepped on the shit form the verry beginning,

 

[ebs17]

Your shown form is a single form. You can simply embed a subform with the following RecordSource, which is linked after the Title_ID.

SELECT
   J.Title_IDFK,
   J.ClassCode,
   D.Domain
FROM
   TDJunction AS J
      INNER JOIN Domains AS D
      ON J.ClassCode = D.ClassCode

Since this eliminates the three bottom lines, you get virtually the same view. By the way, you don't have to shoot yourself in the knee if another domain #4 and #5 would be displayed.

You can remove the monsters added to the original query.

 

[bastanu]

The behaviour you encountered is what happens with unbound controls on a continuous form.

Overcoming unbound continuous forms limitations by staging data | Experts Exchange

Learn more about Overcoming unbound continuous forms limitations by staging data from the expert community at Experts Exchange

www.experts-exchange.com

unbound text in continuous form

table data id_no p_name q_work i use a continuous form and insert data in table easily but i use unbound text in continuous form it is not work need insert data in table unbound text in use continuous form

www.access-programmers.co.uk

Cheers,

 

[Romio_1968]

Your shown form is a single form. You can simply embed a subform with the following RecordSource, which is linked after the Title_ID.

SELECT
   J.Title_IDFK,
   J.ClassCode,
   D.Domain
FROM
   TDJunction AS J
      INNER JOIN Domains AS D
      ON J.ClassCode = D.ClassCode

Since this eliminates the three bottom lines, you get virtually the same view. By the way, you don't have to shoot yourself in the knee if another domain #4 and #5 would be displayed.

You can remove the monsters added to the original query.

That is easy stuff... I used it in some Title-Domain related operations.
But here are returned 3 sets of records for a title with 3 domains.
If I add the Author_Name as criteria, it will multiply the records by the number of authors per title.
Adding Inventory_No, will multiply again.
So, imagin a Book with 2 authors, 2 domains and 10 copies, each copy having its own Inventory_No.
That leads to 40 records.
If they would have to be the displayed in a report, there is no problem in doing that and using all sort of grouping
When using them in a form, it becomes a killer.

I will try to use some tricks in the concatenation function, to pack somehow the Domain and ClassCode in the same searchable item. Something like <ClassCode1 - Domain1; ClassCode2 - Domain2>.
Maybe:

SQLConcatenate("SELECT TDJunction.ClassCode & ' - ' & Domains.Domain 
                FROM TDJunction 
                INNER JOIN Domains ON TDJunction.ClassCode = Domains.ClassCode 
                WHERE TDJunction.Title_IDFK = " & [Titles].[Title_ID],", ") AS ClassCodesWithDomains,

There will never be allowed more than 3 domains, by rulle, so the initial rank field i used in the Junction table will be removed. Its purpose was to rank multiple domains, but there is no case. The records in the junction table are added in a consecutive order, so the first domain will alway have a lower ID then a 2nd or 3rd.

As I said, my mistakes are my best teachers, yet I have to do something with that gun, beacause my boots are full of bullet holes. Also, I'm 55 and I feel I am running out of time with this project, given a life span expectation of 80 and a hard smokin habbit.

 

[bastanu]

You can link the main form\subform by multiple fields separated by semicolon:

Link multiple fields from a subform

Good Afternoon to all... I am looking to link more than one field from my subform to my main form. I have one field linked by properties (link child field/link master field). Is there a simple way to link another field from my subform to my main form? Please Help??? Cori

www.access-programmers.co.uk

Cheers,

 

[ebs17]

my mistakes are my best teachers

OK, you still want to learn and play. I won't disturb you any further.

You have to kiss many frogs before you find your prince. This is an experience that everyone wants to have for themselves and anew.

 

[Romio_1968]

ebs

OK, you still want to learn and play. I won't disturb you any further.

You have to kiss many frogs before you find your prince. This is an experience that everyone wants to have for themselves and anew.

ebs17
I didn't know that is a place reserve for extra, ultra, mega, maxi professionals.
If so, please receive my apologies for disturbing the sacred silence of the Ivory Tower where only grand wizards are levitating on clouds of knowledge and wisdom, and the newbies are only allowed to worship in silent and humble obedience.

I really don't understand this kind of attitude but I am not here to learn that, anyway.
Feel free to disturb in any way that adds something I don't know, and there is a lot out there. Show me what I don't know, but keep in mind that "I know that I know nothing", so yes, don't disturb to remind me that.

I hope when you wrote this "You have to kiss many frogs before you find your prince. This is an experience that everyone wants to have for themselves and anew." you were not litteraly speaking of kissing frogs, isn't it?

Thank you for all the help up to date.

 

[Romio_1968]

You can link the main form\subform by multiple fields separated by semicolon:

Link multiple fields from a subform

Good Afternoon to all... I am looking to link more than one field from my subform to my main form. I have one field linked by properties (link child field/link master field). Is there a simple way to link another field from my subform to my main form? Please Help??? Cori

www.access-programmers.co.uk

Cheers,

Thank You.

I am changing a bit the approach. Yet, that mouse wheel stuff is quite a nuissance .
I removed the scrollbar property, so the problem does not exist anymore. (Yet, is down there, in the shadows, waiting...)

 

[Pat Hartman]

I disagree with everything you are doing but you can and should be using embedded reports rather than forms to display the concatenated data. That gives you the ability to group items in a more visually pleasing way. HOWEVER, this entire endeavor is not viable for a form where you want to update data.

 

[Romio_1968]

I disagree with everything you are doing but you can and should be using embedded reports rather than forms to display the concatenated data. That gives you the ability to group items in a more visually pleasing way. HOWEVER, this entire endeavor is not viable for a form where you want to update data.

Probably you noticed that it poped into my mind that reports are a better way to do it.
The funny part is that the topic was about mousewheel and navigation buttons. Only one pertinet answer until now.
"Don't use unbound controls on subforms"

On the other hand, assuming that I am crossing the street on red light and I notice that if i run, no bad thing happends and if I am crowling, i get smashed, the correct answer for the question "why did the car hit me when I was crawling and missed me when I was running is not <<because you were crossing on red light>>, but <<because you was moving faster while running, but anyway, is not a good thing to cross the street on red light>>"

On the other hand, I am not updading data, but searching in the data.
As collateral, i didn't know that I can embed reports in forms. That is really usefull.