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
816 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

832 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