user interface decision

  • Thread starter Thread starter mcleester
  • Start date Start date
M

mcleester

I have set up a database in access. I am now trying to make a decision about
how to make the user interface to it. My choices are between doing it in
Excel and doing it in Access. I am more comfortable in Excel, but I am
wondering if there are benefits to doing the VBA/forms in Access.

I am also wondering if I create the UI in Excel, can someone without Access
on their machine then use it? Because that would be a benefit for using
Excel. Any guidance is appreciated.
 
If the Excel UI is using an Access database, you will need Access installed
or the Access Runtime.

I would suggest you use Access. If you can write Excel macros using VBA,
the transition to Access wont be that hard.
 
Thanks for the suggestion. What are the advantages of using Access (so I can
try to implement them)? Is there a good form to use to let users choose from
existing values from a field (and also multiple select)?
 
You can use a combo box to allow users to select from existing values in a
field.
You do that with a combo box. Set the combo's row source to a query that
gets unique values in the field with a query:

SELECT DISTINCT [SomeField] FROM SomeTable;

This will give you a list of one occurance of each value currently found in
the field.

Can you give more details about your multiselect question?
 
That couldn't be easier! Much easier to tie it to a query then to take
everything out of a Excel ComboBox and then add it back in through a
recordset after every possible refresh.

As for the multi-select, I have normalized the database, but I don't want
the user to know about it. So I want them to be able to do something like
assign multiple authors to the same book in one action. So they see all
available authors as they're adding a new book. They can choose to put
attach two of them to the book they are adding (and it will update their
bridge table as well -- I can handle that SQL, just wondering about the
controls).

Thanks!

Klatuu said:
You can use a combo box to allow users to select from existing values in a
field.
You do that with a combo box. Set the combo's row source to a query that
gets unique values in the field with a query:

SELECT DISTINCT [SomeField] FROM SomeTable;

This will give you a list of one occurance of each value currently found in
the field.

Can you give more details about your multiselect question?
--
Dave Hargis, Microsoft Access MVP


mcleester said:
Thanks for the suggestion. What are the advantages of using Access (so I can
try to implement them)? Is there a good form to use to let users choose from
existing values from a field (and also multiple select)?
 
If you are going to allow multiple authors to a book, you will need an
addtional table to relate the book to the authors. (I know there is a
multivalue field type in 2007, but I have not worked with it, so I can't give
advice on it. In any case, I would avoid using it if I ever thought I might
upsize the application.)
It would need to carry the primary key of the book and the author's name, or
if you have a table of authors, it would carry the primary keys of both.

Then, to select the authors, use a list box with the multi select property
set to simple or extended (I prefer extended, it is most like other multi
select object is Microsoft apps where you can use shift or control keys to
quickly make multipe selections.) Then you need to read in VBA Help
regarding the Items Selected property so you can return the selected values.
It will require some VBA code to use the selected values to add records to
the table I mentioned earlier.
--
Dave Hargis, Microsoft Access MVP


mcleester said:
That couldn't be easier! Much easier to tie it to a query then to take
everything out of a Excel ComboBox and then add it back in through a
recordset after every possible refresh.

As for the multi-select, I have normalized the database, but I don't want
the user to know about it. So I want them to be able to do something like
assign multiple authors to the same book in one action. So they see all
available authors as they're adding a new book. They can choose to put
attach two of them to the book they are adding (and it will update their
bridge table as well -- I can handle that SQL, just wondering about the
controls).

Thanks!

Klatuu said:
You can use a combo box to allow users to select from existing values in a
field.
You do that with a combo box. Set the combo's row source to a query that
gets unique values in the field with a query:

SELECT DISTINCT [SomeField] FROM SomeTable;

This will give you a list of one occurance of each value currently found in
the field.

Can you give more details about your multiselect question?
--
Dave Hargis, Microsoft Access MVP


mcleester said:
Thanks for the suggestion. What are the advantages of using Access (so I can
try to implement them)? Is there a good form to use to let users choose from
existing values from a field (and also multiple select)?

:

If the Excel UI is using an Access database, you will need Access installed
or the Access Runtime.

I would suggest you use Access. If you can write Excel macros using VBA,
the transition to Access wont be that hard.
--
Dave Hargis, Microsoft Access MVP


:

I have set up a database in access. I am now trying to make a decision about
how to make the user interface to it. My choices are between doing it in
Excel and doing it in Access. I am more comfortable in Excel, but I am
wondering if there are benefits to doing the VBA/forms in Access.

I am also wondering if I create the UI in Excel, can someone without Access
on their machine then use it? Because that would be a benefit for using
Excel. Any guidance is appreciated.
 
I have set up a database inaccess. I am now trying to make a decision about
how to make the user interface to it. My choices are between doing it inExceland doing it inAccess. I am more comfortable inExcel, but I am
wondering if there are benefits to doing theVBA/forms inAccess.

I am also wondering if I create the UI inExcel, can someone withoutAccess
on their machine then use it? Because that would be a benefit for usingExcel. Any guidance is appreciated.

Hi
You do not need Access installed in case you want to use Excel and
just want Excel application to get and store data in Access tables
including running Access queries. All you need is DAO to be installed.
This is installed when you install a Data Access package from
Microsoft. ( I do not remember exactly the name of the package).
You need to have Access installed only if you want to do Access
automation from your Excel application. In most situations this is
never required.
Alok
 
Back
Top