Making a query using a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ive got a table with customers information and also a linked table with
products on it. I have made a query which checks for certain customers that
want a certain product and wondered whther there was a way that when a
command button is pressed it brings up a box which the user can type a
product name in and then the results of this are brought up in a query table
view.
 
You can use the built-in query parameter box, or a more attractive form as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks, it seems to work but there are two questions i have. when i press the
cmd button i first get a box up which says Forms!Enquiry - Query!Search and a
text box. this doesnt seem to do anything and i just wondered if it did
anything and if not can it be got rid of. the next box bring up one that says
enter product and then that does bring up results but too many. the prodcut
are chemicals so if i type in silver i also get silver oxide coming up in the
results. is there a way to stop this?

Sry about all the questions i just have never done any coding in VB before
so i am trying to learn

Arvin Meyer said:
You can use the built-in query parameter box, or a more attractive form as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ive got a table with customers information and also a linked table with
products on it. I have made a query which checks for certain customers
that
want a certain product and wondered whther there was a way that when a
command button is pressed it brings up a box which the user can type a
product name in and then the results of this are brought up in a query
table
view.
Click to expand...
Click to expand...
 
Ah, ive sorted the first bit out, also is there a way I can make the query
output save a copy of the results into an excel file?

Arvin Meyer said:
You can use the built-in query parameter box, or a more attractive form as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ive got a table with customers information and also a linked table with
products on it. I have made a query which checks for certain customers
that
want a certain product and wondered whther there was a way that when a
command button is pressed it brings up a box which the user can type a
product name in and then the results of this are brought up in a query
table
view.
Click to expand...
Click to expand...
 
Several ways: In code or with a macro, you can use the TransferSpreadsheet
Method or you can use the OutputTo Method. Check the helpfiles for those
keywords. You can also use the menu with an open query:

Tools ... Office Links ... Analyze It With Microsoft Excel
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ah, ive sorted the first bit out, also is there a way I can make the query
output save a copy of the results into an excel file?

Arvin Meyer said:
You can use the built-in query parameter box, or a more attractive form
as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that
instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ive got a table with customers information and also a linked table with
products on it. I have made a query which checks for certain customers
that
want a certain product and wondered whther there was a way that when a
command button is pressed it brings up a box which the user can type a
product name in and then the results of this are brought up in a query
table
view.
Click to expand...
Click to expand...
Click to expand...
 
Thanks ive got it working now :)

Is there any coding that when i press cancel in the query message box it
will just go back rather than bring up the debug/ end menu?

Arvin Meyer said:
Several ways: In code or with a macro, you can use the TransferSpreadsheet
Method or you can use the OutputTo Method. Check the helpfiles for those
keywords. You can also use the menu with an open query:

Tools ... Office Links ... Analyze It With Microsoft Excel
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ah, ive sorted the first bit out, also is there a way I can make the query
output save a copy of the results into an excel file?

Arvin Meyer said:
You can use the built-in query parameter box, or a more attractive form
as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that
instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Ive got a table with customers information and also a linked table with
products on it. I have made a query which checks for certain customers
that
want a certain product and wondered whther there was a way that when a
command button is pressed it brings up a box which the user can type a
product name in and then the results of this are brought up in a query
table
view.
Click to expand...
Click to expand...
Click to expand...
 
You can add error handling if you know the specific error number. Try adding
a message box in your error handling routine:

On error GoTo Err_Handler
' ... your code
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

That shout give you an error number that you can trap for:

Err_Handler:
If Err.Number = 12345 Then
' Do something
End If

Or you can simply use:

On Error Resume Next

which is not a good idea during debugging
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Thanks ive got it working now :)

Is there any coding that when i press cancel in the query message box it
will just go back rather than bring up the debug/ end menu?

Arvin Meyer said:
Several ways: In code or with a macro, you can use the
TransferSpreadsheet
Method or you can use the OutputTo Method. Check the helpfiles for those
keywords. You can also use the menu with an open query:

Tools ... Office Links ... Analyze It With Microsoft Excel
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

alexr114 said:
Ah, ive sorted the first bit out, also is there a way I can make the
query
output save a copy of the results into an excel file?

:

You can use the built-in query parameter box, or a more attractive
form
as a
dialog box. In the query, use a criteria like:

Like [Please enter a product] & "*"

for the standard query parameter or for the form, something like:

Like [Forms]![FormName]![TextBoxName] & "*"

A line of code in the command button opens the query:

DoCmd.OpenQuery "QueryName"

but you may want to base a form on the query resuls, and open that
instead.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Ive got a table with customers information and also a linked table
with
products on it. I have made a query which checks for certain
customers
that
want a certain product and wondered whther there was a way that when
a
command button is pressed it brings up a box which the user can type
a
product name in and then the results of this are brought up in a
query
table
view.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top