Results 1 to 9 of 9
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Writing a listbox and additional column to table (should be pretty basic; right???

    i have a form with 5 sections; Contractor, Vacancy, HCW (Health Care Worker), CDR, Termination.

    I have a listbox for each section which allows the user to select a Reason ie, lstContractorReason. Each lstbox has two columns. Column(0) is the reason and Column (1) is an IssueKey. Each lstbox will use a different IssueKey. lstContractorReason has 5 reasons to pick from and all the Issuekeys are "C". For lstVacancyReason column (0) is the reason and IssueKey is Column(1) and all values are "V" and so on.

    tblPerfIssueKey has 2 fields. field 1 has the following values; P, C, V, H, T and Field 2 has the Description.

    My lstbox is showing both columns on the form; Coiumns 2, 1";1"

    All the fields are writing to the table except the IssueKey.
    txtIssueKey is on the form and the Control Source is set to IssueKey (from tblPerfIssues) which is bound to the form.

    Below are 2 of the five tables and they are all set up the same.
    tblContractorReason tblVacancyReason
    ContractorReason VacancyReason
    IssueKey IssueKey


    Here is the code for the command button which writes to the tblPerfIssues.

    Code:
    O
    ption Compare Database  '<-- should be at the top of every code page
    Option Explicit         '<-- should be at the top of every code page
    Private Sub cmdAddPerfIssue_Click()
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Variant
        Dim issuesValue As String
        
        On Error GoTo ErrorHandler
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblPerfIssues")
        If Len(Me.cboAnalyst & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNumber & vbNullString) = 0 Then
            MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
        Else
            If Me.lstContractorIssues.ItemsSelected.Count = 0 Then
                MsgBox "Please select at least one item from Contractor Issues"
            Else
                    If Len(Me.txtContractorOnset & vbNullString) = 0 Then
                    MsgBox "Please enter a Contractor Issue Onset Date"
                Else
                
                   With rs  '<<--!!!!
                    .AddNew
                  rs.Fields("AnalystSpec") = cboAnalyst.Value
                    rs.Fields("ReportDate") = Me.txtReportDate.Value
                    rs.Fields("ContractNumber") = Me.cboContractNumber.Column("0")
                    rs.Fields("Contractor") = Me.txtContractor.Value
                    rs.Fields("MATO") = Me.txtMATO.Value
                    rs.Fields("ContractorOnset") = Me.txtContractorOnset.Value
                    rs.Fields("ContractorResolved") = Me.txtContractorResolved.Value
                    rs.Fields("ContractorComments") = Me.txtContractorComments.Value
                    rs.Fields("ContractorIssues") = Me.lstContractorIssues.Value
                    rs.Fields("IssueKey") = Me.lstContractorIssues.Column("1")
                End With '<<--!!!!
            End If
        End If
                    Me.lstContractorIssues = ""
                    Me.txtContractorOnset = ""
                    Me.txtContractorResolved = ""
                    Me.txtContractorComments = ""
                    Me.txtIssuekey = ""
                    
    ErrorHandler_Exit:
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
    ErrorHandler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
        Resume ErrorHandler_Exit
    
    End Sub
    I know this is very basic but I am not sure what I am missing!! Any help would be greatly appreciated!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't use the quotes. No quotes for ContractNumber also.
    Code:
     rs.Fields("IssueKey") = Me.lstContractorIssues.Column(1)
    Also you can eliminate the complication of nested IFs as below:

    Code:
    Option Compare Database  '<-- should be at the top of every code page
    Option Explicit         '<-- should be at the top of every code page
    Private Sub cmdAddPerfIssue_Click()
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Variant
        Dim issuesValue As String
        
        On Error GoTo ErrorHandler
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblPerfIssues")
    
        If Len(Me.cboAnalyst & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNumber & vbNullString) = 0 Then
            MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
            exit sub    
        end if
    
        If Me.lstContractorIssues.ItemsSelected.Count = 0 Then
            MsgBox "Please select at least one item from Contractor Issues"
            exit sub
        end if
    
        If Len(Me.txtContractorOnset & vbNullString) = 0 Then
            MsgBox "Please enter a Contractor Issue Onset Date"
            exit sub
        end if
                
                   With rs  '<<--!!!!
                    .AddNew

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I tried that and it didn't work so I thought I would try "".

  4. #4
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Is it a problem that I used IssueKey as fieldname in all 5 tables?

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Actually, the problem is that you are not using the ItemSelected property of the listbox. We covered this in one of your previous posts a week or so ago. You define variables to hold the listbox values, and then use those variables in your update.
    It's no problem to have the same field name in multiple tables. If necessary to be specific in code, you prefix the field name with the tablename; tblCustomer.Addr1, where Addr1 can be in many tables.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Quotes round the 1 not needed maybe?

    Me.latcontractorissues.column(1)

  7. #7
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Andy
    I changed the listboxes over the weekend (per management) from multi-select to one selection and I deleted some code.
    I have updated my code based on your previous post and would like it if you could look at the code again.
    Code:
      Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblPerfIssues")
        If Len(Me.cboAnalyst & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNumber & vbNullString) = 0 Then
            MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
        Else
            If Me.lstContractorIssues.ItemsSelected.Count = 0 Then
                MsgBox "Please select at least one item from Contractor Issues"
            Else
                Me.txtIssuekey = Me.lstContractorIssues.Column(1, Me.lstContractorIssues.ItemsSelected(0))
                    
                    If Len(Me.txtContractorOnset & vbNullString) = 0 Then
                    MsgBox "Please enter a Contractor Issue Onset Date"
                Else
                
                  With rs  '<<--!!!!
                    .AddNew
                  rs.Fields("AnalystSpec") = cboAnalyst.Value
                    rs.Fields("ReportDate") = Me.txtReportDate.Value
                    rs.Fields("ContractNumber") = Me.cboContractNumber.Column(0)
                    rs.Fields("Contractor") = Me.txtContractor.Value
                    rs.Fields("MATO") = Me.txtMATO.Value
                    rs.Fields("ContractorOnset") = Me.txtContractorOnset.Value
                    rs.Fields("ContractorResolved") = Me.txtContractorResolved.Value
                    rs.Fields("ContractorComments") = Me.txtContractorComments.Value
                    rs.Fields("ContractorIssues") = Me.lstContractorIssues.Value
                    rs.Fields("IssueKey") = Me.lstContractorIssues.Column(1)
                End With '<<--!!!!
            End If
        End If
    End If
                    Me.lstContractorIssues = ""
                    Me.txtContractorOnset = ""
                    Me.txtContractorResolved = ""
                    Me.txtContractorComments = ""
                    Me.txtIssuekey = ""

  8. #8
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I have been trying several things to get the IssueKey to write, but no luck!
    I tried this code as well.

    Code:
    Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim val As String
        
        
        On Error GoTo ErrorHandler
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblPerfIssues")
        If Len(Me.cboAnalyst & vbNullString) = 0 Or Len(Me.txtReportDate & vbNullString) = 0 Or Len(Me.cboContractNumber & vbNullString) = 0 Then
            MsgBox "Please fill in Analyst/Specialist, Report Date and Contract Number"
        Else
            If Me.lstContractorIssues.ItemsSelected.Count = 0 Then
                MsgBox "Please select at least one item from Contractor Issues"
            Else
                val = Me.lstContractorIssues.SelectedItem.Value
                     
                    If Len(Me.txtContractorOnset & vbNullString) = 0 Then
                    MsgBox "Please enter a Contractor Issue Onset Date"
                Else
                
                  With rs  '<<--!!!!
                    .AddNew
                  rs.Fields("AnalystSpec") = cboAnalyst.Value
                    rs.Fields("ReportDate") = Me.txtReportDate.Value
                    rs.Fields("ContractNumber") = Me.cboContractNumber.Column(0)
                    rs.Fields("Contractor") = Me.txtContractor.Value
                    rs.Fields("MATO") = Me.txtMATO.Value
                    rs.Fields("ContractorOnset") = Me.txtContractorOnset.Value
                    rs.Fields("ContractorResolved") = Me.txtContractorResolved.Value
                    rs.Fields("ContractorComments") = Me.txtContractorComments.Value
                    rs.Fields("ContractorIssues") = Me.lstContractorIssues.Value
                    rs.Fields("IssueKey") = Me.lstContractorIssues.Column(1)
                End With '<<--!!!!
            End If
        End If
    End If
                    Me.lstContractorIssues = Null
                    Me.txtContractorOnset = Null
                    Me.txtContractorResolved = Null
                    Me.txtContractorComments = Null
                    Me.txtIssuekey = Null

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Don't you need a ".Update" before the End With? I don't know what all those "rs" do to the code. "With rs" then "rs.Fields", should be ".Fields"?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Writing listbox results to table
    By PinkLady50 in forum Forms
    Replies: 4
    Last Post: 05-11-2017, 03:34 PM
  2. Replies: 2
    Last Post: 03-07-2017, 05:02 PM
  3. Replies: 6
    Last Post: 02-24-2014, 09:26 AM
  4. Replies: 8
    Last Post: 02-21-2014, 09:26 AM
  5. Replies: 1
    Last Post: 09-26-2013, 08:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums