run update query from form

[orcaa]

Hi created in access 2007 a couple of tables and created query to get number of fields from these tables.

Now one of the fields is a "checkbox", so true or false.

I created an update query to change from "False" to "True ". When I run this as a parameterquery this works fine. But now I wanted to use a textfield on a form as input and a button on the form to run the update query. When I run the query from the form with the button it doesnot update the "checkbox" . Ans when I run the update query in the "query view" then it works fine.

Can someone help me with running an update query from a Form and the result should be shown in reportview

[orcaa]

Vriezer_forum.zip
I inseted a zip file of the database

[A S MANN]

I had downloaded the DB. I will reply soon.

[A S MANN]

View the new file attached below, Your Drop box view was wrong The Combo box is to have iD field while query was filter by plate name. Hence the mismatch hence the update does not run
Try new file

[orcaa]

Thanks, I have a look at it and will let you know

[orcaa]

Thanks for your solution, it works fine. But it was only a part of the problem. Perhaps you can help me with this.
First a small explanation about the items in the database.
If you have a look at the tables and the relations between the tables, next will be an explanation of this.

There are Plates (Plate_01 in table "T_Plate") and these are stored in a rack (Rack_01, a Rack has 10 positions for plates) and these Racks are stored in a freezer (Freezer_A, and 8 racks in a freezer).
These plates can be taken out the freezer and out a rack for a couple of days. In this period the Plates should be checkedout , the checkoutdate updated to today in table "T_Plate", but also the plate should be removed from the rack. (in table "T_Rack_Plate") And in case a rack is empty (so no plates stored in this rack) the rack should be removed from the freezer (In table "T_Freezer_Rack").

The question is : How can I update all these fields in the tables with an updatequery
Please help with this , if you need more explanation please ask.

This is a screenshot of how the results of a query should look like before and after checkout of a plate .

[A S MANN]

In your form on Combo box Property After Update Put a VBA code
If Me.Combobox.Value = -1 Then
Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
stDocName1 = "Update Query1"
stDocName2 = "Update Query2"
stDocName3 = "Update Query3"
DoCmd.OpenQuery , stDocName1, acViewNormal, acEdit
DoCmd.OpenQuery , stDocName2, acViewNormal, acEdit
DoCmd.OpenQuery , stDocName3, acViewNormal, acEdit
Else
End If
You can create many update query as you had created one these will run one by one and update the date in multiple table as per need
I could not make out your relatioship
If one Freezer had many rack and One Rack has many Plates
Then
Freezer Table Should have 1 to many relationship with Rack
Rack Table should have 1 to many relationship with Plate table
Are you creating a new Database or using a old Database
What is the reason of using many to many relationship in between that will create problem in your update query design
Correct Update query design need Freezer ---Rack---Plate all in one / Few query for it to work
Thanks

[orcaa]

Thanks for your reply.
I will try to create VBA code and see what happens.

In between I have a question to you about the relationships you suggested. This is a new design for a database and not yet in use. Your explanation about the 1 to many relations is logical, but I don't know how to implement hat with the tables T_Rack_Plate in the middle of T_Plate and T_Rack and the same for T_Freezer_Rack in the middle of T_Rack and T_Freezer.
You have the database can you send me an example of how the relation should look like according to you?

Many thanks

[orcaa]

I tried to run an update query with the vba code (see picture) , but get an error message. I included the vba code and also the querycode

[A S MANN]

The code are generic in nature you need to modified as per your object name and conditions.
I am attaching a new Db file to view it. You acn run many update query through it

What do you need a intermediate table T_Rack_Plate in the middle of T_Plate and T_Rack and the same for T_Freezer_Rack in the middle of T_Rack and T_Freezer.
i think You need to use only three tables
Freezer ---Rack---Plate
all having one to many ralatioship.
Link primary Key of Freezer to Foreign key of Rack
Link Primary key of Rack to Foreign key of Plate to create one to many ralationship
you can Read more information about ralationship.
Thanks

[orcaa]

Thanks again for the update query, I tried it and it worked fine.

Also I had a look at the different tables and I thought it was always neccassery to have a table in between to connnect the different tables. But now I removed these two inbetween tables and created new connections between the remaining tables.
Then I created an update query that updates the fields "Checkout", " Checkoutdate" and "RackId" of the table "T_Plate".
When this update query is started from the form " F_update_checkout_plates",
this errormessage pops up

Do you have any idea what is the reason of this error.
Thanks

This a zip of the new database
Vriezer_forum_new_tables_b.zip

[A S MANN]

Vriezer_forum_new_tables.zipLook at attached file.

At Update form :
To update Freezer Check Out all rack and Plate are updated
Place Check mark at at Rack Check Out Update in sub form to Update all plates
Click at + mark to expand Rack Check mark at Checkout to update date.

Regarding your error massage that was coming due since you are trying to update ID field by --
which is a string while ID fields are No Hence type conversion error.

[orcaa]

Thanks a lot.

I need some time to go over it and to see al the changes you made.

Many thanks and I will come back to it

[A S MANN]

If that resolve your issue than mention that the issue is resolved
A S MANN

[orcaa]

Wow, this looks great. I never could make all this vb code and macros to run the form.

There is only one small issue. As the database is now created, when a plate is taken out a rack and out a freezer it is checked out and when you put it back the plate is chekedin in the same rack and the same freezer.
But in practice. When a plate or a set of plates are checked out, they are used somewhere else for a couple of days and when they are put back in a rack and in a freezer it is not always in the same rack or in the same freezer, because some one else could have placed other plates already in that rack or freezer.
You must be able to place a plate randomly in a rack and a rack randomly in a freezer.

Did I explain this clear enough?

But thanks for all the time you already spend on my problem. I appreciate it .