Solved

Trying to add a combo and list box that pulls from seperate tables and updates a table

Posted on 2013-01-07
20
291 Views
Last Modified: 2013-01-09
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?
0
Comment
Question by:gracie1972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38752320
you can achieve this using multi valued field in Table3, but i would recommend to create another table "tblEmpSkills" with Employee ID , and Skills ID fields

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
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38752334
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?
0
 

Author Comment

by:gracie1972
ID: 38752352
@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?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38752410
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
0
 

Author Comment

by:gracie1972
ID: 38752460
I am getting a too few parameters error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38752505
<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.
0
 

Author Comment

by:gracie1972
ID: 38752744
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38752784
what is the rowsource of the listbox?

what is the recordsource of the form?
0
 

Author Comment

by:gracie1972
ID: 38752816
List Box:
SELECT [tblSkills].[ID], [tblSkills].[Abbr] FROM [tblSkills] ORDER BY [Abbr];

Record Source of Form:
tblResources
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38753325
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

Open in new window

0
 

Author Comment

by:gracie1972
ID: 38755660
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38755702
upload a copy of your db, and give details about the process you are trying to do.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38755813
To save the record, add:
Dirty = False
To clear the list box selections, use:
Me.lstSkills=Null
0
 

Author Comment

by:gracie1972
ID: 38755842
@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
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 38757654
Here's a revised database.  I created a new subform so you don't need the other one.  I also did not use the SkillID fields in your tblResourceSkills and tblSkills since the Abbr field itself is a unique field.
NACS-Resource-DB---Copy.mdb
0
 

Author Comment

by:gracie1972
ID: 38759669
Thank You  That does exactly what I needed to.
0
 

Author Comment

by:gracie1972
ID: 38760025
@ 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?
0
 

Author Comment

by:gracie1972
ID: 38760055
Never mind, I figured it out :-)
0
 

Author Comment

by:gracie1972
ID: 38760143
@ 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?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38761486
Just change the OnCurrent event to:
Call HighlightSkills
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question