Access - Forms

[cmstforr]

Hi there,

First of all thanks for taking time out to read this.

I have designed a database to hold details regarding hardware audits I carry out for my customers. I want a database within Access to hold this info so I can run queries and reports for me and my customers.

I have created three tables. These are

Customer TBL
Asset TBL
Customer Asset TBL.

There relationships are as follow:

1 type of asset can be used in many cutomer assets
1 customer will have many assets within customer assets

The attributes for each entitiy are below

Customer
CustomerID (P.K)
Customer_Name

Asset
EquipID (P.K)
Manufacturer
PartNo
Description

CustomerAsset
CustomerAssID (P.K)
CustomerID (F.K)
EquipID (F.K)
DateInstalled
Warranty
SerialNumber
AssetNo

I now want to create a form. I want the user to user the customer to navigate to their customer and then add a new record(s) to the subform. It is here that they will select the asset and input other details about the audit.

Is it best to create a query and use this query when creating the form. I want to use combo boxes i.e. when selecting a Dell computer for customer x. This would have been added to my asset table so I want to know how I should start putting things together now that I have a design.

As you can see I'm a little confused about how I should use access to create the interface and put everything together so any suggestions and advice would be much appreciated.

Thanks and kind regards,

Tom

 

[M8KWR]

I would not have a query control the form.

The main form would be control by the Customer TBL, then you would have to create a subform, in here would be the Asset tbl, and inside here you would have another subform bound to the CustomerAsset tbl.

Hope that make sense. When you set this up, you could use the access wizards to help you setup the links between the tables.

I would set the tables out like:-

Customer
CustomerID (P.K)
Customer_Name

Asset
CustomerID (P.K)
EquipID (P.K)
Manufacturer
PartNo
Description

CustomerAsset
EquipID (P.K)
DateInstalled
Warranty
SerialNumber
AssetNo

What i can see in your table design, I do not know why you don't combine the last two together, why do you need two seperate tables, is there a reason for it.

I hope this make sense, and that i have figured out what you want to do exactly. If you need any help let me know. I would be happy to do an example for you so you can mess about with it, and change it to how you want it...

 

[Hap007]

Dear Tom,

From the questions you ask, you sound like you know what you want but that you have little or no actual programming experience.

I would suggest you examine the sample databases that came with Access first and look for forms that do what you desire.
Then, just use that code and forms to guide you.

These databases are normally located in:
C:\Program Files\Microsoft Office\Office\Samples folder
and called NorthWind.mdb and Solutions.mdb

Also, here is a link to a good, free online tuitorial:

http://www.functionx.com/access

Hope This Helps,
Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121