VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a form to assign employees to different projects. I
have 2 list boxes. The first is available employees and the second is
assigned employees. I built append and delete queries to manage the
appropriate movement between the 2 lists. When I click on the button to add
an employee from the available list to assign to a project, the whole list of
available employees dissappears, and they do not show up on the assigned
list. HELP!
 
hi,
I am trying to build a form to assign employees to different projects. I
have 2 list boxes. The first is available employees and the second is
assigned employees. I built append and delete queries to manage the
appropriate movement between the 2 lists. When I click on the button to add
an employee from the available list to assign to a project, the whole list of
available employees dissappears, and they do not show up on the assigned
list.
Click to expand...
I normaly use two sub forms based on the following queries, the main
form is based on the project table, but you can do this also with ListBoxes:

Available employees:

SELECT *
FROM Employee
WHERE NOT ID IN (
SELECT Emplyee_ID
FROM Employee_Project
WHERE Project_ID = [Forms]![mainform]![ID]
)

Assigned employees:

SELECT E.*
FROM Employee E
INNER JOIN Employee_Project P
ON E.ID = EP.Employee_ID
WHERE EP.Project_ID = [Forms]![mainform]![ID]

Two buttons on the main form:

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO Employee_Project " & _
"(Employee_ID, Project_ID) VALUES (" & _
sfrmAvailable.Form![ID] & ", " & Me![ID] & ")"

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub

Private Sub cmdUnassign_Click()

CurrentDb.Execute "DELETE FROM Employee_Project " & _
"WHERE Employee_ID = " & sfrmAssigned.Form![ID] & " " & _
"AND Project_ID = " & Me![ID]

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub


mfG
--> stefan <--
 
Stefan, your code was a great help, I just need some assistance with the
second half of it! Here is what I am using for first part so you will be
able to see if I am on track:

Avail Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee
FROM qryEmpDirectory
WHERE (((qryEmpDirectory.[Emp Id]) Not In (SELECT EmpId FROM
tblAssignedTransitionClass)));

Assigned Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee,
tblAssignedTransitionClass.TransitionClassID
FROM qryEmpDirectory INNER JOIN tblAssignedTransitionClass ON
qryEmpDirectory.[Emp Id] = tblAssignedTransitionClass.EmpId
WHERE
(((tblAssignedTransitionClass.TransitionClassID)=[Forms]![frmEmpDirectory]![cboClassList]));


Thank you inadvance for your help!

Stefan Hoffmann said:
hi,
I am trying to build a form to assign employees to different projects. I
have 2 list boxes. The first is available employees and the second is
assigned employees. I built append and delete queries to manage the
appropriate movement between the 2 lists. When I click on the button to add
an employee from the available list to assign to a project, the whole list of
available employees dissappears, and they do not show up on the assigned
list.
Click to expand...
I normaly use two sub forms based on the following queries, the main
form is based on the project table, but you can do this also with ListBoxes:

Available employees:

SELECT *
FROM Employee
WHERE NOT ID IN (
SELECT Emplyee_ID
FROM Employee_Project
WHERE Project_ID = [Forms]![mainform]![ID]
)

Assigned employees:

SELECT E.*
FROM Employee E
INNER JOIN Employee_Project P
ON E.ID = EP.Employee_ID
WHERE EP.Project_ID = [Forms]![mainform]![ID]

Two buttons on the main form:

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO Employee_Project " & _
"(Employee_ID, Project_ID) VALUES (" & _
sfrmAvailable.Form![ID] & ", " & Me![ID] & ")"

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub

Private Sub cmdUnassign_Click()

CurrentDb.Execute "DELETE FROM Employee_Project " & _
"WHERE Employee_ID = " & sfrmAssigned.Form![ID] & " " & _
"AND Project_ID = " & Me![ID]

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub


mfG
--> stefan <--
Click to expand...
 
hi,
Stefan, your code was a great help, I just need some assistance with the
second half of it! Here is what I am using for first part so you will be
able to see if I am on track:

Avail Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee
FROM qryEmpDirectory
WHERE (((qryEmpDirectory.[Emp Id]) Not In (SELECT EmpId FROM
tblAssignedTransitionClass)));

Assigned Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee,
tblAssignedTransitionClass.TransitionClassID
FROM qryEmpDirectory INNER JOIN tblAssignedTransitionClass ON
qryEmpDirectory.[Emp Id] = tblAssignedTransitionClass.EmpId
WHERE
(((tblAssignedTransitionClass.TransitionClassID)=[Forms]![frmEmpDirectory]![cboClassList]));
Click to expand...
Both queries seem to be okay.


mfG
--> stefan <--
 
Thank you, but I cannot get the buttons to work. Based on my queries, how
would I code the buttons?

Stefan Hoffmann said:
hi,
Stefan, your code was a great help, I just need some assistance with the
second half of it! Here is what I am using for first part so you will be
able to see if I am on track:

Avail Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee
FROM qryEmpDirectory
WHERE (((qryEmpDirectory.[Emp Id]) Not In (SELECT EmpId FROM
tblAssignedTransitionClass)));

Assigned Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee,
tblAssignedTransitionClass.TransitionClassID
FROM qryEmpDirectory INNER JOIN tblAssignedTransitionClass ON
qryEmpDirectory.[Emp Id] = tblAssignedTransitionClass.EmpId
WHERE
(((tblAssignedTransitionClass.TransitionClassID)=[Forms]![frmEmpDirectory]![cboClassList]));
Click to expand...
Both queries seem to be okay.


mfG
--> stefan <--
Click to expand...
 
hi,
Thank you, but I cannot get the buttons to work. Based on my queries, how
would I code the buttons?
Click to expand...
Try

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO tblAssignedTransitionClass " & _
"(EmpId, TransitionClassID) VALUES (" & _
sfrmAvailable.Form![ID] & ", " & Me![ID] & ")"

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub


Avail Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee
FROM qryEmpDirectory
WHERE (((qryEmpDirectory.[Emp Id]) Not In (SELECT EmpId FROM
tblAssignedTransitionClass)));

Assigned Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee,
tblAssignedTransitionClass.TransitionClassID
FROM qryEmpDirectory INNER JOIN tblAssignedTransitionClass ON
qryEmpDirectory.[Emp Id] = tblAssignedTransitionClass.EmpId
WHERE
(((tblAssignedTransitionClass.TransitionClassID)=[Forms]![frmEmpDirectory]![cboClassList]));
Click to expand...
Both queries seem to be okay.
Click to expand...
Click to expand...


mfG
--> stefan <--
 
Hi,
It is giving me the error message that the variable is not defined. Where
you have "sfrmAvailable", I replaced it with " qryEmpDirectory.[Emp Id]"
because I do not have that object in my dbase. I am using list boxes
instead of forms. What am I doing wrong? Here is what I wrote:

Private Sub cmdAddtoList_Click()
CurrentDb.Execute "INSERT INTO tblAssignedTransitionClass " & _
"(EmpId, TransitionClassID) VALUES (" & _
qryEmpDirectory.[EmpId] & ", " & Me![ID] & ")"
Me.lstSelectEmp.Requery (this would be your form available)
Me.lstTransitionClass.Requery (this would be your form assigned)
End Sub



Stefan Hoffmann said:
hi,
Thank you, but I cannot get the buttons to work. Based on my queries, how
would I code the buttons?
Click to expand...
Try

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO tblAssignedTransitionClass " & _
"(EmpId, TransitionClassID) VALUES (" & _
sfrmAvailable.Form![ID] & ", " & Me![ID] & ")"

sfrmAvailable.Requery
sfrmAssigned.Requery

End Sub


Avail Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee
FROM qryEmpDirectory
WHERE (((qryEmpDirectory.[Emp Id]) Not In (SELECT EmpId FROM
tblAssignedTransitionClass)));

Assigned Employees

SELECT qryEmpDirectory.[Emp Id], qryEmpDirectory.Employee,
tblAssignedTransitionClass.TransitionClassID
FROM qryEmpDirectory INNER JOIN tblAssignedTransitionClass ON
qryEmpDirectory.[Emp Id] = tblAssignedTransitionClass.EmpId
WHERE
(((tblAssignedTransitionClass.TransitionClassID)=[Forms]![frmEmpDirectory]![cboClassList]));
Both queries seem to be okay.
Click to expand...
Click to expand...


mfG
--> stefan <--
Click to expand...
 
hi,
It is giving me the error message that the variable is not defined. Where
you have "sfrmAvailable", I replaced it with " qryEmpDirectory.[Emp Id]"
because I do not have that object in my dbase.
Click to expand...
This is the subform control.
I am using list boxes instead of forms. What am I doing wrong? Here is what I wrote:
Click to expand...
You must reference your listbox, e.g. & lstSelectEmp.Column(0) (you need
the column index of the identity value).



mfG
--> stefan <--
 
I am sorry to keep imposing! I am new to VBA and just took a class! The
class did a project doing this same task using the docmd.openquery functions.
So, I am really lost as to what you are suggesting. Where would I insert
that code...

Stefan Hoffmann said:
hi,
It is giving me the error message that the variable is not defined. Where
you have "sfrmAvailable", I replaced it with " qryEmpDirectory.[Emp Id]"
because I do not have that object in my dbase.
Click to expand...
This is the subform control.
I am using list boxes instead of forms. What am I doing wrong? Here is what I wrote:
Click to expand...
You must reference your listbox, e.g. & lstSelectEmp.Column(0) (you need
the column index of the identity value).



mfG
--> stefan <--
Click to expand...
 
hi,
I am sorry to keep imposing! I am new to VBA and just took a class! The
class did a project doing this same task using the docmd.openquery functions.
So, I am really lost as to what you are suggesting. Where would I insert
that code...
Click to expand...
Take a look at

http://ste5an.de/db2.zip

mfG
--> stefan <--
 
THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!
THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!
THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!
THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!
THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!
 
Greetings...

I am almost there!! Everything is working great! EXCEPT, the remove from
list button. The code I used is listed below. I get an error message that
says, data type mismatch in criteria expression. I cannot figure it out!

Private Sub cmdRemovefromList_Click()
CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
"WHERE EmpId=" & lstTransitionClass.Column(0) & " AND
tranclassID = " & cboClassList.Column(0)

lstSelectEmp.Requery
lstTransitionClass.Requery


End Sub
 
I am almost there!! Everything is working great! EXCEPT, the remove from
list button. The code I used is listed below. I get an error message that
says, data type mismatch in criteria expression. I cannot figure it out!

Private Sub cmdRemovefromList_Click()
CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
"WHERE EmpId=" & lstTransitionClass.Column(0) & " AND
tranclassID = " & cboClassList.Column(0)

lstSelectEmp.Requery
lstTransitionClass.Requery
Click to expand...

are either EmpID or tranclassID Text fields? If so, you need the syntactically
required quotemark (either ' or " will do) delimiters. Assuming that
tranclassID is a Text field rather than a Number field, try

CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
"WHERE EmpId=" & lstTransitionClass.Column(0) & _
" AND tranclassID = '" & cboClassList.Column(0) & "'"

For clarity, that has a ' mark after the = sign, and a ' mark between two "
marks at the end.

John W. Vinson [MVP]
 
This is what I tried, however I think I did it wrong:
CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
""WHERE EmpId = "" & lstTransitionClass.Column(0) & "
AND tranclassID = " & cboClassList.Column(0)
 
This is what I tried, however I think I did it wrong:
Click to expand...

You need quotemarks both before and after the text value, but not before the
WHERE; and you need to use either a singlequote ' within the string, or TWO
doublequotes within the doublequote delimited string.

CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
"WHERE EmpId = '" & lstTransitionClass.Column(0) & _
"' AND tranclassID = " & cboClassList.Column(0)

Spaced out for readability, that's

"WHERE EmpId = ' " & lstTransitionClass.Column(0) & _
" ' AND tranclassID = " & cboClassList.Column(0)

Don't include the extra spaces in your real query though! If EmpID is, say,
A1345 and the class list value is 8, this will translate to

WHERE EmpID = 'A1345' AND tranclassID = 8


John W. Vinson [MVP]
 
Thank you so much!

John W. Vinson said:
You need quotemarks both before and after the text value, but not before the
WHERE; and you need to use either a singlequote ' within the string, or TWO
doublequotes within the doublequote delimited string.

CurrentDb.Execute "DELETE FROM tblAssignedTransitionClass " & _
"WHERE EmpId = '" & lstTransitionClass.Column(0) & _
"' AND tranclassID = " & cboClassList.Column(0)

Spaced out for readability, that's

"WHERE EmpId = ' " & lstTransitionClass.Column(0) & _
" ' AND tranclassID = " & cboClassList.Column(0)

Don't include the extra spaces in your real query though! If EmpID is, say,
A1345 and the class list value is 8, this will translate to

WHERE EmpID = 'A1345' AND tranclassID = 8


John W. Vinson [MVP]
Click to expand...
 
Greetings...

I am getting a complie syntex error with the code listed below! Can someone
please help me with it?

Thanks!
 
Back
Top