Hi Everyone,

Below code comes from my Master Form AddProject(using table Project), the data show up correct after I choose IndexCode. But none of them can be saved in my Project table.

Here is my code:
*****
Private Sub IndexCode_AfterUpdate()

Me.IndexTitle.RowSource = "select IndexTitle from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.SectionDirector.RowSource = "select [Section Director] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramManager.RowSource = "select [Program Manager] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramAdminAsst.RowSource = "select [Program Admin Asst] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)

End Sub
*******
What should I do to save the records?

******* On this Master form, also has some data entry fields, and these fields data can be saved. And also, the data in the Subform can be saved too.
******* I even added a SAVE BUTTON in the Master form, but it still can not save these fields data.******
******* I am using "RowSource" on my code because these fields are list box. Is that the problem? I can change fields from list box to text box, but I don't know what object I should put in the code to replace "RowSource". I tried "ControlSource" or "Text", but it didn't work.

Please help me.........

Thanks,
Cindy

You just need to bind the controls to the underlying fields in the Project table using the ControlSource property. For example set up a [Section Director] field in the Projects table and set the SectionDirector ListBox control's ControlSource to [Section Director].

Tip, where possible avoid spaces in field names.

HTH.

Thanks, HTH, but it didn't work.

All the fields on the FORM have their data field attached on the control source, but the fields on my code for this FORM are little bit different, and on the Row Source Type, I have to use Table/Query, and on Row Source, I have to use SELECT statement. When the user selects a IndexCode, these fields will auto show up the items which relates with that IndexCode, and these fields do not need the user do data entry. Here is my code:
*****
Private Sub IndexCode_AfterUpdate()

Me.IndexTitle.RowSource = "select IndexTitle from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.SectionDirector.RowSource = "select [Section Director] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramManager.RowSource = "select [Program Manager] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramAdminAsst.RowSource = "select [Program Admin Asst] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)

End Sub
*******

Rest the fields on this FORM need the user do data entry, and they can be saved on the Project table; but just the fields which on my code can not be saved.

Thank you very much, please help me..........

Cindy

Cindy,

To make sure we understand what you are describing it would help if you use the terminology for Access.

Fields are in Tables and queries results.

Controls are object on forms and reports. There are not any Fields on a form and report. Some controls on forms and reports can be bound top fields int he forms/reports record source.

I do not understand what you are trying to do.

The best I can tell is that you have at least two tables. A projects table and a lookup table [Index Codes, Titles].

Please post the table structure (fields, primary keys, foreign keys, etc) for each table involved.

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Thank you so much, HiTechCoach.
1. My “Projects” table structure: Primary Key is: ID
Field Name Data Type
ID AutoNumber
IndexCode Text
IndexTitle Text
ProjectName Text
Program Manager Text
Program Admin Asst Text
Objective Text
Justification Text
Notes Text
Project Manager Text
Office Director Text
Section Director Text
Program Coordinator Text
GrantProject Text
FY Number
2. FORM AddProject (Master Form)--- Record Source is using table “Projects”
3. SubForm ProjectDetails, and it uses “Project Details” table.
“Project Details” table: Primary Key is ID, data type is Number.
4. “Index Codes, Titles” table. It is a Lookup table; On Projects table, IndexCode, IndexTitle use it for a lookup; and also on the FORM AddProject, Section Director, Project Manager, Program Admin Asst use it for the Row source (select statement).

So, on the Master FORM Projects:
Part1. part of information come from “Index Codes, Titles” table: When the user selects a IndexCode, these fields will auto show up the information which relates with that IndexCode.
**
Private Sub IndexCode_AfterUpdate()
Me.IndexTitle.RowSource = "select IndexTitle from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.SectionDirector.RowSource = "select [Section Director] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramManager.RowSource = "select [Program Manager] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
Me.ProgramAdminAsst.RowSource = "select [Program Admin Asst] from [Index Codes, Titles] where IndexCode=" & Chr(34) & Me.IndexCode & Chr(34)
End Sub
**
These fields are List box in FORM AddProject, they should use Text box. But I don’t know what I should use to replace "RowSource" on the code if I use Text box for them.

Part2. And rest of information come from users’ data entry.
Part3. All of information in the FORM should be saved in “Projects” table. But right now, only users’ data entry information (Part2) can be saved in the “Projects” table.

Thanks,
Cindy

Hi Cindy,

You need to create an autolookup query using your projects and indexcodestitles tables.

In the query include the IndexCode field from the Projects table and all other fields required for this form plus the fields from the IndexCodesTitles you want to 'fill' in automatically.

On the form have the IndexCode control as a combobox with records from your IndexCodesTitles table with IndexCode bound to the control. No need to have combo boxes for sectiondirector etc.

The sectiondirector, programmanager and programadminasst controls should be bound to the fields from IndexCodesTitles table within the query above. Also create hidden controls bound to the corresponding fields from the Projects table within the above query.

When you select an IndexCode the three fields above will be automatically filled in.

To ensure the data is saved into the Projects table you need to set up a little bit of code in the Form's BeforeUpdate event to set the hidden controls value to the values in the controls that are automatically filled in. For example:
Me.hiddenSectionDirectorTextBox = Me.sectionDirectorTextBox.

BTW (by the way) HTH means Hope That Helps, my name is Malcolm.

Cindy,

I do think part of your issues are caused by the fact that the Projects table really is not properly normalized.

You have a lot of repeating fields for the project team members. These rally should be in a child table. This would be on the many side of a one-to-many relationship with projects.

A design that follows the rules of data normalization would look something like this:

Projects table
ProjectsMembers table
MemberRoles - Lookup Table
The MemberRoles table would contain records like this:
1 - Program Manager
2 - Program Admin Asst
3 - Project Manager
4 - Office Director
5 - Section Director
6 - Program Coordinator

People table

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Hello HiTechCoach,
Thank you so much for your suggestion, but I can not redesign or change this database. Because:
This database has been used for many years and was designed by someone (consultant, many years ago before I came in), and he only gave us mde file, not mdb. As you know we can not use mde file to update to Access 2007, so I have to re-create this database, then update it to 2007. And also, many tables data in this database come from another Oracle application. Every month, the group people who use this database do updating tables. So, I can not do any design change, have to make the new one exactly like the current one.

Now, (thanks everyone, specially you)I almost get there, only 2 things left:
1. few fields data can not be saved in the projects table;
2. the error message for the SubForm --ProjectDetails “The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

Thanks,
Cindy

If you are recreating the database then this is a perfect time to improve the design!

Do you have the legal rights to reverse engineer this database since you only have the MDE?

Have you tried contacting the person who created the MDE to see if you can get the MDB?

Since you do not want to redesign the database but only upgrade to 2007, it will probably be a whole lot cheaper in both time and money to just purchase the MDB from the original developer.

Curious, will the MDE run in Access 2007?

__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Hello HiTechCoach,
That group boss has been changed before I came in, and no one knows that consultant's contact info....... the current database MDE only working on Access 2003.
I think MDE file can run on Access 2007, but it has to be come from 2007 mdb. If we have the original mdb file, I can update it to 2007 mdb first, then from there, save as lock down file mde.

So, for my two problems, what should I do to let this database work?
1. few fields data can not be saved in the projects table;
2. the error message for the SubForm --ProjectDetails “The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.”

Thank you very much,
Cindy