filter a certain type only

[rainbows]

Private Sub StockQty_AfterUpdate()

Me.DateModified = Now()

Dim OldValue As String
Dim NewValue As String
Dim MaterialID As Double
MaterialID = Me.MaterialID

OldValue = Me.StockQty.OldValue
NewValue = Me.StockQty

DoCmd.RunSQL "INSERT INTO TblDataChanges ( Materialid, ChangeDate, ControlName, OldValue, NewValue, StockNumber ) VALUES ('" & MaterialID & "',Now(),'stockqty1' ,'" & OldValue & "', '" & NewValue & "', '" & StockNumber & "');"  


Exit Sub
Me.DateModified = Now()


End Sub

hi above is the code i use for detailing all the changes i make to the stocklist in a table called " tbldatachanges " what i would like to do is now only show the changes to a certain type " for example " resin " not sure how to do that

thanks steve

 

[moke123]

Select * from tbldatachanges Where Type = 'Resin'

Note that type is a reserved word and should not be used as a field name

 

[Mike Krailo]

Dim MaterialID As Double

Also, why is your MaterialID a Double, it should be a Long.

 

[Gasman]

Also, why is your MaterialID a Double, it should be a Long.

Appears to be a string?

('" & MaterialID & "'

 

[Mike Krailo]

Appears to be a string?

Going by the screenshot, it looks like a Long number to me.

 

[rainbows]

thank you
I have changed it to long
not sure where to put this part Select * from tbldatachanges Where Type = 'Resin' within the code
steve

 

[Mike Krailo]

thank you
I have changed it to long
not sure where to put this part Select * from tbldatachanges Where Type = 'Resin' within the code
steve

To view changes, you need a form with a record source from TblDataChanges. Then maybe in the header a combo with all the distinct types and a simple after update to filter the results. You can add other filters to this as desired, but the coding gets more complicated.

' cboTypeFilt is an unbound combo box control in the header of your continuous form
' for recordsource from TblDataChanges that has the distinct Types available to choose from.
Private Sub cboTypeFilt_AfterUpdate()
   Dim FiltStr As String
   FiltStr = "Type = '" & Me.Type & "'"
   Me.Filter = FiltStr
   Me.FilterOn
End Sub

You can also launch this form with an OpenArgs value of Me.Type and use that in the Open event of your TblDataChanges form.

Update: I forgot to mention that the changes have to be viewed based on a query that joins the two tables together so your Type value will be visible along with what ever other fields you wish to view. Hope that makes sense.

 

[Pat Hartman]

@rainbows What happens if the user decides to not save the record he just changed? Are you backing out the insert you did?

You also have the StockQty defined as a string. That can't be right.

 

[rainbows]

Pat, if he enters the stock qty and clicks of it , it changes the table so if he needs to change it back I will see what he has done. SO NO i was not thinking of backing out of it.

you are correct it should not be string

steve

 

[Pat Hartman]

SO NO i was not thinking of backing out of it.

Then you cannot rely on the data being valid. You always have to worry about this when one update depends on another and the first is not committed before you commit the second.

I would probably keep track of the update and then in the form's AfterUpdate event, run the inventory adjustment only after I knew that the current record had been saved.