Solved

Using InputBox to Collect User Input in an Access Form

Posted on 2008-10-02
12
1,606 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now