2 Forms into One Table

[webmaster999]

Alright guys, I have a question...

I am building an MS Acess db for a guy who has 6 locations countrywide that he does bulk mailing from.

He needs a table list of all customers for each location (I have made the 6 identical tables, labeled appropriately) and a form to enter data into them. I suppose that I will need to create a form for each table otherwise it wouldn't know which one to enter the info into. Am I right here?

Also, he wants the phone number field all by itself on its own form and when the data entry people enter the phone# of a client, it will automatically check the back table to determine if its a duplicate. If so, it should bring up a message saying so. If not, then it would continue to the other form for data entry of everything else. I have NO IDEA how to do this. I dabbled in VBasic in high school but got into networking instead. Now I am in a fix - I wanna do this for him - but I am stumped.

And not only that, but he also wants to be able to enter the 5digit Zip and have it search (lookup) a 7th table and bring in the 4digit extra coding and the city/state. Is this even possible?

Any help is GREATLY APPRECIATED IN ADVANCE!!!!

Matt A+, MCP, MCP+I, MCSE Windows NT 4.0, MCSE Windows 2000 Early Achiever with Security Emphasis

 

[RickSpr]

A partial answer: If you have some way of determining which location is opening a form, you can have the form dynamically alter its Record Source property to use the associated table. Assuming you're going to do a front end/back end application, the front end for each location could contain a small table in which the location is stored, and all your forms and reports could test that value to set their Record Source properties.

The phone number form transition to the client data form doesn't sound like a problem. You'll need code in the phone number form to do the lookup, probably with the DLookup() function. Depending on whether a record was found, you either display a MsgBox or DoCmd.OpenForm to open the big form, then DoCmd.Close to close itself. You haven't given enough details to be more specific.

The zip code lookup I'm not sure about. You can't just use the 5-digit zip code to choose the Zip+4 extension, you'd have to use the street address or something with it. But street addresses have many variants, so processing them is tough, unless you're using only pre-processed and formatted addresses that always come out the same way. Again, you haven't provided enough details about how this would work. Rick Sprague

 

[webmaster999]

Thanks Rick!

Last night I went out and bought me an Access book. I am not sure how much it will help, so I am going to continue using this forum.

What I have done is to make the phone# field the primary key and so now, if there is a duplicate, it provides a message that there is a duplicate and it can't continue. I don't know if using the primary key is smart or not?

Now I need to figure out how to get it to go to the next form instead of the next record on the phone# form. Perhaps I should use DLookup?

Thanks again for your help!

Matt A+, MCP, MCP+I, MCSE Windows NT 4.0, MCSE Windows 2000 Early Achiever with Security Emphasis

 

[RickSpr]

Making phone# the primary key probably isn't the best idea, because it doesn't truly identify your customer (phone #s change). But you could still make it uniquely indexed to enforce the rule, without making it the primary key.

DLookup doesn't address navigating to the next form. It's for easily accessing a single fact from the database in code. Look up OpenForm in the Help file. That should get you started.

Your questions suggest that you have quite a learning curve ahead of you. I hope you have plenty of time to learn Access. Rick Sprague

 

[webmaster999]

Rick,

Thanks for your help! I hope you are willing to be used a LOT in the near future! I could sure use the help!

Yes, I have a LARGE learning curve ahead. I have created 4 databases in my lifetime now, and none of them have been this involved/massive. Two of them were for some online registration stuff for my church (pretty easy front form into back table). The other two were for my dad's work and they were pretty simple too. Now this guy wants me to essentially make this 'program' for him, that's really what it boils down to. Not a database, but a program... :0

Any help you can provide in the future will be greatly appreciated!

Matt A+, MCP, MCP+I, MCSE Windows NT 4.0, MCSE Windows 2000 Early Achiever with Security Emphasis