Newbie Forms Programmer Question(s)

[Tim Rogers]

I'm a little confused about the Access Forms paradigm.

1) Is a form supposed to be specifically connected only to a
particular table or query? It appears this might be the case since I
have to supply one table or query when I want to create a new form.

Here is what I am trying to do: I have a form that I have created and
it is tied to a table called Humans. I also want to put a Text Box on
the form that will contain a field from a table called Groups. There
is a field in Humans that is a foreign key to the Groups table. The
Groups table has a primary key (long) field and a Description field.
I am trying to display the Groups description field for the record
that is associated with the currently displayed Humans record. I
attempted to create a SQL statement that would provide me with that
information, but I keep getting a #Name? in the Text Box.

I assume what I am trying to do is possible. The latest incarnation
of my query is:

select DISTINCTROW Groups.Description FROM Groups WHERE Groups.GroupId
= [TestForm]![GroupId].Text

2) Do forms always have to have those controls on the bottom that
allow you to iterate through the records in the table/query that the
form represents? I guess I'm a little confused because it doesn't
appear that you can/should create a blank form, create the fields that
you want, and push/pull data to/from the tables as you please like you
would do with MFC, ASP, etc and a separate database like SQL Server.
(It just appears that forms and the tables/queries are so tightly
coupled.) I'm sure you can achieve this, it just doesn't appear
that's part of the normal Access forms paradigm. Maybe I am wrong.


If someone would please point me in the right direction on these two
points, I would appreciate it.

Thanks,

Tim Rogers

 

[Me]

When you first press the button to create a form (New), it
gives you several options, one is design view. There is a
combo box at the bottom as you've said, with all of the
tables in there, but you do not have to select one. Click
New - > Pick Design View. This will start you with an
Unbound form, no textboxes...nothing.

So...
1 )

Create a new form. Open it's Properties.
Click on the box where you would enter a RecordSource
(it's on the Data tab). A little button with the
caption "..." on it appears to the right of the box.
Click on that.
Access asks if you would like to invoke the query builder.
Click Yes.
A box appears with the tables available in your DB for the
query. Double click: Humans and Groups.
Click close.
The query window will be open.
it should have a visual image of your 2 tables. Double
click on every field from each table that you want to see
on the form.
Close the query down when you are satisfied. Click yes to
save.
You should be back at the form now.
In the menu, click View, and select Field List. This will
show all the fields returned from the query.

Drag them onto your form, and that's it.

2) The forms always start with the "Navigation buttons".
It is a property of the form though. Bring up the form's
properties, and change the property "Navigation Buttons"
to "No". This will remove the buttons you are seeing

Basically, as soon as you click new and select the table
you want the form based on, you have told access to create
a default form for you based on some table. Make sure to
just select New, then Design...don't pick a table to base
it on. I create very few forms that are based on one table
alone, and many that aren't based on any.

Good luck, hope this helps

-----Original Message-----
I'm a little confused about the Access Forms paradigm.

1) Is a form supposed to be specifically connected only to a
particular table or query? It appears this might be the case since I
have to supply one table or query when I want to create a new form.

Here is what I am trying to do: I have a form that I have created and
it is tied to a table called Humans. I also want to put a Text Box on
the form that will contain a field from a table called Groups. There
is a field in Humans that is a foreign key to the Groups table. The
Groups table has a primary key (long) field and a Description field.
I am trying to display the Groups description field for the record
that is associated with the currently displayed Humans record. I
attempted to create a SQL statement that would provide me with that
information, but I keep getting a #Name? in the Text Box.

I assume what I am trying to do is possible. The latest incarnation
of my query is:

select DISTINCTROW Groups.Description FROM Groups WHERE Groups.GroupId
= [TestForm]![GroupId].Text

2) Do forms always have to have those controls on the bottom that
allow you to iterate through the records in the table/query that the
form represents? I guess I'm a little confused because it doesn't
appear that you can/should create a blank form, create the fields that
you want, and push/pull data to/from the tables as you please like you
would do with MFC, ASP, etc and a separate database like SQL Server.
(It just appears that forms and the tables/queries are so tightly
coupled.) I'm sure you can achieve this, it just doesn't appear
that's part of the normal Access forms paradigm. Maybe I am wrong.


If someone would please point me in the right direction on these two
points, I would appreciate it.

Thanks,

Tim Rogers
.

 

[Albert D. Kallal]

I'm a little confused about the Access Forms paradigm.

1) Is a form supposed to be specifically connected only to a
particular table or query? It appears this might be the case since I
have to supply one table or query when I want to create a new form.

Yes, the above is generally the case. However, often for just general UI
stuff like ask the person questions, selecting what report to view, and host
of other UI stuff, then the form can be un-bound. That simply means that you
do NOT have attach a form to a table, or a query. Often, since the form is
NOT for editing data, then you can turn off virtually all of the extra
editing stuff like the scroll bars, navigation bar/buttons at the bottom,
and the record selectors. There is a ton of settings in the forms property
sheet that you want to become familiar with. So, no, forms are not always
bound to a table. These forms are generally what we call un-bound forms, and
generally are not used for editing data. here is some nice screen shots of
forms make JUST to launch reports in ms-access, and thus, they are NOT forms
bound to a table, and nor are they for editing.

Check out:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

In addition, sometimes forms are used for displaying data, but again NOT for
editing. Often, these forms are bound. Here is some screen shots of using
ms-access forms to replace "grids" in VB. This is a fabulous feature of
ms-access. Check out:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

I also want to put a Text Box on
the form that will contain a field from a table called Groups.

You can base a form on sql, but NOT a individual text box. However, since
looking up values is so common, you can place a text box on the form and
then simply use the dlookup() function to grab that text description based
on your id

So, the expression for the data source of that ext box can be:

=(dlookup("field name","table Name","condition"))

So, in your case, you could use:

=(dlookup("GroupsDescription","Groups","GroupId = " & GroupId))

Of course, you will replace "groupID" with whatever is the key id in table
groups (it looks like in your example that both field are the same name).
And, you will replace Groupsid with whatever the current field in the form
is used to "look", or relate to that groups table.

Also note, we use the controlname.Value in ms-access, and NOT
contorlname.Text as you do in VB. The .text is ONLY available while the
control has focus, and thus is generally not much good use. So, use .Value,
or just the default (by default, don't place any property of the control).
So, you get

forms!FormName!ContorlName

And, you don't have to specify the .value, but you can go:

forms!FormName!ContorlName.Value

In addition, you can base a form on a query that joins in those lookup
values, but really depends on what kind of lookup you need. If you need to
pull in MORE then one value from that other SAME table, then you can use a
sub-form also. Check out the following:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html

You *really* need to understand how sub-forms work in ms-access!

2) Do forms always have to have those controls on the bottom that
allow you to iterate through the records in the table/query that the
form represents?

No, you can disable and hide all of that stuff in the propriety sheet.

I guess I'm a little confused because it doesn't
appear that you can/should create a blank form, create the fields that
you want, and push/pull data to/from the tables as you please like you
would do with MFC, ASP, etc and a separate database like SQL Server.

Ms-access does all the work for you. In most applications that are
business/data centric, ms-access will produce software at LEST 3 times the
rate of given tools like VB. This means that 3 months of work in ms-access
generally translates into 1 year of work on these other platforms. So,
ms-access is true rapid application development environment, and you can
generally do about $40,000 of programming work on a $10,l00 budget.

(It just appears that forms and the tables/queries are so tightly
coupled.) I'm sure you can achieve this, it just doesn't appear
that's part of the normal Access forms paradigm. Maybe I am wrong.

You can use un-bound forms, and load up the controls with code, allow the
user to edit, and then write the data back using even more code. Then again
why bother when using ms-access as you don't have to do all the work?

Remember, you will only get the productivity out of ms-access AFTER you lean
how the forms objects work. If you try and think in your "old" way, then you
will find ms-access will NOT work for you. You need to go through a paradigm
shift here.

For some tips and ideas on what I went through in converting application to
ms-access, you can read the following:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000003.html

 

[Marshall Barton]

Here is what I am trying to do: I have a form that I have created and
it is tied to a table called Humans. I also want to put a Text Box on
the form that will contain a field from a table called Groups. There
is a field in Humans that is a foreign key to the Groups table. The
Groups table has a primary key (long) field and a Description field.
I am trying to display the Groups description field for the record
that is associated with the currently displayed Humans record. I
attempted to create a SQL statement that would provide me with that
information, but I keep getting a #Name? in the Text Box.

select DISTINCTROW Groups.Description FROM Groups WHERE Groups.GroupId
= [TestForm]![GroupId].Text

In addition to what others have said, instead of using a
text box for the group, you can use a combo box (drop down
list). Note that a combo box can display one field
(description) while it stores a different field (GroupID),
so it is ideal for automatically looking up values in other
tables. Just set the combo box's RowSource property to the
simple query:
SELECT GroupId, Description
FROM Groups
ORDER BY Description

Then set its ColumnCount to 2, BoundColumn to 1 and its
ColumnWidths to 0;2

 

[John]

Application Generator for MS SQL Server

Hi Tim,

I have written an application generator that allows developers to
build business applications for MS SQL Server in just a fraction of
the time it used to take. All you need to do is create the tables and
answer a few yes or no questions about each table. The generator does
the rest.

Please see attached files for screen shots and more information.

In order to demonstrate the my generator to developers, I am issuing a
promotion:

The Promotion will cost you nothing. All you have to do is send me the
TSQL scripts necessary to create your tables and to define the indexes
and foreign key relationships or I can create them for you. My
generator will do the job, and I will deliver the finished and fully
functional UI.exe along with a finished and fully functional SQL
Server back end which contains a robust security system and a
comprehensive data dictionary, completely free of charge.

The benefits of this system are as follows:
1. The system generates true Client Server and Multi Tier
applications.
2. There is Zero Coding for common functionality such as;
Adds, Updates, and Deletes,
Creating application wide unique Ids for each record,
Maintaining an Audit trail for each table,
Posting to the general ledger,
Rolling down changes to dependent tables,
Cascading deletes,
Transactions and rollbacks,
Calling validation and business rule stored procedures,
Calling post processing stored procedures,
Importing and revalidating data,
Security,
Spell checking, language translation and more.
3. There is zero work of any kind for generation of data entry screens
and their lookups.
4. The back end is completely independent from the front end. You can
hit the database with any application or user interface and still be
sure that you have complete security and valid data.
5. Easy navigation through out the application. The generated user
interface is a familiar modern metaphor with a navigation tree on top
or at the side and data entry screens at the bottom. Also, the
generated user interface remembers customizations to each data entry
screen. This allows you to make sweeping changes to the interface, and
regenerate all data entry screens, without loosing your
customizations.
6. Consistent look and feel via OOP Inheritance and code generators.
7. Major changes in look and functionality are made in one place only,
and ripple down to all affected parts of the system without programmer
intervention. Again, this was accomplished with OOP inheritance and
also with code generators.
8. Users to have the ability to create queries and reports on the fly.
And the ability to save and reload those queries and reports in many
formats including Excel and HTML.
9. Users to have complete flexibility in customizing the look and feel
of the system. The extent to which each user can customize the
interface must be seen to be believed. This high level of
customizability creates a high degree of user acceptance.
10. Logical use of hot keys and local popup menus allow for easy mouse
free operation, permitting the user to keep his or her hands on the
keyboard, if the user so desires.
11. All custom code added to generated data entry screens and
generated backend code persists after regeneration.
12. Comes with a business rule generator
13. Comes with a data import utility
14. Comes with a data revalidation utility to use when you change your
business rules.
Get all this without programming.
If you need to quickly build feature rich, bug free business
applications for MS SQL Server, then please call (201 665 8906) or
write to (e-mail address removed)