# Solved: Access 2007 Subform display problem



## cathyann10 (Feb 29, 2008)

I used a template to create a database for a food pantry. I then imported the excel speadsheet into the database. The problem is that when I did this the information that I imported didn't all work as I had hoped. I had a subform for calls and notes date/time stamped (3 reports & 1 table) just for this part that isn't working at lease. The subform is suppose to be linked by ContactID and ContactID which it is. and on the table where the notes will go when we start using the database goes blank (meaning the form goes to a box only no place to enter any data) when I start a new record? The few test records I had put in as I created the forms and queries and reports work. So I thought possibly the problem was that the contactID numbers were not in the table so they (the files I imported) needed to have the number imputed (which worked!) but now still when I want to add a "new" record it goes to a blank box.

Please help if you can.

Thank you!!!!


----------



## OBP (Mar 8, 2005)

It would be much easier for us to help you if you could post a zipped copy of the database for us to look at.
It is possible that your ContactIDs in both tables are Autonumber fields if this is so then that makes it difficult to use subforms, the main Table should be an Autonumber and the Sub table should be a Number type Integer, but without seeing the database I am only guessing.


----------



## cristobal03 (Aug 5, 2005)

Wont be able to post an attachment, Tony, she's new. Welcome, by the way :up:

I think you're right, though, the dependency hasn't been correctly established. However, that shouldn't make the subform control go blank, should it? Seems to me like the form would display, only you'd get errors when you tried to _save_ the record. But I'm not sure about that. I've had similar problems in the past, but it's been so long and the fix was apparently simple enough that I have no clue what I did.

Anyway.

chris.

[edit]
Ah, but. A bound form will be blank if it has no record source, I think. Maybe that's worth pursuing?
[/edit]


----------



## OBP (Mar 8, 2005)

I have Pmd her my email.


----------



## cathyann10 (Feb 29, 2008)

I am downloading the zip proggram and will zip it by email to you.. I have the relationship as a one to many.
You asked:
It is possible that your ContactIDs in both tables are Autonumber fields if this is so then that makes it difficult to use subforms, the main Table should be an Autonumber and the Sub table should be a Number type Integer, but without seeing the database I am only guessing. 
My reply is the main PK is a autonumber the other ContactID is Integer. I will send you the file and any help would be wonderful. I have spent hours on this for a non-profit food pantry and I really want to do a good job for them... so thank you very much!


----------



## cathyann10 (Feb 29, 2008)

I also want to know how to put the dates on the reports that I select when I run them... Can't find this info in all my books or online... Possilby don't know what to call it to get the answers...?

Any Ideas???

Thanks Again!!!

Cathyann10


----------



## jimr381 (Jul 20, 2007)

The date will be done inside a "Textbox" control using a =now() within it.


----------



## cathyann10 (Feb 29, 2008)

I'm sorry I don't mean todays date and time I need the Parameter dates that were selected for the report...
Thanks,
Cathy


----------



## slurpee55 (Oct 20, 2004)

cathyann, don't worry - you are at the right place. I'm not bad, jimr and cristobal are incredibly better with databases than I am, and OBP (old man that he is, of course) has forgotten things we never learned yet - he had to, in order to retain what he knows.


----------



## cathyann10 (Feb 29, 2008)

I wonder if =parameterdates() would work?

You think it could be that easy?


----------



## cathyann10 (Feb 29, 2008)

Tried and didn't work...


----------



## OBP (Mar 8, 2005)

cathyann, one thing at a time please , the dates for the query/report parameters are easy to do, you just have 2 unbound fields on a form and then reference those (one or both) in the Query Criteria Row.
I will get back to you on the blank Sub Form.
Can you tell me why the main form Notes say the same thing as the Sub form Notes?


----------



## slurpee55 (Oct 20, 2004)

OBP, can you post the database? We want to play too....


----------



## OBP (Mar 8, 2005)

Cathyann, the problem with your subform is that it has the Property setting "Allow Additions" set to "No", thus preventing any new records.
Also you do not need three subforms, the 2 subsub forms can be replaced with the Fields from the Calls Table.


----------



## OBP (Mar 8, 2005)

cathyann, so what dates do you want on what report(s)?
Where do you want them on the Report(s)?


----------



## OBP (Mar 8, 2005)

slurpee, just for you


----------



## cathyann10 (Feb 29, 2008)

You said:
Also you do not need three subforms, the 2 subsub forms can be replaced with the Fields from the Calls Table.
I guess I must have done each report that showed in the list in the wizard (not knowing what I was doing)

Also the reason I have the notes in both places is due to the fact I needed t see them on the form and wasn't sure I was going to get it working before we go "Live" with this AND that field will disapear when the other is working...

Also I would like the Parameter dates to be the ones we select when we click on the report. The can be on the top next to and under the report name but I will be happy with anything at this point I need to get this done... Thank you!!!!


----------



## OBP (Mar 8, 2005)

cathyann, where do you select the reports?

have got the subform working OK now?

Do you have a Menu or switchboard?


----------



## cathyann10 (Feb 29, 2008)

have got the subform working OK now? Wonderful!!!! What was the problem, so I can learn from my mistakes..

I just click on the report in the navigation pane. Then it asks for the dates (Start and End) and it runs the report. Hope that is what you want...?


----------



## OBP (Mar 8, 2005)

cathyann, that should have read 
have *you* got the subform working OK now?
I posted the fix in Post #14.
I will have look at the Reports


----------



## cathyann10 (Feb 29, 2008)

I tried the Fix in #14 When I click new record the form is there (Yippie) but I entered data and went to exit and it gave me this "Run-time error 2109 there is no field named 'call detail subform record' When I said yes to debug I go to VB & it says you must enter a value in the "Visits notes ContactID Field" I thought that was automatic? The highlighed line in VB reads " DoCmd.GoToControl "Call Details Subform" Hope this is what you need.


----------



## OBP (Mar 8, 2005)

Did you have all three Sub forms still on there?
If so ensure that each one has the Contact ID on it, but preferably just have the one form.
The parameters for the reports are in the queries and cna be transferred to the reports, however that is not a very efficient way to do it as you have to enter the dates for each report.
If you have the dates on a form you only have to enter them once.


----------



## cathyann10 (Feb 29, 2008)

When I run a report and then print the report how will I know what dates were selected each time? We run these different all the time for 1 day or wk or mo. Qtr 6 mo and yr etc...
Also I haven't deleted the other subforms out of fear.... Do I delete all but the call1 one? As you can probly see there was another one on a tab but it don't work. So that is when I created a new one and I guess I got three?? I will save a copy of the file and try and do that.... 
Thanks,
Cathyann


----------



## OBP (Mar 8, 2005)

Have a look at this version, it has a working subform and the dates on the Food Stamps Report.


----------



## cathyann10 (Feb 29, 2008)

Not sure what to delete? Do I delete the forms in Navigation pane or on the form because there is only one on the form correct? even thou there is 3 seperate boxes in the subform those aren't what you mean correct? I am trying to get one subform and not three just not sure what you mean... Thanks!


----------



## slurpee55 (Oct 20, 2004)

Tony, there is something odd with, to start with, the food stamps count. I was just looking at the basics, and her query just doesn't count everyone, regardless of what dates are entered. I made a new query and had no problems.
I haven't looked at much else yet...been trying to get this to make sense.
Never mind - figured it out. Despite asking for a start date, it pulls off the Entry Date column - my mistake. And there are some entries with no entry date, which confused me more....


----------



## cathyann10 (Feb 29, 2008)

Are you emailing it to me?


----------



## cathyann10 (Feb 29, 2008)

Could the food stamp counts be wrong due to neg. number??? I don't know why that is?


----------



## OBP (Mar 8, 2005)

cathyann, you can download it from this forum at post #24


----------



## cathyann10 (Feb 29, 2008)

That attachment wasn't there a min ago! ? Thanks I going to look now


----------



## cathyann10 (Feb 29, 2008)

The dates aren't working there were supose to be automatic with today. Can I just put in =today() Im not sure how to do time


----------



## cathyann10 (Feb 29, 2008)

Can I do the other report dates that easy just put text box with start and end in them as the Food Stamps report?


----------



## cathyann10 (Feb 29, 2008)

Now that I am looking at the subform I see it will only let me use 1 visit? right? I did have a table on the form so I could track many visit. See we have clients who will do anthing to get more then one food pick up each month so we need to see other visits and notes from other viist as well linked by dates.. This is what we didn't have before that is where those notes came from the "old" way we did things... Does this make since?


----------



## slurpee55 (Oct 20, 2004)

little things - run the food stamp report for all possible date (1/1/1998 to 1/1/2010 just to cover all) and two things - one person gets left out because there is no Entry Date - OBP, can you make it a requirement that there be an entry here if the Food Stamps box is checked?
Also, since your sums are negative, I would just multiply your report values (when needed) by -1, such as in the food stamps report
=Sum([SumOfFood Stamps])*-1


----------



## slurpee55 (Oct 20, 2004)

oops, OBP did the second part already...


----------



## cathyann10 (Feb 29, 2008)

Yes, I can make the entry date req. the only reason it isn't is due to the import process and the testing o the database I don't want to have to fill out the whole form each time. Many fields will be req whem all is said and done...
So I just change the formula to add *-1 and this will fix the problem... Going to do that now... Not sure what database to do it on??? the one I downloaded from here fixed? I guess but I need the notes to have a table and multi dates(visits) and notes by visit as well.... 
Thanks!!!


----------



## OBP (Mar 8, 2005)

Change the form's Property from Single Form to Continuous Forms and it will display more than one record, you just have to rer-arrange the labels and fields to make it more usable.
The fields on the report come from the new Columns in the query.


----------



## cathyann10 (Feb 29, 2008)

Can't I use the datasheet because if I have 12 visits the form will be soooo veryyy longgg.


----------



## cathyann10 (Feb 29, 2008)

I sure am learning alot here!!! I really do appricate all you guys are doing.... THANK YOU!!!


----------



## OBP (Mar 8, 2005)

cathyann, I will make the desired changes tomorrow.


----------



## slurpee55 (Oct 20, 2004)

Goodnight OBP!


----------



## cristobal03 (Aug 5, 2005)

I can't look at the database, but just judging by this discussion, there's an awful lot of bandaging and creep going on here. For example, multiplying a control's value by -1 to coerce it to a positive value. Will the value for that control always be negative? Why not derive the absolute value instead? What about the data causes it to be negative in the first place if it should be positive? If the Entry Date is populated sometimes and not others, how denormalized is the data structure? That attribute apparently should be required since it's a determinant value for properly populated queries, in which case it should be a (member of a) primary key or an alternate key. I realize I can't contribute much without Access, but it might be a good idea to step back, have a look at the cost-benefit of managing the creep, and think about rebuilding based on the business rules. It certainly can't be any more difficult than tweaking the current application, as many changes are being made.

Just a suggestion.

chris.


----------



## cathyann10 (Feb 29, 2008)

cristobal03 said:


> I can't look at the database, but just judging by this discussion, there's an awful lot of bandaging and creep going on here. For example, multiplying a control's value by -1 to coerce it to a positive value. Will the value for that control always be negative? Why not derive the absolute value instead? What about the data causes it to be negative in the first place if it should be positive? If the Entry Date is populated sometimes and not others, how denormalized is the data structure? That attribute apparently should be required since it's a determinant value for properly populated queries, in which case it should be a (member of a) primary key or an alternate key. I realize I can't contribute much without Access, but it might be a good idea to step back, have a look at the cost-benefit of managing the creep, and think about rebuilding based on the business rules. It certainly can't be any more difficult than tweaking the current application, as many changes are being made.
> 
> Just a suggestion.
> 
> chris.


Chris, I agree with the neg number needing to be fix better but I don't know how else to do it. Do you have access 2003 or not at all? well anyway the field that puts the sum as a neg is only checkbox yes or no so why it is neg ?? it only counts the Yes's. The only reason the entry date is no set to req. is because when I imported it I didn't want any problems. and now that I am working on it I don't want all the fields that will be req. to be that way yet so I don't have to keep entering all the data to try things out. Also I used a template in 2003 to start this project for the non-profit food pantry cause that is what I had and they bought 2007 so I had to as well. I don't think I could do all of the stuff on my own from scratch and I can't find the template online that I used. When I installed 2007 it took out my 2003 so I can't go back and get it. Well anyway that is how I got here... Volunteering my time.. Alot of it at that...


----------



## cristobal03 (Aug 5, 2005)

I think that's all the more reason to restart. Database schemas are not conducive to rapid application development, which is basically what you're describing when you say you didn't make certain fields required for testing purposes. You shouldn't think of a rewrite as wasted effort; developers do it all the time. The product is almost always better in the end.

I'm just saying, in my professional opinion, based on what I've seen in this thread, the best course is to redesign the database from scratch, starting with the context drawing or business rules. By that I mean, write down--in prose--all the rules and requirements you need to satisfy by using your database. Then draw out your tables on scratch paper (have a look at Wikipedia's article on normalization first). Once you have that done, design your tables so that they're pristine, then get your forms working like they need to be. Finally, export the data you have in a way that it'll fit correctly into the new schema.

I bet you won't even have to do all that by yourself, if you want to do it at all. There are plenty of helpful people around her that undertake projects like this pro bono.

I'm just saying, given the state of the file, it's what I would _probably_ recommend if you gave me your database and asked for help.

And no, I don't have Access anymore. I use more Web-ready technologies now since I've shifted my focus from small business to Web design. But database design principles are pretty universal.

chris.


----------



## OBP (Mar 8, 2005)

Chris, ther is actually not much wrong with the design of the database considering it comes from a template, it is quite a simple one with just 2 Tables at this stage.
It just has a few "Property" and "Control" issues that cathyann was not aware of. They are, as you know, quite difficult to pin down unless you are very familiar with development work.


----------



## cathyann10 (Feb 29, 2008)

Tony, Thanks for reasruing me I was worried all night I had to start over that it was bad.... I am glad to here it isn't as bad as I was thinking...
Thanks!


----------



## OBP (Mar 8, 2005)

cathyann, I have emailed you a new copy of the database to play with.


----------



## cathyann10 (Feb 29, 2008)

OK Thank you!! I can't wait to see it...


----------



## cathyann10 (Feb 29, 2008)

Why can I not look at the calls form in layout view? I would like to replace all the Ref. to "Calls" and put in "Visit". Can I do that without screwing up anything... I like the Report form and splash screen as well I also like the added buttons on the contacts form as well... You did nice work. Also would it hurt anything if I made the dates come up with =now() for date and time on the notes so we don't have to enter them? For the city I can make it a drop down to select city that is fine I thnk I can do that?! I did it before. (In access 2003) The state I have set up to make it always IL due to the people we serve must live in out county to be eligable. Again Thank you so very much for all you have done for me!! I really do appricate everything!!!!


----------



## cathyann10 (Feb 29, 2008)

Tony I just emailed you an error I get asking for contactID in the contact form while using the subform notes.


----------



## OBP (Mar 8, 2005)

When you say "can You can look at the Calls form in Layout view" do you mean do you mean "Design View? Or "Datasheet" view?


----------



## cathyann10 (Feb 29, 2008)

When I right click on Calls report and go to layout view I got a error msg Run-time error but after I close it then it opens in layout view. Then Do you see all the lines in that report with just just callID and contactID (no notes or anything) I imported those numbers trying to make the subform work thinking I needed to have a line for each row of data. Can I clean that up? If so how?


----------



## cathyann10 (Feb 29, 2008)

Also in the splash screen where it says "Good evening" could it be set at your time? Cause it is 10 Am here???  Is the place to chage time in properties?


----------



## OBP (Mar 8, 2005)

You can clean up the records in the visits table if you want by deleting them all, or the ones that you want, but if you leave any in the table you cannot reset the Autonumber ID back to zero.


----------



## cathyann10 (Feb 29, 2008)

Your talking only the call ID number I can't make to zero correct? Cause I don't care as long as I don't screw up the Contact ID numbers they will be ok right? I just enter new notes when the client comes in for March food and it will add them to the list? Correct? with new Call ID number and same Contact ID number. Am I correct in my thoughts and understandings of how it is working?


----------



## OBP (Mar 8, 2005)

Yes that is correct, or you could leave the current blank records in place and fill them as required.
Someone has pointed out an error in my Main Menu "Greeting" message, they have suggested that this would work better
Me.TimerInterval = 300000
DoCmd.Maximize
If Time() < #12:00:00 PM# Then
Me.Text37.Value = "Good Morning "
ElseIf Time() > #6:00:00 PM# Then
Me.Text37.Value = "Good Evening "
Else
Me.Text37.Value = "Good Afternoon "
End If

You could make a good start on the cities by Creating Cities table and making the City Field Indexed with No Duplicates and then create an Append query using the contacts cities for data.


----------



## cathyann10 (Feb 29, 2008)

WOW!!! All the notes work!!!!!!! YIPPIE!!!!! I have spent 5 days 8 hrs a day trying to fix this problem.... You are amazing!!!!!!!!!!!!!!

I hate to ask for alittle more help but can you help me change the words calls to visits? I tried in the last database which could have created some of the problems cause at some point the subform that was working stopped and I am not sure what I did. But I know I imported data and changed some names... I want the volunteers (mostly retired older ladies that are not real computer savy and aren't always the same ladies either) to be able to follow what the program is doing. How I ended up with calls is due to the template I used. I need it to be visits. Thank you again !!!! I have learned alot....


----------



## cathyann10 (Feb 29, 2008)

Do I need to do something to the main menu? The cities are very few due to our clients must come from our small county of not alot of cities (About 5 of them) so do you think I need to do all that for such a few will a drop down work. But I guess the append query will add a city if not there correct? I remember doing a drop down that you can add a city if not on the list right in the form isn't that possile. I think I did that about 3 wks ago but started over with this template instead.

Also is there useless forms in the list I don't need anymore?


----------



## OBP (Mar 8, 2005)

The "Call Listing Subform" is no longer needed.
You can just type in 5 Cities straight in to the table. It is much better to use a Table and query for the Combo as it is easier to add new cities.


----------



## cathyann10 (Feb 29, 2008)

What if I am not there and they need to enter a city that is not in the list. Remember these are old ladies with "NO" computer skills but data entry. Would someone need to go into the table to enter the new city? Don't think they could do that? Even though I probly do need to do something as many mispelled words (cities incl) that I found in the database that makes the quries not accurate Huh!


----------



## OBP (Mar 8, 2005)

That is why it would be best to create a table with as many of the Cities as you can identify right at the start.
You can use the Limit to list and let them add any new ones, or I like to add a bit of VBA in the Combo Double Click event to open a Cities Form to let them add them in that.


----------



## slurpee55 (Oct 20, 2004)

cathy, are your cities being pulled from the main table of data or have you (or OBP) set up a separate table with the cities listed in it? I would do the latter - especially if it is just a few cities, because then you can alter your list much more easily.


----------



## cathyann10 (Feb 29, 2008)

Ok I am going to get a map and make a table an put all cities in our county in the table. then I will link them Not sure how to do that but maybe contactID or a wizard will help do that I will make a back up of the database before I start. Thanks!


----------



## OBP (Mar 8, 2005)

Create the Table and then a Query. Then on the Contact form create a Combo based on your new query and have it "Bound" to the City field on the form. Ideally you would only store the City's ID in the Contacts table, but you can probably get away with storing the city's name instead.

Cathy, what state are you in, have you checked the Internet, they may have lists already.?


----------



## cathyann10 (Feb 29, 2008)

I looked at the contact list and filtered and looked at the listing of cities and created my list from that. I see we have helped pople in Emergency situations that were not in our county. This could create a problem for the people entering the data at a later date when I am not there to edit the table. But I will try and teach the Director of the Food Pantry to do this. 

I am done with the table now I am doing the query, Can I use a wizard? I will try it.

I am in IL

Thanks!


----------



## OBP (Mar 8, 2005)

Cathy have a look at this Excel sheet.

If you need any other local States I have those as well.


----------



## cathyann10 (Feb 29, 2008)

I made the query and clicked and put the combo box where the city was on the form in desgin view but the box says unbound (it does have a drop arrow on the right side) how can I change to bound. I thought the wizard would make it bound to the query? Am I wrong? Not real sure about what queries are doing behind the scene.


----------



## cathyann10 (Feb 29, 2008)

It does seem to be working thou... it has the list of cities and won't let me type one not in the list... why does it say unbound.


----------



## OBP (Mar 8, 2005)

In the Combo box Properties look at the Row Source for the Combo it should be City.
When you change records the Combo should change to reflect the city of the record that you are on.
Did you see the list of Cities on the Excel sheet?
You can import that in to your database and use it for your Cities table.


----------



## cathyann10 (Feb 29, 2008)

What did I do that now my Contact form is not avalible in layout form?


----------



## cathyann10 (Feb 29, 2008)

The excel spreadsheet hasn't shown up yet? It's going to be in # 66 but not there yet.


----------



## OBP (Mar 8, 2005)

I don't know do, if you click on the Form in the Forms tab and then "Design" view does it come up Ok?


----------



## cathyann10 (Feb 29, 2008)

This is what is in the row source SELECT [Cities Query].[Cities] FROM [Cities Query] ORDER BY [Cities]; 
Is that correct? Nothing is in control source. Am I ok?


----------



## cathyann10 (Feb 29, 2008)

Yes in Design veiw but no layout is available it says??


----------



## cathyann10 (Feb 29, 2008)

I got the spreadsheet but wouldn't that be a to long list of city to scroll thru for needing only a few? Or would we be attaching it to zip codes so they enter the zip and the city is auto?


----------



## OBP (Mar 8, 2005)

In Control Source select City.
I do not recognise "layout", so I am not sure what to advise you.
I have emailed you the Excel sheet.


----------



## cathyann10 (Feb 29, 2008)

Or poss I can put the list in order of most used 1st??? I think that is possible. That may work.


----------



## cathyann10 (Feb 29, 2008)

Layout view is not avilable to others as well???? Why? I think it is a 2007 feature


----------



## cathyann10 (Feb 29, 2008)

Why does my red dot on my msg say I am not online when I am?


----------



## OBP (Mar 8, 2005)

That would be neat wouldn't it .
Can you do that do you think?


----------



## OBP (Mar 8, 2005)

Cathy, I think entering the Zip and looking up the City would be a really good feature.


----------



## OBP (Mar 8, 2005)

Do know about Dlookup?


----------



## cathyann10 (Feb 29, 2008)

I can try never done that before.


----------



## cathyann10 (Feb 29, 2008)

I don't know what it is but I know it is in my book, I've seen it. I can try and do it. 

Was there something I am supose to do on the splash screen you said there was a error or something??


----------



## cathyann10 (Feb 29, 2008)

Also Can I change all references to Calls and make it Visit? Will that hurt anything???


----------



## cathyann10 (Feb 29, 2008)

When I import the Excel spreadsheet you sent me do I import the ID as well? Or does access create its own?


----------



## OBP (Mar 8, 2005)

It was the Good Morning/Good Adternoon/good Evening message on the main menu, apparently it is not working properly.
It should say Good morning before 12 noon, Good afternoon after 12 noon and good evening after 6pm.


----------



## OBP (Mar 8, 2005)

I would let Access create the ID and then delete the one that comes with the spread sheet.


----------



## cathyann10 (Feb 29, 2008)

I went back to look at the databases you have emailed me and V.02 is the last one I could go into layout view. So when the splash screen and that stuff was added it took away Layout view.


----------



## cathyann10 (Feb 29, 2008)

OK I will let access do the ID that is what I thought.


----------



## OBP (Mar 8, 2005)

It could be because they were created in Access 2000/2002.
You could go back to your original Database and try importing the forms in to that, or possibly convert it to Access 2007.
I do not have Access 2007.


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> It was the Good Morning/Good Adternoon/good Evening message on the main menu, apparently it is not working properly.
> It should say Good morning before 12 noon, Good afternoon after 12 noon and good evening after 6pm.


So is there something I need to do to the splash screen so it is correct?


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> It could be because they were created in Access 2000/2002.
> You could go back to your original Database and try importing the forms in to that, or possibly convert it to Access 2007.
> I do not have Access 2007.


I tried conveting to access 2007 Didn't work. So I guess I will live with it this way... at leaset it works!


----------



## OBP (Mar 8, 2005)

You do not need to do anything with the Splash Screen.
But you could copy that code that I posted in to the Main Menu's On Current and On timer Event procedures to replace what is there, but it is not essential.
You could just delete the code from them and the Unbound field if you do not want it to say anything.


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> It could be because they were created in Access 2000/2002.
> You could go back to your original Database and try importing the forms in to that, or possibly convert it to Access 2007.
> I do not have Access 2007.


I am trying to import the spreadsheet into access but it is asking me what the "Data Type" is? the default is Double? This is for the Zip code column


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> You do not need to do anything with the Splash Screen.
> But you could copy that code that I posted in to the Main Menu's On Current and On timer Event procedures to replace what is there, but it is not essential.
> You could just delete the code from them and the Unbound field if you do not want it to say anything.


OK thank you


----------



## OBP (Mar 8, 2005)

cathyann, I have imported the Worksheet in to my version of the database, I also have the dlookup working, which is a bit tricky because of the Format of the Zip Code field.
What I have done is to create an unbound field, which can be hidden and then made it's row source 
=Left([Zip Code],5)
in the after update event of the Zip code field I have adde this code 
Me.City = DLookup("[City]", "City", "[ZIP] =" & "forms![Contacts]![text229]")
It really works a treat, you type in the code and it puts in the city.
I will email you a copy so that you can Import the City Table and look at what I have done on the Contacts form.
Would you like all of the Cities in the US to look up as it says your Zip Code in record 4 (58321) is not Belvidere.
ps I used to live in a village in Kent called Belvedere.


----------



## OBP (Mar 8, 2005)

I have emailed you.


----------



## cathyann10 (Feb 29, 2008)

If I import the table from one access file to another is everything going to be the same Like "Text 229" will I have that? All cities in US is ok too!!
This is cool!!! Really neat!! How did you do all that so fast!! I must be slow! But I am learning!!!
I can't wait to see it!


----------



## cathyann10 (Feb 29, 2008)

Can I just use yours? Is the fix on the splash screen already?


----------



## OBP (Mar 8, 2005)

The Table of cities will just come straight in. If you import the Contacts Form that will come in as Contacts1, which you can try out and either make the same changes to your form (good learning) or rpelace your form with this one by removing the "1" from it's name.
But you will lose any changes you may have made.
I will send you a seperate database with just all the cities in.


----------



## cathyann10 (Feb 29, 2008)

How come we don't have to create a realationship for the cities Table? I thought you had to if you were going to use it? I didn't know you could just create a table of info to use? Hmmm..

Nice work! I don't think I did anything on the other copy of the database that I couldn't just use this one???


----------



## OBP (Mar 8, 2005)

You can just use it. I am emailing the Cities database, it is a pretty big table.


----------



## cathyann10 (Feb 29, 2008)

Did you just move the city & zip code, right? I know how to do that.....  Or am I missing something?


----------



## cathyann10 (Feb 29, 2008)

Did you notice there is one city with a zip but no city name. I went online and there is no city for the zip code on the list. I should just delete it?


----------



## OBP (Mar 8, 2005)

I added the text 229 field and some VBA in the Zip Code after update event.
I have emailed the big City database to you.
I have to go now, let me know how you get on tomorrow.
don't be afraid to dissect the forms to see how they work as you have a backup in the Zip file


----------



## cathyann10 (Feb 29, 2008)

Thank You!! You have been So VERY HELPFUL!!!!! A great BIG THANK YOU!!!!


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> I added the text 229 field and some VBA in the Zip Code after update event.
> I have emailed the big City database to you.
> I have to go now, let me know how you get on tomorrow.
> don't be afraid to dissect the forms to see how they work as you have a backup in the Zip file


I just tried the zip code you made, where you enter the zip code and the city is filled in. All it does is beep at me won't let me enter a zip (in text 229 zip box) Please advise... thanks!


----------



## cathyann10 (Feb 29, 2008)

Is there a way to import from the excel spreadsheet all the "notes only" and have them link up with the correct Contact ID due to I had the field set at 255 Charachters and needed it to be memo for larger amt of data so I missed some of the notes. I want them to go into the Reports box on the main contact form (not on the sub form) ??? I hope so cause cut and paste will take forever!!! and Ever! Thanks agin!!!


----------



## slurpee55 (Oct 20, 2004)

Run the Import wizard and you can select what you want imported - if you have Contact IDs in the Excel file that are the same as your main table, import that with the data you need and skip the rest. (On about the 4th screen of the import, there is a box on the right that you can check that says "Do not import field" - at least in 2003. If you are using 2007 it may be a bit different.) Or, alternatively, just make a copy of the worksheet in Excel, delete what you don't want and import that sheet into Access.
It needs a different name than the main table, and, to append those newly-imported columns to the original table, they will need new (but presumably just slightly different) names - if you field is named, say, Comment, call the new one Comment1.
Run an append query, delete the old fields. Put the new fields in where you want them, if location in the table is important, then delete the "1"s or whatever so that they have the same name as - and can take the place of - your old fields to all your queries, reports, forms, etc.


----------



## cathyann10 (Feb 29, 2008)

Ok I am going to try to import into a new table and append into old table... Never thought of doing it that way... Thanks!!! I am on my way to gettting this done thanks to you 2!!
Thanks!
Cathyann


----------



## slurpee55 (Oct 20, 2004)

hardly due to me!!! OBP is pretty great to have around, isn't he?


----------



## OBP (Mar 8, 2005)

Cathy, you enter the Zip Code in to the Zip Code Field, not text229.


----------



## cathyann10 (Feb 29, 2008)

OBP said:


> Cathy, you enter the Zip Code in to the Zip Code Field, not text229.


I did that and the city don't change???


----------



## cathyann10 (Feb 29, 2008)

slurpee55 said:


> Run the Import wizard and you can select what you want imported - if you have Contact IDs in the Excel file that are the same as your main table, import that with the data you need and skip the rest. (On about the 4th screen of the import, there is a box on the right that you can check that says "Do not import field" - at least in 2003. If you are using 2007 it may be a bit different.) Or, alternatively, just make a copy of the worksheet in Excel, delete what you don't want and import that sheet into Access.
> It needs a different name than the main table, and, to append those newly-imported columns to the original table, they will need new (but presumably just slightly different) names - if you field is named, say, Comment, call the new one Comment1.
> Run an append query, delete the old fields. Put the new fields in where you want them, if location in the table is important, then delete the "1"s or whatever so that they have the same name as - and can take the place of - your old fields to all your queries, reports, forms, etc.


Wow! I just did an update query!! Wow, there was alot of confusing stuff to figure out but I did it.. now the complete notes are in there as a memo field. Not text that is how I lost some of them....
But then I deleted my Copy's of the 2 tables I made copies of before I ran the update then closed the database and made a copy of it while everything except the zip codes were working and then I re-opened it to do some other things and now the splash screen is all blue and no buttons?? Why! I didn't do anything to that???


----------



## cathyann10 (Feb 29, 2008)

cathyann10 said:


> Wow! I just did an update query!! Wow, there was alot of confusing stuff to figure out but I did it.. now the complete notes are in there as a memo field. Not text that is how I lost some of them....
> But then I deleted my Copy's of the 2 tables I made copies of before I ran the update then closed the database and made a copy of it while everything except the zip codes were working and then I re-opened it to do some other things and now the splash screen is all blue and no buttons?? Why! I didn't do anything to that???


The reports buttons don't work either??? Why?? Hmmm... I didn't do anything really!!


----------



## cathyann10 (Feb 29, 2008)

I went back to the Ver #4 and did the update query again and everything is working fine, but I did lose the zip code thing we had on the ver#5


----------



## cathyann10 (Feb 29, 2008)

I have emailed you the most up to date database. With some notes as well.

Thanks!
CathyAnn


----------



## cathyann10 (Feb 29, 2008)

I just thought of another I think small problem. Is it possible to put a scroll bar in the "reports" box on the "Contact" Form? Is it just a propertiy setting? ( I was looking there but affraid to do anything casue everything is working GREAT!) Some of the report notes now that I imported the full amount of them are off the screen and I don't want to make the box bigger.


----------



## OBP (Mar 8, 2005)

cathy, take a copy of the database for backup and then have a play with one of them.
The Field's property that you are looking for is "Scollbars", it can be set to "None", "Both", "Horizontal" or "Vertical".
I have the Grand Children coming this afternoon so I am not sure how much I can get done.
One thing that I have added to the Contacts Form is for the State to be entered with the City when the Zip Code is entered. So that is another field that the users do not need to worry about entering.


----------



## OBP (Mar 8, 2005)

Cathy, I have emailed you, I can't open the attachment to make any sense of it. Can you resend the database.


----------



## cathyann10 (Feb 29, 2008)

Tony, Thats ok you go enjoy your grandkids! They are only little for a short time then they don't want to go spend the afternoon with grandpa!  Are you sending me back the database so I can use the most updated one to do all the things I need to do? I will play with the scroll settings, and yes make a backup first...


----------



## OBP (Mar 8, 2005)

Cathy, I think that you sent me an Access 2007 version, which I can't work on.
You should have your own copy, haven't you?


----------



## OBP (Mar 8, 2005)

Cathy the reason that the Dlookup code didn't work is due to you changing the name of the "City" Table.
Unlike Access, the Access VBA Editor does not pick up and adjust for Table, Query, Form or Report name changes.


----------



## cathyann10 (Feb 29, 2008)

I wanted to use SN #'s but I found out we couldn't do that. That was a way to make sure we had the correct person each time. A unique number for ea of them. But I guess we must use the last name to find them in the database but the spelling must be correct. (But if I have a print of a report of all people by names and address and entry date I could correct mispelled names and add missing person in the home as well. You see they used to do all this with 3x5 cards and enter the data at a later time off the card. Now we will be doing as the customer is sitting in front of us. Much better system... I hope this makes sense.


----------



## cathyann10 (Feb 29, 2008)

Yes I have a copy but I want to make changes on the one with the working zip code? Right? Or Can I correct that? What Name was the cities under? I don't remember?


----------



## OBP (Mar 8, 2005)

You changed the name to "all cities in IL", I have changed it back for now.
I have emailed you with some ideas.
I can send you back your latest one with a working Zip, but please read the email about Combos.


----------



## cathyann10 (Feb 29, 2008)

What was it, so I can change it back? I know what it is now, I need to know what it was?? I have no memeory due to lack of sleep???? This keeps me up when I have problems and keeps me up when It is working casue I am doing things to it.... I guess no sleep until it is done for me!!!


----------



## cathyann10 (Feb 29, 2008)

I see a real problem that could create a disaster. I think it is an easy fix if you know how which I don't. I have an Idea but how to do it is another issue? When the tab stops at the "Reports" box in the contact form it highlights all the text if they hit a key it is gone!!! This will happen!! Can we make the curser only at the end when the tab stops in that box and not hightlight all text inside it?


----------



## cathyann10 (Feb 29, 2008)

Also I changed the Zip code table back to "city" Which I looked back in the VB and I think it refers to "City" so I changed it but still nothing? Am I wrong?


----------



## cathyann10 (Feb 29, 2008)

cathyann10 said:


> I see a real problem that could create a disaster. I think it is an easy fix if you know how which I don't. I have an Idea but how to do it is another issue? When the tab stops at the "Reports" box in the contact form it highlights all the text if they hit a key it is gone!!! This will happen!! Can we make the curser only at the end when the tab stops in that box and not hightlight all text inside it?


I see I could just make it not a tab stop in properties, Correct? Maybe that is the best thing to do... I did add the scroll bar as well. Can I make it show all the time. When I go to a client that has more info then you can see in the box it don't show the scroll bar unless you click inside the box then it is there?


----------



## OBP (Mar 8, 2005)

You can also make it "Enabled"= no or "locked" = yes to prevent changes, but how can they then make entries?
The tab stop is best.


----------



## cathyann10 (Feb 29, 2008)

OK that is what I will do! Easy fix!! My kind of fix!!


----------



## cathyann10 (Feb 29, 2008)

I just emailed you zip code print screen problem... why does it not work for me????


----------



## OBP (Mar 8, 2005)

I didn't get the Screen Print with the email.


----------



## cathyann10 (Feb 29, 2008)

OK I sent it to ways in the same email hope you get it this time.. but it is a run time error 2110 Access can't move the focus to the control callID?


----------

