Subform problem

[Jerome]

Hello,

I am modifying an excising database for a acquaintance. now I have the following problem:

The database consists of two tables (TblA and TblB) and two forms (FrmA and FrmB). One of the two forms (FrmB) has a subform (SubA).

Form A is used to populate data from table A using listbox and a textbox. The user can double click a row, then form B is opened and the data in table A is showed in a better way (using bound textboxes that show data from table A).

There is also a subform (SubA) opened in form B (FrmB) that shows additional data. How many appointments a client had and when and some remarks.

Loading the data into form B (using from A) works great but when I want to add a new row to the subform I get the following error:

The linkMasterFields property has produced this error: 'The object doesn't contain the Automation object 'CustomerInfo'.'

When I confirm the error I can add a new row, but when I check Table B (that's the content displayed in the subform), the customers ID coming from Table A (link master fields) is not displayed.

Does anyone have an idea what causes this error?

Thanks in advance and with best regards.

 

[missinglinq]

Does CustomerInfo exist in FrmB and SubA?

 

[Jerome]

Some additional info on the problem:

Well the "automation object 'CustomerInfo' " is actually Table A (TblA) I think.

Every customer gets a unique ID (Primary key) that is stored in table A. In table A is also a lot of other info about this customer (first name, last name ect....).

The subform is used to display customer appointments in the form. On this form also data from table A is shown (first name, last name ect..). Because a customer can have multiple appointments this data is stored in a separate table (TblB).

This table stores the unique customer ID and and an Appointment ID (Primary key) and some additional data.

When the error occurs and in confirmed I can enter a new appointment date ect. When I open the table later on all data is there except the unique customer ID from table A?

What does this error mean? I am trying to figure out why it is caused. I have already invested a lot of time trying to solve this problem but without any success.

 

[JANR]

You have to link the subform to the mainform by using Linking master and Child properties. Open the properties of your subformconteiner and under the Datatab you should see the properties.

Make Link Master to CustomerID and the same goes for the Link Child property. (Just type in the fieldnames)

Also check the properies in your table B that the CustomersID field is set to Required, that should stop any orfant records to go in your table B.

Hope this helps.

JR

 

[Jerome]

They where already linked. The master field to the unique ID in Table A (TblA, primary key) and the child to the uniquecustomer ID that is table B (not the primary key) (TblB). The error still occurs even with these linked fields. the error is also produced int teh linkMasterFields property:

The linkMasterFields property has produced this error: 'The object doesn't contain the Automation object 'CustomerInfo'.'

 

[JANR]

This table stores the unique customer ID (table B)

I hope this field is a Number and NOT and Autonumber. This soulden't be difficult. If you have set up the relationship correctly, one-many in the realtionshipwindow Access would have set up the correct form-subform for you.

Table A CutomerID (One-side) and Table B CustomerID (Many-side)

Other than seeing your DB i'm at a loss.

JR

 

[JANR]

Here is a little simpel db on how to set up form-subform and the relationship, see if you can dublicate it in your db

JR

 

[Jerome]

Hello,

I've made a small example to illustrate the error (see attachment).

The example consists of two tables:

Table 1: Table to store the customers personal data.
Table 2: Table to store the appointments

and two forms:

FrmSearch: FrmSearch is a flexible search form that with a bouble click on a record will open a new form.

FrmResult: Frm result shows some personal data (only the name in the example) and a subform with the appointments.

To generate an error:

Open the file double click on a line in the listbox and add some new data on the last (NEW) row. The error will occur! When this is confirmed the data can be entered, this will be stored in table 2 but the unique customer ID will not be stored! please open table 2 to see what the problem is.

Note. The rowsource of the form FrmResult uses an SQL statement to get the unique customer ID.

SELECT Table1.*, Table1.ID FROM Table1 WHERE (((Table1.ID)=forms!FrmSearch!List8));

With best regards and thanks in advance

 

[JANR]

Sorry I don't have Access 2007, can you post it in 2000 or 2003 format

JR

 

[Jerome]

Here is an Access 2003 version

Thanks in advance.

 

[JANR]

First off, change the controlsource of form "FrmResults" to this:

SELECT Table1.* FROM Table1 WHERE (((Table1.ID)=[forms]![FrmSearch]![List8]));

Basicly remove the tickmark under ID field, you don't show the where-clause field, it confuse everybody.

Second in your Master/child link, you have in your Masterlink this "Table1.ID" remove the Table1 bit so it just say ID, it
should now work as you intend.

JR

 

[Jerome]

Great!! thanks a lot.

I still have a few questions:

1. Why should you use brackets [] in the controlsource?

2.

Basicly remove the tickmark under ID field, you don't show the where-clause field, it confuse everybody.

What do you mean with this?

3. Why is only ID sufficient enough? Does Access sees the relationship automatically?

4. What was causing the actual problem?

Thanks in advance.

 

[JANR]

1. Why should you use brackets [] in the controlsource?

The brackets is something Access put in around Form refrences, i Think

2. 
Quote:
Basicly remove the tickmark under ID field, you don't show the where-clause field, it confuse everybody. 
What do you mean with this?

I guess when you made the SQL for frmResults you used the visual interface instead of typing the SQL, If you notice that without removing the "show" checkbox in the query you where refrensing the ID field twice, and that was what Access was complaining about.

You original SQL looked like this:

SELECT *,[COLOR=red]Table1.ID[/COLOR] FROM Table1 WHERE ....

When you used the asceric * mean ALL the fields and the ID field one more time.

3. Why is only ID sufficient enough? Does Access sees the relationship automatically?

There is no need to include the tablename because its only one table in the recordsource, you would however do it if there is another table in the recordsource with the samename do disambigue the 2 fields.

4. What was causing the actual problem?

Se answer 2

JR