Addings records to a query

[Chad]

Sorry if my lingo is wrong, I don't use access very much.

We have a form. On this form we have a button. This button opens a query.
That is it.

The first column in the query is Truck#. When this button is pressed I want
the query to open, and then ask for 2 truck numbers (the first in the series
and the last in the series). Once the user does this, it will create a
record for each of the trucks in the series.

I have excel vba knowledge, so I can do the msgboxes and get the truck
numbers, I just need to know how to create new records at the end of the
query.

Thanks
Chad

 

[tina]

you can add records to a *table* using an Append query (queries are
"windows" into tables; all data is stored in tables), or by opening a
recordset in code. the specifics of how to add x number of records based on
a variable number range provided at runtime, depends on the specifics of the
table the records will be stored in, etc. my guess is that you'll end up
needing to use some sort of looping code on a recordset, but suggest you
start by reading up on Append queries and practicing a few on a *copy* of
your database, so you understand how they work (it's good basic Access
knowledge you'll use elsewhere, even if not in this instance). also suggest
you do not open a query directly - it's almost always a bad idea in a user
interface; instead, bind the query to a form and, after appending the new
records, requery the form's RecordSource.

hth

 

[Chad]

thanks,

that is where I went wrong. I needed to deal with the table not the query.

Chad

 

[Chad]

I see you mentioned that we shouldn't open the query directly. The problem
is, we run querys to filter our data (get rid of columns that are not needed
for the current instance) and then enter in data. I think we do it just
because it is easier. I am assuming it is not good practice to do this,
because we could easily wipe out a bunch of data.

As I said earlier, I am not an access wiz, and neither are the people that
use the database, so I really don't want to change it at this point. But I
will keep your comments in mind when I revamp the database.

Chad

 

[Steve Schapel]

Chad,

I agree with Tina that it is not good practice to allow users access to
query datasheets during real-time usage of the database, and the same
applies to tables. This is not their purpose. The appropriate database
object to use for accessing the data, for entry, editing, viewing, is a
Form. So in your example, certainly use your query to sort and filter
data, and manipulate columns, etc -- and then use the query as the basis
of a form, which you use to interact with the data.

 

[tina]

that's up to you, hon. but bear in mind that when it comes to working
directly with raw data, Access is very unforgiving - unlike Excel, or the
other Office Suite software. changing or deleting data is done immediately,
and if it's wrong it's wrong - "sorry about your bad luck". the *less*
familiar with Access your users are, the *more* you have to protect the data
from their mistakes. you can do that when you control the data entry in a
form, but you've no hope of doing it in an open Select query or an open
table.

hth