Datagrid in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have a customer who has a database that they use almost entirely in
queries views; I recently made some upgrades for them, with new entry forms,
and a new easier way to get to and manage the queries. In the process, in
order to relate different aspects of their business, I also added some
additional relationships in Access, which aren't really anything all that
special, aside from a many-to-many in a couple of places.

The side effect that I did not see coming, however, was that when you create
a multi-relational query, it's data is not editable in query view! Now they
must leave the query, and find the record in the forms to edit it -- not good.

I would settle for a "double click" event on the query view, but I don't
think there is one, so I tried continuous forms, but then the users cannot
resize the columns!

Being a college student learning .net, I discovered the grid control, but
the problem with that is that the control itself is not on very many
computers, and I don't know how to redistribute it.

At least using the double-click event, or a button, I could send the user to
the correct place to edit the particular record they want to edit.

Anybody have some solutions or ideas? I don't know why access won't let me
edit those queries, but I've tried a million different combinations trying to
get around it, and I'm kind of hitting a wall. I could even link out to a
VB.Net application, but I'm sure that's overkill, there's GOT to be something
that I'm missing here!

Any help and/or ideas would be greatly appreciated!!!!

--Jon Jaques
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming that you've created look-up tables that hold attributes for
the "main" records. E.g.: The main record holds personal info: name,
sex, DOB, Language Spoken, etc. There is a look up table named
Languages that holds an ID and the language name. In the form for the
personal info you have a ComboBox for the languages attribute. In the
main record that attribute will be stored as a number, but the user will
select the language name from a ComboBox. When you run a query on that
table you've JOINed the personal table to the Languages table so the
user will see the language in human-readable form instead of the number.

Instead of joining the tables together you can just open the personal
table and create ComboBoxes on the Language column. Here's how:

Open the query in design view; select the columns you want into the
design grid. In the Language column -

1. right-click & select Properties
2. Click the Lookup tab
3. Select "Combo Box" in the Display Control field
3. Set the following:
Row Source Type: Table/Query
Row Source: Select LanguageID, LanguageName FROM Languages

ORDER BY LanguageName -- or something like this
Column Count: 2
Column Widths: 0";1.5" -- this hides the LanguageID

When the query runs you will get the language names in the LanguageID
column instead of numbers. Also, you can change the column value by
selecting the appropriate language name from the column's ComboBox.

Since the query has only one table it will be an updateable query.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQaFJuYechKqOuFEgEQJu4QCgziOy4D1RIFT1qHhFNBd/hecieYMAoMug
zRCO21otny9hA+bI5Is2fGh0
=32Tt
-----END PGP SIGNATURE-----
 
Hello, thank you for the reply! I see where you're going with that, but
unfortunately, I need to pull from 3 and sometimes 4 tables. For instance,
they are tracking group tours -- each tour has one or more teachers assigned
to it, and may have one or more busses assigned as well. A given query that
needs to be edited would have the tour title, the teachers' name, and
several fields to help them remember to verify and reverify all
confirmations, which is business critical for them.

Looking at this given query tells them all bus confirmations which are not
yet completed -- they may then go down the list, calling the bus companies
entering data as they collect it. But only if the query is editable, and it
is not!

I've tried using code to edit these data sets, and there is no problem --
it's only a limitation of the built-in access query viewer/grid?

Hmmm, I do see one or two of my queries that can be simplified using your
idea of combo boxes in the queries, though. That is a help.

I'm at a loss, though, why Access will not edit a complex query that *is*
editable, and where is the Flex Grid?

Thanks again,

--Jon
 
Hmm, no, in practice, setting the field type to a combo box did not work for
me, but I *was* able to make use of DLookup in a couple of places; This
works, for now, even though I found specific advice reccomending not to use
it in my situation (in a query) because of increased network traffic.
 
Back
Top