To: Albert D. Kallal

[Laurel]

Today you've addressed two of my mysteries with some degree of depth. Would
you mind looking "Repost: Yet another problem with 'No current record'", in
Access.formscoding, dated August 26? I was just about to take a vacation day
to go to my local Access Users Group for their clinic, to see if anyone
there could solve it.

tia

las

 

[Albert D. Kallal]

I think the first problem is that you are "clearing" the form by using a
data source that "hopefully" resolves to no records being returned.

I think you would be better off to have the form un-bound from the start.

Further, in place of a setting some text box value, and then doing a
requery, you be better to go:

Me.RecordSource = ""
Me.Refresh

The above would thus ensue no records are returned. I mean really, the error
message of

no current record

Really actually does mean what it says!!..there is not a current record.

further,I am guessing that you have the form based on a sql query, and that
query has parameters that look back to the form.? (is this correct?).

What happens when *some* of the text boxes are NOT filled out..how does the
sql manage this?

Further, enabling, or disabling controls will NOT be reflected in the sql
use for the form.

This just all gets quite messy when you have controls on a form that are
referenced by sql.

In summary:

To blank out the form, don't set the sql query to return no records, but
simply REMOVE the datasocue of the form
eg:

Me.RecordSource = ""
Me.Refresh

Further, get rid of that sql that is full of forms parameters. They are hard
to use, cause pain, don't work well when you leave some of the values out,
are ugly to read, are not standard sql.

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.


At this point, we then open the report, or form as follows

docmd.OpenReprot "myRerpot",acViewPreview,,strWhere

Or, if you are in a form, and want to requery with the new sql, then

me.RecordSource = "select * from tblCustomers where " & strWhere

 

[Laurel]

Thanks!

I think the first problem is that you are "clearing" the form by using a
data source that "hopefully" resolves to no records being returned.

I think you would be better off to have the form un-bound from the start.

Further, in place of a setting some text box value, and then doing a
requery, you be better to go:

Me.RecordSource = ""
Me.Refresh

The above would thus ensue no records are returned. I mean really, the
error message of

no current record

Really actually does mean what it says!!..there is not a current record.

further,I am guessing that you have the form based on a sql query, and
that query has parameters that look back to the form.? (is this correct?).

What happens when *some* of the text boxes are NOT filled out..how does
the sql manage this?

Further, enabling, or disabling controls will NOT be reflected in the sql
use for the form.

This just all gets quite messy when you have controls on a form that are
referenced by sql.

In summary:

To blank out the form, don't set the sql query to return no records, but
simply REMOVE the datasocue of the form
eg:

Me.RecordSource = ""
Me.Refresh

Further, get rid of that sql that is full of forms parameters. They are
hard to use, cause pain, don't work well when you leave some of the values
out, are ugly to read, are not standard sql.

As you are finding out, putting forms! expression in queries can get
really messy real fast.

Even worse, is now that the query is now "married" and attached to that
ONE form. Often, I have a nice query that I could use MANY times for
different reports, and often even that same query could be used for
reports...but then someone comes along and puts in a expression that means
the query is ONLY good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the
other 2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly,
and hard to read. and, the sql then is not standard anymore (it will not
work with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the
reports (or forms), and attached them to the query, BUT NO FORMS!
conditions are placed in the query.

To "send" the conditions to the report (or form), you simply use the
"where" clause. This is exactly why ms-access has this feature...and it
solves a zillion problems...and will reduce your development costs by a
substantial amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more
conditions you want. Lets say we have a check box to only include Special
Customers. We can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny
bit more code eliminates the messy query problem.. For me, this is very
worth while trade.


At this point, we then open the report, or form as follows

docmd.OpenReprot "myRerpot",acViewPreview,,strWhere

Or, if you are in a form, and want to requery with the new sql, then

me.RecordSource = "select * from tblCustomers where " & strWhere

 

[Laurel]

1- If I replyce Me.Requery with Me.RecordSource = ""/Me.Refresh, it works,
but I get an empty row in my form with #Name? in each of the items. I tried
setting Me.AllowAdditions = False at the same time. Didn't help.

2 - (Perhapsh this should be #1). I'm not sure I understand what you mean
when you say I should have my form unbound from the start. If you're simply
talking about referencing txtboxes on the form in the SQL statement, then
that seems a simple thing to accomplish (keep building a new RecordSource
and assigning it to Me.RecordSource as needed). But is there more to it
than that? Something, perhaps, that would prevent the phenomenon described
in #1?

I think the first problem is that you are "clearing" the form by using a
data source that "hopefully" resolves to no records being returned.

I think you would be better off to have the form un-bound from the start.

Further, in place of a setting some text box value, and then doing a
requery, you be better to go:

Me.RecordSource = ""
Me.Refresh

The above would thus ensue no records are returned. I mean really, the
error message of

no current record

Really actually does mean what it says!!..there is not a current record.

further,I am guessing that you have the form based on a sql query, and
that query has parameters that look back to the form.? (is this correct?).

What happens when *some* of the text boxes are NOT filled out..how does
the sql manage this?

Further, enabling, or disabling controls will NOT be reflected in the sql
use for the form.

This just all gets quite messy when you have controls on a form that are
referenced by sql.

In summary:

To blank out the form, don't set the sql query to return no records, but
simply REMOVE the datasocue of the form
eg:

Me.RecordSource = ""
Me.Refresh

Further, get rid of that sql that is full of forms parameters. They are
hard to use, cause pain, don't work well when you leave some of the values
out, are ugly to read, are not standard sql.

As you are finding out, putting forms! expression in queries can get
really messy real fast.

Even worse, is now that the query is now "married" and attached to that
ONE form. Often, I have a nice query that I could use MANY times for
different reports, and often even that same query could be used for
reports...but then someone comes along and puts in a expression that means
the query is ONLY good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the
other 2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly,
and hard to read. and, the sql then is not standard anymore (it will not
work with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the
reports (or forms), and attached them to the query, BUT NO FORMS!
conditions are placed in the query.

To "send" the conditions to the report (or form), you simply use the
"where" clause. This is exactly why ms-access has this feature...and it
solves a zillion problems...and will reduce your development costs by a
substantial amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more
conditions you want. Lets say we have a check box to only include Special
Customers. We can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny
bit more code eliminates the messy query problem.. For me, this is very
worth while trade.


At this point, we then open the report, or form as follows

docmd.OpenReprot "myRerpot",acViewPreview,,strWhere

Or, if you are in a form, and want to requery with the new sql, then

me.RecordSource = "select * from tblCustomers where " & strWhere

 

[Albert D. Kallal]

1- If I replyce Me.Requery with Me.RecordSource = ""/Me.Refresh, it works,
but I get an empty row in my form with #Name? in each of the items. I
tried setting Me.AllowAdditions = False at the same time. Didn't help.

Yes, you do get that empty row. You can either live with that, or better
simply build a routine that can set/unset the fields to visible or not
(that what I often do). Another approach I used in the past is place all of
the result fields on a sub-form, and then you can simply set the sub-form
control to visible = false when you clear out the search. (the sub-form just
lets you easy group the data/display fields together).

2 - (Perhapsh this should be #1). I'm not sure I understand what you mean
when you say I should have my form unbound from the start. If you're
simply talking about referencing txtboxes on the form in the SQL
statement, then that seems a simple thing to accomplish (keep building a
new RecordSource and assigning it to Me.RecordSource as needed). But is
there more to it than that? Something, perhaps, that would prevent the
phenomenon described in #1?

No, I was talking about the form being un-bound. There is little, if any
reason to have the form attached to data until you actually set the filter,
or data source of the form. I would actually move the form with data to a
sub-form, as then you will find this works a bit more clean. You "search
button" (or after update event of each un-bound text box control) can then
simply call a routine that builds the sql, and then sets the sub-forms data
source:

me.mysubFormName.Form.ReocrdSource = strMySql

And, if you use a sub-form, then you can still use your approach of setting
the data source that resolves to a record that does NOT exist as you been
doing. However, I find the visible setting/idea works just as well in this
case....