Checkbox Updating Another Checkbox 1

[BJNK]

I have a form "Batch" that shows the status of a batch and a subform with all the orders that are selected into a batch.

Batch:
Batch_ID
In Production Y/N
Shipped Y/N

Production Subform:
Order_ID
Order_Date

There are two checkboxes; one for In Production and one for Shipped. These two checkboxes are also in the Order form.

I would like it so that when the checkbox(es) in the Batch form is selected, it will update the checkbox(es) in their respective Orders as well. That way they dont need to individually go through and check off each order, and when a user needs to see the status of an order they can bring up that order and quickly see what stage it is in.

Thank you!

 

[Remou]

You can loop through the subform recordset and update the fields or else use an update query.

 

[BJNK]

I actually made an update query, but Im unsure how to add it to my checkboxes in the Batch form. I went into the expression builder and went to the queries, but update queries are not displayed.

How would I go about applying it?

 

[Remou]

I think you will need a little code. Something like:

Private Sub ProductionYesNo_AfterUpdate()
   If ProductionYesNo=True Then
        DoCmd.RunQuery "qryChangeToYes"
   Else
        DoCmd.RunQuery "qryChangeToNo"
   End If
End Sub

The above is not real code, only an indication of the direction to go. You can change the above to include the names of real queries and controls and post back, if you wish.

 

[BJNK]

I think Im gonna need baby steps here...

Code:

Private Sub bprodyesno_AfterUpdate()
If bProdyesno = True Then
DoCmd.OpenQuery "UpdateProd"
Else
DoCmd.OpenQuery "UpdateProdNo"
End If
End Sub

My sloppy SQL for "UpdateProd":

UPDATE (Batch INNER JOIN [Order] ON Batch.InProduction = Order.[InProduction]) INNER JOIN Production ON (Order.Order_ID = Production.Order_ID) AND (Batch.Batch_ID = Production.Batch_ID)
SET [Order].[InProduction] = Yes
WHERE (((Batch.InProduction)=Yes) AND (([Forms]![Batch]![Order_ID])=[Order].[Order_ID]));


When I click the box, the message appears, are you sure you want to update, I click yes, then says You are about to Update (0) Records. Same for unclicking.
But neither seem to have any effect on the combobox in the Orders form.

I dont think this is that complicated, but I dont have the understanding of you real pros, so thank you for your help!

 

[Remou]

I think you will need to have a look at the SQL, because of the marked part:

[tt]UPDATE (Batch INNER JOIN [Order] [red]ON Batch.InProduction = Order.[InProduction])[/red] INNER JOIN Production ON (Order.Order_ID = Production.Order_ID) AND (Batch.Batch_ID = Production.Batch_ID)
SET [Order].[InProduction] = Yes
WHERE (((Batch.InProduction)=Yes) AND (([Forms]![Batch]![Order_ID])=[Order].[Order_ID]));[/tt]

It is contradictory. You want to set Order InProduction to 'Yes' where it does not match. I think it should be safe to just leave the marked join out.

 

[BJNK]

Ok, I have tried changing the SQL to the following, and also without the use of the Production table (the table that links Batch and Order) and still no luck.

UPDATE [Order] INNER JOIN (Batch INNER JOIN Production ON Batch.Batch_ID = Production.Batch_ID) ON Order.Order_ID = Production.Order_ID SET [Order].InProduction = Yes
WHERE (((Batch.InProduction)=Yes) AND (([Forms]![Batch]![Order_ID])=[Order].[Order_ID]));

Any other ideas? What about the first suggestion you had "loop through the subform recordset and update the fields"? What exactly do you mean by that? Have a link to open each record for editing? I would prefer something automated, but in times of need I will take what I can get/use/understand.

Thanks

 

[Remou]

It would be automatic, I am just a little worried that I do not understand your set-up. Can you post the SQL for the subform, please? In the meantime, the general outline would be:

Private Sub bprodyesno_AfterUpdate()
'Needs a reference to the Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset
  Set rs=Me.[i][Name of Subform Control][/i].Form.RecordsetClone
  rs.MoveFirst
If bProdyesno = True Then
  Do While Not rs.EOF
     rs.Edit
     rs!InProduction = True
     rs.Update
     rs.MoveNext
  Loop
  Set rs=Nothing
  Me.[i][Name of Subform Control][/i].Form.Requery   
Else
  Do While Not rs.EOF
     rs.Edit
     rs!InProduction = False
     rs.Update
     rs.MoveNext
  Loop
  Set rs=Nothing
  Me.[i][Name of Subform Control][/i].Form.Requery   
End If
End Sub

Once again, this is not real code, just a sketch.

 

[BJNK]

The SQL for the subform is very simple (assuming you mean record source)

SELECT Order.OrderDate, Production.Order_ID, Production.Batch_ID
FROM [Order] INNER JOIN Production ON Order.Order_ID = Production.Order_ID;

I am kind of following your code, but I am not that familiar with VB and all of its commands.

 

[Remou]

Try this for the update query and see if it does what you want:

[tt]UPDATE [Order] INNER JOIN Production ON Order.Order_ID = Production.Order_ID SET [Order].InProduction = True
WHERE Order.Order_ID=[Forms]![Batch]![Order_ID];[/tt]

 

[BJNK]

Hmm. Progress! Well I took the above SQL you provided, and no luck. I then changed the [Forms]![Batch]![Order_ID] to [Forms]![Prodction Subform]![Order_ID] Production Subform is the name of the subform within the Batch form. When I clicked the box it then prompted me to enter the value for [Forms]![Prodction Subform]![Order_ID] and I entered 001, which is a hypothetical order in the batch, and it worked! Order 001 was marked as in Production.

Now maybe you could tell me how to make it actually read the Order_ID instead of being confused and prompting me for a value... Its definitley on the right track, Im just not sure what is missing. Thank you!

 

[Remou]

Are all the Order_Id's on the subform the same? If not, you have a problem. It would be more usual to reference an ID on the main form, in this sort of set-up. To reference a subform, you need an extra 'form':

[tt][Forms]![Production Subform].Form![Order_ID][/tt]

You will find that 'Production Subform' is the name of the subform control, but you wish to reference the form contained by this control.

 

[BJNK]

The Order_ID's are different, as in this case a batch is made up of different orders that are due to be shipped within 30 days, and can be manaufactured together most efficiently.

So the Batch is the main form and the Orders are what are added into the batch. Each bacth could have anywhere from 1 to 10 orders in it. Im not sure how to incorporate that setup into the main form itself, and I thought it would be necessary to have it as a subform... Of course I am learning as I go.

And also [Forms]![Production Subform].Form![Order_ID]
or [Forms]![Production Subform].[Batch]![Order_ID]
Does not seem to solve the pop up. Is there anyway to input multiple values into that popup? If there is I would be willing to settle for that for the time being lol.

 

[Remou]

No, there is not, but you must have a link child and link master field? I assumed it was Order_Id but I can see I am wrong. What field do the two forms have in common?

 

[BJNK]

I thought I had shown the connection earlier, but I just went and read through my posts and realise I have not. My apologies. Here are how my tables relate:

tblOrder
Order_ID (pk)
InProduction
Shipped
many others that are irrelevant.

connects to...

tblProduction
Order_ID (pk)(fk)
Batch_ID (pk)(fk)

connects to...

tblBatch
Batch_ID(PK)
InProduction
Shipped

The form is made up of Batch with all its values, and Production (as the subform) with only the Order_ID and that is a combo box. From that combo box I have added columns that link to the Order_ID in the Order table to show things like order date, total $ amount etc that fill automatically based on the Order_ID.

Does it all make sense? Have I made a fatal rookie mistake?

Thank your Remou for your help I REALLY appreciate it!

 

[Remou]

So does each record in the subform have the same Batch ID? That is, what is the Link Child Field?

 

[BJNK]

Yes each record in the subform would have the same Batch ID.

 

[Remou]

Ok, so that should be it then, unless there are a bucket of other records with the same Batch ID yes no? So we have:

[tt]UPDATE [Order] INNER JOIN Production ON Order.Order_ID = Production.Order_ID SET [Order].InProduction = True
WHERE Production.Batch_ID=[Forms]![Batch]![Batch_ID];[/tt]

Or does Batch_Id also appear in Order? Is Batch_ID a control on the main form? Is the main form called Batch. So many questions!

 

[BJNK]

OH PRAISE BE TO REMOU! Haha unreal, works like a charm! Thank you so much. You have no idea how happy this makes me.. its the last (hopefully) hurdle Ive had to overcome to finish this database! Im so pumped.

Thank you again Remou for your continued help! No attitude either when I didn't give all the info.. what a guy

BTW what is it that you do, that you are able to be on here and help everyone in these forums?

 

[PHV]

Anyway, the correct way for referencing the subform's control:
[Forms]![Batch]![Prodction Subform].Form![Order_ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886