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

[June7]

Not clear to me what needs to be explained. This is really basic Access functionality.

Suggest using a form/subform arrangement. Main form bound to Staff and subform bound to Outstanding. Open main form (the subform will synchronize) and go to desired staff record, enter records in subform.



Create form/subform http://office.microsoft.com/en-us/ac...010098674.aspx

One way to filter form records http://datapigtechnologies.com/flash...tomfilter.html

[Mahou]

Thank you for your reply. I have follow your sending link instruction. However, it is unable to success to do it(datapigtechnologies.com) . I am unable to display staff record. Here I attached the file I modified the file. Please help. Thanks.

[June7]

The combobox used to enter search criteria must be UNBOUND. You do not have code in the combobox AfterUpdate event. The form RecordSource is not a parameterized query. Review the video again and follow the design.

Alternative to parameterized query is:

Me.Filter = "StaffID=" & Me.myselector
Me.FilterOn = True

You have not fixed the primary/foreign key situation noted in posts 5 and 7.

[Mahou]

I am apologised for different file I sent it. Here is corrected file I did.

[June7]

Need to set combobox properties:

RowSource: SELECT Staff.StaffNumber, Staff.LastName & ", " & Staff.FirstName AS StaffName, Staff.TeamCode FROM Staff WITH OWNERACCESS OPTION;

ColumnCount: 3

ColumnWidths: 0";1.5";1"

Code in combobox AfterUpdate event is Me.Requery but you are not using a query as the form RecordSource. If you review the DataPig video you will see that the form uses a parameterized query as its RecordSource.

Alternative to parameterized query is this code:

Me.Filter = "StaffNumber='" & Me.myselector & "'"
Me.FilterOn = True

[Mahou]

Thank you for your reply. I have tried to follow your instruction. However, Last name, First name, team code are not changable when the combo box value change. And also when I tried to change to design view. It said the changes you requested to the table were not successful because they would create duplicate value in the index.


Please help.

[June7]

I downloaded your db again. Did the following changes:

Set myselector combobox properties:

RowSource: SELECT [Staff].[StaffNumber], [Staff].[LastName & ", " & [Staff].[FirstName] AS StaffName, [Staff].[TeamCode] FROM [Staff];
ColumnCount: 3
ColumnWidths: 0";1.5";1"

Code for the combobox:

Private Sub myselector_AfterUpdate()
Me.Filter = "StaffNumber='" & Me.myselector & "'"
Me.FilterOn = True
Me.myselector = Null
End Sub

That's it. Do the exact same things and should work.

What do you mean by 'are not changeable'? If you want to add a new record then must move to New Record row. All works for me. No problem adding new record. I added new staff then new record in subform.

[Mahou]

Thank you for your reply. I followed your instruction I did exact same things. The messages were coming up. I attached some pictures for your reference. Thanks.


This picture is when the combo box changed, the staff information (Last name, First name & Team code) did not work.



This picture is when it changed to Design View the message was coming up.

Please Help!!

[June7]

Then the db is different from the one I worked with and/or you really have not done what I did.

Provide db with YOUR edits I will try to figure out why different behavior.

[Mahou]

O.K I am apologzied for this situation. I dont know why.Here is file I attached. Please have a look. Thanks.

[June7]

The myselector combobox must be UNBOUND - no ControlSource.

Controls used to input search criteria must be UNBOUND, otherwise you change the data in the record.

[Mahou]

Thank you for your reply. I have tried to follow your instruction. However, the record does not change when changing the combo box. So. What can I do?

[June7]

I just downloaded your db again. I removed StaffNumber from the myselector combobox ControlSource so it is UNBOUND. That's it, everything works. Can't get any simpler.

[Mahou]

Thank you for your reply. here is picture I show you what issue is. Thanks.

[June7]

Did you do what I said to fix the combobox? Did you open form in design view and delete StaffNumber from the ControlSource property?

As I said, I did only that one change to your database and the form works properly. There's nothing else I can do to help you.