Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 28th, 2011, 11:10 AM
Registered User
 
Join Date: Feb 2011
Location: Ann Arbor, MI
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
Reply With Quote
  #2 (permalink)  
Old February 28th, 2011, 12:06 PM
Registered User
 
Join Date: Feb 2011
Location: Ann Arbor, MI
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
Reply With Quote
  #3 (permalink)  
Old February 28th, 2011, 02:06 PM
Registered User
 
Join Date: Feb 2011
Location: Ann Arbor, MI
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:01 AM.


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