Form sub-form problem

[steve456]

Hello all, I’m a newbie so please forgive me if my description of the problem is not clear. Here goes. I’m developing a new database for my contracting company. I have a form with a sub form. The main-form has all the customer names, addresses and phone numbers and the sub-form has the descriptions and the cost of each service.

I may need to generate 3 different records per year for one single customer. When I start typing in the CustomerID for a new record the last and only record for that customer appears in the detail section. I’m in a position where I can only enter one customer name and one detailed section _____________period. I’m sure the problem lies in properties but I can’t figure it out. I tried it out with the wizard and drag and drop and it still won’t work.

Regards,

Steve

[nico5038]

Do you have a tblDescription table with the KeyField of the Company table ?

Nic;o)

[steve456]

Hello Nico5038

Yes as a matter of fact I do. I used the phone number as the unique identifier for customer's names and addresses and included that very phone number field in the discription table. The discription form is in datasheet view by the way.


Regards

Steve

[nico5038]

I'ts better to use an autonumber as the ("meaningless") ID for your clients as a phonenumber can change.
For the additional records you can use that ID as the so-called "ForeignKey".
The additional records will have to be in a table e.g. named tblClientService.
Now this table will hold the ClientID and the ServiceID. When defining this table select the ClientID and press the "Lookup" tab. Change the textfield into a Lookup from a table and "connect" the field to the tblClient. Do the same for the ServiceID field that can be connected to the tblService.

Now add a ServiceDate field to be able to separate the rows from this table and have multiple same Services linked to a Client. Finally select all three fields (ClientID, ServiceID and ServiceDate) and press the "Key" button to indicate that these form the unique identifier.

Now you can define a mainform for the client and add the tblClientServiceas the subform for the additional rows.

Getting the idea ?

Nic;o)

[steve456]

Hi nico5038,

Thanks for the response. I'm going to need some time to digest that. I'm a real newbi to this. If I do what you suggest I'm sure it will work.

regards,

Steve

[steve456]

Hello nico5038,

Is this what you have in mind?

Two tables for a service database

CustomersTable

ClientID “autonumber”
FirstName
LastName
Address
City
Prov
PostalCode
PhoneNumber


ClientServiceTable

ServiceID “Lookup” connect to CustomersTable “key”
ClientID “Lookup” connect to CustomersTable “key”
Discription
Cost
ServiceDate “key”



Regards

[nico5038]

No, I guess the same service will be applied to multiple clients.
Thus it's best to have a separate tblService that can hold the name and price, etc.

Getting the idea ?

Nic;o)

[steve456]

Hello again nico5038,

Not sure I understand what your last post was referring to. Could you rephrase?
Also not sure what the term "ForeignKey" means, can't find it's definition in the help section of Access2000.

Having said all that do the two table I describled in my last post look OK to you?


Regards,

Steve

[nico5038]

The tables are OK, but you need a third table for the Services.

ClientServiceTable

ServiceID “Lookup” connect to CustomersTable “key”
ClientID “Lookup” connect to ServicesTable “key” <===== new table
Discription
Cost
ServiceDate “key”

Both the "connect to" fields are called "Foreign keys" as they are the primary key of another table.

Nic;o)

[steve456]

Hello again nico5038

I’m sorry for being such a pain but there must be some elementary step I’m just not getting. If I’m to understand you correctly I’m to create another table which is a mirror copy of my already established ClientServiceTable. If I do that I will have 2 service tables.

I’ve taken the liberty of uploading to my website two tables from a previous post that we discussed. Before pressing on I was wondering if you would just look at them and tell me if the keys are correct. The link is at http://stevecale.com/database

Being new to this forum I wasn’t sure how far one goes in getting help from the moderators or senior programmers from this forum.

Just in a nutshell in case I haven’t mentioned it A few years ago I built a flat database with 1 table for my contracting company. I’m a contractor not a programmer, so I found it easy to do it this way. I know now that I have really limited the functionality of this database. I’ve got customer names addresses and phone numbers at the top of the form and below that there are 14 description fields and 14 cost fields and totals. It’s clumsy but it works.

So with this new design I’m try to get the top part of a newly designed database to refreshed the below sub-form. Have not been successful. If I enter a customer which is already in the database it will pull up the last and only record in the sub-form. In other words if I want to enter a new project for an already established customer it won’t let me do it. It keeps pulling up the last record and only record in the sub-form.


Regards,

Steve

[nico5038]

Hello again nico5038

I’m sorry for being such a pain but there must be some elementary step I’m just not getting. If I’m to understand you correctly I’m to create another table which is a mirror copy of my already established ClientServiceTable. If I do that I will have 2 service tables.

I’ve taken the liberty of uploading to my website two tables from a previous post that we discussed. Before pressing on I was wondering if you would just look at them and tell me if the keys are correct. The link is at http://stevecale.com/database

Being new to this forum I wasn’t sure how far one goes in getting help from the moderators or senior programmers from this forum.

Just in a nutshell in case I haven’t mentioned it A few years ago I built a flat database with 1 table for my contracting company. I’m a contractor not a programmer, so I found it easy to do it this way. I know now that I have really limited the functionality of this database. I’ve got customer names addresses and phone numbers at the top of the form and below that there are 14 description fields and 14 cost fields and totals. It’s clumsy but it works.

So with this new design I’m try to get the top part of a newly designed database to refreshed the below sub-form. Have not been successful. If I enter a customer which is already in the database it will pull up the last and only record in the sub-form. In other words if I want to enter a new project for an already established customer it won’t let me do it. It keeps pulling up the last record and only record in the sub-form.


Regards,

Steve

Hi Steve,

You can never be a pain, it's only me not being able to judge your level of expertise :-)
I'll help as long as it's not clear, so keep posting :-)

The idea of the separate tblService is the same as for a tblProducts in an Order database.
When defining an order you're combining the data of a Client to one or more Order details. Each OrderDetail will contain a product, quantity and price.
The Product will be extracted from a tblProduct as multiple customers can order the same product.

Same goes for your services. Each service can have a different set f properties (Price, Duration, etc.) and the same client can use it overtime multiple times.
To ease the dataentry you use in the tblClientService only the ID of the service. The other Service properties we can always get by joining tables in a query.

Bit clearer now ?

Nic;o)