Use ComboBox to show and fill data in a subform

I have a table named PROJECTS that contains data related to projects, such as:

Project_ID (Primary Key)
Project_Name
Phase
Region…………and so on.

I want to:

1) Build a form with a ComboBox where the user will be able to select the 
project in this ComboBox

2) Have a subform in datasheet view where users should be able to input 
activities for that project (each project has more than one activity) in such 
a way that each new activity is automatically saved AND related to the 
project selected in the ComboBox.

A) Since I have many activities for each project

	- should I build a new table (PROJECTS_ACTIVITIES) containing all the 
projects (should I use the field Project_ID or Project_Name?) and its 
activities?

	- should I make a 1 to “infinite” relationship from table PROJECTS to table 
PROJECTS_ACTIVITIES?

	- What is the best way to make this new table? 
	(I was thinking of using a make-table query, using Project_ID instead of 
Project_Name. Then I'd use the form’s ComboBox to pick a project and input 
activities in the subform. Doing so, I would populate the projects’ 
activities.)

B) The ComboBox that selects the project shouldn’t be bound to the table, I 
just want to use it as a command to choose projects and automatically bring 
its activities

	- Should this ComboBox be populated through a query? 
	- Should it be based on PROJECT table or on PROJECT_ACTIVITIES table? 
	- And Most Important: When I pick a project in this ComboBox, does it 
automatically update the subform, relating them to the selected project?

I'm having trouble to code all of this!

Thank you for your time and assistance!
Antônio Machado.

0
Utf
6/27/2007 6:40:03 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
851 Views

Similar Articles

[PageSpeed] 17

Answers in line:

"AntonioRio" <AntonioRio@discussions.microsoft.com> wrote in message 
news:D244A22C-575A-4F07-B37D-3D3B929614B0@microsoft.com...
>I have a table named PROJECTS that contains data related to projects, such 
>as:
>
> Project_ID (Primary Key)
> Project_Name
> Phase
> Region....and so on.
>
> I want to:
>
> 1) Build a form with a ComboBox where the user will be able to select the
> project in this ComboBox
>
> 2) Have a subform in datasheet view where users should be able to input
> activities for that project (each project has more than one activity) in 
> such
> a way that each new activity is automatically saved AND related to the
> project selected in the ComboBox.
>
> A) Since I have many activities for each project
>
> - should I build a new table (PROJECTS_ACTIVITIES) containing all the
> projects (should I use the field Project_ID or Project_Name?) and its
> activities?

Yes build a new table. Use Project_ID as the foreign key.

> - should I make a 1 to "infinite" relationship from table PROJECTS to 
> table
> PROJECTS_ACTIVITIES?

Yes

> - What is the best way to make this new table?
> (I was thinking of using a make-table query, using Project_ID instead of
> Project_Name. Then I'd use the form's ComboBox to pick a project and input
> activities in the subform. Doing so, I would populate the projects'
> activities.)

Just make the table and create the subform.. You do not need to populate the 
many-side table with data. As your users enter their activities it will be 
populated.

> B) The ComboBox that selects the project shouldn't be bound to the table, 
> I
> just want to use it as a command to choose projects and automatically 
> bring
> its activities
>
> - Should this ComboBox be populated through a query?

A saved query is slightly easier, but a select statement will work just as 
well.

> - Should it be based on PROJECT table or on PROJECT_ACTIVITIES table?

The combo box is based on the Project table. It needs to fields: Project_ID 
and Project_Name

> - And Most Important: When I pick a project in this ComboBox, does it
> automatically update the subform, relating them to the selected project?

Use the unbound combo box on the main form to find the project with as many 
details as you need to see on the main form. There is a combo box wizard 
that will write the code for you. Link the Project_ID on the main form to 
Project_ID on the subform.

> I'm having trouble to code all of this!

As previously mentioned, there is a combo box wizard that will write the 
code for you.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com 


0
Arvin
6/27/2007 7:59:18 PM
I’m new to Access and this is all difficult for me. 

Here’s what I was able to do: 

1) Tables

	I have the PROJECT table with the Project_ID field and others.

	I’ve created the PROJECT ACTIVITIES table, containing Project_ID field and 
Activities field.

	PROJECT table and PROJECT ACTIVITIES table have a one-to-many relationship 
made checking the “enforce referential integrity” box.

2) Form

	Form is bound to PROJECT table

	In the form I’ve created:

	An unbound ComboBox (no control source) that uses “Row source type” equal 
to Table/Query 

	and “Row source” equal to SELECT PROJECT.Project_ID, PROJECT.Project_Name 
FROM PROJECT ORDER BY [Project_Name];

	I’ve added the PROJECT and PROJECT_ACTIVITIES tables to the query builder 
(the one to many relationship appears).

3) I’ve created a subform that has the following properties:

	Subform “source object” is “activities subform” (created using subform 
wizard, choosing PROJECT ACTIVITIES table fields and defining “show <SQL 
Statement> for each record in PROJECT”)

	Subform “link child fields” is “Project_ID”

	Subform “link master fields” is also “Project_ID”

When I click where the rulers meet on the subform datasheet view, there’s 
another property box. I've set its “record source” field to 

SELECT PROJECT_ACTIVITIES.Project_ID, PROJECT_ACTIVITIES.activities, 
FROM PROJECT INNER JOIN PROJECT_ACTIVITIES ON PROJECT.Project_ID = 
PROJECT_ACTIVITIES.Project_ID;

Still nothing happens when I pick a project in the combobox. The 
corresponding activities (I’ve filled out some on the activities table for 
testing) don’t show on the subform.

The only way something happens is when I use the record navigator on the 
bottom of the form. But that's not my purpose.

I thought that by doing all of the above, I’d have the subform values to 
update automatically when I pick a project in the combobox.

What am I missing? I can’t think of anything. 


Thank you for your help.
Antonio Machado.


"AntonioRio" wrote:

> I have a table named PROJECTS that contains data related to projects, such as:
> 
> Project_ID (Primary Key)
> Project_Name
> Phase
> Region…………and so on.
> 
> I want to:
> 
> 1) Build a form with a ComboBox where the user will be able to select the 
> project in this ComboBox
> 
> 2) Have a subform in datasheet view where users should be able to input 
> activities for that project (each project has more than one activity) in such 
> a way that each new activity is automatically saved AND related to the 
> project selected in the ComboBox.
> 
> A) Since I have many activities for each project
> 
> 	- should I build a new table (PROJECTS_ACTIVITIES) containing all the 
> projects (should I use the field Project_ID or Project_Name?) and its 
> activities?
> 
> 	- should I make a 1 to “infinite” relationship from table PROJECTS to table 
> PROJECTS_ACTIVITIES?
> 
> 	- What is the best way to make this new table? 
> 	(I was thinking of using a make-table query, using Project_ID instead of 
> Project_Name. Then I'd use the form’s ComboBox to pick a project and input 
> activities in the subform. Doing so, I would populate the projects’ 
> activities.)
> 
> B) The ComboBox that selects the project shouldn’t be bound to the table, I 
> just want to use it as a command to choose projects and automatically bring 
> its activities
> 
> 	- Should this ComboBox be populated through a query? 
> 	- Should it be based on PROJECT table or on PROJECT_ACTIVITIES table? 
> 	- And Most Important: When I pick a project in this ComboBox, does it 
> automatically update the subform, relating them to the selected project?
> 
> I'm having trouble to code all of this!
> 
> Thank you for your time and assistance!
> Antônio Machado.
> 
0
Utf
7/2/2007 4:52:02 PM
Reply:

Similar Artilces:

detect a filled out cell
Hi, I would like to check a column. If a cell has text, i want to increase a counter. could it be done using SUMIF ? something like SUMIF(Page1!A:A;cell <>"";total=total+1) i don't want to use VBA thanks, Alain Hi Alain, =COUNTIF(A:A,"<>") It will count all non-empty cells, also numbers -- Kind Regards, Niek Otten Microsoft MVP - Excel "Alain R." <no.valid@email.com> wrote in message news:uhSHao02DHA.3468@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to check a column. > If a cell has text, i want to increase a coun...

Cannot show, import, export userform: error &H80004005 / component is not corretly installed
Please, help me! I can not find where is the the information I need. My problem is: I had Windows 2000 and Office 2000. Now they installed Windows XP and Office 2000 (serv.pack 3). And my forms do not work anymore! The error message says nothing useful, only: 1) the component is not corretly installed (ok! but it's a common form with only normal windows common buttons and labels, not a single userparty creative control) 2) error: &H80004005 -2147467259 3) memory insuficient (ahah, at 512 Mb, only Excel running... ) 4) can not set OleObjectBlob property But if I try to open the...

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Browse for Drive/Dir/File using MFC
Is it possible, in an MFC app, to use one of the standard dialog-based classes/functions to browse for either a drive OR a directory OR a file? Any help appreciated, Steve. Take a look at the SHBrowseForFolder() API. -- Cheers Check Abdoul [ VC++ MVP ] ----------------------------------- "Steve Carroll" <ctrl@optusnet.com.au> wrote in message news:#tR$bmdfDHA.696@TK2MSFTNGP09.phx.gbl... > Is it possible, in an MFC app, to use one of the standard dialog-based > classes/functions to browse for either a drive OR a directory OR a file? > > Any help appreciated...

Adding an item to a popup menu in outlook's inbox using VBA
Hello all, On rightclicking on the mailitems in the outlook's inbox we can see a popup menu.i want to add a menu item to that popup menu using outlook VBA. Please do help me in this regard. Thanking you regards, Revathy.v ...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Filling Web Form
Dear freinds Hello, I have written code in vc++ using MFC to fill the Webform using HTTP post method and I'm able to fill the form but I'm facing a problem that I'm unable to fill login name and password. So any one amongst you have any Idea or help then please help me With Regards Sachin ...

Fill Down Excluding First Row
Greetings, I have a spreadsheet where the first row (1) consists of labels that describe the column located below each label. For example, I have calories, fat, carbs, etc. It is desirable to have some of these columns filled in automatically. For example one row might be "calories from fat," "calories from carbs," etc, and so I'm doing a "fill down" on those columns. Unfortunately, the fill downs are completing the columns with the labels. So I'm wondering if it's possible to: (a) Exclude the label row (row 1) from the fill down or, better yet;...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

Adding extra data options
Is there a way to customize CRM to allow for adding another heading? I would like to add a second field similar to topic and would like to call it type. Can you add extra data fileds and types in CRM 3.0? You can add extra data fields to an entity. Go to entities customization at setting area. -- Marco Amoedo Plain Concepts http://geeks.ms/blogs/marco/ "xxdcmast" escribió: > Is there a way to customize CRM to allow for adding another heading? I would > like to add a second field similar to topic and would like to call it type. > > Can you add extra data ...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...