Creating a queery Ussing VBA and AQL 
Author Message
 Creating a queery Ussing VBA and AQL

I have to set-up a database which will pull parts based on model as well as
part option, for my company.  I am a first time user, however have education
with Visual Basic.  I separated the parts by unit ussing a simple select
{*filter*}y, and now I have to eliminate parts which are not included in the
option.  This is the code I am trying to use:

Dim opt1 As String
Dim Option{*filter*}y As QueryDef
Dim db As DATABASE
Dim Part_Option As String

    Set db = CurrentDb()
    Set Option{*filter*}y = db.CreateQueryDef("DeleteMeIfYouSeeMe")
    Option{*filter*}y.SQL = "SELECT Test.[Part Number], Test.Description,
Test.Material, Test.[Parts/Unit], Test.[Tool Change C2000], Test.[Program
#C2000], Test.[Sheet Length C2000], Test.[Sheet Width C2000],
Test.[Parts/Sheet C2000], Test.[Clamp Location 1 C2000], Test.[Clamp
Location 2  C2000], Test.[Program # New Machine], Test.[Tool Change New
Machine], Test.[Sheet Length New Machine], Test.[Sheet Width  New Machine],
Test.[Parts/Sheet New Machine], Test.[Clamp Location 1 New Machine],
Test.[Clamp Location 2 New Machine], Test.Kanban, Test.[Unit One],
Test.[Unit Two], Test.[Unit Three], Test.[Unit Four] FROM Test"

    Set opt1 = Forms![Hrv600i(6LC)]!option1.Value
    Set Part_Option = Test![1) D - Recirc Defrost].Value

    If Part_Option = "1) D - Recirc Defrost" And opt1 = -1 Then
        WHERE Part_Option = -1
        ORDER BY([Test]![Tool Change C2000], [Test]![Program #C2000])
        Else
            Set Part_Option = [Test]![2) E - Exhaust Only]
            If opt1 = "2) E - Exhaust Only" And Part_Option = -1 Then
                WHERE Part_Option = -1
                ORDER BY([Test]![Tool Change C2000], [Test]![Program
#C2000])
                Else
                    Set Part_Option = [Isolate Unit]![3) N - Non Defrost]
                    If opt1 = "3) N - Non Defrost" And Part_Option = -1 Then
                    WHERE Part_Option = -1
                    ORDER BY([Test]![Tool Change C2000], [Test]![Program
#C2000])
                    End If
            End If
    End If

It is a three part conditional statement which cycles thru the three
options.  My problem is that it doesn't recognize the test table which is
based off a make table {*filter*}y, and the sql statements aren't working in the
code.  They work in the {*filter*}y builder, but they are unrecognized in module
programing.  Please let me know if this is possible, or is there another way
to do this?

Brad W



Sat, 03 Nov 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Help with Queries ussing SQL

2. Access2 Database queery

3. Run A Queery From A VB Prog

4. SQL Create table vs Creating TableDef Object in VBA

5. create a relation with vba

6. Create Validation Rule for Form Control using VBA or Macro

7. How to create a dynamic filter with VBA?

8. Create MDE from VBA

9. Create Microsoft Graph from VBA

10. Data Type Conversion Error when creating table in VBA

11. Creating Folders With VBA

12. creating queries using vba

 

 
Powered by phpBB® Forum Software