mark all products from subform

[ailyn]

I have a form orders with a subform orderdetails where we can order more than one product. When the order is finished you save it with a save button where it will also automathically mark the products ordered as not available.
Now, this code works, the problem is that it only marks one product. how do you make it mark all the products that where ordered (in orderdetails - the subform)? :


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'set values here!
'loop?
Dim stAvai As String
Dim strSetProd As String

stAvai = "= " & Me.OrderDSubform.Form!ProductIdctrl.Value & " "
strSetProd = "UPDATE Products " & _
"SET Products.[Avail]= True " & _
"WHERE Products.[ProductId]" & stAvai & " ;"
DoEvents
DoCmd.RunSQL strSetProd

 

[macca the hacke]

How about referencing the field in a query, and then using an update query to update the table.

I am sure could do in code also, but above is probably simplest

 

[ailyn]

Sorry but could you, please, indicate me how to do that?
I also was thinking that maybe it would be a good idea to count how many orderdetails there are and making the RunSql as many times as necessary. But I have no idea how to start a code for that. Do you know how to or where I could find that?
the problem is when I try Microsoft Access help it alwya s explains all the parts but doesn't give appropriate examples. So I end up as lost as I was.

 

[macca the hacke]

Say you have a form called [frm_Data]with a text box called [txtOrderDetails], then you can reference what is in the text box in a query by typing

forms![frm_Data]![txtOrderDetails] in the criteria field of the query

You could then build an update query that uses this query

If this doesn;t help try posting screen shots of form/underlying tables and I could have a look (although I will be logging off in about 40 mins 'till Monday - sorry!)

 

[seth_belgium]

The is the code for doing it with a loop:

'Set focus to subform
    [Forms]![frmOrder]![frmSUBProducts].SetFocus
'Goto first record
    DoCmd.RunCommand acCmdRecordsGoToFirst
'Get the total number of records
    Dim TotalRecords
    TotalRecords = DCount("[ProductID]", Forms![frmOrder]![frmSUBProducts].Form.RecordSource)
'Start the loop
Do
    'Mark record
    Forms![frmOrder]![frmSUBProducts].Form![Unavailable].Value = True
    'Goto next record
    DoCmd.RunCommand acCmdRecordsGoToNext
'Loop condition
Loop Until Forms![frmOrder]![frmSUBProducts].Form.Currentrecord = TotalRecords + 1

frmOrder is your mainform (order-form).
frmSUBProducts is your subform that contains the products.
ProductID is a (unique) field in your subform (you can choose whichever field you want to count).

Give it a try, I hope it works! If it doens't, Macca's way does also work (using queries to do the trick).

Greetz,

Seth

 

[ailyn]

Thanks but I get an error message saying that the field is to little for pasting so much data. I tried your code like this:

'Set focus to subform
    [Forms]![Orders]![OrderDSubform].SetFocus
    'Goto first record
    DoCmd.RunCommand acCmdRecordsGoToFirst
    'Get the total number of records
    Dim TotalRecords
    TotalRecords = DCount("[OrderDetailId]", Forms![Orders]![OrderDSubform].Form.RecordSource)
    'start the loop
    Do
    'Mark record
    Forms![Orders]![OrderDSubform].Form![Available].Value = True
    Forms![Orders]![OrderDSubform].Form![Avail].Value = True 'Start the loop
    'Goto next record
    DoCmd.RunCommand acCmdRecordsGoToNext
    'Loop condition
    Loop Until Forms![Orders]![OrderDSubform].Form.CurrentRecord = TotalRecords + 1

I counted the OrderDetailId because for each combination ProductId/BlowerId I get one OrderDetailId (it could be that there no ProductId or no BlowerId in the OrderDetails table).

It seems that my code was not even counting the TotalRecords because I added once a 'MsgBox TotalRecords' & it never showed up. Would a For Each work better here?(I don't know the syntax for that one either ^.^')

 

[ailyn]

Forget what I said before about the For Each I tried it and all it did was set and reset the first ProductId and BlowerId (from the first OrderDetailId).

a bit more of useful info on my tables:
tblOrders (=form Orders)
OrderId
OrderDate
Seller
Customer
...

tblOrderDetails (= Form OrderDSubform)
OrderDetailId
OrderId
ProductId (from tblProducts)
BlowerId (from tblBlowers)
Price
...

tblProducts
ProductId
Ref
Serial
Avail (yes/no) /for item ordered = not available (value yes/true)
uit (yes/no) / for item invoiced = out of wharehouse (value yes/true)

tblBlowers
BlowerId
Type
Serial
Available (yes/no) /for item ordered = not available (value yes/true)
out (yes/no) / for item invoiced = out of wharehouse (value yes/true)

 

[seth_belgium]

Lets first try to get rid of that error.

Check if these fields are both Boolean (Yes/No) in your table.

    Forms![Orders]![OrderDSubform].Form![Available].Value = True
    Forms![Orders]![OrderDSubform].Form![Avail].Value = True

If one of the two is a text-field, change the = True with = "True".

If that still doesn't do the trick, please post the database in attachment because I can't think of any other reasons why it doesn't work...

Seth

 

[seth_belgium]

Oh... I see they are both boolean's

Consider posting the database, I need to see it...

Seth

 

[ailyn]

Ok. I had to erase a lot to be able to zip it with not to many Kbs.

 

[seth_belgium]

The error was caused the recordsource of the subform; it was a SQL-string while I had expected a queryname.

So, I fixed this by creating a query from your query-string. I also fixed the loop because the way it was, it would never include the last record on the form.

Everything works now, however I'm not sure wether the code will still work when you create a new order (the form to test that was missing ). So, please test that and if it works, then we're all happy!

Greetz,

Seth

 

[ailyn]

Wow, Thanks a lot Seth. It works I forgot to put the query as the source for my subform in the first try but now it works perfectly. THAAANKS!

P.S.: About the form for the new order (Products) I still have got problems with the fact that from Products I load the ProductId into the subform, but that's a long one. I'll post it on another thread. I think your code will work perfectely there. so Thaaanks again! XD

 

[seth_belgium]

No problemo