Formating a texbox

[alextol]

I think I have a simular issue in the Start Date field. The Date formating is different from is on the query.

[JoeM]

I can honestly say, I have never seen that kind of design before, where all the fields are unbound, are all the various are stored in the combo box used for selection. I would never do it that way, and have never seen it done before. I tried messing around with the formats on the table, query, and form levels, all with no success. I have a feeling that something may be getting lost in translation using that method.

Rather than do it that way, I would recommend using bound records, and have a selection box to choose which record you want to see.

[ItsMe]

My observation is you should be focusing on your tables and not worying about your form. You really need to do some research on what an RDMBS is and does. With good table structure, you will never get past word GO.

You have one table. It is large. Nothing wrong with having a bunch of columns. It is only that you built this thing like a spreadsheet and it is not. You need to start thinking about how you are going to split these columns up into different tables. Then you need to think about what data types the fields will have. Then you can START to draw relationships between Primary Keys and Forign Keys.

client_no is OK to have as Text data type. It identifies a specific client. The client number may have been produced by the company many years ago. In this case you will need to add another field to your table that will act as a Primary Key. Your new PK should be an AutoNumber type.

client_no and your unique PK field should represent information about a specific client. Things like the client name, client address, customer since Date, main phone number, main fax, etc. All of these types of fields should be in a table called something like, tblCustomers or tblClients.

You could also have another table, a child table. It would contain additional information about a specific client. Information that could not be stored in one record but was still relevant to the Client Name, Address, phone, etc. This child table could contain individual contacts. You could call it tblContacts. There would be MANY contacts for one ClientID or client_no. It is quite possible that Joe, Mary, and Sam would all be related to the same client_no. Joe needs his own record within tblContacts to store Joe's Name, Number, Address, etc. In addiyion his record will have a Foreign Key that holds the relative Primary Key value from the Parent table.

Back to the other fields in your table. (NAICS) This field may very well belong in the Client table. Only if the client is an actual underwriter for Ins. policies though. If this is an NAICS that is listed on their Ins Cert then it should go in another table of its own. They may have many NAICS's that represent different policies. Another example of a one to many relationship. Plus, why not list out the underwriter address info too. You could even include the Agent name an address. Naybe even put Agent in one table and underwriter in yet another.

new_region, could possibly go in its own table. Norther Region, Southern Region, Etc.

Product Line, If it only has two options like Standard and Non standard this field could be a Yes/No. Otherwise, new table.

OE Quarter, put it in its own table. It can have four records. Maybe three columns. One column for PK, a column for Q1 and a column to spell it out, First Quarter or 1st Quarter.

There are others too.

Then you have calculated fields. These do not belong in any table anywhere. Things Like Cobra and % this and % that. They all apear to be calculations based on data you already have in a table somewhere. So you do the calculation in a report or a form at the very instant a user wants to see it. If you print it in a report. Shred it right after you are done reading it. Because it is dated info.

2012 NAICS Title - When I got to this I started to wonder if you were placing Clients into catagories to determine WC. So, new table. Just assign the catagory to the Client using the PK from the table that describes the catagory.

[alextol]

JoeM, I just started using access few weeks ago and don't know much about it. Can you please tell me more how to use selection box method?

[alextol]

ItsMe, thanks for the lesson on tables! I really don't much about access and this was helpful. When I first started this database, my table was split up into 6 different tables, but I didn't know how to make my form look up all separate tables because some of which had no relationships. I have to have many to many relationship to be able to pull all the records. So, to make it simplier I combined all the tables into one.

I know a little bit about building "clean" tables. What I am having trouble is pulling data on the form for the select client.

[JoeM]

JoeM, I just started using access few weeks ago and don't know much about it. Can you please tell me more how to use selection box method?

Create a query from your table, selecting the the fields you wish to show.
Then, create a form from this query. This should automatically create a "bound" form for you. You can format it however you like, moving fields around, etc.
Now, you can add a Combo Box record selector as described here: http://support.microsoft.com/kb/287658

A word to the wise:
Access is not nearly as intuitive as Excel. If you have very little experience in it, you cannot expect to dive right in and create a nice designed functioning database. What you experienced here is just the tip of the iceberg, you will be in for hours of countless frustration. Believe me, that was me 10 years ago.

It is really important to understand the concepts of Relational Databases and Rules of Normalization when designing databases. ItsMe hit on some of those details in his response. Here is a good write-up on a bunch of those details: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Then of course, you also need to learn about Access. I would recommend starting with a good introductory book or course.

Unfortunately, there really aren't any shortcuts to this learning. The learning curve for Access is steep, but the rewards are great!

[alextol]

JoeM, thank you for the help! It's a lot easier to create bound form! I got another question though. What is the easiest way to connect two combo boxes or make a text box look up value in the combo box located on the different tab of the form?

[ItsMe]

What I am having trouble is pulling data on the form for the select client.

Obviously this is a problem. However, focusing on this is not going to help you create a functioning application.

You need to focus on the tables. Then you can start creating relationships using PK's and FK's. Then you can build a query. Then, MAYBE, you can build a form.

If you, as a developer, can not update the fields you desire via an Access query you are not doing something right. And I am not referring to a query based on one table that used to be six individual tables.

JoeM has offered some references to start learning how to achieve this. As JoeM has mentioned, you need to be able to develop data tables and use Access to develop an application based on a good table matrix. You are trying to develop an application based on an Excel spreadsheet.

[alextol]

Thanks ItsMe and JoeM for your help! I'll keep working on it.