how to store a value as a variable

[cer5yc]

I use the following query to determine whether a record is stored in my underlying Supplier Information table based on the value the user enters for the Tax ID. What I need to be able to do with this is once it runs, if the query returns empty I need to append the Tax ID that was entered into the below paramater query to the Supplier Information table.

Here's what I need:
(1)Run the Below Select Query
(2)If the value entered for WHERE ((([Supplier Information].[Tax ID])=[Enter Tax ID #])) is not in the underlying Supplier Information table then
(3) Append the value entered for WHERE ((([Supplier Information].[Tax ID])=[Enter Tax ID #])) to the Supplier Information table

SELECT [Supplier Information].[Tax ID], [Supplier Information].[Supplier Name], [Supplier Information].Address, [Supplier Information].City, [Supplier Information].State, [Supplier Information].[Zip Code], [Supplier Information].[Duns #], [Supplier Information].[M/WBE Owned], [Supplier Information].[FEIN/SSN]
FROM [Supplier Information]
WHERE ((([Supplier Information].[Tax ID])=[Enter Tax ID #]));

Some more details: The query runs if the user selects "Yes" on a MsgBox
When the query runs the user gets the Enter Tax ID # box

Can anyone give me some ideas on how to get this accomplished

Thanks!

 

[GingerR]

Well, a variety of possibilities.

One is that you could make a little pop-up form that does all of this for you. It asks whatever it is your msgbox asks, and if the user picks NO, the form just closes. If they select YES, a text box is made visible that they enter the Tax ID (instead of prompting from the query) and the user hits OK, then you could hide the form and use the data in the text box in the query and in adding to your table, or anywhere else you want - you'd reference Forms!FormName!txtTaxID. Also when they hit OK you can make sure they are entering the proper number of chacters, etc (validate their data).

You could also use an InputBox, in a similar way.

In this kind of situation, I think of the user and try to make something that 1) makes common sense and 2) they have to make the fewest decisions and physical moves possible.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[MajP]

Using what Ginger said I would maybe do something like

Public Sub test()
  Dim x As Variant
  Dim strDomain As String
  Dim strExpr As String
  Dim strCriteria As String
  strDomain = "tblPersonnel"
  strExpr = "strLastName"
  strCriteria = "strLastName = "
  x = InputBox("enter a value or cancel to exit")
  'do some error checking
  'If they hit cancel it returns "" and exits
  If Not x = "" Then
    If DCount(strExpr, strDomain, strCriteria & "'" & x & "'") = 0 Then
      'run some sql with your variable
    End If
  End If
End Sub

I personally do not use parameter queries. Not a very user friendly interface. I made the parameters of the dlookup variables so that the code could be easily modified.