Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 28th, 2011, 11:10 AM
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with forms

Hello everyone,

I'm to the point I'm ready to pull my hair out. I am new to VBA. I Know what i want to do and could easily do it in Java, but this is access and I'm stuck with VBA a language I'm learning in the fly.

The form I'm having trouble with is under the CAL(corrective Action list) Assignment add edit table.

Code:
Option Compare Database
Option Explicit
Public calNum As Integer

Private Sub Form_Load()
    calNum = Form_frmCAL.Action_ID.Value
    Me.AssignedID.Value = calNum

    SQL.assignLookUpEmp (calNum)
    SQL.assignLookUpGrp (calNum)
    
    
    
End Sub
Code:
Sub assignLookUpEmp(assignment As Integer)
    Set SQL.DB = CurrentDb()
    
    Dim rst As Recordset
    Dim rst2 As Recordset
    
    
    Set rst = DB.OpenRecordset("SELECT * FROM tblEmployee ORDER BY LastName ;")
     '''builds Assignment list of employees list
    Do While Not rst.EOF
        
        Form_frmAssignAddEdit.lstEmp.AddItem rst![FirstName] & "  " & rst![LastName] & "   " & rst![Clock]
       
        rst.MoveNext
    Loop
    
End Sub

Sub assignLookUpGrp(assignment As Integer)
    Dim rst As Recordset
    Dim rst2 As Recordset

    Set rst = DB.OpenRecordset("SELECT * FROM tblAssigned WHERE" _
                               & " tblAssigned.[Assigned List ID] = " & assignment & ";")
    Set rst2 = DB.OpenRecordset("SELECT * FROM tblEmployee ;")
     '''builds Assignment list of employees list
    Do While Not rst.EOF
        
        Do While Not rst2.EOF
            If rst![Group or Employee ID] = rst2![Clock] Then
                Form_frmAssignAddEdit.lstAssign.AddItem rst2![FirstName] & "  " & rst2![LastName] & "   " & rst2![Clock]
            End If
            rst2.MoveNext
        Loop
        
        rst.MoveNext
    Loop

End Sub
Any suggestions on how to populate my list box would be great.

Thank you,

Joe
 
Old February 28th, 2011, 12:06 PM
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The reason I'm trying to loop is Access allows only one record returned in a sub-query. This is a limitation I hit.
Code:
Set rst = DB.OpenRecordset("SELECT * FROM tblEmployee WHERE [Clock] <> " _
                               & "(SELECT [Group or Employee ID] FROM tblAssigned " _
                               & " WHERE [Assigned List ID] = " & assignment & ") ORDER BY LastName ;")
    
     '''builds Assignment list of employees list
    Do While Not rst2.EOF
        
        Form_frmAssignAddEdit.lstEmp.AddItem rst![FirstName] & "  " & rst![LastName] & "   " & rst![Clock]
            
        rst.MoveNext
    Loop
now if i run this with a sub-query in MySQL it works just fine, but I have to get this info from an access table not mysql. Why are sub-queries so diffrent in access vs oracle and MySql?

Thanks everyone for your help
Joe
 
Old February 28th, 2011, 02:06 PM
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok this works if you have either one employee or one group in the assignment list. However if more than one i get the 3354 runtime error

Code:
Sub assignLookUpEmp(assignment As Integer)

    Set SQL.DB = CurrentDb()
    
    Dim rst As Recordset
    Dim rst2 As Recordset
   
    Set rst = DB.OpenRecordset("SELECT [FirstName],[LastName],[Clock] FROM tblEmployee WHERE [Clock] <> " _
                               & "(SELECT [Group or Employee ID] FROM tblAssigned " _
                               & " WHERE [Assigned List ID] = " & assignment & ")  ORDER BY LastName ;")
                               
    Set rst2 = DB.OpenRecordset("SELECT [FirstName],[LastName],[Clock] FROM tblEmployee WHERE [Clock] = " _
                               & "(SELECT [Group or Employee ID] FROM tblAssigned " _
                               & " WHERE [Assigned List ID] = " & assignment & ") ORDER BY LastName ;")
    
     '''builds Assignment list of employees Available to work on assignment
     Do While Not rst.EOF
        
        Form_frmAssignAddEdit.lstEmp.AddItem rst![FirstName] & "  " & rst![LastName] & "   " & rst![Clock]
            
        rst.MoveNext
     Loop
    
     '''builds Assignment list of employees Assigned
     Do While Not rst2.EOF
        
        Form_frmAssignAddEdit.lstAssign.AddItem rst2![FirstName] & "  " & rst2![LastName] & "   " & rst2![Clock]
            
        rst2.MoveNext
    Loop
    
End Sub

Sub assignLookUpGrp(assignment As Integer)
    Set SQL.DB = CurrentDb()
    Dim rst As Recordset
    Dim rst2 As Recordset
    
    Set rst = DB.OpenRecordset("SELECT DISTINCT [Group Name] FROM tblGroups WHERE [ID] <>" _
                               & "(SELECT [Group or Employee ID] FROM tblAssigned " _
                               & " WHERE [Assigned List ID] = " & assignment & ");")
    Set rst2 = DB.OpenRecordset("SELECT DISTINCT [Group Name] FROM tblGroups WHERE [ID] = " _
                               & "(SELECT [Group or Employee ID] FROM tblAssigned " _
                               & " WHERE [Assigned List ID] = " & assignment & ");")
                               
    '''builds Assignment list of groups Available to work on assignment
    Do While Not rst.EOF
        
        Form_frmAssignAddEdit.lstEmp.AddItem rst![Group Name]
        rst.MoveNext
    Loop
    
     '''builds Assignment list of groups Assigned
    Do While Not rst2.EOF
        
        Form_frmAssignAddEdit.lstAssign.AddItem rst2![Group Name]
            
        rst2.MoveNext
    Loop
    

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
C# forms barrytn C# 1 March 22nd, 2005 09:08 AM
Opening forms from other forms Paulsh Access VBA 1 September 30th, 2004 06:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.