Help with entering new information or changing current information

[mpnorris]

I have a new database that I could use some guidence in how to create to forms.

This database is to house a list of projects that my department is working on. I have created the following tables:

Project_Name (Project_Name_ID, Project_Name)-lookup table
Team_Leader (Team_Leader_ID, Team_Leader_Name)-lookup table
Coach (Coach_ID, Coach_Name)- lookup table
Project (Project_ID, Project_Name_ID, Team_Leader_ID, Coach_ID)- houses all of the project information

I currently have information in each of the lookup tables (Project_Name, Team_Leader, Coach) and in the main table (Project)

I want to create a form where I can enter all new information or change information on each of the projects.

My thought was using combo boxes to lookup the projects and displaying the underlying data, but if new data needs to be entered or changed I could do it in the same place.

Is this doable on one form? I tried creating a form with a combo box with the ability to enter new information but I had an error. I used the information from the FAQ on this site but it didn't work.

I wasn't sureif I would need to do subforms or not. Any help to get me started in the right direction would be greatly appreciated.

Thanks in advance for any help.

 

[hneal98]

<<My thought was using combo boxes to lookup the projects and displaying the underlying data, but if new data needs to be entered or changed I could do it in the same place.>>

Yes.

<<Is this doable on one form? I tried creating a form with a combo box with the ability to enter new information but I had an error. I used the information from the FAQ on this site but it didn't work.>>

instead of doing this, you could put a button on your form to add new item. Then when the button is clicked, put the form in ADD mode.

<<I wasn't sureif I would need to do subforms or not. Any help to get me started in the right direction would be greatly appreciated.>>

Deffinately do subforms. You need to determine which table is your main table. Your data is either one to many, many to many, or many to one. You need to determine the one to many and use the one as the main form. Hope that makes sense.

 

[mpnorris]

<instead of doing this, you could put a button on your form to add new item. Then when the button is clicked, put the form in ADD mode.>

How do you put a form or part of a form in ADD mode? I am a little confused.

<Deffinately do subforms. You need to determine which table is your main table. Your data is either one to many, many to many, or many to one. You need to determine the one to many and use the one as the main form. Hope that makes sense.>

My main table in Projects. This table references the Primary keys of each lookup table. So based on your question, my Projects table would be one to many but my lookup tables would be one to one. Am I correct here?

One of my other problems is that if there is a new project I want to add the project to two tables at the same time: Add the new name to the Project_Name table but also create a new row in the Project table so I can add the other information. (Such as Coach and Team Leader)

I have a form where I can add the new Project Name and update the Project_Name table but I do not know how to then link that to the Project table. Any suggestions?

Once I have the new project in the Project_Name table and linked to the Project Table I want to update the Coach & Leader information. I was thinking of using combo boxes to list the Coaches and Leaders that are currently in the Coach and Team Leader tables. The problem is if I need to add a new Coach or Team leader. I have seen many postings about using the NotInList function, but I am having a hard time understanding the usage.

If I understand the concept correctly you type in the combo box your information. It looks to see if it is already in the list, if not a message box opens asking if you want to add the new data. If you choose &quot;Yes&quot; a new form opens and the data is passed to the new form. What happens after that I don't understand. Do you have to hit a button that updates the table with the new data? Does it happen automatically? Why do you need to open another form, can't you just do it on the current form? Once you have the data in the combobox how to I get it to the Projects table?

I know this is alot of questions but I am hoping to start a dialog that will help me understand what I am doing. I am learning Access/VB as I go along. I have some programming experience but it seems to not be sufficient.

Any help would be greatly appreciated. There is alot of information on these posts but they can sometimes be confusing.

Thanks in advance for any help.

Mike