Solved

Using InputBox to Collect User Input in an Access Form

Posted on 2008-10-02
12
1,611 Views
Last Modified: 2013-11-28
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
Comment
Question by:slamond
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 22628380
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
 
LVL 6

Expert Comment

by:mgalig1010
ID: 22628503
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
 

Author Comment

by:slamond
ID: 22799491
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:omgang
ID: 22799623
Please post the SQL of your query
OM Gang
0
 

Author Comment

by:slamond
ID: 22799640
Here's the SQL view:


SELECT CUSTFIL2_Archive.*
FROM CUSTFIL2_Archive
WHERE (((CUSTFIL2_Archive.CNUM)=GetCnumber()));
0
 

Author Comment

by:slamond
ID: 22933108
Anyone have any ideas?
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 22943358
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
 

Author Comment

by:slamond
ID: 22955135
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
 
LVL 28

Expert Comment

by:omgang
ID: 22955474
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
 

Author Comment

by:slamond
ID: 22998995
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
 
LVL 28

Expert Comment

by:omgang
ID: 22999214
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
 

Author Closing Comment

by:slamond
ID: 31502563
It works!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question