Hi Pete,
Learn how to use Forms! And a little VBA programming too
http://www.utteraccess.com/forums/showflat...p;Number=377430
the crystal_code module in that example has been re-written with more comments. You can find it here
basic VBA procedures
http://www.utteraccess.com/forums/showthre...;Number=1036232
Full Version: Okay Im at a loss:
Steps for Documenting a database
http://www.utteraccess.com/forums/showflat...p;Number=753657
(about 10 screens down in flat mode)
http://www.utteraccess.com/forums/showflat...p;Number=753657
(about 10 screens down in flat mode)
Hi Pete
you absolutely should not feel this way -- your questions, and responses have been great! It is a true pleasure to be your guide...
There are hundreds of others who have read and will read this thread ... your very methodical approach is helping them learn too.
QUOTE
I feel silly for requiring this much hand-holding, but this isnt my main area of expertise
you absolutely should not feel this way -- your questions, and responses have been great! It is a true pleasure to be your guide...
There are hundreds of others who have read and will read this thread ... your very methodical approach is helping them learn too.
Thanks Crystal, this weekend Im going to poke around with the coding and whatnot, try and get a better feel for this.
In the sample form you referenced (the one you use to show layouts for forms) you mention code 'behind' the form. I see there are TWO module pages, but Im not sure what other code you might be referring to.
Doing a screen dump of the form IS helpful, as I can get the bosslady's input as to how we might want to have information involved.
Oh and I notice the tables you use for that example are very... flat? Was that by design?
Have a great weekend!
In the sample form you referenced (the one you use to show layouts for forms) you mention code 'behind' the form. I see there are TWO module pages, but Im not sure what other code you might be referring to.
Doing a screen dump of the form IS helpful, as I can get the bosslady's input as to how we might want to have information involved.
Oh and I notice the tables you use for that example are very... flat? Was that by design?
Have a great weekend!
thanks, Pete
******* Code Behind the Form (CBF) *******
when you are in the form design, choose View, Code from the menu
you can also click on the View Code icon (I call it the "code bracelet" because that is what it looks like...a bracelet with rhinestones in the corners)
this will take you to the code behind the form -- different than a general module in that code behind the form is stored with the form and is specific to the form
******* TeachForm database *******
flat? you mean using bland grays? Or do you mean data structure? The structure is not the best as it could be normalized more, but it is something everybody understands. I threw in fields you wouldn't use such as Send Christmas Card, next contact date, password, etc just to get examples for different types of controls and properties.
******* Code Behind the Form (CBF) *******
when you are in the form design, choose View, Code from the menu
you can also click on the View Code icon (I call it the "code bracelet" because that is what it looks like...a bracelet with rhinestones in the corners)
this will take you to the code behind the form -- different than a general module in that code behind the form is stored with the form and is specific to the form
******* TeachForm database *******
flat? you mean using bland grays? Or do you mean data structure? The structure is not the best as it could be normalized more, but it is something everybody understands. I threw in fields you wouldn't use such as Send Christmas Card, next contact date, password, etc just to get examples for different types of controls and properties.
Morning Crystal
Yeah I was talking about the normalization
Oh and the Xmas card field is somewhat useful too oddly enough, as we have a Jehova's Witness volunteer, so for reference we could have something with 'send holiday cards' and leave it unchecked for her (and there are a few more volunteers who would rather not get cards either)
Okay more code-reading and tweaking the db... As I come across difficulties I'll be sure to bring em up.
Yeah I was talking about the normalization
Oh and the Xmas card field is somewhat useful too oddly enough, as we have a Jehova's Witness volunteer, so for reference we could have something with 'send holiday cards' and leave it unchecked for her (and there are a few more volunteers who would rather not get cards either)
Okay more code-reading and tweaking the db... As I come across difficulties I'll be sure to bring em up
.
Well Im sitting here with a bunch of notes (yeah my whole desk gets littered with small diagrams and whatnot when Im working like this ) and I just want to 'think out loud' as it were (with some input from you):
Basically we need three things: To be able to enter 'people', to be able to assign Clients to the Volunteers, and to enter the times the Volunteers spend with the Clients.
Looking over the potential data pool, I see that a lot of the "people" we are tracking are emergency contacts for the Clients and Volunteers. Im still mulling over the best way to enter the emergency contacts (and how best to have them 'attach' to the clients/volunteers). It'd be nice to have the form collect information based on the specific type of person you are entering... Im sure thats possible, but Id need to have a bit more of these skills under my belt first I see how the form you provided as a reference applies (in glmpses, it comes and goes), and I think I have a good starting point to at least start figuring out.
The connecting people is interesting, I defintiely want to make it so that you cant assign (by accident) a volunteer to a volunteer, client to a client or ADS, etc. That doesnt strike me as TOO severly complicated (in theory, according to how Im visualizing it).
The hours and work thing is another matter entirely. Ive got a consensus that overall people (in the office) would prefer it if you could put in the end of the time period ("cycle ending X date"), then it would bring up each volunteer and on a subform it would show their clients, then you could enter the number of hours during that period they spent with each client, and on that form also enter whatever benefit time (vacation) and emergency days and such they took.
If you have any thoughts or suggestions, by all means jump in and suggest away This is turning out to be one [censored] of a project, but the challenge is entertaining at least!
Back to scribbling...
) and I just want to 'think out loud' as it were (with some input from you):Basically we need three things: To be able to enter 'people', to be able to assign Clients to the Volunteers, and to enter the times the Volunteers spend with the Clients.
Looking over the potential data pool, I see that a lot of the "people" we are tracking are emergency contacts for the Clients and Volunteers. Im still mulling over the best way to enter the emergency contacts (and how best to have them 'attach' to the clients/volunteers). It'd be nice to have the form collect information based on the specific type of person you are entering... Im sure thats possible, but Id need to have a bit more of these skills under my belt first
I see how the form you provided as a reference applies (in glmpses, it comes and goes), and I think I have a good starting point to at least start figuring out.The connecting people is interesting, I defintiely want to make it so that you cant assign (by accident) a volunteer to a volunteer, client to a client or ADS, etc. That doesnt strike me as TOO severly complicated (in theory, according to how Im visualizing it).
The hours and work thing is another matter entirely. Ive got a consensus that overall people (in the office) would prefer it if you could put in the end of the time period ("cycle ending X date"), then it would bring up each volunteer and on a subform it would show their clients, then you could enter the number of hours during that period they spent with each client, and on that form also enter whatever benefit time (vacation) and emergency days and such they took.
If you have any thoughts or suggestions, by all means jump in and suggest away
This is turning out to be one [censored] of a project, but the challenge is entertaining at least!Back to scribbling...
Hi Pete,
Good morning
how about a combobox that limits the SQL for the combo to pick the assignment? you could set the default value to what you would expect it to be...
making combobox sql
http://www.utteraccess.com/forums/showflat...p;Number=722735
you can modify this structure that I previously suggested:
VolTime -- you may want to change this to something more generic like TimeLog with PID_worker, PID_client -- or something like that
VTimeID< Autonumber
VolID, long integer
PID, long integer
TimeIn, date -- date/time of visit or appt
TimeOut, date -- date/time visit end -- or you can store Length of visit in minutes or hours
Good morning
QUOTE
It'd be nice to have the form collect information based on the specific type of person you are entering
how about a combobox that limits the SQL for the combo to pick the assignment? you could set the default value to what you would expect it to be...
making combobox sql
http://www.utteraccess.com/forums/showflat...p;Number=722735
QUOTE
The hours and work thing...
you can modify this structure that I previously suggested:
VolTime -- you may want to change this to something more generic like TimeLog with PID_worker, PID_client -- or something like that
VTimeID< Autonumber
VolID, long integer
PID, long integer
TimeIn, date -- date/time of visit or appt
TimeOut, date -- date/time visit end -- or you can store Length of visit in minutes or hours
Hi Crystal,
Took a few days away from dealing with this (and enjoyed the nicer weather).
Since we are mostly concerned with tracking hours across a date range (rather than by individual dates), Im curious as to how you'd work that (setting up the range of dates I mean).
As far as the grant is written, we only have to concern ourselves with number of hours in a given pay period and, ideally, the number of visits; the hours would be averaged across the number of visits for reporting purposes (which we've been told is acceptable for reporting/auditing purposes).
My goal is to have the form require the minimal amount of data entry as possible. Currently with the Excel spreadsheet we can plug in the number of hours they do irrespective of the actual clients they see. The problem arises when we have to report out how many hours are spent with a 'respite' client as opposed to a 'hospisce' client. Thus the neccesity (in my view anyways) of a database.
As I become more familiar with how the coding end of it works, and the forms, Im seeing this project taking a good shape At least its not FRUSTRATING (just daunting)
Took a few days away from dealing with this (and enjoyed the nicer weather).
Since we are mostly concerned with tracking hours across a date range (rather than by individual dates), Im curious as to how you'd work that (setting up the range of dates I mean).
As far as the grant is written, we only have to concern ourselves with number of hours in a given pay period and, ideally, the number of visits; the hours would be averaged across the number of visits for reporting purposes (which we've been told is acceptable for reporting/auditing purposes).
My goal is to have the form require the minimal amount of data entry as possible. Currently with the Excel spreadsheet we can plug in the number of hours they do irrespective of the actual clients they see. The problem arises when we have to report out how many hours are spent with a 'respite' client as opposed to a 'hospisce' client. Thus the neccesity (in my view anyways) of a database.
As I become more familiar with how the coding end of it works, and the forms, Im seeing this project taking a good shape
At least its not FRUSTRATING (just daunting)
Hi Pete,
Sounds like you are starting to see how huge the scope of your project is... you do have a complex application.
This is not accurate:
"tracking hours across a date range"
You will track (store) hours according to the day they were expended and REPORT hours across a date range
Just as you calculate PayPeriod as a grouping, you could do the same for your date ranges. Access makes it very easy to group by pieces such as Year and Month
Year(DateExpression) --> ie: 2006
Month(DateExpression) --> 1 to 12
you can also use Year and Month in one grouping by doing this:
Format(DateExpression,"yyyymm")
the Format function is quite handy and has a lot of options for dates.
to get more help on the format function, type FORMAT into the debug window (CTRL-G) and press F1 on the last character
You can also easily group by Quarter using the DatePart function, which is also very handy to know about.
DatePart("q",DateExpression) --> 1 to 4
Sounds like you are starting to see how huge the scope of your project is... you do have a complex application.
This is not accurate:
"tracking hours across a date range"
You will track (store) hours according to the day they were expended and REPORT hours across a date range
Just as you calculate PayPeriod as a grouping, you could do the same for your date ranges. Access makes it very easy to group by pieces such as Year and Month
Year(DateExpression) --> ie: 2006
Month(DateExpression) --> 1 to 12
you can also use Year and Month in one grouping by doing this:
Format(DateExpression,"yyyymm")
the Format function is quite handy and has a lot of options for dates.
to get more help on the format function, type FORMAT into the debug window (CTRL-G) and press F1 on the last character
You can also easily group by Quarter using the DatePart function, which is also very handy to know about.
DatePart("q",DateExpression) --> 1 to 4
Mornin Crystal,
While I see what you are saying, what would be the most efficient way of inputting the volunteer's hours for storing? Something Im trying to avoid is having to add a substantial amount of data entry to the process. Anything to make the process easier to enter data into would be keen.
I always knew the project wouldnt be EASY per se, so this is only reinforcing what I suspected early on. I dont think my boss knew what she was getting me into tho Eh, I have until the end of the summer, Im sure I can have it done by then.
While I see what you are saying, what would be the most efficient way of inputting the volunteer's hours for storing? Something Im trying to avoid is having to add a substantial amount of data entry to the process. Anything to make the process easier to enter data into would be keen.
I always knew the project wouldnt be EASY per se, so this is only reinforcing what I suspected early on. I dont think my boss knew what she was getting me into tho
Eh, I have until the end of the summer, Im sure I can have it done by then.
Hi Pete,
I would suggest a (possibly continuous?) form with an unbound date at the top
Whatever date is chosen acts as a filter for the form, so all records on that date show
on the BeforeInsert event, make sure the date at the top is filled out (if not, CANCEL the event and undo the control) -- if so, copy that date to an invisible control on the record and continue making a new record
Then, on the record, enter the person with a combo or listbox, their location with a combo or listbox, how long they were there, and any other fields you will need ... not really seeing the complexity in this... I did outline what I thought would be good for storing volunteer time in an earlier post on this thread and, so far, I have not changed my mind on how it should be stored.
VolTime
VTimeID< Autonumber
PID_Vol, long integer --> volunteer's PID
PID_client, long integer --> client's PID
TimeIn, date -- date/time of visit or appt
TimeOut, date -- date/time visit end -- or you can store Length of visit in minutes or hours
Depending on how you look at the time, you could substitute the date in the form header for another piece of information such as the volunteer od the location. Or you could have Date and volunteer at the top of the form ... just depends on what works best for you.
Here is a link for a Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
I would suggest a (possibly continuous?) form with an unbound date at the top
Whatever date is chosen acts as a filter for the form, so all records on that date show
on the BeforeInsert event, make sure the date at the top is filled out (if not, CANCEL the event and undo the control) -- if so, copy that date to an invisible control on the record and continue making a new record
Then, on the record, enter the person with a combo or listbox, their location with a combo or listbox, how long they were there, and any other fields you will need ... not really seeing the complexity in this... I did outline what I thought would be good for storing volunteer time in an earlier post on this thread and, so far, I have not changed my mind on how it should be stored.
VolTime
VTimeID< Autonumber
PID_Vol, long integer --> volunteer's PID
PID_client, long integer --> client's PID
TimeIn, date -- date/time of visit or appt
TimeOut, date -- date/time visit end -- or you can store Length of visit in minutes or hours
Depending on how you look at the time, you could substitute the date in the form header for another piece of information such as the volunteer od the location. Or you could have Date and volunteer at the top of the form ... just depends on what works best for you.
Here is a link for a Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
*sigh* was sick most of last week... now have to get back into the 'swing of things'. May be a day or two before Im back in the groove.
I hate being sidelined by illness... messed my headspace ALL up *grrrrr*
I hate being sidelined by illness... messed my headspace ALL up *grrrrr*
Hi Pete,
I was wondering what happened to you ... hope you are feeling better soon. Try eating just FRESH fruit
(and only one kind at a time, don't mix), it works miracles.
I was wondering what happened to you ... hope you are feeling better soon. Try eating just FRESH fruit
(and only one kind at a time, don't mix), it works miracles.
Ive noticed, after a brief scrounge through the site, that there doesnt seem to be an actual FORMS tutorial.
Is the best way to set up a form to use the *shudder* wizard?
I was curious about the actual 'starting from scratch' part of this... I wanted to play a bit with data and see what might happen (whilst I digest more coding stuff).
I see lots of 'how to adjust" and subform stuff... but if you know of something offhand that would point me to a good 'step-one' kind of thing that would be keen.
Thanks Crystal. (oh and Im actually feeling tons better, getting a bit of sun and fresh air yesterday worked wonders... It was a particularly tenacious stomach bug... (and I was eating bananas and mangoes... that resolved itself nicely)
Is the best way to set up a form to use the *shudder* wizard?
I was curious about the actual 'starting from scratch' part of this... I wanted to play a bit with data and see what might happen (whilst I digest more coding stuff).
I see lots of 'how to adjust" and subform stuff... but if you know of something offhand that would point me to a good 'step-one' kind of thing that would be keen.
Thanks Crystal. (oh and Im actually feeling tons better, getting a bit of sun and fresh air yesterday worked wonders... It was a particularly tenacious stomach bug... (and I was eating bananas and mangoes... that resolved itself nicely
)
Hi Pete,
I am not a fan of using Wizards, so I find it best to design a form from scratch.
1. make a new form, go into the Design View
2. display the form Header/Footer
from the menu --> View, Form Header/Footer
3. turn on the Properties window
from the menu --> View, Properties
4. choose a RecordSource for the form
if the Properties titlebar does not say "Form", then select the form by clicking in the upper left corner where the rulers intersect
click on the Data tab of the properties window
click in the RecordSource property, drop the list, and pick something. Personally, I base forms directly on tables. This may not be a good idea for huge databases lots of users and back-ends on Oracle, Sybase, etc, but for DBs on PC's, I find that it works just fine.
5. turn on the Fieldlist display
from the menu --> View, Field List
Then, drag fields from the list to the form
As you move the control, the label moves too.
to just move ONE control, drag it by the big box in the upper left corner, when the mouse pointer becomes a pointing finger.
I usually use the Property sheet to size controls (Width, Height) after I have dragged them because I like to pick easy-to-work with numbers.
In the form header, in addition to a title, I put one or more unbound comboboxes to find records.
I like to make a private function behind the form and have several lookups -- or one lookup and change the lookup SQL based on an option group selection so the user has more than one way to find the record -- in any case, the bound column is always a hidden ID, so it can use the same code (I am posting a simple version of my FindRecord code)
AfterUpdate --> =FindRecord()
(you can also use the BeforeUpdate event)
as for tutorials, here is one... I haven't read it in detail, but it seems pretty good; you can do a search for more
http://www.softlookup.com/tutorial/access/ch06.asp
Did you study the sample form I pointed you to on 3/22? Did you do the Steps to Document a Database on it?
I am not a fan of using Wizards, so I find it best to design a form from scratch.
1. make a new form, go into the Design View
2. display the form Header/Footer
from the menu --> View, Form Header/Footer
3. turn on the Properties window
from the menu --> View, Properties
4. choose a RecordSource for the form
if the Properties titlebar does not say "Form", then select the form by clicking in the upper left corner where the rulers intersect
click on the Data tab of the properties window
click in the RecordSource property, drop the list, and pick something. Personally, I base forms directly on tables. This may not be a good idea for huge databases lots of users and back-ends on Oracle, Sybase, etc, but for DBs on PC's, I find that it works just fine.
5. turn on the Fieldlist display
from the menu --> View, Field List
Then, drag fields from the list to the form
As you move the control, the label moves too.
to just move ONE control, drag it by the big box in the upper left corner, when the mouse pointer becomes a pointing finger.
I usually use the Property sheet to size controls (Width, Height) after I have dragged them because I like to pick easy-to-work with numbers.
In the form header, in addition to a title, I put one or more unbound comboboxes to find records.
I like to make a private function behind the form and have several lookups -- or one lookup and change the lookup SQL based on an option group selection so the user has more than one way to find the record -- in any case, the bound column is always a hidden ID, so it can use the same code (I am posting a simple version of my FindRecord code)
AfterUpdate --> =FindRecord()
(you can also use the BeforeUpdate event)
CODE
Private Function FindRecord()
'thanks for ideas, freakazeud
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If
End Function
'thanks for ideas, freakazeud
If IsNull(Me.ActiveControl) Then Exit Function
'save current record if changes were made
If me.dirty then me.dirty = false
Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If
End Function
as for tutorials, here is one... I haven't read it in detail, but it seems pretty good; you can do a search for more
http://www.softlookup.com/tutorial/access/ch06.asp
Did you study the sample form I pointed you to on 3/22? Did you do the Steps to Document a Database on it?
** continuous forms **
to design a continuous form, I usually DO let the wizard make the initial form for me since the labels will go into the header and the controls in the detail section. Then I move and resize them.
** StatusBar text (OTHER tab) **
BEFORE you design your form, make sure you have filled the DESCRIPTION property of all of your fields in your table designs; this is what will be used for the StatusBar text when the user is in that control.
** transparent fill **
If you choose a fill color and then set it to transparent, the control will turn that color when it is active -- another indication to the user where they are
** CUSTOMIZING YOUR FORM DESIGN TOOLBAR **
look here for useful icons to add to your toolbar for form and report design:
Highlight Current Record, Customize Form Design Toolbar
http://www.utteraccess.com/forums/showflat...;Number=1071774
** for date controls **
Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
to design a continuous form, I usually DO let the wizard make the initial form for me since the labels will go into the header and the controls in the detail section. Then I move and resize them.
** StatusBar text (OTHER tab) **
BEFORE you design your form, make sure you have filled the DESCRIPTION property of all of your fields in your table designs; this is what will be used for the StatusBar text when the user is in that control.
** transparent fill **
If you choose a fill color and then set it to transparent, the control will turn that color when it is active -- another indication to the user where they are
** CUSTOMIZING YOUR FORM DESIGN TOOLBAR **
look here for useful icons to add to your toolbar for form and report design:
Highlight Current Record, Customize Form Design Toolbar
http://www.utteraccess.com/forums/showflat...;Number=1071774
** for date controls **
Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
Hi Crystal,
Im starting to work on things, I'll keep you apprised of how things are going (and what problems I encounter)
I havent dropped off the face of the Earth, worry not!
Im starting to work on things, I'll keep you apprised of how things are going (and what problems I encounter)
I havent dropped off the face of the Earth, worry not!
wishing you continued success, Pete
Hiya Crystal!
Okay we got seriously side-tracked with trainings and whatnot (getting a new batch of volunteers always causes MASSIVE schedule/project disruptions LOL). Im going to spend the next few days or so going over what we've covered, and my notes, and ask a whole BUNCH of questions Most likely Friday or Monday at this rate (we've covered a lot of ground!)
Just wanted you to know Im still around, still trying to plug away at this.
Okay we got seriously side-tracked with trainings and whatnot (getting a new batch of volunteers always causes MASSIVE schedule/project disruptions LOL). Im going to spend the next few days or so going over what we've covered, and my notes, and ask a whole BUNCH of questions
Most likely Friday or Monday at this rate (we've covered a lot of ground!)Just wanted you to know Im still around, still trying to plug away at this.
thanks, Pete
Today, I received a very nice thank you from someone who downloaded several of my examples from UA and received an award for the program he wrote. As a result (flattery gets you everywhere ), I compiled this list. I know I have given you some of these -- but here is a more complete reference in case you haven't seen them.
some of my links on UA to some of what I consider to be "basics"...
A new guy figuring out how to search -- discussion on normalizing data, designing tables and forms
http://www.utteraccess.com/forums/showflat...p;Number=619663
about halfway through the post, there is a lengthy discussion on data structure, naming, etc ... and about 1/3 from the end is instructions for documenting the relationships and table structures -- this is an invaluable reference as you are building your database
The whole thread is an excellent read -- ScottGem does an awesome job (as always) at opening up another perspective
"thinker's cramp" --> links on normalization
http://www.utteraccess.com/forums/showflat...;Number=1113989
Learning the lingo
http://www.utteraccess.com/forums/showflat...;Number=1100706
How to learn --- Syntax --- VBA --- SQL
http://www.utteraccess.com/forums/showflat...;Number=1102351
basic VBA procedures
http://www.utteraccess.com/forums/showthre...;Number=1036232
Learn how to use Forms! And a little VBA programming too
http://www.utteraccess.com/forums/showflat...p;Number=377430
Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
List Box Picker
http://www.utteraccess.com/forums/showflat...p;Number=426483
"move" items between picked and not picked
Appointment Scheduler by day for time slots
http://www.utteraccess.com/forums/showflat...p;Number=724942
good example of using one subform multiple times
Pinewood Derby
http://www.utteraccess.com/forums/showflat...p;Number=612762
(please pass along to any Cub Scout packs you know)
Today, I received a very nice thank you from someone who downloaded several of my examples from UA and received an award for the program he wrote. As a result (flattery gets you everywhere
), I compiled this list. I know I have given you some of these -- but here is a more complete reference in case you haven't seen them.some of my links on UA to some of what I consider to be "basics"...
A new guy figuring out how to search -- discussion on normalizing data, designing tables and forms
http://www.utteraccess.com/forums/showflat...p;Number=619663
about halfway through the post, there is a lengthy discussion on data structure, naming, etc ... and about 1/3 from the end is instructions for documenting the relationships and table structures -- this is an invaluable reference as you are building your database
The whole thread is an excellent read -- ScottGem does an awesome job (as always) at opening up another perspective
"thinker's cramp" --> links on normalization
http://www.utteraccess.com/forums/showflat...;Number=1113989
Learning the lingo
http://www.utteraccess.com/forums/showflat...;Number=1100706
How to learn --- Syntax --- VBA --- SQL
http://www.utteraccess.com/forums/showflat...;Number=1102351
basic VBA procedures
http://www.utteraccess.com/forums/showthre...;Number=1036232
Learn how to use Forms! And a little VBA programming too
http://www.utteraccess.com/forums/showflat...p;Number=377430
Date Picker Popup
http://www.utteraccess.com/forums/showflat...p;Number=377434
List Box Picker
http://www.utteraccess.com/forums/showflat...p;Number=426483
"move" items between picked and not picked
Appointment Scheduler by day for time slots
http://www.utteraccess.com/forums/showflat...p;Number=724942
good example of using one subform multiple times
Pinewood Derby
http://www.utteraccess.com/forums/showflat...p;Number=612762
(please pass along to any Cub Scout packs you know)
Hiya Crystal, 'happy' Monday...
Okay here's something Ive noticed: When dealing with lookup tables, Im slightly confuzzled by how you sync them all up when you start creating forms.
When you have something like a state field, a lookup is easy since you have a set number of variables to work with, but in the case of cities, how does the information you enter sync up to the table? What's to stop the db from assigning the first instance of (for example) Hatfield one identifier and then the second instance of Hatfield ANOTHER identifier?
The data structure I've come up with LOOKS right, but somehow something must be missing.
Im also in the midst of figuring out what forms would be needed for each variety of input, so that'll keep me busy while you get me an answer.
Oh and I was toying with forms creation (via the wizard) and put in a whole bunch of fields to see what happened, and then when I went into view mode, EVERYTHING was blank?!? *chuckle* I still am not sure WHAT exactly did that.
well thats enough for the morning Back to the salt mines!
Okay here's something Ive noticed: When dealing with lookup tables, Im slightly confuzzled by how you sync them all up when you start creating forms.
When you have something like a state field, a lookup is easy since you have a set number of variables to work with, but in the case of cities, how does the information you enter sync up to the table? What's to stop the db from assigning the first instance of (for example) Hatfield one identifier and then the second instance of Hatfield ANOTHER identifier?
The data structure I've come up with LOOKS right, but somehow something must be missing.
Im also in the midst of figuring out what forms would be needed for each variety of input, so that'll keep me busy while you get me an answer.
Oh and I was toying with forms creation (via the wizard) and put in a whole bunch of fields to see what happened, and then when I went into view mode, EVERYTHING was blank?!? *chuckle* I still am not sure WHAT exactly did that.
well thats enough for the morning
Back to the salt mines!
Happy Monday, Pete,
How are your cities stored? Do you have a seperate table for the names and storing in ID? If so, What I often do is put a "+" button to the right of a drop-down arrow to add entries to the list. Clicking on it pops up another form. When that form is unloaded, the list on the first form is requeried and, if there is an ID chosen on the record, it is put into the combo of the first form for user convenience.
just want to re-iterate...Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm
on forms: it is my opinion that each form/subform should just fill one table. In other words, there should be a seperate form/subform to fill each one. Make use of LinkMasterFields and LinkChildFields to fill hidden IDs on and match up data with the subform
Occassionally, I do make a form recordset include fields from another table for sorting and filtering, but I lock those fields for data input (if they even show).
I have attached a ZIP containing 3 JPG files -- screen shots from my AddressBook program ... just some food for thought. Since I made these shots, I have changed phone number from a combobox to a textbox control.
How are your cities stored? Do you have a seperate table for the names and storing in ID? If so, What I often do is put a "+" button to the right of a drop-down arrow to add entries to the list. Clicking on it pops up another form. When that form is unloaded, the list on the first form is requeried and, if there is an ID chosen on the record, it is put into the combo of the first form for user convenience.
just want to re-iterate...Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm
on forms: it is my opinion that each form/subform should just fill one table. In other words, there should be a seperate form/subform to fill each one. Make use of LinkMasterFields and LinkChildFields to fill hidden IDs on and match up data with the subform
Occassionally, I do make a form recordset include fields from another table for sorting and filtering, but I lock those fields for data input (if they even show).
I have attached a ZIP containing 3 JPG files -- screen shots from my AddressBook program ... just some food for thought. Since I made these shots, I have changed phone number from a combobox to a textbox control.
Aha! I think that answers my question!
And no, I think I have thus far avoided using lookup fields. I think the form you posted for the pinewood derby will help a bit leading me in the specific direction I need.
Now the other thing I came across in here is the concept of an 'audit trail'... why wouldnt our database have something like it? It SEEMS (to the untrained eye... like mine) that we could benefit from one, but I may be overthinking this.
Overall its coming along nicely...
Now, on a completely unrelated note (and because I want a handy answer for my manager), we print out, every two weeks (roughly) a set of timesheets for the volunteers. Would it be better to do create the timesheet as a report or would it work better to send merged data to something like Word? The timesheet itself is simple, so making something in Access wouldnt be difficult at all IMO, so I was curious what your take would be on that.
You deserve every bit of thanks/praise you get Crystal, you have been an exceedingly helpful and patient instructor to everyone Ive seen you interact with! Thanks again!
And no, I think I have thus far avoided using lookup fields. I think the form you posted for the pinewood derby will help a bit leading me in the specific direction I need.
Now the other thing I came across in here is the concept of an 'audit trail'... why wouldnt our database have something like it? It SEEMS (to the untrained eye... like mine) that we could benefit from one, but I may be overthinking this.
Overall its coming along nicely...Now, on a completely unrelated note (and because I want a handy answer for my manager), we print out, every two weeks (roughly) a set of timesheets for the volunteers. Would it be better to do create the timesheet as a report or would it work better to send merged data to something like Word? The timesheet itself is simple, so making something in Access wouldnt be difficult at all IMO, so I was curious what your take would be on that.
You deserve every bit of thanks/praise you get Crystal, you have been an exceedingly helpful and patient instructor to everyone Ive seen you interact with! Thanks again!
thank you, Pete, that is very nice of you to say
you are very welcome
On Timesheets, use Access to make a report. You can have an option on your report menu to emaiil an Access report in RTF or SNAP format.
This code would go into a general module:
For Timesheet data, you may want to create Excel Worksheets for the output for that type of data. This allows the receiver to do subtotaliing, charting, or whatever else they may with to do with it in Excel.
on "audit trail" -- that is a feature I have not used so I can't make a comment. I always do everything myself through code and am stuck in my ways
you are very welcome
On Timesheets, use Access to make a report. You can have an option on your report menu to emaiil an Access report in RTF or SNAP format.
This code would go into a general module:
CODE
'======================================================= Email
'SendObject
'[objecttype]
'[, objectname]
'[, outputformat]
'[, to]
'[, cc]
'[, bcc]
'[, subject]
'[, messagetext]
'[, editmessage]
'[, templatefile]
'------------------------------------ EMailReport
Sub EMailReport(pReportName As String, pEmailAddress As String, pFriendlyName As String _
, pBooEditMessage As Boolean, pWhoFrom As String)
'Email a report to someone and construct the subject and message
'SNAPSHOT Format
'example useage: on the command button code to process a report
' EMailReport "rptSonglist", "anyone@mymailbox.com", _
"A List of the Original Songs from an upcoming Star", _
false, "Susan Manager"
'PARAMETERS
'pReportName --> "rptSonglist"
'pEmailAddress --> "anyone@mymailbox.com"
'pFriendlyName --> "A List of the Original Songs from an upcoming Star"
'pBooEditMessage --> true if you want to edit the message before mail is sent
' --> false if you want it to get sent automatically
'pWhoFrom --> "Susan Manager"
On Error GoTo Err_proc
On Error Resume Next
'----------------------- RTF FORMAT
DoCmd.SendObject acSendReport, pReportName, acFormatRTF, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
'----------------------- SNAPSHOT FORMAT -- commented out
' DoCmd.SendObject acSendReport, pReportName, acFormatSNP, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
Exit_proc:
Exit Sub
Err_proc:
msgbox Err.Description, , "ERROR " & Err.Number & " EMailReport"
'press F8 to find problem and fix -- comment out next line when code is done
Stop : Resume
Resume Exit_proc
End Sub
'SendObject
'[objecttype]
'[, objectname]
'[, outputformat]
'[, to]
'[, cc]
'[, bcc]
'[, subject]
'[, messagetext]
'[, editmessage]
'[, templatefile]
'------------------------------------ EMailReport
Sub EMailReport(pReportName As String, pEmailAddress As String, pFriendlyName As String _
, pBooEditMessage As Boolean, pWhoFrom As String)
'Email a report to someone and construct the subject and message
'SNAPSHOT Format
'example useage: on the command button code to process a report
' EMailReport "rptSonglist", "anyone@mymailbox.com", _
"A List of the Original Songs from an upcoming Star", _
false, "Susan Manager"
'PARAMETERS
'pReportName --> "rptSonglist"
'pEmailAddress --> "anyone@mymailbox.com"
'pFriendlyName --> "A List of the Original Songs from an upcoming Star"
'pBooEditMessage --> true if you want to edit the message before mail is sent
' --> false if you want it to get sent automatically
'pWhoFrom --> "Susan Manager"
On Error GoTo Err_proc
On Error Resume Next
'----------------------- RTF FORMAT
DoCmd.SendObject acSendReport, pReportName, acFormatRTF, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
'----------------------- SNAPSHOT FORMAT -- commented out
' DoCmd.SendObject acSendReport, pReportName, acFormatSNP, pEmailAddress _
, , , pFriendlyName & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " & pWhoFrom, pBooEditMessage
Exit_proc:
Exit Sub
Err_proc:
msgbox Err.Description, , "ERROR " & Err.Number & " EMailReport"
'press F8 to find problem and fix -- comment out next line when code is done
Stop : Resume
Resume Exit_proc
End Sub
For Timesheet data, you may want to create Excel Worksheets for the output for that type of data. This allows the receiver to do subtotaliing, charting, or whatever else they may with to do with it in Excel.
on "audit trail" -- that is a feature I have not used so I can't make a comment. I always do everything myself through code and am stuck in my ways
... and Im back... (last few days I was out 'in the field' as it were).
Okay I think I ought to clarify: The timesheets, as such, are printed out for the volunteers to fill in manually (these are senior citizen volunteers, we're happy if they can fill the forms out properly at all *sigh*). We mail them to the volunteers and they write in their hours and they get mailed to us and we enter the data into the system (currently Excel) and do the calculations and whatnot.
This entire process came out of the desire to streamline some of the processes involved. The back-end maintainance of the Excel spreadsheets were starting to cause more problems than they solved.
Of course SIMPLIFY is a relative term *chuckle* We're starting to pare down the actual data we want in the system to see if we really NEED all the 'extraneous' (the boss's words, not mine) data in the file. There is something to be said for going the simple route
Hope your day is going well.
(last few days I was out 'in the field' as it were).Okay I think I ought to clarify: The timesheets, as such, are printed out for the volunteers to fill in manually (these are senior citizen volunteers, we're happy if they can fill the forms out properly at all *sigh*). We mail them to the volunteers and they write in their hours and they get mailed to us and we enter the data into the system (currently Excel) and do the calculations and whatnot.
This entire process came out of the desire to streamline some of the processes involved. The back-end maintainance of the Excel spreadsheets were starting to cause more problems than they solved.
Of course SIMPLIFY is a relative term *chuckle*
We're starting to pare down the actual data we want in the system to see if we really NEED all the 'extraneous' (the boss's words, not mine) data in the file. There is something to be said for going the simple route Hope your day is going well.
How about using Excel to enter the time and then have Access go read the data?
For reporting, you can put the timesheet info right back into Excel, where others are more comfortable.
If you do not use Outlook, you can use Workbook.SendObject in Excel (or from Access to to eMail to the default eMail handler).
If you use Outlook, you could set up a general procedure like this:
'~~~~~~~~~~~~~~~~~~~
The following code assumes you are behind a report menu where you have a place to fill out email address (EmailAddress) for the recipient as well as a checkbox (chkEdit) to signify if they wish to edit the message before it is sent. In this case, the chosen report was output to Excel (code is not here). A variable, mFilename, was used to keep track of that file's name. Then, the report was eMailed using Outlook.
For you, the email address would probably come from a recordset you are looping through to process the timesheets. If you create the Excel worksheets with a template, you can make it so when the user fills them out, there is less chance for error ... restrict data entry to certain cells as well as performing validation on cells as they are changed, before workbook is saved, etc.
'~~~~~~~~~~~~~~~~~~~
For reporting, you can put the timesheet info right back into Excel, where others are more comfortable.
If you do not use Outlook, you can use Workbook.SendObject in Excel (or from Access to to eMail to the default eMail handler).
If you use Outlook, you could set up a general procedure like this:
CODE
Option Compare Database
Option Explicit
'written by Crystal
'strive4peace
'5-5-06 EmailOutlook
Function EmailOutlook( _
pFilename As String, _
pEmailAddress As String, _
pSubject As String, _
pBooEditMessage As Boolean, _
pBody As String)
Dim outApp As Outlook.Application, outMsg As MailItem
' Dim outApp As Object, outMsg As Object
Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg
'.Importance = olImportanceHigh
.To = pEmailAddress
'.CC = "CC EMAIL ADDRESS GOES HERE"
'.BCC = "BCC EMAIL ADDRESS GOES HERE"
.Subject = pSubject
.Body = pBody
.Attachments.Add pFilename
If pBooEditMessage Then
.Display
Else
.Send
End If
End With
Set outApp = Nothing
Set outMsg = Nothing
End Function
Option Explicit
'written by Crystal
'strive4peace
'5-5-06 EmailOutlook
Function EmailOutlook( _
pFilename As String, _
pEmailAddress As String, _
pSubject As String, _
pBooEditMessage As Boolean, _
pBody As String)
Dim outApp As Outlook.Application, outMsg As MailItem
' Dim outApp As Object, outMsg As Object
Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg
'.Importance = olImportanceHigh
.To = pEmailAddress
'.CC = "CC EMAIL ADDRESS GOES HERE"
'.BCC = "BCC EMAIL ADDRESS GOES HERE"
.Subject = pSubject
.Body = pBody
.Attachments.Add pFilename
If pBooEditMessage Then
.Display
Else
.Send
End If
End With
Set outApp = Nothing
Set outMsg = Nothing
End Function
'~~~~~~~~~~~~~~~~~~~
The following code assumes you are behind a report menu where you have a place to fill out email address (EmailAddress) for the recipient as well as a checkbox (chkEdit) to signify if they wish to edit the message before it is sent. In this case, the chosen report was output to Excel (code is not here). A variable, mFilename, was used to keep track of that file's name. Then, the report was eMailed using Outlook.
For you, the email address would probably come from a recordset you are looping through to process the timesheets. If you create the Excel worksheets with a template, you can make it so when the user fills them out, there is less chance for error ... restrict data entry to certain cells as well as performing validation on cells as they are changed, before workbook is saved, etc.
'~~~~~~~~~~~~~~~~~~~
CODE
If IsNull(Me.EmailAddress) Then
MsgBox "Cannot send eMail -- you need to specify an eMail address", , "Need eMail address"
Else
EmailOutlook _
mFilename, _
Me.EmailAddress, _
"Report Title " & Format(Now(), "ddd m-d-yy h:nn am/pm"), _
Me.chkEdit, _
"attached is the Report in Excel format: " & Dir(mFilename) _
& IIf(Not IsNull(Me.EmailFrom), _
" -- From " & Me.EmailFrom, "")
End If
'~~~~~~~~~~~~~~~~~~~
MsgBox "Cannot send eMail -- you need to specify an eMail address", , "Need eMail address"
Else
EmailOutlook _
mFilename, _
Me.EmailAddress, _
"Report Title " & Format(Now(), "ddd m-d-yy h:nn am/pm"), _
Me.chkEdit, _
"attached is the Report in Excel format: " & Dir(mFilename) _
& IIf(Not IsNull(Me.EmailFrom), _
" -- From " & Me.EmailFrom, "")
End If
'~~~~~~~~~~~~~~~~~~~
Hi Crystal / all
we have a dbase with more than 3000 tables which changes on a regular base (weekly). Do you have any idea how i can count the tables inside as fast as possible? or just a statistic, which writes it out?
thanks
Bence
we have a dbase with more than 3000 tables which changes on a regular base (weekly). Do you have any idea how i can count the tables inside as fast as possible? or just a statistic, which writes it out?
thanks
Bence
Hi Bence,
look at this link -- it tells how to use the MsysObjects table to list what is in your database. Once you have a query to list things, you can make a Totals query and count them...
http://www.utteraccess.com/forums/showflat...;Number=1097606
look at this link -- it tells how to use the MsysObjects table to list what is in your database. Once you have a query to list things, you can make a Totals query and count them...
http://www.utteraccess.com/forums/showflat...;Number=1097606
*Sproing* Back again
Ive been gone because there's been a lot of work going on in the background (other things taking priority over DB development) and on top of which they are eventually going to be moving everything over to some form of online database/PIM/tracking software.
However...
This may not happen for several months (read: late fall), so I have to continue to work on the solution in the meanwhile. Once they enact this new software solution they are planning on having someone ELSE come in and move whatever data Ive collected to the new format (*giggle* better them than me I say).
Okay so after reading through most of this (rather lengthy) post I reworked the database to contain less data (we're paring down what data we need, as I believe I mentioned before).
Now I was looking at the pictures of your addressbook, and if Im seeing things right, each button on the sidebar loads another form which fills in data related to the person at the top of the form.
Where I get fuzzy is when you have 'normalized' data (the rest of the address) that (in my db anyways) is comprised of 4 separate tables. As I start creating the forms to fill in this info, arent I trying to enter data into several tables at once? (is that the right way of describing it?)
Bleh... gotta get my head back into this...
Oh and Access 2007 is cool... very very cool I am beta testing it at home and it actually WANTS to normalize data across a database... how novel!
Thanks for your continued support. I'll go bang my head on the keyboard while things sink in
Ive been gone because there's been a lot of work going on in the background (other things taking priority over DB development) and on top of which they are eventually going to be moving everything over to some form of online database/PIM/tracking software.
However...
This may not happen for several months (read: late fall), so I have to continue to work on the solution in the meanwhile. Once they enact this new software solution they are planning on having someone ELSE come in and move whatever data Ive collected to the new format (*giggle* better them than me I say).
Okay so after reading through most of this (rather lengthy) post I reworked the database to contain less data (we're paring down what data we need, as I believe I mentioned before).
Now I was looking at the pictures of your addressbook, and if Im seeing things right, each button on the sidebar loads another form which fills in data related to the person at the top of the form.
Where I get fuzzy is when you have 'normalized' data (the rest of the address) that (in my db anyways) is comprised of 4 separate tables. As I start creating the forms to fill in this info, arent I trying to enter data into several tables at once? (is that the right way of describing it?)
Bleh... gotta get my head back into this...
Oh and Access 2007 is cool... very very cool
I am beta testing it at home and it actually WANTS to normalize data across a database... how novel! Thanks for your continued support. I'll go bang my head on the keyboard while things sink in
you are welcome, Pete
I switch subforms instead of using a tab control -- it loads the form faster and gives me more flexibility. The buttons on the sidebar switch the SourceObject in the subform control. They are also colored so that the user can easily see what is active and what there is to pick from.
For an example of how that is done, look here
tab control vs replace SourceObject
http://www.utteraccess.com/forums/showthre...;Number=1076790
I switch subforms instead of using a tab control -- it loads the form faster and gives me more flexibility. The buttons on the sidebar switch the SourceObject in the subform control. They are also colored so that the user can easily see what is active and what there is to pick from.
For an example of how that is done, look here
tab control vs replace SourceObject
http://www.utteraccess.com/forums/showthre...;Number=1076790
Hi Crystal,
I wanted to touch base and I need somewhat of a clarification of something:
Earlier in the post you said that pay periods would be a calculation and thus not be stored...
I see (more or less) how the calculation WORKS, but you say it wouldnt be stored, I get the feeling that Im not sure what you meant by that. The only dates we concern ourselves with are the 1st and 15th, so how/where would you enter that?
Sometimes I think coming from more of an Excel background is hindrance here, since in a spreadsheet format you'd simply have another column for the next reporting cycle (actually thats what we DO have, and its the data Im currently working from) and you could total the columns across the entire year (which is how it's set up currently)
I see the potential for really neat things that we could do with Access, but I think I have to overcome the desire to store EVERYTHING that gets entered in a form (maybe its because I want to be sure that if someone wants to know the hours our volunteers worked two months ago, Id want to show a 'snapshot' of that data.
Im also kinda fuzzy on how you manipulate data that isnt in a table of some sort, but I think thats just more reading I need to do.
I wanted to touch base and I need somewhat of a clarification of something:
Earlier in the post you said that pay periods would be a calculation and thus not be stored...
I see (more or less) how the calculation WORKS, but you say it wouldnt be stored, I get the feeling that Im not sure what you meant by that. The only dates we concern ourselves with are the 1st and 15th, so how/where would you enter that?
Sometimes I think coming from more of an Excel background is hindrance here, since in a spreadsheet format you'd simply have another column for the next reporting cycle (actually thats what we DO have, and its the data Im currently working from) and you could total the columns across the entire year (which is how it's set up currently)
I see the potential for really neat things that we could do with Access, but I think I have to overcome the desire to store EVERYTHING that gets entered in a form (maybe its because I want to be sure that if someone wants to know the hours our volunteers worked two months ago, Id want to show a 'snapshot' of that data.
Im also kinda fuzzy on how you manipulate data that isnt in a table of some sort, but I think thats just more reading I need to do.
Hi Pete,
You could set up a PayPeriod table if you like
*PayPeriods*
PayPerID, autonumber
PerStart, date
PerEnd, date
By doing this, you will have better ability to crosstab results by PayPeriod
A pay date will fall between a PayStart and a PayEnd, so there would not be a need to store the actual pay period with hours worked
You could set up a PayPeriod table if you like
*PayPeriods*
PayPerID, autonumber
PerStart, date
PerEnd, date
By doing this, you will have better ability to crosstab results by PayPeriod
A pay date will fall between a PayStart and a PayEnd, so there would not be a need to store the actual pay period with hours worked
Ah! Yes, that would work methinks! Back to playing with the db
Its also interesting to play with Access 2007, so similar and yet SOOOOO different. Feels like Im using a Mac half the time
More as I come to think of it. *waves*
Its also interesting to play with Access 2007, so similar and yet SOOOOO different. Feels like Im using a Mac half the time
More as I come to think of it. *waves*
Hi Pete,
you also may wish to add PeriodNum, integer (as long as it will be <32K ) to your PayPeriods table since you can't count on the autonumber field being sequential without skipping numbers
... "waving" back to you -o!
you also may wish to add PeriodNum, integer (as long as it will be <32K
) to your PayPeriods table since you can't count on the autonumber field being sequential without skipping numbers... "waving" back to you -o!
Hi Crystal,
Okay doubling back for a sec (and only somewhat related to what we're talking about).
What is a lookup field as opposed to a lookup table?
Sometimes I see similar terminology used and I want to be sure Im clear on definitions as I go along (all information is good information).
I also have the 'enviable' task of trying to document and explain the db as I go, so the last thing I want to be doing is using faulty terminology.
Thanks
Okay doubling back for a sec (and only somewhat related to what we're talking about).
What is a lookup field as opposed to a lookup table?
Sometimes I see similar terminology used and I want to be sure Im clear on definitions as I go along (all information is good information).
I also have the 'enviable' task of trying to document and explain the db as I go, so the last thing I want to be doing is using faulty terminology.
Thanks
Hi Pete,
to me, a lookup field is defnied in the table to be a combobox or a listbox -- and that, of course, is not a good idea -- in the table, the field should show the real data -- combo and listboxes can be used on forms and reports to hide the ID and show the text.
Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm
A lookup table is a table that is used to store a list of values that are acceptable for puposes of providing choices for a combobox or listbox on a form -- like a table with States.
to me, a lookup field is defnied in the table to be a combobox or a listbox -- and that, of course, is not a good idea -- in the table, the field should show the real data -- combo and listboxes can be used on forms and reports to hide the ID and show the text.
Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm
A lookup table is a table that is used to store a list of values that are acceptable for puposes of providing choices for a combobox or listbox on a form -- like a table with States.
So bascially the goal is to avoid 'hiding' any data behind other data - the data displayed should be the DATA rather than CODE SHOWN as data?
That makes sense for the reasons listed at the link above.
So I take it it was a good practice (when I was planning out the db) that I put little *'s next to certain table fields noting "combobox on form"?
That makes sense for the reasons listed at the link above.
So I take it it was a good practice (when I was planning out the db) that I put little *'s next to certain table fields noting "combobox on form"?
Hi Pete,
yes, EXACTLY!!! ... you never want to hide anything from yourself!!!
A seemingly unrelated comment (but one that comes to mind) is to change the defaults that Windows uses to display files in Windows Explorer and My Computer...
from the menu --> Tools, Folder Options, View tab...
UNcheck anything that has "Do not show" or "Hide" in the prompt
Check anything that has "Show" or "Display" in the prompt
The most important thing you will now see is file extensions
'~~~~~~~~
if * helps you, that is good -- usually, any foreign key will be a combo or listbox on a form -- all my foreign keys are called somethingID
yes, EXACTLY!!! ... you never want to hide anything from yourself!!!
A seemingly unrelated comment (but one that comes to mind) is to change the defaults that Windows uses to display files in Windows Explorer and My Computer...
from the menu --> Tools, Folder Options, View tab...
UNcheck anything that has "Do not show" or "Hide" in the prompt
Check anything that has "Show" or "Display" in the prompt
The most important thing you will now see is file extensions
'~~~~~~~~
if * helps you, that is good -- usually, any foreign key will be a combo or listbox on a form -- all my foreign keys are called somethingID
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.