How do I fix this?

[hunterfan48]

I accidentally entered a value in a query, that may at some time have a value but doesn't as of yet. So, I need to get this value out but it won't let me know because it can't contain a null value?

Any tips?

I want this to go back to being a blank value I believe, but it won't let me because now since I entered a value it is saying it can't be null. I'm just trying to get it to go back to when there wasn't any value at all.

 

[GinaWhipp]

Are you saying you can't get to Design View of the query?

 

[hunterfan48]

Nope...I can get to the design view of the query.

 

[GinaWhipp]

Ummm, your answer is a little confusing. If you meant that you cannot get to Design View exactly how did you enter the value?

 

[hunterfan48]

http://www.access-programmers.co.uk/forums/showthread.php?t=220352

I wrote a novel here...hopefully you can see where I'm coming from.

Thanks

 

[hunterfan48]

I entered the value in datasheet view.

 

[GinaWhipp]

Well, first issue from what I read I am not sure the tables are properly normalized.

First you say you entered the value in a Query then you say in a Datasheet which sounds more like a Table.

How about some screen shots? Because I am more confused then ever now...

 

[hunterfan48]

LOL I'm sorry for confusing you. I'm talking about the query in datasheet view.

I'll try and post some shots. I found one way...I'd have to go back to the table from which is the field is, and take off my primary keys that way I can have null values. Once I go remove the values for the records I don't want, I can go back in and set up my primary keys again.

 

[GinaWhipp]

That should not be the way to fix it... removing PK's. Try to post a screen shot.

 

[hunterfan48]

Thank you for taking the time to help me. I appreciate it. Here are a lot of screen shots that hopefully explain better than I can in words what is happening.

Thanks again.

 

[hunterfan48]

Here are some more attachments.

 

[GinaWhipp]

Okay, to answer your question, the way you set this up the only way to remove that value is to to tblTransactionID and delete the record. To avoid this error remove the PK's from tblTransactionID permanently which shouldn't be PK's anyway. They should be Foreign Keys (FK's).

 

[hunterfan48]

Ok...thank you for your help!

So, I went into my table (Transaction ID) and simply clicked the Primary Key button which took off both PK's on the two fields. I then went into my table relationships and everything looked the same. No changes there right??

Would taking the PK's off this table affect any other queries or forms that I'm not aware of?

Getting back to fixing the problem, I was able to open up my query and scroll down to that specific card and remove the value in the transaction ID field. This time it let me (because of taking the PK's off I presume right??)

Well,
I'm still getting this error message for entering a new transaction for other cards and would like some help in figuring out what I need to do to get over this hump.

See the attached screen shot for my problem.
"The value cannot be added to this new row until the row has been committed. Commit the row first, and then try adding the value."
Thanks!
Brady

 

[GinaWhipp]

There should be no changes to the Relationships, so no worries there. Apparently, the error is asking you to save first. Did you remove the PK from both the fields in that table?

 

[hunterfan48]

Yes I did n the weird thing is it won't let me put in any transaction number I haves saved in my table

 

[GinaWhipp]

There must be something else going on... check the tblTransactions in Design View and make sure those fields aren't set to Required as a result of being a PK.

 

[hunterfan48]

Nope...nothing going on there.

Hmmm...this is tricky. Again, thanks for your help. I can't wait till we get it figured out! LOL

I'll keep trying things

 

[hunterfan48]

This really is weird. I just was going through some of my backups and I had the exact same query and I'm able to add values left and right. No problems at all...why all the headaches with this one?

 

[GinaWhipp]

Something changed... Did you add a field, change the joins?

 

[hunterfan48]

Nope. I keep looking for something but can't find any difference.