VBA for applying Value on only the filtered data in subform?

[rodvaN]

Greetings..
I would like to know if there is a VBA to apply a value on all the fields that are filtered in a subform..
For a better understanding I took a screenshot and made a detailed explanation on it.
Thanks.

 

[Guus2005]

I am afraid that i don't understand the question.
You want to set a field to false for the all the records in the subform?

If that is in fact the case then it depends on your database design. If SalidaAlmacen is part of the mainform then you only have to change one record. if it is part of the records in the subform then you have to change x records.

update TableMainform set SalidaAlmacen=False where ShipmentNo='KTN2009000001' and OrderNo = '000001'

update TableSubform set SalidaAlmacen=False where ShipmentNo='KTN2009000001' and OrderNo = '000001'

I don't think this is what you want but you have to be more specific.

HTH

 

[rodvaN]

Guus2005 .. you are right.. thats what I want, I want to update a field on my main table where in the fields
ShipmentNo='KTN2009000001' and OrderNo = '000001'

But this must be filtered as the current value on a field in the form..
for example if Im navigating on KTN2009000003 and order 000003
then change fields values to those current filters.

Maybe it should be where ShipmentNo='Forms!Ship!Shipments' for the current value on the record.

I hope to make clear on this one..
Thanks in advance.

 

[Guus2005]

In your case it would be something like this:

private sub button_click()
    dim strSql as string
    strsql = "update TableMainform set SalidaAlmacen=False " & _
         " where ShipmentNo='" & [B]Forms!Ship!Shipments[/B] & "' " & _
         " and OrderNo = '" & [B]Forms!Ship!OrderNo[/B]  & "'"
    currentdb.execute strsql
end sub

HTH

 

[rodvaN]

Guus2005.. We're almost there!
But now the problem is with the debug in the line ..
CurrentDb.Execute strSql
It is displaying me an error.. how can I solve this?
Thanks for all the great help.

 

[rodvaN]

Up! .. anyone?

 

[Guus2005]

When the error occures display the contents of the strSql variable in the immediate window (ctrl+g)

?strSql

The result will be something like this:

update TableMainform set SalidaAlmacen=False where ShipmentNo='KTN2009000001' and OrderNo = '000001'

You will get an error message when OrderNo is not a string but a number. Then remove the single quotes.

update TableMainform set SalidaAlmacen=False where ShipmentNo='KTN2009000001' and OrderNo = 000001

When you check the contents of the strSql variable, you'll find that the preceeding nuls have vanished

update TableMainform set SalidaAlmacen=False where ShipmentNo='KTN2009000001' and OrderNo = 1


Adjust the code accordingly

private sub button_click()
    dim strSql as string
    strsql = "update TableMainform set SalidaAlmacen=False " & _
         " where ShipmentNo='" & Forms!Ship!Shipments & "' " & _
         " and OrderNo = " & Forms!Ship!OrderNo
    currentdb.execute strsql
end sub

HTH

 

[rodvaN]

It has been produced an error '3075' in the runtime..
Syntax error (missing operator) in the consult expression 'Shipment='KTN2009000001' and' .

I follow your steps on the CTRL-G but the Immediate window is empty.. However, I dont mind using the ORDER NO.. Its more than enough with only the Shipment.
Sorry for all the mess.

 

[Guus2005]

So the problem is solved?

 

[rodvaN]

So the problem is solved?

Im afraid it dont work :S
It keeps displaying me missing parameters, or the error before.. :S
Is there other way for running and SQL command with a button click ?

 

[rodvaN]

Guus..
The code I got is..

Private Sub Comando40_Click()
    Dim strSql As String
    strSql = "update BASE set SalidaAlmacen=False " & _
         " where Shipment=" & Forms!salidas!Shipment & ""
    CurrentDb.Execute strSql

End Sub

Its throwing me this error..

Its been an error '3061' in the runtime..
Few parameters.. 2 expected.

Its the only thing left for me to finish this project.. S.O.S

 

[rodvaN]

Guus2005 .. Im really breaking my head doing all kind of crazy codes for making it work.. could you do an example file.. I could pay you via paypal.. please send me a private message with your contact to talk more deeply about this.. im willing to pay for this..
Sincerely..
rodvaN

 

[Guus2005]

The query statement suggests you have a table named BASE and in that table at least two two fields named SalidaAlmacen (yes/no) and Shipment (string)
Make sure you do.

If you do have a tables with that name and those fields, something else is wrong. In that case, post a sample database (zipped).

HTH

 

[rodvaN]

Here I post the access database..
The second window when you do the product pickign and save it, it has a cancel button that will unmark all the products in the list and return them to warehouse.
Hope it can be done.
Thanks

 

[rodvaN]

Up!.. Anyone can help please?

 

[rodvaN]

Guus2005.. I just uploaded a sample database, could you help me out of this?
Thanks alot in advance.

 

[Guus2005]

on vacation, get back to you.