Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Variable to SQL (XP)

    Is it possible to pass a variable to SQL code? I have a query that finds duplicate records in a table and would like to prompt the user to enter in a table name to verify if duplicate records exist in the table of choice.

    The variable code being: oPrompt = Application.InputBox("" & Chr(10) & "Enter A Table Name", "Find Duplicate Records")

    SQL Code:
    SELECT [04_02].Id, [04_02].Unit, [04_02].Name
    FROM 04_02
    WHERE ((([04_02].Id) In (SELECT [Id] FROM [04_02] As Tmp GROUP BY [Id] HAVING Count(*)>1 )))
    ORDER BY [04_02].Id;


    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Variable to SQL (XP)

    You would have to do much more than that, since another table than 04_02 will probably have different fields, so just changing the table name would result in an error.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variable to SQL (XP)

    Most of the tables have the same fields. The code would only be run on those particular tables sharing the same fields.

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Variable to SQL (XP)

    You could do something like this:
    Create a form based on the query you now have.
    Create the following On Load event procedure:

    Private Sub Form_Load()
    Dim strTable As String
    Dim strSQL As String

    strTable = InputBox("Enter a table name")
    If strTable = "" Then Exit Sub

    strSQL = "SELECT Id, Unit, Name FROM [" & strTable & "] " & _
    "WHERE Id In (SELECT Id FROM [" & strTable & "] As Tmp " & _
    "GROUP BY Id HAVING Count(*)>1) ORDER BY Id"
    Me.RecordSource = strSQL
    End Sub

    A more user-friendly solution would be to put an unbound combo box in the form header that lists the appropriate tables. Use the After Update event of the combo box to change the record source as above, referring to the combo box instead of using InputBox.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variable to SQL (XP)

    Hans,

    Good points. I'll fiddle with it.

    Thanks,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Variable to SQL (XP)

    For what it's worth, I have attached the test database I used while replying to your questions in this thread and the one on queries.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •