Solved

Access 2007: Command Box VBA code to pass on contents of one field in form1 to another field in form2

Posted on 2011-03-16
1
817 Views
Last Modified: 2012-05-11
Hello, I am attaching the database to this question, but perhaps it won't be needed for some of the experts here.
Basically, I have a form (frmPtCharacteristics) with many forms which describe characteristics of it (i.e. groups present in the form, disease information etc...).  I've attached an image of the database relationships here as well.  I have tried to make the form normal and relate each table to the necessary keys.  

 test
On the main form there are several boxes (Groups, Race, Ages, Disease, Site/Category) displayed horizontally.  Currently, when the user clicks one of this the current form will close and it will open the selected form.  However, I want to pass on the [Call Number] (listed as 'record number' in label).
So for example, someone clicks the Groups command button when on record number 0001 in the frmPtCharacteristics.  The frmGroup should now ONLY let the user enter information pertaining to record number 0001.  Similarly, if someone selects the Race button they should only be able to enter information about groups belonging to the parent call number ([tblPtCharacteristics.[Call Number] is related to tblGroup.[Call Number] and  tblGroup.[GroupNumber] is related to tblRaceDesc.[Group Number])
How would I go about setting these controls to the command button in VBA?


EE-example-relationships.bmp
0
Comment
Question by:Bevos
1 Comment
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35151183
there are two options in the docmd.OpenForm command line that you can use

docmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)


* Where condition

WhereCondition   Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

docmd.OpenForm "formname",,,"[Call Number]=" & ControlName

* openArgs

docmd.OpenForm "formname",openArgs:=Me.ControlName

OpenArgs   Optional Variant. A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs    argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question