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.)

Dim stLWRStatus As String
    If IsNull(Me.cmbLWRStatus) Or Me.cmbLWRStatus = "" Then
        stLWRStatus = "[LWRNumber] Is Not Null"
        stLWRStatus = cmbLWRStatus
        stLWRStatus = "[LWRStatus] = '" & stLWRStatus & "'"
    End If
Dim stRequestor As String
    If IsNull(Me.cmbRequestor) Or Me.cmbRequestor = "" Then
        stRequestor = "[LWRNumber] Is Not Null"
        stRequestor = cmbRequestor
        stRequestor = "[Requestor] = '" & stRequestor & "'"
    End If
Dim stProduct As String
    If IsNull(Me.cmbProduct) Or Me.cmbProduct = "" Then
        stProduct = "[LWRNumber] Is Not Null"
        stProduct = cmbProduct
        stProduct = "[Product] = '" & stProduct & "'"
    End If
Dim stRegion As String
    If IsNull(Me.cmbRegion) Or Me.cmbRegion = "" Then
        stRegion = "[LWRNumber] Is Not Null"
        stRegion = cmbRegion
        stRegion = "[Region] = '" & stRegion & "'"
    End If
Dim stTestSpec As String
    If IsNull(Me.cmbTestSpec) Or Me.cmbTestSpec = "" Then
        stTestSpec = "[LWRNumber] Is Not Null"
        stTestSpec = cmbTestSpec
        stTestSpec = "[TestSpec] = '" & stTestSpec & "'"
    End If
Dim stRequestType As String
    If IsNull(Me.cmbRequestType) Or Me.cmbRequestType = "" Then
        stRequestType = "[LWRNumber] Is Not Null"
        stRequestType = cmbRequestType
        stRequestType = "[RequestType] = '" & stRequestType & "'"
    End If
Dim stPartNumber As String
    If IsNull(Me.cmbPartNumber) Or Me.cmbPartNumber = "" Then
        stPartNumber = "[LWRNumber] Is Not Null"
        stPartNumber = cmbPartNumber
        stPartNumber = "[PartNumber] = '" & stPartNumber & "'"
    End If
Dim stAssignedTo As String
    If IsNull(Me.cmbAssignedTo) Or Me.cmbAssignedTo = "" Then
        stAssignedTo = "[LWRNumber] Is Not Null"
        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.)

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.