Cant Update yes/no field via query

[tagteam]

I have a form that I allow users to update record information. It will update the temp table I use to store the data until the user wants to process it but it wont update the main table.


Here is how it works:

1. From a form a user selects a car
2. This form uses an append query to add that car record to a temp table
3. It then opens another form that has a field for each record in the table
4. The user can enter new values in the field and click a button to update the temp table. This button updates the record and then opens the table in datasheet view so they can see the changes they made.
5. If the user is ready to make the changes they click another button that will update the details from the temp table to the main table

The update of the temp table works fine, however, when it tries to update the main table it doesn't work. I use the same if statement to update both tables:

Update to the temp table

IIf([Forms]![DataEntry]![Available] Is Null,[temptable].[Available],[Forms]![DataEntry]![Available]) - this one works fine and will update and change correctly

The final update is to the main table

IIf([Forms]![DataEntry]![Available] Is Null,[maintable].[Available],[temptable].[Available]) - This one does not work

I have confirmed the following:
Main table is yes/no - format yes/no -lookup checkbox
temp table is yes/no - format yes/no -lookup checkbox
Tried changing to yes/no - format yes/no - lookup text box and no difference


So both field types are the same and on the update form it is a checkbox as well. I use a triple state check box on the update form so that the user can leave it at its current value. I have tested and it works correctly on the temp table

Does anyone have any ideas why this does not work for the final update query?

The working query has only one table in the query - temptable
the non working query has two tables - main table and temp table with a left join on railcar id

Last note this works for all the regular text fields, the only thing not working is the yes/no fields

[June7]

You mean the test for Is Null fails on the Yes/No field? That's probably because a Yes/No field is never null, it is either 0 or -1. That's the nature of Yes/No, must be one or the other. Don't bother with the Is Null test, just set the field to whatever the temptable field is.

[John_G]

In the main table update:

IIf([Forms]![DataEntry]![Available] Is Null,[maintable].[Available],[temptable].[Available])

What does [temptable].[Available] represent? How does it know which record in [temptable] to use?
(I'm surprised that it runs at all - normally you cannot reference a table in that way)

It would help if you can show us the context in which you are using these Iif statements - are they in VBA?

John

[June7]

John_G, I think this is VBA and inclusion of the table name is because the form is bound to query with two tables each with field named Available as well as a checkbox named Available. Yes, confusing.

[tagteam]

Gentlemen, thank you for your help. I am very sad to report that the problem was that all the fields in the final update query referenced the main table but the last two (the check boxes) referenced the temp table. This was causing the final update query to update the temp table again instead of the main table Therefor the problem all along was an error in my entry and not looking closely enough at my code. Thank you for your replies, I am sorry that it was a false alarm.