Solved

Using InputBox to Collect User Input in an Access Form

Posted on 2008-10-02
12
1,610 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

776 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