Spin buttons...

[DasT]

In my database there will be the needs to store a large amount of contacts (up to about 15) and on the form this would obviously take up a lot of space, so i need to know if its possible to use a spin button in some way to change the Control source for a text box (and the name of the label next to this) so data can be entered into different columns of the table through the same textbox on the form.

If this is not possible then is there another method of pulling this off anyone could reccomend that would work in a similar way to this?

thanks for any help

 

[Norie]

What about using a combobox, listbox or subform?

BTW

How are you storing the contacts. Have you given each contact it's own field? If so that isn't really the way to handle it.

You should have a contacts table that links back to the main table, allowing from 0 to many contacts.

 

[DasT]

At present i have 10 fields for each contact in the same table as all the other information, but the most important thing is that i need to be able to enter each seperate contact from a simgle box, into seperate fields, and then be able to create a report from this which shows each contact seperately

Plus i havn't before used a listbox or a subform before (at least i think not) so i am unsure on how they work,

 

[Norie]

At present i have 10 fields for each contact

Do you mean for each record you have 10 fields called say Contact1, Contact2,..., Contact10?

What happens if there are more than 10 contacts?

Also you probably have quite a few records with say only one or two contacts, leaving the rest of the fields blank.

If you have you seriously need to rethink your design.

As I said previously you should have a seperate contacts table which is linked to the main table.

 

[DasT]

well yeah, it is a bad design i admit, but let me just start over with a bigger description of my situation.

There are loads of companies in the database (at least when they're inputted) say about 100-200 each company will represent a different record and in each record there are about 20 different fields e.g. referance, name etc, i need to fine a way to have loads of different contacts per company added to the record without having to fill up my form with loads of text boxes.

and my first thought was a spin button which changed the control source of a text box and the label name next to it, (so the data enters into a different field and the user can tell which field that is) although this prooved a bit too difficult to work out.

This (once fully working) needs to then be able to generate a report and , if possible, only show contact fields that have data entered (i was going to work this bit out later , i dont' plan stuff very well do I?) then i think my brain exploded.

so i posted here for help ^_^

hope that sounds easier to understand

again thanks for your help

 

[Norie]

What you want to have is two tables one called say Companies and the other Contacts.

They would have the following fields

Company Table

CompanyID - a unique identifier for each company, normally generated using Autonumber. Or perhaps yor reference field is unique.
CompanyName
CompanyAddress
etc

Contact Table

ContactID - a unique identifier for each company, normally generated using Autonumber
CompanyID - a reference to the company table
ContactName
ContactAddress
etc

You would then link these tables with a one(Company) - to - Many(Contacts) relationship.

Now a company can have 0 to N contacts.

There would (should be no empty records/fields in the contacts table.

One you have a setup like this doing the type of things you describe is made far easier.

eg This query will return all the contacts for Company1

SELECT Contacts.ContactName
FROM Companies LEFT JOIN Contacts ON Companies.CompanyID = Contacts.CompanyID
WHERE (((Companies.CompanyName)="Company1"));

I hope you unsderstood at least some of this.

 

[DasT]

Sorry for the late reply, had to go enjoy my weekend .

I think i understand what you mean, its been about 3 years since i've done anything to do with Access which is why i'm a little rusty.

I'll give this a go and see if it works like i want it to.

thanks for your help

 

[DasT]

I hate to trouble you more like this, but any help is greatly appreciated

I've pretty much set this up right, but how would i enter these extra contacts via a form? as in my original post i said i wanted to try using a spin button in some way to change the control source in the table, because as well as entering this data via the form there needs to be a facility to view each contact to make amendments or delete the contact when necessary.

I'll experiment with different methods myself and see if i can get anything to work properly, hopefully i'll be able to sort something out

thanks