Help Getting Started (1 Viewer)

jms3257

Member
Local time
Today, 14:28
Joined
Apr 18, 2020
Messages
33
Definitely get your forms developed so you can get a good workflow going with them. Design and use the forms from the perspective of someone else using them. Access isn't much better than Excel if just using tables. Good luck!
Already have a form for the (tblSchools) or at least 95% anyway. I am bringing in data from a spreadsheet and find it easier to do this in the table. Once I have everything setup will use form to edit or add new.
 

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
I didn't, but I usually just hide the navigation pane, ribbon, and everything so nothing is visible at all. No need to hide objects individually in my case :). I don't have users making queries though, at this point.
when I create usys objects, if they're needed for the appllication, I use object properties to note that ... but I also name them differently so I don't have to look. F11 shows the Navigation Pane so even if it is normally hidden, I can show it. When I disable the Navigation Pane in the startup code, then I open databases holding SHIFT to bypass startup code -- and if I've also disabled use of F11 (which is rare), I run code to make it available again. Hopefully this is helpful ~
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
Already have a form for the (tblSchools) or at least 95% anyway. I am bringing in data from a spreadsheet and find it easier to do this in the table. Once I have everything setup will use form to edit or add new.
If you create tables by importing data, a few things to note that should be changed in the table design for fields...

1. for Text fields:
  • delete Format property of "@"
  • set Unicode Compression to Yes

2. for Number fields
  • check Field Size -- this is set to Double -- change to whatever is right. Unless you actually need decimal places, choose Integer or Long Integer. If you need data to be Currency, change that too, in the upper pane

3. for date/time fields
  • make sure data type is right

> "Once I have everything setup will use form to edit or add new. "

awesome!
 

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
I find it easier to add data in the table, at least for now. However I am trying to develop sound procedures from the start. I find it better especially when this project starts getting larger.
Easy to create quickie forms using the wizard. Good to make forms to usenot only for possible users but yourself too -- you can always change them ;)

> "However I am trying to develop sound procedures from the start"

awesome!! Measure twice, cut once ... can't un-cut!
 

zeroaccess

Active member
Local time
Today, 15:28
Joined
Jan 30, 2020
Messages
586
when I create usys objects, if they're needed for the appllication, I use object properties to note that ... but I also name them differently so I don't have to look. F11 shows the Navigation Pane so even if it is normally hidden, I can show it. When I disable the Navigation Pane in the startup code, then I open databases holding SHIFT to bypass startup code -- and if I've also diabled use of F11, I run code to make it available again. Hopefully this is helpful ~
I just opened my database and hit F11, nothing happens. Nav pane still hidden. What am I missing?

Tried all the other F keys - just realized F12 will perform the Office "Save As" for the form. None of the others do anything, but I should probably see about disabling that. It might not matter as ultimately I deploy .accde.
 

zeroaccess

Active member
Local time
Today, 15:28
Joined
Jan 30, 2020
Messages
586
press SHIFT when opening database to bypass startup code
Ok, I shift bypass my .accdb to do development work. When I do, it also bypasses startup restrictions on hiding the ribbon, nav pane, and shortcut menus. So, the nav pane is already showing when I bypass.

Still, my users aren't doing that on their .accde as I disabled the bypass. :)
 

jms3257

Member
Local time
Today, 14:28
Joined
Apr 18, 2020
Messages
33
Easy to create quickie forms using the wizard. Good to make forms to usenot only for possible users but yourself too -- you can always change them ;)

> "However I am trying to develop sound procedures from the start"

awesome!! Measure twice, cut once ... can't un-cut!
Learned a long time ago from an old timer cut it big then sneak up on it. not the same as un-cutting but same idea.
 

jms3257

Member
Local time
Today, 14:28
Joined
Apr 18, 2020
Messages
33
I would put Schools in the Contacts table. Then in the Schools table (with CID to link to the contact information for the school), I would also create CID_AD (long integer, default value is null) to link to who the athletic director is.
Redoing the (tblAthleticDirector) renaming to create as Contact table. Have created a table of the Titles (AD, Asst. AD, Head Coach ect.) when I add this to Contacts would I use the ID or the shortend title or long title (AD-1, AD, Athletic Director) ?

Also from above "CID to link the contact information for the school" and "create CID_AD (Long integer, default value is null" the items above in your post I have changed to red, I don't understand what this is.

Sometimes I think I should stop making this database until I read and study more, but want to keep moving forward as I am learning.

(Added After Posting)
Along the same lines, when I convert the school list box info to real data would I use the school name ID or both?
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
Ok, I shift bypass my .accdb to do development work. When I do, it also bypasses startup restrictions on hiding the ribbon, nav pane, and shortcut menus. So, the nav pane is already showing when I bypass.

Still, my users aren't doing that on their .accde as I disabled the bypass. :)
put these procedures in your code so you can run them

Rich (BB code):
Sub NavPaneShow()
'200421 strive4peace
'change MyTablename to a name of a table in the databse
   DoCmd.SelectObject acTable, "MyTablename", True
End Sub

Sub NavPaneHide()
'200421 strive4peace
'change MyTablename to a name of a table in the databse
   DoCmd.SelectObject acTable, "MyTablename", True
   DoCmd.RunCommand acCmdWindowHide
End Sub
 

zeroaccess

Active member
Local time
Today, 15:28
Joined
Jan 30, 2020
Messages
586
Can you tell me what this would net me? Maybe I wasn't clear, but I already hide everything and lock down the DB.

At first glance it looks like you let users access the navigation pane. Mine is completely unavailable and I intend to keep it that way...
 

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
At first glance it looks like you let users access the navigation pane. Mine is completely unavailable and I intend to keep it that way...
yes, because I also teach. If the one I'm helping wants to lock it down, I give them a way to show it. I don't protect anything I help others develop.
 

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
hi Jim,

CID means contact ID, which is the primary key in the Contact table

When I use "ID" in a field name, it is a long integer data type where the ultimate primary key is a long integer, and usually also an AutoNumber (special form of Long Integer that is a number and automatically gets a value).

When CID is used as a foreign key in a related table, it is also called CID ... but if a related table has more than one CID, then I qualify the second one. For instance, if your related tables is Schools, then CID refers to the contact infomormation for the school. However, the school also might also have am athletic director with a contact record. This second foreign key to contacts could be called CID_AD

> "AD, Asst. AD, Head Coach ect"

This is what I am referring to as a Role. RoleID is the primary key in the Roles table. People can have one or more roles in any circumstance. In the Roles table, I call the text field that describes RoleID just Rol since Role is a reserved word.

Problem names and reserved words in Access, by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html

> "when I add this to Contacts ..."

I would not add a role to the Contacts table, unless it is a default role. In my contact database, I added a generic text field called JobTitle, which doesn't have a table to define what it is, but is really a role. A role is a part that someone plays.

Contacts, in my way of thinking (we're all different, so someone else will have other ideas) is a generic table for information about humans and companies that is connected to tables with related contact information such as phone numbers, address(es) and email addresses. Most tables have a type also ... so if phone, what kind is it? business, personal, during day, evening, vacation, ...

> "Sometimes I think I should stop making this database until I read and study more, but want to keep moving forward as I am learning."

that is great! Good to build as you learn, and remain open and flexbile. Just realize that if you build forms and reports they will also need to be changed if you change the table design. As you would repair a crack in the foundation of a house upon discovery, so should you repair cracks in the data structure (tables, relationships, fields).

> "Redoing the (tblAthleticDirector) renaming to create as Contact table"

imo (in my opinion), "Contact" without qualification should be to a human or company, and not in any specific role.

> "Along the same lines, when I convert the school list box info to real data would I use the school name ID or both?"

when storing the school, in a related table (such as events that the school is involved in), use the SchoolID and/or CID for the school. CID is contact ID. SchoolID is the Autonumber field in the Schools table that is also related to a record in the Contacts table.

One reason to use Schoolid instead of CID, in tables that are related to schools and not general information about people (human or company) is that a table can only have a limited number of relationships. A relationship is defined using the Relationships Diagram and has referential integrity enforced. Referential integrity means that a child record must first have a record in the parent table.

Enforce Referential Integrity (RI) on Access Relationships (cc)
 

zeroaccess

Active member
Local time
Today, 15:28
Joined
Jan 30, 2020
Messages
586
Makes sense. My DB exists in a production environment where data is entered and reports are printed out. Therefore all functionality needs to be baked into the forms and I try to make it as bulletproof as I can...it's a lot of work, but it's rewarding when it all works. I admit I have pulled a 10-hour development session as you admitted to yesterday.

Sorry to derail the thread! Crystal will take it from here.
 

strive4peace

AWF VIP
Local time
Today, 15:28
Joined
Apr 3, 2020
Messages
524
ps, Jim -- I realize that what I'm describing might seem complex -- and maybe more so than you want to get into

In addition to my thoughts, it is important to listen to others and adopt what works for you.

zeroaccess, your ideas are also good! Just because we disagree only means just that.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 19, 2002
Messages
28,998
First question, should I separate first and last names in the AD table?
A good table always separates data into individual columns. Names are used for many purposes in an application. Sometimes you want only the first name if you are writing a leater -
Dear Jane,
If you are going to be more formal, you would want the person's title and his last name:
Dear Mr. Smith,
On the address label you want the full name:
John Paul Getty

This follows for address and anything else that can be broken down. One of my clients did direct marketing and it broke just the addr1 line down to 8 separate parts on top of the more standard addr1, addr2, City, State, Zip so they could more easily dedup their data and take advantage of postal rules for second class mail. It is far easier to concatenate the parts as appropriate than it is to split them on the fly once they're mushed. How do you split Mary Ellen Cowles? Mary isn't the first name. Mary Ellen is the first name and what about Dick Van Dyke. In this case, his first name is not Dick Van. It is the last name that is two parts.

You never know what you will be called on to do with data later on so start by storing it cleanly rather than mushing it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 19, 2002
Messages
28,998
Zero Access:
There are many issues with lookup fields. Use them if you want. They were introduced as a crutch for new users who didn't understand how to create a query with a join. Apparently you haven't run into any of the issues. For example, make a union query that includes a lookup field. What do you see?

The solution for your personal use is to use queries that join a table to any lookup tables it needs. Just remember to use left joins for optional fields.
 

zeroaccess

Active member
Local time
Today, 15:28
Joined
Jan 30, 2020
Messages
586
Zero Access:
There are many issues with lookup fields. Use them if you want. They were introduced as a crutch for new users who didn't understand how to create a query with a join. Apparently you haven't run into any of the issues. For example, make a union query that includes a lookup field. What do you see?

The solution for your personal use is to use queries that join a table to any lookup tables it needs. Just remember to use left joins for optional fields.
I don't use union queries (or at least I haven't had a need for them) but my Select query results are fine. I don't see lookup fields in tables as having anything to do with the queries and joins I use throughout. They are not mutually exclusive.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 19, 2002
Messages
28,998
You never have to follow the advice given to you by experts. Lookup fields are a "feature" that looks good on paper but ultimately cause more problems than they solve. If you're lucky, you won't ever need to use a union query or to write code to process a recordset.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom