specify return

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

Guest

Simple question but I'm getting fustrated.

I just want to open a query that shows only records for value entered into
text box.
Could someone show me what to add to this code to make it specify only the
records for value entered into txtName.


Private Sub cmdView_Click()
Dim stLinkCriteria As String

If IsNull(Me.txtName) Then
stLinkCriteria = stLinkCriteria & " and [Name]=" & "'" & Me.txtName & "'"

End If

DoCmd.OpenQuery "q_NameQuery"
End Sub
 
Simple question but I'm getting fustrated.

I just want to open a query that shows only records for value entered into
text box.
Could someone show me what to add to this code to make it specify only the
records for value entered into txtName.

Private Sub cmdView_Click()
Dim stLinkCriteria As String

If IsNull(Me.txtName) Then
stLinkCriteria = stLinkCriteria & " and [Name]=" & "'" & Me.txtName & "'"

End If

DoCmd.OpenQuery "q_NameQuery"
End Sub
Click to expand...

That's not how OpenQuery works.
There is NO WHERE argument in the OpenQuery method.
Also, your IsNull() logic is reversed. If the control IsNull, there is
no value to pass to the query.

To run the query as a stand-alone query, open your query in Design
View.
As criteria on the Name field, write:
forms!NameOfYourForm!txtName
Save the query change.

Next, on your form, delete all of your existing code in the cmdView
click event.

As code in this event, write:

Private Sub cmdView_Click()
If Not IsNull(txtName]) then
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
End IF
End Sub

To run the query, open this form. Enter the wanted name in txtName.
Then click the command button. The query will display and the form
will close.

You didn't state what you would ike to have happen if the user does
not enter a value in txtName, nor did you say if the query is all you
wanted to run (i.e. the query is actually the record source for a
report... in which case this is not the method you should use).
 
Something is missing: do I need to declear it? I'm getting acompile error.

Private Sub cmdViewPV_Click()
If Not IsNull(Prov]) then
DoCmd.OpenQuery "q_PVtoExcel"
DoCmd.close acForm, Me.ToExcel
End If
End Sub

End Sub




fredg said:
Simple question but I'm getting fustrated.

I just want to open a query that shows only records for value entered into
text box.
Could someone show me what to add to this code to make it specify only the
records for value entered into txtName.

Private Sub cmdView_Click()
Dim stLinkCriteria As String

If IsNull(Me.txtName) Then
stLinkCriteria = stLinkCriteria & " and [Name]=" & "'" & Me.txtName & "'"

End If

DoCmd.OpenQuery "q_NameQuery"
End Sub
Click to expand...

That's not how OpenQuery works.
There is NO WHERE argument in the OpenQuery method.
Also, your IsNull() logic is reversed. If the control IsNull, there is
no value to pass to the query.

To run the query as a stand-alone query, open your query in Design
View.
As criteria on the Name field, write:
forms!NameOfYourForm!txtName
Save the query change.

Next, on your form, delete all of your existing code in the cmdView
click event.

As code in this event, write:

Private Sub cmdView_Click()
If Not IsNull(txtName]) then
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
End IF
End Sub

To run the query, open this form. Enter the wanted name in txtName.
Then click the command button. The query will display and the form
will close.

You didn't state what you would ike to have happen if the user does
not enter a value in txtName, nor did you say if the query is all you
wanted to run (i.e. the query is actually the record source for a
report... in which case this is not the method you should use).
Click to expand...
 
Never mind I found it: Here is what I worte.

And the reason I'm displaying the query (source data) is because I need to
view what would be exported. Because on the next command button I say
DoCmd.OutputTo acOutputQuery, "QUERY..


Private Sub cmdViewPV_Click()
Dim stLinkCriteria As String
If Not IsNull(prov) Then
stLinkCriteria = stLinkCriteria & " and [PVNO]=" & "'" & Me.prov & "'"
DoCmd.OpenQuery "q_PVtoExcel"
'DoCmd.close acForm, Me.ToExcel
End If
End Sub

Dan @BCBS said:
Something is missing: do I need to declear it? I'm getting acompile error.

Private Sub cmdViewPV_Click()
If Not IsNull(Prov]) then
DoCmd.OpenQuery "q_PVtoExcel"
DoCmd.close acForm, Me.ToExcel
End If
End Sub

End Sub




fredg said:
Simple question but I'm getting fustrated.

I just want to open a query that shows only records for value entered into
text box.
Could someone show me what to add to this code to make it specify only the
records for value entered into txtName.

Private Sub cmdView_Click()
Dim stLinkCriteria As String

If IsNull(Me.txtName) Then
stLinkCriteria = stLinkCriteria & " and [Name]=" & "'" & Me.txtName & "'"

End If

DoCmd.OpenQuery "q_NameQuery"
End Sub
Click to expand...

That's not how OpenQuery works.
There is NO WHERE argument in the OpenQuery method.
Also, your IsNull() logic is reversed. If the control IsNull, there is
no value to pass to the query.

To run the query as a stand-alone query, open your query in Design
View.
As criteria on the Name field, write:
forms!NameOfYourForm!txtName
Save the query change.

Next, on your form, delete all of your existing code in the cmdView
click event.

As code in this event, write:

Private Sub cmdView_Click()
If Not IsNull(txtName]) then
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
End IF
End Sub

To run the query, open this form. Enter the wanted name in txtName.
Then click the command button. The query will display and the form
will close.

You didn't state what you would ike to have happen if the user does
not enter a value in txtName, nor did you say if the query is all you
wanted to run (i.e. the query is actually the record source for a
report... in which case this is not the method you should use).
Click to expand...
Click to expand...
 
Never mind I found it: Here is what I worte.

And the reason I'm displaying the query (source data) is because I need to
view what would be exported. Because on the next command button I say
DoCmd.OutputTo acOutputQuery, "QUERY..

Private Sub cmdViewPV_Click()
Dim stLinkCriteria As String
If Not IsNull(prov) Then
stLinkCriteria = stLinkCriteria & " and [PVNO]=" & "'" & Me.prov & "'"
DoCmd.OpenQuery "q_PVtoExcel"
'DoCmd.close acForm, Me.ToExcel
End If
End Sub

Dan @BCBS said:
Something is missing: do I need to declear it? I'm getting acompile error.

Private Sub cmdViewPV_Click()
If Not IsNull(Prov]) then
DoCmd.OpenQuery "q_PVtoExcel"
DoCmd.close acForm, Me.ToExcel
End If
End Sub

End Sub

fredg said:
On Fri, 22 Jun 2007 13:06:01 -0700, Dan @BCBS wrote:

Simple question but I'm getting fustrated.

I just want to open a query that shows only records for value entered into
text box.
Could someone show me what to add to this code to make it specify only the
records for value entered into txtName.

Private Sub cmdView_Click()
Dim stLinkCriteria As String

If IsNull(Me.txtName) Then
stLinkCriteria = stLinkCriteria & " and [Name]=" & "'" & Me.txtName & "'"

End If

DoCmd.OpenQuery "q_NameQuery"
End Sub

That's not how OpenQuery works.
There is NO WHERE argument in the OpenQuery method.
Also, your IsNull() logic is reversed. If the control IsNull, there is
no value to pass to the query.

To run the query as a stand-alone query, open your query in Design
View.
As criteria on the Name field, write:
forms!NameOfYourForm!txtName
Save the query change.

Next, on your form, delete all of your existing code in the cmdView
click event.

As code in this event, write:

Private Sub cmdView_Click()
If Not IsNull(txtName]) then
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
End IF
End Sub

To run the query, open this form. Enter the wanted name in txtName.
Then click the command button. The query will display and the form
will close.

You didn't state what you would ike to have happen if the user does
not enter a value in txtName, nor did you say if the query is all you
wanted to run (i.e. the query is actually the record source for a
report... in which case this is not the method you should use).
Click to expand...
Click to expand...
Click to expand...

I'm happy you got it working.

Regarding my suggested use of...
DoCmd.Close acForm, Me.Name
which you changed to
DoCmd.close acForm, Me.ToExcel

I included that to close the current form so that the query would be
visible without being covered by the form.
Me.Name was meant to be written literally, Me.Name, not changed to
Me.ToExcel (the name of the form).
Name is a reserved keyword in Access and refers to the name of the
active object, in this case the form on which this code resides...
"ToExcel". It's useful so that you can use the same code without
having to re-write the name of the object it is used on.
 
Back
Top