Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 November 3rd, 2010, 04:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Select records & insert to a table

Hi all!

Here is what I want to do. I have an unbound form with fields for the user to list filter requirements. Then I want to open a form displaying the results of the filter. The catch...there are two different tables it is searching and I need it to be modifyable when opened.

Here are the steps I need help to create...
1.) Delete any existing data within the table "tblReportCriteria"
2.) I want the code to take the user inputs and filter the table "View_unionqryAllLWR"
3.) Take the values in field [LWRNumber] of the filtered data and insert those values into table "tblReportCriteria"
4.) Open form "frm2010LWR" with showing only those records that have the same [LWRNumber] as those records within table "tblReportCriteria"

Here is what I have so far. #1 I think I can do with a simple delete query and just run it through code...it is #2-4 that I am struggling with. (table "View_unionqueryAllLWR" is an SQL view joining two tables...(1) that has tha main LWR data, and (2) containing the multiple part numbers that relate to the LWR. A one-to-many relationship.)

Code:
 
Dim stLWRStatus As String
    If IsNull(Me.cmbLWRStatus) Or Me.cmbLWRStatus = "" Then
    
        stLWRStatus = "[LWRNumber] Is Not Null"
        
    Else
        
        stLWRStatus = cmbLWRStatus
        
        stLWRStatus = "[LWRStatus] = '" & stLWRStatus & "'"
        
    End If
    
Dim stRequestor As String
    If IsNull(Me.cmbRequestor) Or Me.cmbRequestor = "" Then
    
        stRequestor = "[LWRNumber] Is Not Null"
        
    Else
        
        stRequestor = cmbRequestor
        
        stRequestor = "[Requestor] = '" & stRequestor & "'"
        
    End If
    
Dim stProduct As String
    If IsNull(Me.cmbProduct) Or Me.cmbProduct = "" Then
    
        stProduct = "[LWRNumber] Is Not Null"
        
    Else
        
        stProduct = cmbProduct
        
        stProduct = "[Product] = '" & stProduct & "'"
        
    End If
    
Dim stRegion As String
    If IsNull(Me.cmbRegion) Or Me.cmbRegion = "" Then
    
        stRegion = "[LWRNumber] Is Not Null"
        
    Else
        
        stRegion = cmbRegion
        
        stRegion = "[Region] = '" & stRegion & "'"
        
    End If
    
Dim stTestSpec As String
    If IsNull(Me.cmbTestSpec) Or Me.cmbTestSpec = "" Then
    
        stTestSpec = "[LWRNumber] Is Not Null"
        
    Else
        
        stTestSpec = cmbTestSpec
        
        stTestSpec = "[TestSpec] = '" & stTestSpec & "'"
        
    End If
    
Dim stRequestType As String
    If IsNull(Me.cmbRequestType) Or Me.cmbRequestType = "" Then
    
        stRequestType = "[LWRNumber] Is Not Null"
        
    Else
        
        stRequestType = cmbRequestType
        
        stRequestType = "[RequestType] = '" & stRequestType & "'"
        
    End If
    
Dim stPartNumber As String
    If IsNull(Me.cmbPartNumber) Or Me.cmbPartNumber = "" Then
    
        stPartNumber = "[LWRNumber] Is Not Null"
        
    Else
        
        stPartNumber = cmbPartNumber
        
        stPartNumber = "[PartNumber] = '" & stPartNumber & "'"
        
    End If
    
Dim stAssignedTo As String
    If IsNull(Me.cmbAssignedTo) Or Me.cmbAssignedTo = "" Then
    
        stAssignedTo = "[LWRNumber] Is Not Null"
        
    Else
        
        stAssignedTo = cmbAssignedTo
        
        stAssignedTo = "[AssignedTo] = '" & stAssignedTo & "'"
        
    End If
    
Dim WhereStatement As String
    WhereStatement = stLWRStatus & " And " & stRequestor & _
    " And " & stProduct & " And " & stRegion & _
    " And " & stTestSpec & " And " & stRequestType & _
    " And " & stPartNumber & " And " & stAssignedTo
Here will be code to remove existing records from table "tblReportCriteria"

Here needs to be step #2

Here needs to be step #3

Here needs to be step #4

Thanks in advance for all your help.

(Note: the repeated "[LWRNumber] Is Not Null" is just so that it will not filter that field because no criteria was entered...LWRNumber is never null, so that is why I used that statement...I couldn't get a wildcard to work for some reason.)
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Connecting to a table to read and insert records mnolting Word VBA 0 October 1st, 2008 11:39 PM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
Insert multiple records in a Inner Join Table rylemer Access VBA 0 July 19th, 2004 03:54 PM
INSERT & SELECT FROM....? No idea Seb_soum Classic ASP Databases 3 May 14th, 2004 07:06 AM
Select records NOT in table davesav SQL Server ASP 2 June 10th, 2003 04:04 PM





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