Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 18th, 2007, 09:38 AM
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Synchronizing main form w/subform (i.e. backward)


I'm building an assets database and have a main form with all of the various fields of the Assets table. I also have a tabctl with three tabs, one for Maintenance, one for Notes, and one with four columns of the Assets table in datasheet view. I would like to be able to use the Assets subform datasheet view as a selector, to scroll through the table and then, when one of the records in the table is selected, have the record on the main form changed to that same record. Kind of a backward relationship between main form and subform tables.

What is the best approach to achieving this objective? Thank you.

Scott



 
Old April 18th, 2007, 10:54 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Use a continuous form instead of a datasheet, and then add a button to the detail section of the contnous form to set the recordsource of the main form the PK of the Asset in the sub form.

Why are you doing this this way? Why not just use a combo box selector on the main form (use wizard to find a record on my form)?


mmcdonal
 
Old April 18th, 2007, 11:34 AM
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use a continuous form instead of a datasheet, and then add a button to the detail section of the contnous form to set the recordsource of the main form the PK of the Asset in the sub form.

Why are you doing this this way? Why not just use a combo box selector on the main form (use wizard to find a record on my form)?

mmcdonal
========================================
The second method sounds much easier, so I think I'll take that approach. As to why not, most of my experience with MDB files is via DAO and ADO from VB6, rather than through Access itself. This job required a fairly rapid development cycle, and the user has Access 2003, so I decided to use Access directly. Thanks much for the help.

Scott

 
Old April 18th, 2007, 02:38 PM
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Message Preview
OK, I gave that a shot, but ran into a problem--the DB is fully normalized and all of the interesting bits are stored only as FKs in the Assets table (eight fields of the 20, of which 4 others are date fields). So, when I built the Combobox, all I had available were AssetID, Description, Model, and Serial Number. The user won't be able to select by Department, Division, Asset Category, etc., because all that appears in the Combobox are FKs for those fields. Is there a way around this using the Combobox method?

If not, and I need to use the continuous form method, could you give me a clue on the syntax to use (or point me to a good source) because I am more familiar with VB6 syntax than Access 2003 VBA syntax. If it helps, the main form name is Assets, the table name is also Assets (yeah, I know my naming conventions could stand a little work), the subform name is Assets subform, and of course the Assets table is used there. How should the syntax work for either a command button or the OnCurrent event of the subform to change the record selected in the main form when a new record is selected in the subform? Thanks.

Scott




 
Old April 18th, 2007, 03:18 PM
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OH, well, shoulda known. Still another issue. I recreated the Assets subform to try the continuous forms approach. Works fine as long as I'm working with it (the subform) independent of the main form. I see five records at a time in the subform, but before I went to add the command button and try coding it I looked at it in the main form. Whups, only one record at a time. It does change in lockstep with the main form record, but instead of the five I have space for there's only the active one, and the subform's record selector says "1 of 1". Hmmm, now what?

Scott


 
Old April 19th, 2007, 06:15 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you reeeeeally want all that information showing up in the combo box, then just open the query designer on the combo box and modify it by adding those tables and selecting the fields you want, then modify the column count and column widths, the same as with any other query. Bodaboom, bodabing.

mmcdonal
 
Old April 19th, 2007, 06:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

So for the continuous form issue, you do want to make sure your names are distinguishable. If you have a table and form with the same name, you need to refer to them in bang dot, like [Forms]![Assets] and [Tables]![Assets] instead of tblAsset and frmAsset (no s at the end - see set theory).

For the continuous form, just create your normal form, and then change the default view to continuous.

Then add a button on the right side of the form to open the record, and then do something like:

Dim iAssetID As Integer
Dim sDocName, sLink As String

iAssetID = Me.AssetID

sLink = "[AssetID] = " & iAssetID

sDocName = "Assets"
DoCmd.OpenForm sDocName, acNormal, , sLink

Check the syntax for the location of sLink in the WHERE clause of the DoCmd line.

Did that help any?

mmcdonal
 
Old April 19th, 2007, 06:40 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh, I forgot to mention that if the subform is opening the main form to the record, then you may have to close the main form, and then reopen it, or set the recordsource to the AssetID. I am not sure how you have this built.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Main/Subform linkage Ben009 Access 4 April 5th, 2006 01:07 PM
Moving total from Subform to Main Form Shepwad Access 1 March 10th, 2006 10:57 AM
updating main form from subform dartcoach Access 6 April 28th, 2005 08:27 AM
Passing Value from main form to Popup Form smartgir Access VBA 0 February 26th, 2004 06:26 PM
Filter subform from main form DBoulos Access VBA 2 October 30th, 2003 12:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.