Newbie Form help. CustID number field on form but need name field shown and used

[erin5980]

I'm brand new and teaching myself Access the past few weeks. I am wondering if you could tell me if how I have this set up is ok.



My end goal is to have a form where office personnel enter details about proposals that they are sending to customers so we can track. They need to be able to
utilize the CompanyName as a method of entering, not CustID#.

I have several tables. The main one is tblCustomers.

The table that the form in question is created off of is tblProposals.

DesignView:

tblCustomer
CustID# (primary key) autonumber
CEOID# number field
Active checkbox field
CompanyName short text field
etc...etc..

tblProposal

ID# autonumber

Proposal# autonumber with an expression

CustID# number

DateSent Date

etc...etc...

I have the relationship set up from tblCustomer field CustID# to tblProposals CustID#.

Here is how I have the CustID# field properties - lookup set up. I have coumn count set to 4 and column width set to 0";0";0";1" This is allowing the CompanyName to show instead of CustID#

I think this is working but wanted to double check with someone to make sure that this is correct. By doing it this way it looks like my customers are now searchable by name to our office staff, but does this do anything wierd in the background or down the road?. Thanks!

Maybe I should add that when creating the form and adding the CompanyName field from tblCustomer, I could not figure out how to have both the CustID# and CompanyName on the form and make it work. So I 'm not sure if what I've done above is a work around or if this is how you do things normally. Thanks very much.

[orange]

Here is a link with some database planning and design concepts.
I recommend you do some research on Database, and then Access.
You should be aware of Normalization and its importance to database.

Access does not like special characters in field and object names. You will do yourself a great favour by using field names with alphabetic characters and underscore(_) only. No embedded spaces and no special chars..

You will simplify your efforts if you avoid lookups in table fields.

Good luck.

[ssanfu]

Welcome to the forum....

In addition to what orange posted, I'm going to load you up with lots of reading....

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not begin object names with a number.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.



The evils of lookup fields (bad)
Table and PK design tips
About calculated table fields (bad)


You should NOT use an autonumber type field if that field has a real world meaning.
PK fields should NEVER be displayed on forms or reports if they are autonumber type.


AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques
Use Autonumbers properly


For my tables, I have an autonumber is virtually every table. I use a suffix of "_PK" for the primary key field and "_FK" suffix for a foreign key field.

Use in a field name "Num" instead of "#" or "No". Much less confusing.


Naming Conventions
What not to use in names - bad names



What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


The Normal Forms
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form.
The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html


Happy reading......

[erin5980]

Bless you both. Ok, I will change my Fields to alpha/underscore only, change out the # for Num. No spaces. I actually did a better job in the actual tables than I did replicating it here for you all. I do understand PK's and have them in my tables as just an Access generated field that I don't use and has no meaning.

Somewhere along the YouTube rabbit hole these past two weeks I latched on to Lookups. I am removing them from my tables now. So, let me see if I understand:

1. When setting up forms, I will be using querys instead of a lookup, is that correct?

2. Loud and clear on the autonumber. I will research how to create automatic numbering of records, if I don't come across it in my new reading. ;-)

Thanks again. I'm reading everything you both sent me. I've been through most of the Access Bible book (maybe its crap...IDK) after playing with Access for a few weeks, so things are starting to click. But I'm a very visual learner!

Are there any YouTube channels that are recommended? Or maybe that's here already. I'll poke around the forum a little more. Thanks again!

[ArviLaanemets]

When setting up forms, I will be using querys instead of a lookup, is that correct?

Generally, you use controls:
Text Box displays the value from field, it is linked to, or the result of formula you entered there. With linked Text Box, all changes you enter are saved to table field on active row, when the active row is saved;
Combo box displays info from one column of row source, linked with value in another column of row source, all changes you enter are saved to linked table field on active row too. Row source can be a query, or a value list. Only 1st column with widh > 0 is visible.;
List box is similar to combo box, except it takes more place
Radio Button displays all possible choices for integer field, and marks the current choice. And of course all changes are saved too;
Check Box displays TRUE/FALSE status of table field in active row, and saves changes too.

Another feature you want to use is Main Form - Subform design. When a record is selected on main form, linked records (having a foreign Key in source table linked to key in main table, or to control in main form) are displayed in subform. And any changes and added records in subform are automatically linked to active source table record or to linked control value in main form.

I will research how to create automatic numbering of records, if I don't come across it in my new reading.

There are some posts, but not too many. I myself posted an answer to some post in previous year, but I haven't find out, how to search for my own posts here.

[Dave14867]

Erin5980,

These folks are a great help. From 1 newbie to another, I don't use special characters as previously mentioned, also, don't use lookups also previously mentioned, instead create the form and where you want the lookup, create a combo box and the wizard will walk you thru what table or query you want to use to get the info from, select the table and the field you want to use, that way you don't need the lookup in the table, works awesome.

Hope this helps.

After a while it just becomes second nature.

Dave

[ssanfu]

I will research how to create automatic numbering of records

So what type of custom autonumbering do you need? What are the requirements?


Here is Paul's page: http://baldyweb.com/CustomAutonumber.htm
This has a demo dB: https://www.experts-exchange.com/que...MS-Access.html

[erin5980]

Thanks very much!

[erin5980]

So what type of custom autonumbering do you need? What are the requirements?

I think I just got a bit ahead of myself. But what I am looking to do is havee our proposals tracked by number-and would like that auto populated. I will review your links, thanks very much! I sort of started over with my database. Its small right now, and probably easier than trying to figure out how to rework it and have more errors.

[erin5980]

Erin5980,

These folks are a great help. From 1 newbie to another, I don't use special characters as previously mentioned, also, don't use lookups also previously mentioned, instead create the form and where you want the lookup, create a combo box and the wizard will walk you thru what table or query you want to use to get the info from, select the table and the field you want to use, that way you don't need the lookup in the table, works awesome.

Hope this helps.

After a while it just becomes second nature.

Dave

Thanks so much Dave! I am figuring it out and starting over. I think I have a basic grasp and then implementing is of course different. I look forward to gaining knowlege from here!

[ArviLaanemets]

At least did find out how search for my posts

Here I wrote about custom "autonumeric" ID: https://www.accessforums.net/showthread.php?t=65653

[erin5980]

At least did find out how search for my posts

Here I wrote about custom "autonumeric" ID: https://www.accessforums.net/showthread.php?t=65653

Thanks very much! I think it might be beyond my capabilities at the moment. But when I get to that point I will definately revisit this. Much appreciated!!