Error! You cannot change or add a record because a related record is required

[accessingaccess]

Hello!

I am using Access 2016.

I have a form for client details that includes a Client ID which is an auto-number. Within this form, I have three tabs. On one of these tabs, there is another form where users can add or subtract the amount of store credit a client has. This form has a "tradeID" to differentiate each transaction, a date, the amount, and the "ClientID". TradeID is an auto number, and I need the ClientID to auto populate to match the client who's form we are viewing. When I manually enter the client ID myself there is no issue. But when I have tried to make it pull that information from the page we are on with the expression "=[Forms]![ClientTradesForm]![ClientID]", I get the error "You cannot change or add a record because a related record is required in table 'Client'". My client table does has a field for ClientID, so I don't know why this doesn't work. The correct number does auto populate, it just won't let me save the "trade" when it gets the number this way. Whereas if I manually type the same number in, it's fine.

I need it to auto populate so as to minimize mistakes as this will be used in a fast paced environment.

Any help is much appreciated! Thank you.

 

[jdraw]

Typically, that error indicates that there is no Client with that Client ID in the Client table.

 

[theDBguy]

Hi. Are you using a form/subform setup? If not, have you tried? If not, you should try it and let us know if it doesn't work.

 

[Deleted member 147267]

While post 2 may seem contradictory to what you are saying

But when I have tried to make it pull that information from the page we are on with the expression "=[Forms]![ClientTradesForm]![ClientID]", I get the error

it may be worth expounding on that post. The value you see likely has not yet been saved as the parent record. You'd need to force that somehow.

 

[accessingaccess]

My expression must be wrong in the source for the text box... I need the text box to get the value of the TradeClientID from the ClientID of the page that we are on. Is this possible? My Client and Trades table have a one-to-many relationship, linked by the TradeClientID and the ClientID. So is there a way to write the expression so that it understands that the ClientID of the current page is the TradeClientID that will get updated in the Trades table?


I've attached an image to hopefully make this clearer. You'll see that the TradeClientID is under the Add/Redeem tab, and I need it to pull the number from ClientID which is underneath the client name.

 

[theDBguy]

My expression must be wrong in the source for the text box... I need the text box to get the value of the TradeClientID from the ClientID of the page that we are on. Is this possible? My Client and Trades table have a one-to-many relationship, linked by the TradeClientID and the ClientID. So is there a way to write the expression so that it understands that the ClientID of the current page is the TradeClientID that will get updated in the Trades table?


I've attached an image to hopefully make this clearer. You'll see that the TradeClientID is under the Add/Redeem tab, and I need it to pull the number from ClientID which is underneath the client name.

Hi. I don't get it but maybe what you need is DLookup(). Just a thought...

 

[jdraw]

I agree with dbGuy --I'm not following the interaction of ClientID and TradeID (and you also mention TradeClientID. Perhaps you could step back and tell us in simple plain English what you're trying to accomplish. We may be getting too deep with the references to autonumbers etc. Maybe a description free of Access terms will help.

 

[accessingaccess]

I figured it out! I just used the same expression but put it as the default value for that text box instead of as the source.



Thank you everyone for your help!

 

[theDBguy]

I figured it out! I just used the same expression but put it as the default value for that text box instead of as the source.

Thank you everyone for your help!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.