Newbie needs help

[Fred Bloggs]

I've created a simple form to locate members of a database by town, surname
or postcode. I've used Microsoft's Query by Form suggestion, so I have three
unbound text boxes, [BySname], [ByTown] and [ByPCode]. Then I've used a
command button to run a macro which runs the Query which has as its criteria
in the three relevant fields [Forms]![testform]![BySname]or
[Forms]![testform]![BySname]Is Null, etc.

What I want to do is to be able to clear all three fields rather than having
to use the text tool to highlight then delete the contents of the text boxes
on the form. I've tried creating a command button using the wizard's
'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the button
a message comes up that says: The command or action 'refresh' isn't
available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would be
much appreciated.

Cheers
James

 

[Allen Browne]

Just set the 3 unbound text boxes to Null, and turn off the forms filter:

Me.BySname = Null
Me.ByTown = Null
Me.ByPCode = Null
Me.FilterOn = False

 

[Fred Bloggs]

Like I said, Allen, I'm a newbie so forgive, but where does this code go? I
tried clicking on Build Event for the command button and on the opened
window, I put:

Private Sub Command12_Click()
Me.BySname = Null
Me.ByTown = Null
Me.ByPCode = Null
Me.FilterOn = False

End Sub

But when I try to use the button, it gives an error message that says:
"Compile error: method or data member not found."

I'm afraid you'll have to tell me in very simple abc terms until I can get
more used to everything!

Thanks in advance.
James

Just set the 3 unbound text boxes to Null, and turn off the forms filter:

Me.BySname = Null
Me.ByTown = Null
Me.ByPCode = Null
Me.FilterOn = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've created a simple form to locate members of a database by town,
surname or postcode. I've used Microsoft's Query by Form suggestion, so I
have three unbound text boxes, [BySname], [ByTown] and [ByPCode]. Then
I've used a command button to run a macro which runs the Query which has
as its criteria in the three relevant fields
[Forms]![testform]![BySname]or [Forms]![testform]![BySname]Is Null, etc.

What I want to do is to be able to clear all three fields rather than
having to use the text tool to highlight then delete the contents of the
text boxes on the form. I've tried creating a command button using the
wizard's 'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the
button a message comes up that says: The command or action 'refresh'
isn't available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would be
much appreciated.

 

[Al Camp]

Fred,
I take it that you have a dialog form with 3 text controls where you can
enter/not enter criteria for a report.
"Filter by form" is a function to filter a "bound" form's recordset by
selecting criteria values in the bound form fields, and applying that
filter.
It would not apply to your unbound dialog form, or have anything to do
with the clearing of those fields.

Just...
Me.[BySname] = ""
Me.[ByTown] = ""
Me.[ByPCode] = ""
should clear those values on the dialog form.

Also, your query criteria should be...
Like [Forms]![testform]![BySname] & "*"
for all three fields. That will allow a "" value in any criteria to return
all values for that field.

 

[Fred Bloggs]

Al, this works brilliantly!
I noticed that when I used Microsoft's code in the query, if I went back to
the design view after a few moments, it was putting other 'Null' and various
bits of code all over the place, creating new fields, etc. but your is
clean!

One other question: is there any way once I've typed into one of the fields,
that I can make it search by using the enter/return key on the keyboard
rather than having to do the OnClick on the command button?

Thanks so much for your help.
James

Fred,
I take it that you have a dialog form with 3 text controls where you can
enter/not enter criteria for a report.
"Filter by form" is a function to filter a "bound" form's recordset by
selecting criteria values in the bound form fields, and applying that
filter.
It would not apply to your unbound dialog form, or have anything to do
with the clearing of those fields.

Just...
Me.[BySname] = ""
Me.[ByTown] = ""
Me.[ByPCode] = ""
should clear those values on the dialog form.

Also, your query criteria should be...
Like [Forms]![testform]![BySname] & "*"
for all three fields. That will allow a "" value in any criteria to
return all values for that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I've created a simple form to locate members of a database by town,
surname or postcode. I've used Microsoft's Query by Form suggestion, so I
have three unbound text boxes, [BySname], [ByTown] and [ByPCode]. Then
I've used a command button to run a macro which runs the Query which has
as its criteria in the three relevant fields
[Forms]![testform]![BySname]or [Forms]![testform]![BySname]Is Null, etc.

What I want to do is to be able to clear all three fields rather than
having to use the text tool to highlight then delete the contents of the
text boxes on the form. I've tried creating a command button using the
wizard's 'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the
button a message comes up that says: The command or action 'refresh'
isn't available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would be
much appreciated.

Cheers
James

 

[Allen Browne]

While still in the code window, choose Compile from the Debug menu. Access
will highlight the row it does not understand.

If Access can't find the thing, perhaps its name is different, e.g. the name
of the text box might be Text0, or By Sname (i.e. with a space), or ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Like I said, Allen, I'm a newbie so forgive, but where does this code go?
I tried clicking on Build Event for the command button and on the opened
window, I put:

Private Sub Command12_Click()
Me.BySname = Null
Me.ByTown = Null
Me.ByPCode = Null
Me.FilterOn = False

End Sub

But when I try to use the button, it gives an error message that says:
"Compile error: method or data member not found."

I'm afraid you'll have to tell me in very simple abc terms until I can get
more used to everything!

Thanks in advance.
James

Just set the 3 unbound text boxes to Null, and turn off the forms filter:

Me.BySname = Null
Me.ByTown = Null
Me.ByPCode = Null
Me.FilterOn = False

I've created a simple form to locate members of a database by town,
surname or postcode. I've used Microsoft's Query by Form suggestion, so
I have three unbound text boxes, [BySname], [ByTown] and [ByPCode]. Then
I've used a command button to run a macro which runs the Query which has
as its criteria in the three relevant fields
[Forms]![testform]![BySname]or [Forms]![testform]![BySname]Is Null, etc.

What I want to do is to be able to clear all three fields rather than
having to use the text tool to highlight then delete the contents of the
text boxes on the form. I've tried creating a command button using the
wizard's 'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the
button a message comes up that says: The command or action 'refresh'
isn't available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would
be much appreciated.

 

[Fred Bloggs]

Hi Al
My question about enter/carriage return -- I figured it out. I just set the
Default property of the command button to Yes.

Thanks again
James

Al, this works brilliantly!
I noticed that when I used Microsoft's code in the query, if I went back
to the design view after a few moments, it was putting other 'Null' and
various bits of code all over the place, creating new fields, etc. but
your is clean!

One other question: is there any way once I've typed into one of the
fields, that I can make it search by using the enter/return key on the
keyboard rather than having to do the OnClick on the command button?

Thanks so much for your help.
James

Fred,
I take it that you have a dialog form with 3 text controls where you
can enter/not enter criteria for a report.
"Filter by form" is a function to filter a "bound" form's recordset by
selecting criteria values in the bound form fields, and applying that
filter.
It would not apply to your unbound dialog form, or have anything to do
with the clearing of those fields.

Just...
Me.[BySname] = ""
Me.[ByTown] = ""
Me.[ByPCode] = ""
should clear those values on the dialog form.

Also, your query criteria should be...
Like [Forms]![testform]![BySname] & "*"
for all three fields. That will allow a "" value in any criteria to
return all values for that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I've created a simple form to locate members of a database by town,
surname or postcode. I've used Microsoft's Query by Form suggestion, so
I have three unbound text boxes, [BySname], [ByTown] and [ByPCode]. Then
I've used a command button to run a macro which runs the Query which has
as its criteria in the three relevant fields
[Forms]![testform]![BySname]or [Forms]![testform]![BySname]Is Null, etc.

What I want to do is to be able to clear all three fields rather than
having to use the text tool to highlight then delete the contents of the
text boxes on the form. I've tried creating a command button using the
wizard's 'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the
button a message comes up that says: The command or action 'refresh'
isn't available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would
be much appreciated.

Cheers
James

 

[Al Camp]

Fred,
OK... I usually don't include form buttons in my TabStops or TabOrder.
Some users don't realize they have tabbed to a button, and an Enter key
(they really should be using Tab) will trip the Click event code.
Purely a matter of personal style...
If your solution works for you... good deal.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi Al
My question about enter/carriage return -- I figured it out. I just set
the Default property of the command button to Yes.

Thanks again
James

Al, this works brilliantly!
I noticed that when I used Microsoft's code in the query, if I went back
to the design view after a few moments, it was putting other 'Null' and
various bits of code all over the place, creating new fields, etc. but
your is clean!

One other question: is there any way once I've typed into one of the
fields, that I can make it search by using the enter/return key on the
keyboard rather than having to do the OnClick on the command button?

Thanks so much for your help.
James

Fred,
I take it that you have a dialog form with 3 text controls where you
can enter/not enter criteria for a report.
"Filter by form" is a function to filter a "bound" form's recordset by
selecting criteria values in the bound form fields, and applying that
filter.
It would not apply to your unbound dialog form, or have anything to do
with the clearing of those fields.

Just...
Me.[BySname] = ""
Me.[ByTown] = ""
Me.[ByPCode] = ""
should clear those values on the dialog form.

Also, your query criteria should be...
Like [Forms]![testform]![BySname] & "*"
for all three fields. That will allow a "" value in any criteria to
return all values for that field.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


I've created a simple form to locate members of a database by town,
surname or postcode. I've used Microsoft's Query by Form suggestion, so
I have three unbound text boxes, [BySname], [ByTown] and [ByPCode].
Then I've used a command button to run a macro which runs the Query
which has as its criteria in the three relevant fields
[Forms]![testform]![BySname]or [Forms]![testform]![BySname]Is Null,
etc.

What I want to do is to be able to clear all three fields rather than
having to use the text tool to highlight then delete the contents of
the text boxes on the form. I've tried creating a command button using
the wizard's 'refresh' option, and also building an event which says:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Me.Filter = ""
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, acFilterByForm,
acMenuVer70

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

which I found in one of the forums, but in both cases, if I click the
button a message comes up that says: The command or action 'refresh'
isn't available now.

I'm sure clearing fields on a query by form should be simple, but as a
newbie, and not knowing VBA, I can't figure it out, so your help would
be much appreciated.

Cheers
James