Tables and Forms

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

Just post your question. I'm usually here. If not, there are a lot of
knowledgable people here who can help as well as I can.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

Oh, just one other note.
The technique of using Selet * should really only be used when you want all
(or the vast majority) of the fields in your table on the form. Otherwise,
specify the field names. The reason is, the less data you have to move
accross the network, the better.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

Ok, just one last question on this. How can I do a query on a record that I
used a checkbox for? For Example: I want to run a query on just the one's
that have checkmarks, or vise versa...no checkmarks.

Thanks again.

"Klatuu" wrote:

Oh, just one other note.
The technique of using Selet * should really only be used when you want all
(or the vast majority) of the fields in your table on the form. Otherwise,
specify the field names. The reason is, the less data you have to move
accross the network, the better.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

What will you do with this query?
Explain a bit more about what you want to do, please.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok, just one last question on this. How can I do a query on a record that I
used a checkbox for? For Example: I want to run a query on just the one's
that have checkmarks, or vise versa...no checkmarks.

Thanks again.

"Klatuu" wrote:

Oh, just one other note.
The technique of using Selet * should really only be used when you want all
(or the vast majority) of the fields in your table on the form. Otherwise,
specify the field names. The reason is, the less data you have to move
accross the network, the better.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

Sure. Just to break it down I created a fieldtwo fields using a checkbox.

The checkbox will be checked if we have it in our files but left unchecked
if we don't have it in our files.

If I want to run a query on just the data that we DON'T have in our files
(unchecked) how will I be able to do that. What do I need to input onto the
criteria?

I hope this made sense. Thank you.
Mary Ann

"Klatuu" wrote:

What will you do with this query?
Explain a bit more about what you want to do, please.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok, just one last question on this. How can I do a query on a record that I
used a checkbox for? For Example: I want to run a query on just the one's
that have checkmarks, or vise versa...no checkmarks.

Thanks again.

"Klatuu" wrote:

Oh, just one other note.
The technique of using Selet * should really only be used when you want all
(or the vast majority) of the fields in your table on the form. Otherwise,
specify the field names. The reason is, the less data you have to move
accross the network, the better.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK

Just to break it down I created a fieldtwo fields using a checkbox.

Are you saying you created one field or two fields?
To do what you are asking, you really need only 1 field.

You need 1 field that is a Yes/No (boolean) data type in your table.
You need 1 checkbox control on your form so you can check or uncheck it.
(see, the query is paying off already)
Create a new query in design view
Select your table
Select the fields you want to see. Include the new Yes/No field
in the criteria for that field type True
run the query

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Sure. Just to break it down I created a fieldtwo fields using a checkbox.

The checkbox will be checked if we have it in our files but left unchecked
if we don't have it in our files.

If I want to run a query on just the data that we DON'T have in our files
(unchecked) how will I be able to do that. What do I need to input onto the
criteria?

I hope this made sense. Thank you.
Mary Ann

"Klatuu" wrote:

What will you do with this query?
Explain a bit more about what you want to do, please.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok, just one last question on this. How can I do a query on a record that I
used a checkbox for? For Example: I want to run a query on just the one's
that have checkmarks, or vise versa...no checkmarks.

Thanks again.

"Klatuu" wrote:

Oh, just one other note.
The technique of using Selet * should really only be used when you want all
(or the vast majority) of the fields in your table on the form. Otherwise,
specify the field names. The reason is, the less data you have to move
accross the network, the better.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Oh my goodness Dave! You just saved me a whole bunch of time. I changed the
record source of my form to go to the query. I've been playing with it and it
works great. Is there a way to contact you on here just in case I need help
in my new adventures with Access?

"Klatuu" wrote:

No, just open the form in design mode
Open the properties dialog box
Select form in the drop down at the top of the dialog
Select the Data Tab
You will see the name of the table in the Record Source property.
In place of the table name type in:
SELECT * FROM MyTableName ORDER BY [Fund Name]

Just change MyTableName to the actual name of the table.
Now save it. That is all there is too it.

Now, if you change the definition of the table, you don't need to change the
query for the table. To put the new field on your form, just open the form
in design view, add a text box (or whatever kind of control you want for the
data) and put the name of the new field in the Control Source property of the
text box. And, of course, change the caption in the label.

We lazy people get very good at coming up with easy ways to do things
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Fort Worth? That'll be quite a commute from Chicago! I used to travel to
Texas a lot when I was in the Air Force.

Dave, is there a way that I can take my current form that's pulling it's
records from the table and have it pull from the query that I just made? Or
am I going to have to start all over and make a brand new form. I'd hate to
do this cause it took me forever to format my current form.

Mary Ann

"Klatuu" wrote:

Thanks, Mary Ann.
I do have a spare room, but unless you live near Fort Worth, Texas, the
commute may be a bit much

I am usually out here, but if not, there are many people who can help and a
lot of them know more than I do in some areas. We all have our strengths.
Good luck with Access.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
You are great! No wonder you are MVP. I wish I can stay in touch with you.
I'm going to try to create the generic query.

Mary Ann

"Klatuu" wrote:

Yes, provided you order the query by that field.
Actually, it is not a bad idea to always use queries for your forms. One
thing I usually do is create a very generic query so that if there are
changes to the definition of the table, you don't have to modify the form's
record source:
SELECT * FROM MyTable ORDER BY [Fund Name];

You can just type that into the Row Source property of your form in desing
view.

One other thing about naming objects.
1. Use only Letters, Numbers, and the under score _
2. Do not use Spaces or any other characters except the under score
3. Do not use any Access reserved words (Date, Time, Year, Month,
Description, etc).
4. Always include at least 1 under score character so you don't run the risk
of accidently using a reserved word.

As to other object and varialbe naming in Access, this is a good resource.
It wil ensure you don't use a reseved word and it will make it very clear
what kind of object you are working with.
http://msdn.microsoft.com/archive/de...n_20naming.asp

--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Ok I will try doing this. This is my first Access database that I'm
attempting to create so your assistance is greatly appreciated, especially
since I am trying to introduce my office to the database.

By creating a query and using it for my form's record source, will this
allow for each time the form is opened to automatically view the records
alphabetically by the "Fund Name"?


"Klatuu" wrote:

You have two choices.
Rather than use the table directly as the form's record source, create a
query and order the query by the field you want. (I would do it this way)
Or, you can use the OrderBy and OrderByOn properties of the form.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Dave-
Thanks for the quick response. I caught what happened. On my table, I was
trying to correspond the AutoNumber with the amount of records. I deleted
some of the records from the table which caused that AutoNumber to delete.

Is there a way that I can sort the records on my form to where it will sort
it by a specific field in ascending order? I hope that made sense.
For Example: I have a field called "Fund Name" and I want to sort the form
by the "Fund Name"

Thanks again.

"Klatuu" wrote:

The first thing to do is forget about that number that shows in the form. It
only shows the relative position of the current record in the form's
recordset. If you filter your form, the number of records will change. If
you reorder your form, each record will show a differnt number. There are no
record numbers in Access.

Forms and Tables are not one in the same. A table is an object that
contains a collection of records and each record contains a collection of
fields. Each field contains a value. A forms is only a way to view and
manipulate the data in a table or query. The table or query that provides
the data to a form is identified in the form's Record Source property. A
form contains a collection of controls. The controls can be used to
manipulate the view of data, provide navigation through the data, allow other
actions to be taken, or they can contain data. To contain data a control is
bound to a field in the form's recordset.

It appears that some records in your table are being filtered out of the
form. Why, I can't say. It could be there is some filtering applied that
you are not aware of.

See if you can identify the records not included in the form and see what is
different about them.
--
Dave Hargis, Microsoft Access MVP


"tt_Gyrl" wrote:

Hi Guys-
I think this is so awesome how everyone helps eachother here. I hope I can
get an answer to a question I have.

I created a form using a table I created. I assume that the from and the
table are one in the same, meaning I can add a new record onto the table and
it will automatically also appear on the form and vice versa. This seems to
be right since I do see my new record, which I added via the table, on the
form.

This is where I get confused. On the table there is a total of 220 records
but when I go to my form, there are only 216 records. Why?

I go to record 216 on the form and it matches record 220 on the table. This
confuses me because should the record number on the table match the record
number on the form since they are suppose to correspond with eachother? This
occurs throughout the whole table/form. EX: record 143 on my table is
actually record 141 on my form.

Thanks,
MK