Synchronised Subform Scrollbars - Is It Possible?

[DAmoss]

Hi,

I have two subforms placed on a main form (popup / modal), they each allow me to see a given record so that I can compare them side-by-side, I use this to look at possible duplicate records and merge their details into one record before getting rid of the obsolete one.

My question is:
Is there a way to synchronise the two subform vertical scrollbars, so that when I scroll one record , the other scolls the same as well?

Cheers

 

[MajP]

You can do it with this:

http://www.lebans.com/customscrollbar.htm

 

[DAmoss]

Thanks for the link to an example, but i don't want to create my own scrollbars, it doesn't show an example of how to use it with two subforms.

The example given only synchronises two scrollbars being used against one given record and not the two subform records that I am using.

 

[TheAceMan1]

How are ya DAmoss . . .

When someone refers to [purple]duplicates in their Db[/purple], its a reflection on their Db design ... and/or control thereof! If your Db design is normalized, then your still not preventing duplicates despite unique primarykeys!

Instead of pussy footing around with scroll bars (forgive my weak use of words), your focus should involve [green]automatically[/green] getting rid of these duplicates ... saving you the need to [blue]go on the hunt![/blue]

I've had to do this for several clients (taking over design). What I always came up with is a routine that runs when the Db is 1st open, getting rid of duplicates!. However you intend to iterate, merging certain fields thereof. Which can be done. The big question for you is:

[blue]What is your definition of a duplicate record?[/blue]

[ol][li]All fields match?[/li]
[li]Some fields?[/li]
[li]What? . . .[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[DAmoss]

TheAceMan1:
You're quite right in what you say, (pussy footing around forgiven) but ... in this particular case we actually do allow dupicate records to be entered into the database, strange I know.

I have multiple Projects who have quarterly returns who use the same record details with various parts filled in, depending on which part they commission, (in some cases this data can overlap, it's allowed). Sometimes it's best not assume that there is only one way of doing things.

In this particular case we do have duplicate record checking built in which checks the company name & postcode, company registration number and even the company contact, but these are all open to typo errors or wrongly supplied information, hence we do this kind of extra checking.

But at the end of the day, only one master record will exist with all this merged data contained within.

Regardless, I have now managed to show both subforms alongside each other by scrolling the main form instead, god knows why I didn't think of this in the first place!!

Cheers everyone who answered.

 

[TheAceMan1]

DAmoss . . .

Roger That! . . .

However, taking care of these duplicates hasn't been fully investigated yet! (not saying you hav'nt benn down that road yet).

For others and myself . . . again . . . [blue]how are you defining a duplicate record![/blue] ... An unknow door involving automation could open up here! ... or not!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

it doesn't show an example of how to use it with two subforms. The example given only synchronises two scrollbars being used against one given record and not the two subform records that

I modified this code for about 20 minutes and got it to scroll two synchronized subforms. I think Lebans code is the easiest approach and it works well. I know you can not do it directly in VBA and trying to do it by API is out of my league.
Anyone know of another way?
Anyway you can do it with this code and it is pretty neat to have one scroll bar moving 2 or more subforms.

Synching the scrolling is complicated, but synching the records is not. The difference would be that you would have to select a record in subform 1 and then subform 2 would move to that record (vice versa). The difference is subtle in that you have to take your mouse off of the scroll bar and click in the subform before the second form will scroll.

 

[JBinQLD]

I make an assumption here:

Both boxes must be sorted by the same criteria, otherwise there'd be no point in sync'ing the scrolling?

So why not add an AfterUpdate to master list box which changes the criteria of the other to display just the records LIKE the currently selected criteria in the master? The definition of the LIKE criteria is up to you, as AceMan has pointed out, only you are currently aware of what constitutes a likeness/duplicate

 

[MajP]

Personally when it comes to data validation, I beleive in a strategy of solutions not a single approach. A fully automated approach can be difficult to implement and no one wants a fully manual approach.

1) So like AceMan is hinting, try to automate as much as possible. For mispellings have you looked into Soundex functions?
2) Make a flexible user interface for the manual review. You defaulted to the most manual solution. I would consider a similar form with two subforms, but many buttons along the top to apply different search rules. For example if I click on a record in the first subform, I could choose different rules (queries) to populate the second subform:
1)Soundex on company name
2)All companies starting with same firstX letters
3)similar registration number. (x digits)

 

[TheAceMan1]

DAmoss . . .

Hope your still around. There's a [blue]big void with info[/blue] here. I think it would be best if you [blue]provide a decent example[/blue] of what your doing. Espcially when you say:

[blue]I use this to look at possible duplicate records and [purple]merge their details into one record[/purple] before getting rid of the obsolete one.[/blue]

I see automation here! Just need that info!

Also ... are the subforms based on [blue]different tables?[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[DAmoss]

Hi Guys,

Firstly MajP, thank you for your efforts in checking out the Lebans code! I'm glad you got it working.

Wow, I didn't think my original question would end up going off target so much (Re: Scrolling issue), but to try and fill in some more of the detail for closure:

Firstly I have two subforms on which access the same table, these two subforms are identical and contain around 50 fields in a vertical format (hence they line up along side each other) At the top of each sunform I have dropdown list box which lets me pick any record in the table (except the one in the opposite subform)

My 'Possible duplicate records' are derived from a generated report which uses various queries to hunt out our actual and possible duplicates, this report also uses LIKE criteria (hence possible duplicates).

I am unable to show you the actual data as it is covered by the Data Protection Act. There is no problem with automating the merging of the data, I can do it if it was needed, but we have a requirement for all data returns to be manually checked against their associated paper copies as well, it is part of claims process.

I hope that helps!

 

[MajP]

DAmoss,

Going back to the original question this demos the synching of scroll bars and the synching of records if still interested. The functionality is subtly different the amount of code difference is large.

http://www.4shared.com/file/57649353/6454a36e/SynchSubForms.html

 

[DAmoss]

MajP

Glad to hear from someone who sticks to the original question, thank you for your efforts!

I've now had a chance to have a look at the example via the link you supplied. I agree it's an interesting subject which in my particular case I've now solved by scrolling the two long vertical subforms in a main form.

Tank you for your help

 

[SuryaF]

The problem with your solution DAmoss is that it doesn't work with the mouse wheel, does it?
If it does, how did you do it?
I ran into the same issue but the two long subforms in a tall form is not nice because of this silent-wheel problem.
Thanks!

 

[MajP]

I think my demo of synched records pretty well with a scroll wheel. You scroll find a record then click and the forms synch.

 

[SuryaF]

Hi MajP, I saw your example.
The problem is when you don't actually have rows. Imagine two tall subforms, with a bunch of controls in them of various heights. The subforms are neither continuous forms nor datasheet, so no record is changed.
To be more clear, I'm working on a scheduler grid.
In that case you can't capture the mouse movement with the change of record. Makes sense?

 

[MajP]

I get it, I just can not imagine this being a very useful interface technique. I am sure that you can do this with API. If you really need to do this, then I would recommend posting in the windows API forum.

 

[DAmoss]

Sorayaf:
I never said that it had to work with the mouse wheel, as it is I only have to down about 3 screens worth of data fields using the scroll bar (the good old fashioned way!), which is perfectly acceptable.

Thank you all again for your previous help.
DAmoss