Field getting set to zero

[dougmeece]

OK so I have been able to fix all of my problems with much help from the experts...thank you experts.

However, now something really weird is happening. As you know I have two forms (one appends/updates a table and the other is just for searching records).

Since I corrected the problem from my previous post of adding a new blank record when searching I now how one field in only the first record that is being set to zero when I close the search form. There is nothing oin the code sets any field to zero anywhere for either form. I have no idea why it is happening and it only happens, as I said, to one field in only the first record.

The code for the Close Form command button shows nothing that would cause this.

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

    DoCmd.Close

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
    
End Sub

As always, any help ifguring this out would be greatly appreciated. I am in the process of resarching this problem on line but have not had much luck.

Thanks,
Doug

[Megalog]

This happens to the new record you insert? Or record #1 out of a bunch of records?

If you're talking about the new record, then It could be the default value for the field, check the control's 'Default Value' property, or in the table itself, in design view, there's a 'default value' property for the field there as well.

[missinglinq]

There's nothing in the Wizard's boilerplate Close Form code that you've posted that's doing this, of course. I assume it's something related to the query problem in your last post, which you didn't bother to explain to us, other than to say that you had presumably fixed it. I expect that the query still has a problem that's causing this.

Zero (0) is the Default Value for a Numerical field in Access' Table Def, so it sounds to me as if you still have at least a half-new record being generated by your query. That's where I'd look for the culprit here.

Linq ;0)>

[dougmeece]

It is happening on only the first record. I have stepped through and checked the table after each step; the field is set to zero after I click the Close Form button.

- It happens when I run each query and close the form after each one.
- It does not happen when I go to the serach form and then only click the Close
Form button
- It does happen if I go to the search form and click the Clear Form button
followed by the Close Form button. It does not happen when only the Clear
Form button is pressed.
- I do not have to exit the database and reopen it to see the change.

There are 4 queries that can be executed from the form in question. Here are the SQL statements for them:

Records_Search_ Query

SELECT Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted], [Date Entered]
FROM Created_Submitted
WHERE (((Created_Submitted.Title)=Forms!Records_Search!T  xtTitleSearch));

Publisher_Recor ds_Search

SELECT Created_Submitted.[Submitted To], Created_Submitted.Title, Created_Submitted.[Date Submitted], Created_Submitted.Type, Created_Submitted.Accepted
FROM Created_Submitted
WHERE Forms!Records_Search!TxtPublisherSearch=(Created_S  ubmitted.[Submitted To])
GROUP BY Created_Submitted.[Submitted To], Created_Submitted.Title, Created_Submitted.[Date Submitted], Created_Submitted.Type, Created_Submitted.Accepted;

Records_Search_ Submitted

SELECT Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted], Created_Submitted.[Date Entered]
FROM Created_Submitted
WHERE (((Created_Submitted.Submitted)="yes"))
GROUP BY Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted], Created_Submitted.[Date Entered];

Records_Search_ Unsubmitted

SELECT Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted], Created_Submitted.[Date Entered]
FROM Created_Submitted
WHERE (((Created_Submitted.Submitted)="no"))
GROUP BY Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted], Created_Submitted.[Date Entered];

I didn't explain the other problem I had because it was placed in the forum earlier the same day and I did not want to get scolded for double posting again. Sorry, I should have referenced the previous post.

By the way, here os the subject line from the previous posting that referenced the problem I had with a blank line being added.

As always, any assistance would be greatly appreciated.

[dougmeece]

By the way, I forgot to mention this. The field gettin gchanged is not a numeric field. It contains either Yes or No only.

[Megalog]

By the way, I forgot to mention this. The field gettin gchanged is not a numeric field. It contains either Yes or No only.

Ok.. if the field in the table is a boolean type, then it's going to default to a zero (false) answer. Are you using a text box for this field, instead of a toggle switch or check box? But if that's the case, it -should- be putting zeros in every record automatically as they are created.. So this still isnt making a lot of sense to me why it's only in the first record.

[dougmeece]

I have two radio buttons (Yes and No). I also have a text box that I populate with whichever radio button is selected. I return the results in a subform. I have one subform per query search. They are all invisible until the corresponding button is clicked. I am just starting to figure out how to reduce my code so I know I have quite a bit of unnecessary code and I plan to figure how to combine things in time. First though, I would like to get this to work as it is. That way I can have a working backup as I make changes to the code.

Here is the code for the Radio Buttons. The txt box is only referenced in the below code and the search button if statement code that determines if the box has a yes or a no in it.

Private Sub RadNo_Click()
    cmdSubmitted.Visible = False
    CmdUnsubmitted.Visible = True
    CmdUnsubmitted.SetFocus
    Me![txtSubmissions] = "No"
    RadYes = False
    
End Sub

Private Sub RadYes_Click()
    CmdUnsubmitted.Visible = False
    cmdSubmitted.Visible = True
    cmdSubmitted.SetFocus
    Me![txtSubmissions] = "Yes"
    RadNo = False
    
End Sub

[dougmeece]

Well it appears that it this problem had something to do with the radio buttons I was using. I removed them and went to just a combo box with "Yes" and "No" as the options. Everything now works fine. I still will try to find out what the problem was but for now it works...so...Wo o Hoo!!!

[missinglinq]

Of course, in Access VBA No = 0 (Yes = -1)

What version of Access are you running? If I remember correctly, Access 2003's notorious SP3 service pack arbitrarily resets some boolean fields to No (0) and if you're running 2003 with SP3 this may be the problem. If so, I believe the hotfix for SP3 addressed this problem.

Here's a link to Allen Browne's site where you can find the latest news about these bugs, including, I believe, a link to the hotfix:

http://allenbrowne.com/bug-Access2003SP3.html

Linq ;0)>

[dougmeece]

Yep, I have Access 2003 SP3. I'll check out the link you provided and thanks to everyone for the help.

[missinglinq]

Glad we could help! Be sure to let us know if the hotfix for SP3 solves the problem.

Linq ;0)>

[dougmeece]

That seems to have taken care of it. I installed the hotfix, recreated the radio buttons and tested it. I had no problems with the field getting set to zero.

Thanks again...the form just looks so much better with the buttons instead of another drop down.