Forms,Queries,Macros..

[Reez]

This is the first time I use access. What do forms do, I mean on the software, when I create a form, it seems just like a record fromt he table so what's the point? And what are queries?
Another thing is suppose I want this database at teh end to be used be a group of users, but I only want them to be able to enter values and extract values and nothing more. How can I do that?

Alos, if I want a user to be able to choose what columns from what tables under certain conditions ( i mean like select) but I don't want them to write codes. Is there a certain interface that can be used?

thank you,

 

[Kiwiman]

Howzit - and welcome to the forum

Just a couple of thoughts...

What do forms do, I mean on the software, when I create a form, it seems just like a record fromt he table so what's the point?

Forms do represent the data that is in the table - if the form is bound to a table. In an end user environemnt, only the forms should be available to the users for data entry \ record review - they should not have access to the tables themsleves.

And what are queries?

There are a number of different types of queries

• Select Query - used to return specified fields in a table(s) depending on your requirements and can be used as a forms record source, a reports recordsource, copy externally...
• Action Queries - Update data in a table, make tables, delete data add data
• others - I don't user these very often

So if your users whant only to see field a,b,c and D from table1, instead of all the other fields, you could use a query to get the information.

Another thing is suppose I want this database at teh end to be used be a group of users, but I only want them to be able to enter values and extract values and nothing more. How can I do that?

You need to read on splitting the database - which means having a back end (tables) and a front end (forms,queries, macros, modules) - there is a tool to do this on one of accesses menu bars.

Preventing users from doing anything else? I'm not an expert on this, but possibly turning the front end into an MDE file, as opposed to a mdb file - again a tool to do this, will prevent changes to the front end. SOmeone esle with more knowledge thatn me on this can possible comment

Alos, if I want a user to be able to choose what columns from what tables under certain conditions ( i mean like select) but I don't want them to write codes. Is there a certain interface that can be used?

It will all be about database design that will enable this. Proper planning to determine which criteria will trigger which forms and controls will be available for data entry, will help in the vba coding

 

[Reez]

Thanks alot,
but I still don't get one thing, you're saying I can create forms that users can see and enter values in and so on. So does that mean I have to create forms for every possible combination of columns just incase the user wants to view specified columns? THAT is a MESS, I mean that would take forever. I dont know exactly what data does the user want to see, how can i allow a user to use a select code and make it much easier, I mean he has access to a table and he wants to select for example the names of the contacts that reside in Alaska, can he do that using a select code?

Long question, I know, but bare with me please.
Thanks again

 

[Kiwiman]

Howzit

I am not saying build a form for each possible combination, that wouldn't be workable.

Using your example - You may have a from that contains all your contacts - which may reside in different states \ cities \ countries etc

You can apply filters to forms to limit the number of records returned to say just the Alaskan contacts. One way of doing this is by getting the user to select the state Alaska from a drop down box, which will then filter the form accordingly - and only show the contacts of that state.

In your database design you will have to determine what the users want to filter the data on and build it accordingly. I generally apply filters to just indexed fields to make it quicker. I don't apply filters to free text fields normally

 

[Reez]

thank you,

the alaska thing was just an example, i am considering i have no clue what the user would like to filter. So it also wouldn't be workable that I create a drop down box for every category, because i really don't know what the suer would want.

But this filter option, is there a way a user can have access to it entirely.

I think this could help make my case clear, this link has an SQL demo, u see, in this case, I'm the user and I can select and filter, but this is an SQL option and it needs a code. how can that be done with access http://www.w3schools.com/sql/sql_tryit.asp

 

[Kiwiman]

Howzit

At some point you will need to define what the users will want. You can then build the queries based on these requirements.

I would not build a db, and have a query where user 1 wants, CustNo - Custname, user 2 wants CustNo - CustName - Address, user3 wants CustNo - Address, user4 wants CustNo - CustName - State, so on and so forth

I would build jut the one query with all the relevant information and the user can use whatever information they require from it, and ignore the rest.

There may very well be a way to build these SQL type queries on the fly - but who will do it? The end users? - it is my experience that normal users do not understand SQL and will not be able to build a SQL stmt from scratch. End users pretty much like clicking a button and out pops the results.

I have never needed to do this approach - I'm not saying it can't be done.

You can build a form which can run multiple queries (either having multiple command buttons or an option group and one command button - there will be other options I'm sure) depending on what the users select (what query to run) - but you will first need to build the underlying queries first.

You can also add paramters to your queries - not on every field, just the key fields - governed by what outputs are required.

One of the questions I ask when designing a db is "What outputs are required?". Without this question being answered - what is the point. It is fine entereing data into a database, but if you can't use the data, it is not much use to anyone.

 

[Rabbie]

While agreeing with Kiwiman that you should clarify the requirements at the design stage I have written in the past a form which enabled users to select Values and Fields for a query and then built the query on the fly in VBA. It took a bit of time to implement but it did do the job.

It can be useful for a salesman to see just their own figures and even to see the figures broken down by region.

As said you have to know what the users are going to get out of the DB

 

[Mike375]

Think of tables a warehouse or store room.

Think of queries, forms, macros and modules as the retail outlets for the business.

Any data base that only contained tables would be extremely limited in what could be done.

You can have an almost infinite number of search or select or display criteria and the person using the data base does not need to know a thing about queries, macros, code or modules.

I mean he has access to a table and he wants to select for example the names of the contacts that reside in Alaska, can he do that using a select code?

In that example you might have a drop down list (combo box) on a form and the person using the data base would select Alaska from the list of states and when "clicked" the data base would then display what is required that related to Alaska. You can also have more than one selection criteria. For example you might select all the customers who have made one purchase and who live in Alaska etc and etc.

Forms not only serve to display the data in the most readable format they also carry the actions that run code or macro and queries. In other words anything that is "clicked on" will be done on a form. Forms also allow the data base to do things based on the data that is displayed and without the person using the data base doing a single thing exvept for going to the record.

Virtually every post on this forum that involves queries, macros, code, modules will be for something that is run from a form.

Lastly, you could also think of tables as the foundations of a house. The foundations untimately determine what sort of house can be built but once the house is built no one sees or "uses" the foundations.