Can Anyone Help?

[tofter1]

I didnot know where to put this thread: here or in the form..

I am having a problem with MS access 2000. Here it is:

I cannot create forms which are related to several tables.

Suppose i have a field (1)company name set to primary key(in one table), (2)i have another table in which there is a primary key - model of equipment.

I make a many to many relationship between these two.

I further have another table in which service type is the primary key.(3)

I again created a many to many to many relationship between 1 and 3

i.e many to many relationship between 1and 2 + 1 and 3

Now i created an auto form using the wizard- the statement : try mismatch in expression OR the wizard is unable to open your form in form view or datasheet,possibly because another user has a source tableopen in exclusive mode. Form will be opened in design view.

What should i do to open the form in form view?
thx

 

[The_Doc_Man]

Your problem is that the form is having trouble defining its recordsource, I think. Can you possibly build a query (perhaps multi-stage or nested) that touches everything? That might make it easier for you.

OR can you display some of this information in subforms? There is no law that says you can't have two sub-forms on the same parent form. The properties that define the parent/child links are sub-form control properties so there will be no confusion.

In the latter case, if you have one table that these two other tables depend on, the parent form opens that table and the sub-forms open the dependent tables. OR QUERIES, if you have a linking/junction table for the dependent tables. 'cause then you can make a query with the linking table and the dependent table together and link the query's foreign key to the independent table as the parent/child link field.

 

[tofter1]

Your problem is that the form is having trouble defining its recordsource, I think. Can you possibly build a query (perhaps multi-stage or nested) that touches everything? That might make it easier for you.

OR can you display some of this information in subforms? There is no law that says you can't have two sub-forms on the same parent form. The properties that define the parent/child links are sub-form control properties so there will be no confusion.

In the latter case, if you have one table that these two other tables depend on, the parent form opens that table and the sub-forms open the dependent tables. OR QUERIES, if you have a linking/junction table for the dependent tables. 'cause then you can make a query with the linking table and the dependent table together and link the query's foreign key to the independent table as the parent/child link field.

When i created the subforms they did not open. this appeared: cannot join Memo, OLEor Hypertext Object (of a particular table).

The same problem that i am having with forms i am having with queries and reports. What i can note is that the problem is with the many to many relationship. Suppose the situation is like this: I create a customer table consisting of name, tel no., customer ID(which is the primary key), create another table (equipment) consisting of make, model(which is the primary key), create a final table(service) in which the service type is the primary key.

I then created many to many rel. between cust. table and equipment by creating a junction table consisting of the cust. ID and model(primary keys). I linked the three with one to many relationship.
The same i did with customer table and service table.

BUT I COULD NOT CREATE FORMS WITH THESE TABLES. Why?
thx

 

[boblarson]

You wrote:

I then created many to many rel. between cust. table and equipment by creating a junction table consisting of the cust. ID and model(primary keys). I linked the three with one to many relationship.
The same i did with customer table and service table.

After reading your original post and this, I am wondering if you are doing all of this even close to correctly.

How are Customer ID and Model ID related? How are Model ID and Service ID related?

Suppose i have a field (1)company name set to primary key(in one table), (2)i have another table in which there is a primary key - model of equipment.

I make a many to many relationship between these two.

Suppose you have 3 tables like this:

tblCompany
companyID Autonumber (primary key)
companyName Text

tblEquipment
equipmentID Autonumber (primary key)
equipmentName Text

tblService
serviceID Autonumber (primary key)
serviceName Text

Now, nowhere there do any of these connect. Therefore you CANNOT build a relationship linking them. It will NOT work.

If you add this table

tblCustomerTransactions
transactionID autonumber (Primary Key)
companyID (Foreign Key)
equipmentID(Foreign Key)
serviceID(Foreign Key)

Each of the Foreign Key fields must hold the same data for the applicable item (for example, if the autonumber for the companyID for XYZ Company was 142993, then in the transaction table the ID for the company for that one transaction would be 142993.)

You can link 1 to Many for companyID (in the Company table) to the companyID (in the Transactions table), 1 to Many with equipmentID (in Equipment table) to the equipmentID (in the Transactions table), and so on.

Now, if the equipment can have many services, you would want a Services_to_Equipment table where you can link to services via the Services table (serviceID in the services table).

I don't have time to go into further detail, but I'm sure someone else will for you. So, to sum up, you currently have 3 unrelated tables. You cannot link them at this point, until you create the table structure that will tie them all together.

 

[The_Doc_Man]

cannot join Memo, OLEor Hypertext Object (of a particular table).

I think this error says it all. You cannot join tables (parent-to-child or one-to-one or any-to-any) on such fields. The reason you have trouble with these items is that Access cannot build indexes for any of these field types. One of them (memo) is too big and the other two aren't necessarily inside the .MDB file, which is why they can't be indexed directly. If the fields cannot be indexed, you cannot join on the fields, 'cause Access really, REALLY wants that index to establish the JOIN.

Perhaps your problem is that you need a "generated" primary key on the tables. Look up "Autonumber" and consider the possibility that you might want to use a synthetic number (such as an autonumber) for building the relationships you need.

 

[tofter1]

I have been able to create the forms and the queries finally.

I want to know suppose i want to add a drop down menu in my form: e.g in my form there is title and i want to add a drop down menu for the foll. title : Mrs Daisy, The day after yesterday, Do you know why you are here. How will I do that? I want as if to click on the drop down and to get the titles, is that poss.?
Thx

 

[tofter1]

I can write in the form but i cannot switch to another form. An error appears. I do not remember the error. Do you have an idea why this happen?

 

[Pat Hartman]

Take a look at this Many-to-Many sample database to see how to create forms/subforms/queries.

 

[tofter1]

I want to know if it will be possible to: suppose i have two tables containing fields A, B in one table and C, D in the other table. I want to make a one to one relationship between A (data type: autonumber) and c( data type : number). This is succesful. However, in this table i will have to , create a many to one relationship between A and D( data type: text). I cannot do this because of the diff data types.

Is there a solution for this?

 

[Crilen007]

Go to school for access.. or get a tutorial book. Or even look at the Northwind database for examples.

Seems you are trying to do things without knowing how or why you are doing it.