Data entry into table field.

  • Thread starter Thread starter FBrnstrmr
  • Start date Start date
A Query from which a Combo Box or Listbox gets its data from.
Click to expand...

Or a SQL statement. Or a table name. Or a query name.

[]
and

A Query which provides the data for a Form.
Click to expand...

Or a SQL statement. Or a table name. Or a query name.

In both cases, it's actually not advisable to use just a table name
as your rowsource/recordsource. It's always better to use a saved
query or a SQL statement, even if that SQL statement is only:

SELECT MyTable.* FROM MyTable
 
I like to use tables for RecordSource if the data is stored in
Access and it is not necessary to filter the records or calculate
anything
Click to expand...

I think this is bad advice. For instance, if you base a combo box on
a table, and then replicate the back end, you'll end up seeing the
replication fields in your combo box, because Jet replication
inserts the replication tracking fields at the top of your table
design.

Secondly, if your combo box is based on a table with more fields
than you want to display, you have to set a bunch of colum widths to
0 just to keep them from displaying. If you use a SQL statement, you
retrieve only the fields that are needed.

I would say that in all cases, SQL statements (or the name of a
saved query) is superior, and would recommend against every using a
plain table name as rowsource or recordsource.
 
one reason I tell others to use tables is to keep them from
creating a recordset that cannot be updated
Click to expand...

Can you explain what you mean by that? First off, in a combo box,
it's not relevant. Secondly, in a form's recordsource, you end up
with the most inefficient data retrieval possible. There is no
possible way that this recordsource/rowsource:

MyTable

will behave any differently in regards to editability than:

SELECT MyTable.* FROM MyTable

They are exactly equivalent. And the latter is always, in my
opinion, superior.
 
Fortunately My questions were answered before I read this as I only find it
confusing with my limited terminological experience. Hopefully that won't
come back to haunt me. Thanks very much to Crystal and John, my backwards
approach was necessary as when I started this project I was trying to match
an imported table which is regularly changed out for a newer version with the
requirements for my division which already was using a word form requiring
the data entry everytime an inspection occurs. My present form is not as I
require yet but it is a whole lot better than we were using before. That is
after 2 months of trying to learn VB6 and 2007version access,word and other
Office tools all in the same time frame. So again, my thanks to Crystal Long
and John Vinson whose patience I tried and tested as they ruly are capable
people. Now I am going to try to figure out how to improve my form to carry
the same location and date information at the top while adding various lines
of data to up to 10 new records all on the same form.
Any takers?? Thanks again, and sorry for abruptly leaving last night but it
was actually 2:00am my time and I had had enough for 1 night. Thanks again.
 
Hi David,

with all due respect, a combo uses a ROW SOURCE, not a RecordSource ...

RecordSource is only for the data that feeds forms and reports -- and
when it comes to a report, it does not matter if the RecordSource is
not updateable...

sorry if I was not clear. Once someone is proficient with Access, they
can deviate from this general guideline, of course. But -- if you are
new, and base forms directly on tables (if the table is Access, then
there is no performance hit as long as the table doesn't have jillions
of records), then you can alleviate a lot of problems.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
with all due respect, a combo uses a ROW SOURCE, not a
RecordSource ...
Click to expand...

And...what? My reply addressed some of the problems with rowsources,
but I said that the objection applies to both rowsources and
recordsources.
RecordSource is only for the data that feeds forms and reports --
and when it comes to a report, it does not matter if the
RecordSource is not updateable...
Click to expand...

And this has exactly what to do with anything? Updatability is
something that is irrelevant when you're comparing:

MyTable

as recordsource vs.

SELECT MyTable.* FROM MyTable

Both are going to be equally updatable.
sorry if I was not clear. Once someone is proficient with Access,
they can deviate from this general guideline, of course. But --
if you are new, and base forms directly on tables (if the table is
Access, then there is no performance hit as long as the table
doesn't have jillions of records), then you can alleviate a lot of
problems.
Click to expand...

I strongly disagree. Learning to create a basic SQL
recordsource/rowsource is so fundamental to Access that if you can't
do it, you are likely to be creating all sorts of bad problems.

I have always felt that it would be better if Access disallowed
table names/query names as recordsource/rowsource. Access could
easily have a wizard that wrote the equivalent SQL by pointing at a
table/query.

I just don't think it serves anyone's long-term interests to
recommend shortcuts that are not advisable in the long run. I
wouldn't recommend that anyone use macros just because they are
easy, precisely because they become more and more problematic the
more you use them. I see your suggestion as the same kind of
short-sighted recommendation -- it may be easy, but it's still a bad
way to do things in the long run.
 
Hi David,

this is an opinion. I have been using Access for 15+ years and
databases for more than 25. Some of the databases I work on have
hundreds of tables and thousands of records, so I am not wet behind the
ears. I am also a degreed engineer.

I do a lot of one-on-one and classroom training and find it best to make
the recommendations that I do when someone is a beginner. As they learn
more, they can do things differently.

There is nothing wrong with using a Table as the RecordSource in a form
if the data is stored in Access and there are not tens of thousands of
records in it. You have a different opinion -- that is fine -- but
making a statement like, "I see your suggestion as the same kind of
short-sighted recommendation -- it may be easy, but it's still a bad
way to do things in the long run" just serves to confuse posters and I,
obviously, totally disagree.

If you would like to argue, please email me personally (my address is in
the post header) and I will be happy to listen to logical reasons why
you think it is bad -- If you convince me (I am open to changing my mind
if I am given logical reasons that are better than the logic I use to
come to a different conclusion), then I will change what I tell others.
I certainly do not wish to mislead anyone.

And, David, with all due respect, the public newsgroup is not an
appropriate place for the type of remarks you are making.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
And, David, with all due respect, the public newsgroup is not an
appropriate place for the type of remarks you are making.
Click to expand...

I don't see anything at all inappropriate in anything I've posted
here.

You disagree with my position, which is fine. I've simply stated my
opposing position.

If you don't like the tone or the rhetorical voice, then, well, I
don't know what to say, other than that you need to get a thicker
skin.
 
Hi David,

"need to get a thicker skin"

that is true, but it probably won't happen at this stage in my life --
which is why I usually stay out of non-moderated forums

btw, I have respect for your knowledge and it is well-known that you
have a brilliant mind ... but you can also be unnecessarily rough

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
btw, I have respect for your knowledge and it is well-known that
you have a brilliant mind ... but you can also be unnecessarily
rough
Click to expand...

Goes with the territory.
 
Back
Top