• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1636
  • Last Modified:

Using InputBox to Collect User Input in an Access Form

I created a form with an On Open Event Procedure (code to follow) that asks the user for a customer # then runs two Append Queries that finds that customer's information in two different archive tables and restores the data to the respective tables in our active database. Then it runs Update Queries to delete the customer's data from the two archive tables (haven't written those queries yet).

Private Sub Form_Open(Cancel As Integer)
   Dim Cnumber As String
   Cnumber = InputBox("Customer #?")
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryCustomerArchiveRestore"
   Cancel = True
End Sub


I placed a reference to the variable [Cnumber] in the query's criteria area but I'm assuming the syntax is wrong since it is asking the user for input twice (the InputBox and again in the query with the prompt [Cnumber]) (when I test the query as a Select Query).

How does one pass a variable to a query?
0
slamond
Asked:
slamond
  • 6
  • 5
1 Solution
 
omgangIT ManagerCommented:
You can either add a hidden field to your form to capture the user input and reference the field in the query

[Forms]![TheForm]![HiddenField]

change the Form_Open event to

Private Sub Form_Open(Cancel As Integer)
   Dim Cnumber As String
   Cnumber = InputBox("Customer #?")
   Me.HiddenField = Cnumber
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryCustomerArchiveRestore"
   Cancel = True
End Sub


OR

you can set the user input to a public variable and retrieve the value with a function call

Public TheCnumber As String

Private Sub Form_Open(Cancel As Integer)

   TheCnumber = InputBox("Customer #?")
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryCustomerArchiveRestore"
   Cancel = True
End Sub

Public Function GetCnumber() As String
    GetCnumber = TheCnumber
End Function

Call it in the query by
GetCnumber()

OM Gang
0
 
mgalig1010Commented:
I would put a text box on your form to hold the customer number. The form would open, the user would enter the number in the text box then click a button or hit enter to run the queries.

You can reference the text box from the queries by saving the form then clicking on the build button (looks like a ...) in the criteria for the customer field in the queries.

When you add a button to the form you will be prompted for what you want the button to do. Go through the wizard and have it open your query. Action queries don't actually open, they just run. You can edit the code in the button to run the second query. Just use the same syntax the wizard used to open the first query.
0
 
slamondAuthor Commented:
I decided to try omgang's 2nd option and used the following code as an Open Event:

Public TheCnumber As String

Private Sub Form_Open(Cancel As Integer)

   TheCnumber = InputBox("Customer #?")
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryCustomerArchiveRestore"
   Cancel = True
End Sub

Public Function GetCnumber() As String
    GetCnumber = TheCnumber
End Function

But, when I try to run the form, using =GetCnumber() as the criteria for finding the customer's CNUM in the query, I get the error message "Undefined function".
I'm guessing that my syntax is wrong.
I'm simply placing "GetCnumber() in the criteria cell under the CNUM field in the query.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
omgangIT ManagerCommented:
Please post the SQL of your query
OM Gang
0
 
slamondAuthor Commented:
Here's the SQL view:


SELECT CUSTFIL2_Archive.*
FROM CUSTFIL2_Archive
WHERE (((CUSTFIL2_Archive.CNUM)=GetCnumber()));
0
 
slamondAuthor Commented:
Anyone have any ideas?
0
 
omgangIT ManagerCommented:
I've been away so sorry I did not get back to you sooner.

This
Public TheCnumber As String

and this
Public Function GetCnumber() As String
    GetCnumber = TheCnumber
End Function

should be in a standard code module, not in your form module.
I've tested (Access 2003) and it works correctly.

OM Gang
0
 
slamondAuthor Commented:
I'm not sure what a "standard code module" refers to.

I opened Modules and then opened Query Functions and placed the code in there,
but it's still not working.

In case it makes a difference, I'm working in a temporary user-copy of our reports.mdb which is the store for customized Access work.  We are using Datacor's ChempaxVB, written in VB with Access as the database (a chemical industry ERP). I see many Modules and many seem to be written by Datacor as part of ChempaxVB.
0
 
omgangIT ManagerCommented:
Best guess is that ChempaxVB is a Visual Basic application with a MS Access back-end database.

In the Access database window you should see, in the left pane, Modules (see the attached images)

OM Gang
access-modules.jpg
standard-module.jpg
0
 
slamondAuthor Commented:
Yes, we have the chempax.mdb database that houses all of our tables and the reports.mdb that contains all of our customized reports, queries and forms.
When I start Chempax, it opens two temporary databases in C:\Documents and Settings\slamond\Local Settings\Temp\2\~cp7.mdb and C:\Documents and Settings\slamond\Local Settings\Temp\2\~RP7.mdb
Whenever I edit a query, report or form I must export the finished product to D:\ChempaxVB\reports.mdb on another server.
I'm unsure which database and which Module that I'm supposed to add the code.

I'll attach print screens of the modules in chempax.mdb and reports.mdb.


chempax.jpg
reports.jpg
0
 
omgangIT ManagerCommented:
If the form you have created (in your original post) and the query qryCustomerArchiveRestore is in the Chempax.mdb you should put the public variable declaration and the public function into a standard code module in the same.  You can create a new module in Chempax.mdb by clicking the 'New' button in the first screen shot above.  Name is something like 'MyFunctions' or 'SlamondCode'.  Then paste the code in there.

OM Gang
0
 
slamondAuthor Commented:
It works!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now