gracie1972
asked on
Trying to add a combo and list box that pulls from seperate tables and updates a table
Table 1 - Employee Data
Table 2 - Skills Data
Table 3 - Resources
Objective is:
Step 1: Pull employee information from Table 1 by Employee ID #. This Combo box will auto populate my form while updating Table 3 on the back end. >> This works with simple code (see below)
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----
Code that populates Table 3 for Step 1:
Private Sub cboLogin_Change()
Me.txtFirst.Value = Me.cboLogin.Column(1)
Me.txtLast.Value = Me.cboLogin.Column(2)
Me.txtFull_Name.Value = Me.cboLogin.Column(3)
Me.txtEmail.Value = Me.cboLogin.Column(4)
End Sub
-------------------------- ---------- ---------- ---------- ---------- ----------
Step 2: Skills section pulls from Table 2, must be able to multi-select data (Access, SQL, VB) and update table 3 in the skills field.
I can create a combo box with multi-select capabilites, but this does not update Table 3. The skills field is set to text.
What am I missing?
Table 2 - Skills Data
Table 3 - Resources
Objective is:
Step 1: Pull employee information from Table 1 by Employee ID #. This Combo box will auto populate my form while updating Table 3 on the back end. >> This works with simple code (see below)
--------------------------
Code that populates Table 3 for Step 1:
Private Sub cboLogin_Change()
Me.txtFirst.Value = Me.cboLogin.Column(1)
Me.txtLast.Value = Me.cboLogin.Column(2)
Me.txtFull_Name.Value = Me.cboLogin.Column(3)
Me.txtEmail.Value = Me.cboLogin.Column(4)
End Sub
--------------------------
Step 2: Skills section pulls from Table 2, must be able to multi-select data (Access, SQL, VB) and update table 3 in the skills field.
I can create a combo box with multi-select capabilites, but this does not update Table 3. The skills field is set to text.
What am I missing?
If you put a line break in on your first line of cboLogin_Change, are the proper values displayed when you mouse over Me.cboLogin.Column(1)?
As for Step 2, are you asking for a list of selectable skills to be populated from Table2, and assigned to the selected person from CBOLogin? If this is so, are you able to get the populating to work, just not the save?
As for Step 2, are you asking for a list of selectable skills to be populated from Table2, and assigned to the selected person from CBOLogin? If this is so, are you able to get the populating to work, just not the save?
ASKER
@MrBullwinkle, I can get the populating of the skills and the ability to multi-select. However, once I select, it will not populate table 3.
@capricorn1, I am not sure I follow. How would this tie back to my main table in Table 3?
@capricorn1, I am not sure I follow. How would this tie back to my main table in Table 3?
to add multiple skills, using multi select listbox and vba
with me.lstskills
if .itemsselected.count > 0 then
for each j in .itemsselected
currentdb.execute "Insert into tableName([ID], skill) values(" & me.[ID] &", '" & .column(1,j) &"')"
next
end if
end with
with me.lstskills
if .itemsselected.count > 0 then
for each j in .itemsselected
currentdb.execute "Insert into tableName([ID], skill) values(" & me.[ID] &", '" & .column(1,j) &"')"
next
end if
end with
ASKER
I am getting a too few parameters error.
<I am getting a too few parameters error. >
make sure that the field names and table names are spelled correctly and, that the name of the field you are using are in the table.
make sure that the field names and table names are spelled correctly and, that the name of the field you are using are in the table.
ASKER
I added this to my combo box (on enter) event procedure:
My table is tblResources:
ID
Skills
In the " & Me.[ID] & ", '" &
Is ID the Skills ID or Resource ID?
-------------------------- ---------- ---------- ---------- ---------- ---------- -----
Private Sub lstskills_Enter()
With Me.lstSkills
If .ItemsSelected.Count > 0 Then
For Each j In .ItemsSelected
CurrentDb.Execute "Insert into tblResources([ID], Skills) values(" & Me.[ID] & ", '" & .Column(1, j) & "')"
Next
End If
End With
End Sub
My table is tblResources:
ID
Skills
In the " & Me.[ID] & ", '" &
Is ID the Skills ID or Resource ID?
--------------------------
Private Sub lstskills_Enter()
With Me.lstSkills
If .ItemsSelected.Count > 0 Then
For Each j In .ItemsSelected
CurrentDb.Execute "Insert into tblResources([ID], Skills) values(" & Me.[ID] & ", '" & .Column(1, j) & "')"
Next
End If
End With
End Sub
what is the rowsource of the listbox?
what is the recordsource of the form?
what is the recordsource of the form?
ASKER
List Box:
SELECT [tblSkills].[ID], [tblSkills].[Abbr] FROM [tblSkills] ORDER BY [Abbr];
Record Source of Form:
tblResources
SELECT [tblSkills].[ID], [tblSkills].[Abbr] FROM [tblSkills] ORDER BY [Abbr];
Record Source of Form:
tblResources
Since your from is bound to tblResources, if you have a textbox called txtSkills (bound to [Abbr]), you can use this:
Private Sub lstSkills_AfterUpdate()
With Me.lstSkills
If .ItemsSelected.Count > 0 Then
For Each j In .ItemsSelected
Me.txtSkills = Me.txtSkills & .Column(1, j) & ", "
Next
End If
End With
End Sub
ASKER
That partially worked, however the only thing that saved to my table was , , ,.
The data did not save.
Also the fields stayed highlighted after I selected them for all the records as well.
The data did not save.
Also the fields stayed highlighted after I selected them for all the records as well.
upload a copy of your db, and give details about the process you are trying to do.
To save the record, add:
Dirty = False
To clear the list box selections, use:
Me.lstSkills=Null
ASKER
@Capricorn1
I am trying something new to accomplish my task. I am uploading my db.
My main objective is to track skills by each resource.
I have three tables.
Tables:
tblResources
ResourceID
tblResourcesSkills
ResourceID >> Same as ResourceID in tblResources
SkillsID
Skill
tblSkills
SkillsID >>Same as SkillsID in tblResourcesSkills
Abbr >>>Same as Skills in tblResourcesSkills
Forms:
Main Form to add resources: frmResources
>> This is the form that needs to be able to add skills from the resources (tblResources) and stored in (tblResourcesSkills)
I included the old way that I is listed in this thread. However, it stores the data , , ,. Ultametly this will not work if I need to run a report by Skill.
I am trying to add check boxes. Each check box is tied to tblSkills, but needs to update to tblResourceSkills. I am not sure if this is possible?
Main Form in Database linked to all my subForms: frmNACSResources
Sub-Form to view skills: frmSkillsResourcesSubform
These forms are linked to each other for viewing purposes.
I have un-hidden the navagation and there is a login when you open the db.
login: angelam
pw: 123456
If you know of a better way to accomplish this please let me know. It has been a few years since I have developed in Access.
NACS-Resource-DB---Copy.mdb
I am trying something new to accomplish my task. I am uploading my db.
My main objective is to track skills by each resource.
I have three tables.
Tables:
tblResources
ResourceID
tblResourcesSkills
ResourceID >> Same as ResourceID in tblResources
SkillsID
Skill
tblSkills
SkillsID >>Same as SkillsID in tblResourcesSkills
Abbr >>>Same as Skills in tblResourcesSkills
Forms:
Main Form to add resources: frmResources
>> This is the form that needs to be able to add skills from the resources (tblResources) and stored in (tblResourcesSkills)
I included the old way that I is listed in this thread. However, it stores the data , , ,. Ultametly this will not work if I need to run a report by Skill.
I am trying to add check boxes. Each check box is tied to tblSkills, but needs to update to tblResourceSkills. I am not sure if this is possible?
Main Form in Database linked to all my subForms: frmNACSResources
Sub-Form to view skills: frmSkillsResourcesSubform
These forms are linked to each other for viewing purposes.
I have un-hidden the navagation and there is a login when you open the db.
login: angelam
pw: 123456
If you know of a better way to accomplish this please let me know. It has been a few years since I have developed in Access.
NACS-Resource-DB---Copy.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You That does exactly what I needed to.
ASKER
@ IrogSinta I have one final question, can I message you? My business contact wanted to add an additional layer and I am not quite sure how to modify it. I have tried in the code.
tblResourcesSkills they wanted to add a free text box (Other) so that when we choose Other as a choice they can free text additional information. So it would add an additional layer of including what is entered into Other. (Other is linked to [Abbr] (OTH) & [OTHER] (Other).
I can add a check box
chk10 which is linked to [Abbr] OTH
and I added a text box next to it (txtOther)
The field txtOther would upated in tblResourcesSkills would be (Other) which is linked to Abbr.OTR
Can this be done?
tblResourcesSkills they wanted to add a free text box (Other) so that when we choose Other as a choice they can free text additional information. So it would add an additional layer of including what is entered into Other. (Other is linked to [Abbr] (OTH) & [OTHER] (Other).
I can add a check box
chk10 which is linked to [Abbr] OTH
and I added a text box next to it (txtOther)
The field txtOther would upated in tblResourcesSkills would be (Other) which is linked to Abbr.OTR
Can this be done?
ASKER
Never mind, I figured it out :-)
ASKER
@ IrogSinta
My check boxes stay checked when I go to add a new record. When I go to review an existing record they default back to unchecked. Any ideas?
My check boxes stay checked when I go to add a new record. When I go to review an existing record they default back to unchecked. Any ideas?
Just change the OnCurrent event to:
Call HighlightSkills
Call HighlightSkills
so, your table relations between Resources and tblSkills will be 1 to many on the Employee ID field
Resources tblEmpSkills
EmployeeID 1 --- > M EmployeeID
SkillID