Question Please Help!!!

[pedroc]

Hi all,

Very very new to Access. I've been tasked with building a database in Access 2007 to monitor a number of clients that our project deals with. I have created a datasheet for the clients which lists the main contact details and project documents as attachments.

I have then created a separate datasheet for each client to record the details of the different contacts that we have at each client.

When I am viewing the form created for the Clients datasheet I would like to be able to "click a button" to bring up the relevant Contact forms.

Is this possible?
Do i need to start again?

Any help would be really appreciated.

Pete

 

[MStef]

As I can uderstand you, I suggest to make 2 tables, "tblClients" and "tblContacts". Link them via ClientID (primary key in tblClient).
Make 2 forms. Main Form on tblClints, and Subform on tblContacts.
Put Subform in the Main Form as subform, link them via ClientID too,
("Link Child Fields", and "Link Master Fields" properties).
You don't need a command button.

Look at "DemoContactsClientsA2002.mdb" (attachment, zip).

Look at tables, relationships, forms.
Open MainFormClient and see.

 

[pedroc]

Thanks for the reply Mstef.

I know that your response probably makes perfect sense to you but this is the first time i have really ever used Access - could you possibly talk me through it step by step and in plain English?

Many thanks

Pete

 

[MStef]

I have no time to talk step by step, ask if you need something.
But you have to know, I have Access 2002-2003, and it is very different of
Access 2007, (screen presentation).

 

[Trevor G]

Hi all,

Very very new to Access. I've been tasked with building a database in Access 2007 to monitor a number of clients that our project deals with. I have created a datasheet for the clients which lists the main contact details and project documents as attachments.

I have then created a separate datasheet for each client to record the details of the different contacts that we have at each client.

When I am viewing the form created for the Clients datasheet I would like to be able to "click a button" to bring up the relevant Contact forms.

Is this possible?
Do i need to start again?

Any help would be really appreciated.

Pete

Pete

What you need is in the 2 tables a field that will be able to have a relationship with.

So tblContact will have an ID field and is set to Primary and autonumber, this then gives you an individual number per contact.

In the second table tblProject you would have a field called ClientID and have the data type as Number.

At this stage there is no data in either table.

You then would need to create the relationship, so Select the Database tools tab, then look at the relationships. from there select the 2 tables and you will see in the tblContact a keysymbol next to the ID Field, you then drag that field from the tblContact table into the tblProject table onto the ClientID Field when you release the mouse it will show you the relationships window, and should at the bottom state 1 to many then select to use the cascasde update referential integrate option and then the 3 box's below (Tick each box). Save and close the window.

Next create the form.

Open the tblClient table and then select the Create tab, from there select to use the Form Wizard from the More Forms, this will then suggest the tblClient and you select all of the fields then select the next table tblProject and select all of the fields, move on through the wizard until the end and you will have the main form and subform created.

To be able to select the client and show the projects.
Go into design view of the form and in the Design tab you will be able to use a combo box, and as you move through the wizard make sure you want to select a record form the form, and then save the form.

Once you start to populate the form with clients and projects you should then beable to select from the drop down a client and it will show you all of the projects in the subform.

Take your time with these instructions and send a thread if you need more instructions.

 

[pedroc]

Thanks Trevor.

All looking good so far but when i try to create the relationship the relationship box only displays one to one and not one to many.

Any ideas?

Many many many thanks

 

[freelanceaccessdev]

when i try to create the relationship the relationship box only displays one to one and not one to many.

Not sure if this will help, but once you have a line showing between the tables in the relationship manager, double click that line and you should be able to change the type of relationship.

 

[Trevor G]

Thanks Trevor.

All looking good so far but when i try to create the relationship the relationship box only displays one to one and not one to many.

Any ideas?

Many many many thanks

Did you follow my instructions, as you should have had a 1 to Many relationship being indicated.

So tblContact will have an ID field and is set to Primary and autonumber, this then gives you an individual number per contact.

In the second table tblProject you would have a field called ClientID and have the data type as Number.

 

[pedroc]

Trevor

Firstly my apologies.

I have followed the instructions as per your original reply and seem now to have the two forms created and talking to each other.

However - when creating the form I am unable to alter the layout much. It seems to prescribe a very columnar layout and i cannot move the data fields out of this layout.

When creating the form i am not given the option to pick amore customizable layout.

Any ideas?

Again all help very much appreciated

 

[Trevor G]

Trevor

Firstly my apologies.

I have followed the instructions as per your original reply and seem now to have the two forms created and talking to each other.

However - when creating the form I am unable to alter the layout much. It seems to prescribe a very columnar layout and i cannot move the data fields out of this layout.

When creating the form i am not given the option to pick amore customizable layout.

Any ideas?

Again all help very much appreciated

Glad your moving on, in Access 2007 when in Design view generally your fields are locked from moving around, this is a defualt setting.

What you need to do is select all the fields (Ctrl + A) then under the Arrange tab you will see towards the left a remove feature, if you click this then you should be able to move things around.

Again on the Arrange tab on the very left there should be an AutoFormat option and you can click on it and then there are a number of options for you, plus you can customise, other than that you will end up design (as most of us do) part by part.

 

[pedroc]

Trevor,

Thank you for all your help so far. The forms are now set exactly as I wanted - now i just have to populate them and then work out what useful reports i can generate from them!!!! That's for another day though.

Just one last thing - I have successfully split the database and saved on a shared drive at work, however when other users attempt to open the database they get a message along the lines of - Could not locate "filename"-be.accdb

Any reason for this that you can think of? Would it be easier to just not have split the database? Can it be "unsplit"?

Thanks again

Pete

 

[gemma-the-husky]

bear in mind that access is radically different to excel

if you are trying to get an a decent access runnnig for commercial purposes, it is likely that you will have a difficult learning curve - you ought to get somebody experienced to help - either internally or externally

 

[pedroc]

Thanks for the input dave - but actually the info and help from trevor has got me to the point where the database for our relatively small project is as complex as we need it.

Pete

 

[Trevor G]

Hi Pete,

So you are nearly there, thats good to read.

I would have the database in a shared folder near the top of the drive, check the permissions of the folder so everyone is included who needs to be, also check that everyone who is going to use the database has the same version as you have, unlike the other microsoft applications you will trip up badly if multiple versions are being used.

See if having the whole database in one will work, the main reason for splitting a database is to protect the tables and reduce the size of the database, if you consider that it isn't essential to protect the tables (at this stage) keep it as one complete database, you can hide the databse window so if someone close the forms down etc they don't get to see the back end.

It maybe necessary to set up user groups and permissions at some stage, but not until you have all your people accessing the database in the way you want it to.

I hope this resolves your situation for accessing the database.

 

[pedroc]

Trevor

Thanks for all your help. I have now built my very first relational database (albeit a very simple one).

Each site now has a list of contacts only relevant to that site, the databse is split, password protected and accesible via the shared network drive.

Now to play with colours and style!!!

Thank you again for all your help - it was refreshing to hear from someone who gave help on how to complete each stage in plain english.

Cheers

Pete

 

[Trevor G]

So you solved the accessing problem then?

Glad to read you have been successful.

Enjoy the colouring!!!