Having problem entering new data..

[Mechaworx]

Hi all, I am experiencing this problem and I know that the answer is probably simple but I've searched for an answer and I can't find it.

I have a toy database consisting of two tables.

1). A Table of Manufacturers and Product Lines (Primary Table)

2). A table of Images of individual products and corresponding product lines.

The fields are broken down as follows:

tblManufacturers (Primary Table)

Field Names
dteDate (Date/Time)
strToyLine_ID (Autonumber - Primary Key)
strToyLine (Text)
strManufacturer (Text)
strLearn_More (Memo)
strBuy_Them (Memo)

tblImages (Secondary Table)

Field Names
dteDate (Date/Time)
strImageID (Text - Primary Key)
strImageName (Text)
strBasedOn (Text)
strManufacturer (Text)
strToyLine (Text)
strToyLine_ID (Number Indexed = Yes (Duplicates OK))
strSeriesNumber (Number)
dtmYearReleased (Number)
curRetailPrice (Currency)
strPicture (Text)
memComments (Memo)

I have a one to many relationship between the two tables on the strToyLine_ID Field.

I just recently added the strToyLine_ID column to both tables and established a one to many relationship on this field between the two tables for ease.

Since I already had around 1000 records in the tblImages table and 154 records in the tblManufacturers table I performed an update query to have the strToyLine_ID field in tblManufacturers copied over to the strToyLine_ID field in the tblImages table. Everything worked fine.

I have two forms created. One form that enters data into the tblManufacturers table and another form that enters data into the tblImages table. When entering data I always enter data first using the form for the tblManufacturers table.

The problem I'm having is now, when I enter new data in the form for entry in the tblImages table, I keep getting: You cannot add or change a record because a related record is required in table 'tblManufacturers'.

Is there some sort of key feature I'm missing in my form? I figured that since a Toy Line and Manufacturer have already been created in the primary table (tblManufacturers) whatever information I enter in there first, if I spell it correctly will work in the form for my tblImages table.

Sorry if this is long, I do a lot of thinking when I write and I have a tendancy to get long-winded

Thanks for any info.
Gerry

 

[jessa_lee]

I don't know if I'm following you correctly on this, but couldn't you just create a form with a sub-form and link the child/master fields so that when you enter or pull up the Mfg info, you will be able to enter the Toy info into the subform? You can use the Form Wizard in Access to complete this. Let me know if this works or if you need more assistance.

Best of luck,
Jessa

 

[Mechaworx]

Hi Jesse

I think it's simpler than creating a form/sub form but I'll look into it.

Like I stated above, I have two forms created for each table.

Form 1 for tblManufacturers

Form Field Names:

Toy Line inserts into field_column strToyLine
Manufacturer inserts into field_column strManufacturer
Learn More inserts into field_column strLearn_More
Where to Buy inserts into field_column strBuy_Them

It isn't necessary to create fields for dteDate and strToyLine_ID because Microsoft access automatically generates this information when the record is created.

Form 2 for tblImages

Form Field Names:

Image ID Number inserts into field_column strImageID
Image Name inserts into field_column strImageName
Based On inserts into field_column strBasedOn
Manufacturer inserts into field_column strManufacturer
Toy Line inserts into field_column strToyLine
*Toy Line ID inserts into field_column strToyLine_ID*
Series Number inserts into field_column strSeriesNumber
Year Releasedinserts into field_column dtmYearReleased
Retail Price inserts into field_column curRetailPrice
Picture Pathinserts into field_column strPicture
Comments inserts into field_column memComments




*I found out my problem is that the form that I created to enter data into my secondary table (tblImages) does not know the Toy Line ID number which is automatically generated in tblManufacturers.

I think I need to create some sort of bound control in this form so that whenever a particular toy line is selected via a list box, that the toy line ID number automatically get's inserted into the Toy Line ID field.

So I guess my question is how do I create a bound control since I'm fairly new to ACCESS

Does this make sense?

Gerry

 

[Mechaworx]

Hi Jessa

Creating the form/subform method worked though to me there should be an easier way like having access look up a field in one table (In my case tblManufacturers) and have that same information added to a form for entry into another table where a clear one to many relationship has been defined.

However, thanks for your help in this.

Gerry