Form Errors - Help!

[k6saunde]

I have 2 errors I can't seem to resolve (see capture A & B).

Any idea how to fix these?



Thanks,

Keith

[RuralGuy]

It looks like the error messages are pretty accurate. I'm not sure we can assist you further unless you post your db. If you do post your db, be sure and Compact and repair it first and then zip it up so you can post it.

[ssanfu]

This is a picture from you post https://www.accessforums.net/showthread.php?t=58263 Post #5

It is telling you that the form name "Client Information" is misspelled or refers to a form that doesn't exist. Actually it is telling you both!
The form name is misspelled because it has a space in the name.
And the form doesn't exist (with the space in the name).... But the form "ClientInformation" DOES exist (without the space). Check your code and/or macros for "Client Information" (with a space) and correct it.


----------------------------------

This error message

is telling you that you cannot enter the same value in the primary key field because it would create duplicate values; if you have duplicate values, you won't have a PK field. PK fields MUST have unique values.

Referring to Post #7:
I don't know if the record source for the form is a table or a query, but it doesn't really matter. It does show (red circle) that you are trying to enter the same value for "ClientID:" (strange having the colon in the file name) in two records.


----------------------------------

You are going to have lots more problems because the table ""ClientInfoT" is designed like a spreadsheet. I would have break the tables two, maybe three tables. Field names "WIC", "FoodStamps", "Unemployment", "Disability", "Other", "Military", "Veteran" and "Incarcerated" is data - shouldn't be field names. I would have a related table and have multiple records.

For table "ClientInfoT", ask yourself "What are the attributes of a client? "
First Name, MI, Last Name? For sure.
SSN, DOB, address, city, state, zip, email. Yes.
Ethnicity? OK.
Reasons for visit, Referred By?? No way..... So different table.
Manatee County Resident? Nope. Different table.

WIC, food stamps...etc.... already talked about them. Different table.

----------------------------------

Also, I think you are going to need a junction table between "ClientInfoT" and "AllProgramsT", not what you have now.



Good luck with your project.....




BTW, you really should stick with one thread until it is solved (and mark it solved) or you think it is done (and mark it solved), not have multiple threads about the same problem.

[k6saunde]

Thanks, Steve.

So what I'm understanding from your post is I need to redefine the tables to only include what is in each category IE: Client, Programs, Reason for visit, etc.?

So for example: education level, annual income, employment status, number of dependents, marital status would all go under a single table named "personal"?

Or would you break it down further such as: marital status & dependents under say "Personal"

Then: employment status & annual income under "Work"

And finally: Educational Level under "Education"?

Thanks for your help, I really appreciate it.

Keith

One more thing, Should I just make changes to the existing database or will starting over be easier?

[ssanfu]

I don't know your requirements, I was explaining why you were getting the second error - trying to enter a ClientID twice. With a client info table that only has client info, you enter "Bruce" once then the PK for Bruce is used as the FK in other tables.

As far as breaking down the table, you need to decide what fields fit where in a logical manner. What fields deal with the same type pf data. But you can/could group different data together..... depends on your requirements. This is known as normalization. You might work through the tutorials at rogers Access Library http://www.rogersaccesslibrary.com/forum/forum46.html . I think it will help you.... it did (and still does) helps me. I use a whiteboard when designing tables structures.

Your forms will be different because some of the queries might not be updatable. The saying is "One form, one table ( or query - since I always use queries as the record source for forms).
I use a main form and subforms, sometimes with the tab control so I can have multiple subforms on the main form without it looking cluttered.


Start over or use what you already have? Again, that is up to you. A lot of the objects might/will change.

Good luck. Post back with questions.......

[k6saunde]

Steve, thanks for the info, great stuff.

Decided to start over, was having too many issues every time I changed the smallest thing. New Database is coming along much better. Having 2 issues I can't figure out though.

1. In the Programs Field within the subform I created which is set up as a continuous form. Every time I select a new item from the drop down list it adds a number (I assume corresponding to the item ID selected in the list), to the bottom of the list of items in the drop down list. I know a little confusing so see pics below.



2. I have an issue with the DOB date. The mask is setup in both the table and the form as 99/99/99;0; but when I go to next field I get 12/12/1980 instead of 12/12/80.



Any idea how to fix these issues?

Thanks again,

Keith

[ssanfu]

Every time I select a new item from the drop down list it adds a number (I assume corresponding to the item ID selected in the list), to the bottom of the list of items in the drop down list. I know a little confusing so see pics below.

Yes, it is confusing, more so because I cannot see what you have done.

The mask is setup in both the table and the form as 99/99/99;0;

I do not set masks or formats in tables. I think it is a waste of time because users should never interact directly with tables.

In forms, I use an Input Mask of "99/99/0000;0;_". I think the "Format" defaults to American format. (mm/dd/yyyy).


You could post your dB for analysis.....

[k6saunde]

OK, here it is.
New folder.zip

[k6saunde]

This is what I am trying to accomplish but, with the subform you see in the database I posted.

[ssanfu]

OK, first the date problem. There is a setting "FILE/OPTIONS/Client Settings?General" that has an option "Use four-digit year formatting" that has two options: "This database" and "All Databases". I unchecked both and was able to have the year display with two numbers.
(I would remove all formatting and input masks in table fields.)

There are still problems with your table structure.
1) I would remove all look up FIELDS. See http://access.mvps.org/access/lookupfields.htm

2) You have the "ClientID" field in look up tables. The look up table PK would/should link to a FK in the client info table (I left comments in a few look up tables)

(Second problem)
3) For the Programs, you need a junction table. What you have is a many to many relationship.
>> One client can have many programs and one program can be selected by many clients. <<

I created the table, query and subform. (I may have changed the layout of the main form also )
(I never use a table as the record source for a form - I only use queries.

Because you had the "ClientID" in the "ProgramsT" table, you were getting strange numbers - it was how the table was constructed and updated.



4) If you want a form to look like the image in Post #9, that is up to you. It means those field will be in one table. I think it might cause you problems later on, but that is your choice. (notice how I evaded responsibility??? )

5) The "State" field in "ClientT" should be a Number type (Long) if you are going to use the "StateT" table.

6) You have a look up FIELD in "ReferralT" that is looking at itself (ReferralT).

7) I added a tab control. See the first two tabs.....


Anyway, have a look at the attached (modified) dB.

[k6saunde]

Awesome, thanks you so much.

A few questions though.

1. when you say "remove all lookup fields" does that mean delete even the text box out of the field? Like how it appears for a date or is ok to leave it as text box?

2 How do I show the Programs Subfield you created without the tabs, so just "sfPrograms" is showing? I must be doing something wrong because, when I try to add a subform all I get is a blank box.

Thanks again

Keith

[ssanfu]

1)

when you say "remove all lookup fields" does that mean delete even the text box out of the field?

I don't understand what you mean.


Here are two examples of look up FIELDS:
A) Open table "InitialServiceT" in design view. Click on the field "InitialService". Now click on the tab "Lookup". This is a look up FIELD.

B) Open "MaritalStatusT" in design view. Click on the field "MaritalStatus". Now click on the tab "Lookup". The problem with fields set up like this is that the lookup is referring to the same table "MaritalStatusT". It is in a continuous loop.

If you want to keep the look up FIELDS, that is your choice. I see so many questions about "I have a look up field and I can't get 'XXXX' to work".
The reason is that the combo box hides too much.

How do I show the Programs Subfield you created without the tabs

(I think you meant sub FORM??)
2) Open the form in design view.
Click on the subFORM.
Press Ctl-X (to cut the control out of the form)
Select the tab control.
Press the Delete key
Click in the form footer.
Press ctl-V (Paste)

[k6saunde]

1)I don't understand what you mean.


Here are two examples of look up FIELDS:
A) Open table "InitialServiceT" in design view. Click on the field "InitialService". Now click on the tab "Lookup". This is a look up FIELD.

B) Open "MaritalStatusT" in design view. Click on the field "MaritalStatus". Now click on the tab "Lookup". The problem with fields set up like this is that the lookup is referring to the same table "MaritalStatusT". It is in a continuous loop.

If you want to keep the look up FIELDS, that is your choice. I see so many questions about "I have a look up field and I can't get 'XXXX' to work".
The reason is that the combo box hides too much.

Awesome, So just because i'm obviously access challenged..... In the lookup field having this as a combo box is a bad idea, I get that part. what I was trying to confirm is where it says Display Control in the Lookup Field is it OK to leave "Text Box" as a value or should I even delete that and leave it completely blank?

Sorry for the stupid questions but I have been struggling with this thing for 2 weeks and I need to get this done. But I want to do it right not just get it done.

Keith

[ssanfu]

where it says Display Control in the Lookup Field is it OK to leave "Text Box" as a value or should I even delete that and leave it completely blank?

Ah-ha! (the light came on)

It should be "Text box". Access won't let the "Display Control" property to be NULL.

When you start designing a table and you create a field, "Display Control" in the Lookup tab is "Text box".
To create a look up field, the "Display Control" property is changed to "Combo box".
So when you want to remove a look up field, change it back to "Text box"..

[k6saunde]

Cool, now for the big question...

In the table StateT in design view under the Field Name State the data type should be short text and under lookup Display control should be Text Box? (See Picture)

In table ClientT in design view under the Field Name State the data type should be short text and under lookup Display control should be Text Box? (See Picture)

In Forms ClientT in design view in the property sheet for state. "Selection type:" is Combo Box, Control Source is "State" ....Etc. (see picture)

And relationships (see Picture)

Is all this right or did I still "F" it up somehow?



Really appreciate your help Steve. If you ever make to FL I owe you some beers and probably a steak or lobster dinner.