Error! You cannot change or add a record because a related record is required (1 Viewer)

accessingaccess

Registered User.
Local time
Today, 00:28
Joined
Mar 2, 2019
Messages
11
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

Super Moderator
Staff member
Local time
Today, 03:28
Joined
Jan 23, 2006
Messages
15,364
Typically, that error indicates that there is no Client with that Client ID in the Client table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
21,358
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.
 

Micron

AWF VIP
Local time
Today, 03:28
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Today, 00:28
Joined
Mar 2, 2019
Messages
11
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.
 

Attachments

  • databaseimage.jpg
    databaseimage.jpg
    100.4 KB · Views: 108

theDBguy

I’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 03:28
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 00:28
Joined
Mar 2, 2019
Messages
11
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’m here to help
Staff member
Local time
Today, 00:28
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom