Code Help Please

[golfinray]

I am using this code to tell users that there is no record for that school
district:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found on that district", vbOKCancel
Cancel = True
That works great but if there is no record for that district the form just
sits there blank after they push ok. How do I return back to the form to
allow them to select a different district? Thanks so much!!!!

 

[Dorian]

--
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dorian]

You are missing an 'End If' statement and also you need to capture the return
from the Msgbox and take different action depending on whether they clicked
OK or Cancel.
-- Dorian.
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[golfinray]

I have the end if in there, I just need to return to the previous recod so
that my form is not blank. I have a combo filter for selecting the district
and if there is no record for that district I just want them to be able to
return to make another selection. Instead the form just sits there blank.
Thanks!!!

 

[Al Campagna]

golfinray,
How is the user selecting which District to filter on?
We could use more detail as to how this procedure is run,
beginning with how the user initiates this procedure, and any
code associated with this procedure.

It would be best to provide the user a combobox of Districts
that have record counts > 0, rather than allowing them to select
ones that don't.
Better to code to prevent a problem, than code to correct a problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[golfinray]

We have 255 school districts. About 200 have 2009-2011 projects. The
construction managers look up the detailed records on each district from a
filter combo I put on the form. I use on the afterupdate event:
Me.filter = "[district name] = """ & me.combo20 & """"
me.filteron=true
to find the record of the project.
Then I use on the onclick of the combo:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found on that district", vbOKCancel
Cancel = True
To see if there are records on that district. If there are not then it gives
the message. That works fine, but after it gives them the message the blank
form sits there. I want to just return to form so that they can make another
selection of a district that does have records. Thanks!!!!!
--
Milton Purdy
ACCESS
State of Arkansas

golfinray,
How is the user selecting which District to filter on?
We could use more detail as to how this procedure is run,
beginning with how the user initiates this procedure, and any
code associated with this procedure.

It would be best to provide the user a combobox of Districts
that have record counts > 0, rather than allowing them to select
ones that don't.
Better to code to prevent a problem, than code to correct a problem.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Al Campagna]

golfinray,
What I'm saying is that your District selection combo should have
already filtered out any districts that have no records.
The query behind the RowSource of the District combo (a Totals query)
should filter out districts that have 0 "Count" records.

Since your current filter method returns no records, Access sends you
to a blank New record.
So... you should be able to do a
DoCmd.GoToRecord , , acLast
or wherever you want to go.
But, I'll have to stick to my suggestion, as a better way to go.

Also, the OnClick event of your combo has no Cancel option, so Cancel =
True isn't needed.
Actually, a better event to trigger your code would be the AfterUpdate
event, not OnClick.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

We have 255 school districts. About 200 have 2009-2011 projects. The
construction managers look up the detailed records on each district from a
filter combo I put on the form. I use on the afterupdate event:
Me.filter = "[district name] = """ & me.combo20 & """"
me.filteron=true
to find the record of the project.
Then I use on the onclick of the combo:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found on that district", vbOKCancel
Cancel = True
To see if there are records on that district. If there are not then it
gives
the message. That works fine, but after it gives them the message the
blank
form sits there. I want to just return to form so that they can make
another
selection of a district that does have records. Thanks!!!!!