Need to add a new record

[Randomblink]

Ok...

I have Three tables, I am going to give an example and hope someone can help.


Table #1: [tbl_Employee]
Table #2: [tbl_LINK_EmployeeProject]
Table #3: [tbl_Project]

Ok, in Table #1, I have three fields.
#1) Employee_ID (This is an autonumber field, primary key)
#2) Employee_Name
#3) Employee_Department

Ok, in Table #2, I have two fields.
#1) Employee_ID (This is an Integer field, primary key)
#2) Project_ID (This is an Integer field, primary key)
In Table #2, I set them both as Primary keys by highlighting both fields and clicking on the key icon in Access.

Ok, in Table #3, I have many fields, but only two are important.
#1) Project_ID (This is an autonumber field, primary key)
#2) Project_Title

I have gone into the relationship area and linked the following fields.

[tbl_Project].[Project_ID] connected-to [tbl_LINK_EmployeeProject].[Project_ID]
AND
[tbl_Employee].[Employee_ID] connected-to [tbl_LINK_EmployeeProject].[Employee_ID]

Ok...
I create a form to input new Projects. What I want to do is:

I have a drop-down/combo-box: [cmb_AssignEmployee] that looks up values from [tbl_Employee]. What I want to do is select an employee with this box, upon selecting an employee it will then create a new record in [tbl_LINK_EmployeeProject] where the [tbl_LINK_EmployeeProject].[Project_ID] field will equal the [tbl_Project].[Project_ID] AND where the [tbl_LINK_EmployeeProject].[Employee_ID] field is equal to the selection I just made from the combo box.

Does this make sense?

Basically, I want to link several Employees to each Project. Several Employees can be on ONE Project AND several Projects can be handled by ONE Employee.

Other than manually inputting directly into the table I don't know what to do. And the BIGGEST problem is, this Project is linked the same way to many other tables in the exact same way.

Now then, I have tried...

Private Sub cmb_AssignEngineer_AfterUpdate()
Dim rst As Recordset
rst = CurrentDb.OpenRecordSet ("tbl_LINK_EmployeeProject")
With rst
.AddNew
!Project_ID = Project_ID
!Employee_ID = cmb_AssignEngineer
.Update
End With
rst.Close
End Sub

My thinking being that the rst=Curr.... line would open the correct table. Then the add new would add the Project_ID from the current Project open in my form.

Argh!

I am confused. If someone can help me out, I can even upload my database Front & Back end for look-see. Thanks

 

[David R]

Check your original posting...

You've now got your posting in three different topics, Randomblink. Give someone a chance to answer before you post it anew (as I've done in your original thread: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=29754 )

 

[Randomblink]

3 Different Locations - One Great Taste

I did check it.
The thread you sent me to had no answer to my question.
I posted there then realized that that was probably a dead thread and I should move it to another position.

So yes, it is in three locations, but only one is gonna get noticed. And that is the one where it is it's own post. No one is gonna check the other two excepting moderators and they don't always have time to answer questions.

So, I am sorry if I am being bothersome, I am just trying to learn tho, and being the only person where I work who even comprehends Access, this is the only place I can ask questions.

 

[David R]

I'm sorry you felt I was no help

But I did give you a possible solution in your other thread. Since a thread jumps to the top of the list whenever anyone replies to it, it is far from a 'dead' thread; can you go there and tell me what is wrong/didn't work with what I suggested to you?

I've done what I suggested on half a dozen databases in the last year. It does work, unless I'm not explaining something correctly.

 

[Randomblink]

No no no...I appreciate your help!

I was just 'trying' to say that my question has two parts.

Part one you took care of...
I'm sorry I didn't acknowledge that in my post...
But it was the question, "How to assign employees to projects?"
You gave me the answer on that one!

MANY MANY THANKS!

As for the second part...
How do I create a new record from a button or using code...
That wasn't even addressed...

I wasn't upset man...

I was trying to convey a frustration!



I'm just highly impatient. I wish I had someone that lived in town that I could just absorb from. I feel like I could be doing so much if I could just learn. But with financial stress like you wouldn't believe, I am 'forced' to rely onthe kindness of others...grrrrr. And on this board, I get EXCELLENT assistance, just never as fast as I would like it...(obviously faster than I can afford...<sick chuckle>...)...so I am sorry if I came across rudely.

also...<ahem> I didn't, uh, know that it puts it at the top of the forum list.

Now I do...sorry!

I wouldn't have posted so many times.

I always noticed that after I posted a reply I would get replies again and just thought that was because moderators got a special message or something.

So...
Thanks for all the fish man...
I truly appreciate your help...
I am a little impatient...
I apologize if I came across as rude...
Not my intention my friend...

Now then...
Can someone guide me through VB code that will allow me to create a new record in a linked table in a front-end database that will be replicated for distribution to several different departments...?

Danka Shane in advance

 

[David R]

It's all good

I didn't realize you'd gotten half of your answer. Let's see if we can tackle the second part.

A new record - do you mean a brand new record as in a new Project (assuming that's your Main Form), or a new subform record to add another employee?

There's a couple ways, and they should apply more or less to either case. The simplest way is to turn on the Navigation Buttons in your Form's Properties (I think it's under Format). This will turn on the little '[|<] [<] [100] [>] [>|] [>*] of 123456' bar at the bottom of your form. The last one, [>*], will take you to a new record.

However I've found that putting that on both my main form and my subform confuses my users, because they often stack right on top of each other and non-computer geeks have a hard time telling which one goes to which record. So you can make your own buttons which do the same things. If you start a new command button with the Wizard on, it will ask what you want the button to do. Under Record Navigation and Record Operations are the functions to create custom buttons related to those above.
Okay that was unclear. When you start a new command button with the wizard on, it asks you what you want to do. Under "Record Navigation" are the commands for Go to Previous, Go to Last, etc. Under "Record Operations" is the elusive "Add New Record." As you'll see if you look a tthe button code the operative line of code here is this: DoCmd.GoToRecord , , acNewRec

All of this, of course, is dependent on using normal updateable queries behind your forms. If the last button is greyed out, there's a problem and your query isn't updateable, which may be why you're running into troubles in the first place.

Hopefully that helps. I went to school and college in Oklahoma, so I understand about the financial and education system there! I'd recommend the book I used to learn Access (in addition to a truly absurd amount of time reading posts here): VUE's Special Edition Using Microsoft Access <version>. The Dummy book on Access is not for dummies, either; it's a good start, and my non-computers coworker used it to pretty good effect to learn how to use Access (not code in it, but use it).

 

[Randomblink]

<phew!>

Okay...

I guess I wasn't clear on my question...<whoops!>

Ok, here is my question.

What I want to do is:

Select an option from a drop down or combo-box
By selecting from the combo-box I want to create a new record in a table that is NOT being accessed by the form I am using.

Let me try to explain why.

On my Project Form, I have a listbox. This listbox populates itself from a simple SQL statement showing all Employees that are linked to that Project.

I have a drop-down box that is populated by ALL employee in the employee table.

I want to open up Project XYZ.
The listbox will show the current Employee's assigned to that Project.
I want to click on the combo-box and select Employee Joe Schmoe.
After selecting Joe Schmoe here is what I want to happen:

I want to take the Proj_ID from the current project I am working on.
I ALSO want to take the Employee_ID from the combo-box that I just selected Joe Schmoe from, so in other words I want to take HIS Employee_ID.
And I want to create a new record.
And I want it to put the Project_ID (from what I just explained) and the Employee_ID (Joe Schmoe's) in a new record, the new record needs to be in the LINK table.
Then I want to requery the listbox that shows you the current Employees assigned to the current Project (remember? XYZ) in my form, in other words, it would show you that Joe Schmoe is an Employee assigned to the Project.

Does this make sense?

If I could do this...from a SQL statement, VB statement, etc,...it would be like a dream come true. This would allow me to link items in other tables which would make this database a beautiful thing.

Thanks in advance.

(I have tried several books, even took a class...I have yet to learn anything that I hadn't figured out from the Help Files in Access.)

 

[David R]

Two ways to skin this cat...

-> I'm going to give you the easy way first, although it's not what you asked for, because it requires no coding.

Build your main form based on tbl_Project, and a subform based on tbl_LINK_EmployeeProject. On the subform (which should be Continuous), place a combo box. Use the wizard. The Row Source should be tbl_Employees, but it should store its value in the LINK table's Employee_ID field. Insert this subform on your main form.

That's it. Now you can choose from the dropdown as many employees as you need for a project, and remove them by clicking on the Record Selector (the Grey > to the left edge) and clicking on Delete Record. Or make the double-click event do it, or whatever you want your users to do.

-> Now for the hard way, which admittedly was an interesting project, and I learned something along the way.

Your Form is still based on tbl_Project. Make sure the Project_ID field is on the form, though it can be invisible. Add a list box to the form, using the wizard initially. The list box should be based on the LINK table, but don't bind it to anything; it's going to be a display only feature. Lock and Disable the listbox to prevent people trying to change it directly. Click on the Listbox and go to Properties>Data>Row Source. Click the [...] box at the end of the row to get into a query building window. Now you need to add tbl_Employees to the listbox, and remove the Employee_ID field from display. Instead put in the Employee_Name fields from tbl_Employees (possibly as FirstName + " " + LastName). Now under the criteria of Project_ID, put this: [Forms]![formName]![Project_ID]
Save these changes to the Row Source and click the small grey box in the top left of your form to get to Form Properties. Go to the Current event. In the Code Builder put this: Me.listboxName.Requery
Now you've got to add your combo box for new additions. Place an unbound combo box on your form, based on tbl_Employees. Display their name, hide the bound column (Employee_ID). Then put a command button on the form. Don't have it do anything on the wizard's list, you'll have to code it yourself. Put this behind the Click event of your button (changing field names as appropriate, of course):

Private Sub commandButtonName_Click()
    If IsNull(Me.comboEmployees) Then Exit Sub
    If DCount(Me.comboEmployees, "tbl_LINK_EmployeeProjects", "Project_ID = " & _
Me.Project_ID & " AND Employee_ID = " & Me.comboEmployees) Then Exit Sub
    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "appendEmployeeToProject"
        DoCmd.SetWarnings True
    
    Me.listboxName.Requery

End Sub

Now what, you may ask, is the query "appendEmployeeToProject"? Well we have to build that too. In SQL form it will look something like this:

INSERT INTO tbl_LINK_EmployeeProjects ( Employee_ID, Project_ID )
SELECT tbl_Employees.Employee_ID, tbl_Projects.Project_ID
FROM tbl_Employees, tbl_Projects
WHERE (tbl_Employees.Employee_ID)=[Forms]![formName]![comboEmployees]) AND _
(tbl_Projects.Project_ID)=[Forms]![formName]![Project_ID]);

You could also make a delete query and add a second button/make the second IF statement run the Delete query. You'll need a way to remove employees from a project as well, I'm assuming.

I'm not trying to scare you off, just showing you two different ways to do it. Hopefully one will work for you.

 

[Randomblink]

Gotta at least ask ya here

Ok...
I have already made changes to my database where the particular instance of creating that entry is now not necessary...

BUT...
I still have plenty of other related tables that will need this...
So, it is invaluable to say the least.

What I want to know is:

WHERE DID YOU GET THIS? You said you had to learn it along the way as well...WHERE DID YOU LEARN IT?!

Enquiring Minds like mine wanna know...

 

[David R]

2000 lessons and counting...

No seriously. I've spent an absolutely absurd amount of time here. But I've also learned more in reading posts by Pat Hartman, Fornatian, Talismanic, Jack Cowley, and others, than I ever learned in any class. And the archives are invaluable for code snippets. At least half of what I post is simply links to previous answers to the same question.

Learning to use the archives effectively was the biggest help. Getting a good reference book for my desk was good as well. And then, just having several 'experimental' databases where I can poke, prod, and disintegrate things until I understand how they work...more or less. I've been working with Access less than a year; I'm still learning a lot.