Related Record is required

[lloydmav]

I'm a relative beginner and am in need of clarafication on an error message I'm getting.

Two of the tables in my database are called Requests and Status,
They are linked together.The primary key of the Staus Tbl (Status_ID)(Autonumber) is the Foriegn key in the Requests Tbl (Number).

When I try to add a record on a form to the Requests table I get the error message

You cannot add or change a record because a related record is required in table 'Status'

I know this must be simple so could someone tell me what I'm doing wrong and how I can get around it?

All I really need is to have one of the status eg (Proposed) stored with the Request Record from the Status tbl.

Can someone please help me in the right direction?

 

[Jack Cowley]

You are trying to add a record to the Requests table when there is no related record in the Status table. You must have a record in the Status table to add records to the Requests table. The easiest way to do this is with a mainform/subform setup. The main form is based on your Status table and your subform on your Requests table. Select a record in your main form and you can add as many records as you like in the subform....

hth,
Jack

 

[casey]

The Error message says... that you are trying to add a record to the [Requests] table, but there it does not recognize a matching priamary key(StatusID) in your [Status] table.

It has to do with the form's recordsource. You need to add the Primary key (Status.StatusID) to your recordsource. This way if you're trying to add a new StatusID to the record, it will then be added to your Status table at the same time.

HTH

Just a thought....
Try using the query builder on the your form's recordsource. While adding records from the query. You can better see whats happening when do so. You can then try updating a different field or add a field that isn't in you recordsource (ie. Status.StatusID) if you're not getting the results you want.

 

[lloydmav]

Jack, I first of all added records to the status table too see if this was the problem but it still didn't work. If I'm understanding you right you want me to have the main part of the form Status and the subform on that form as Request. This is no good because the point of the form is to Create requests and all I really need is the related field to status as say a listbox so that I can assign a status to the request.

Casey, I tried to add the Status_ID to the Request Creation form's recordsource both manually and by using the query builder but it didn't work.

I'd really appreciate the help because if I can figure this out then I can make sure I design the rest of the database correctly. Do you know what I'm still doing wrong?

Thanks

 

[lloydmav]

Ok Guys I've managed to solve this one.
I simple added a listbox and set the values to the status ID in the status table. Then I selected to fill the data into the Status ID field in the Requests table, it worked!

Thanks for the help!