Help with forms

jswager · February 28th, 2011, 11:10 AM

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

jswager · February 28th, 2011, 12:06 PM

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

jswager · February 28th, 2011, 02:06 PM

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