User changes in a table/form

[ukdane]

If a User changes the data in a form/table, how can I find out excatly which row of data has been changed, and what it was changed from and to? (To will be recorded on the table, but that means that the old data will be overwritten)

The reason I need to know this, is for use with my warehouse stock database.

Everything works fine, as long as the user doesn't make an error, and have to correct an order. The reason for this, is that as soon as the data is input into the form, a series of calculations take place, telling the user which area of the warehouse the user should take the stock from.
If the user then edits the amount of stock required, the database will need to reset the data, and then recalculate where to pull the stock from(as there might not be enough stock in the area originally suggested).

The process I need to run through if the user needs to change the data is:
1) Add the original amount of stock pulled back to each area it was pulled from.
2) Change the status of the changed data from "Calculated" to "Requires Calculation"
3) Run the code that is activated if an order row has the status "Requires Calculation".

Hope this makes a little bit of sense.

[OBP]

Hello again, I have VBA code for a form that logs all of the changes made to it's records and stores the log in a Table.
This could be used by you to do what you want, however if you are really only interested in certain fields you could just store those.
I am not sure how you can log the other changes to the Stock Location areas, do you use a query to do that or VBA that we generated on here?

[ukdane]

I've been playing around with various solutions, and believe that all I need to know is which row is being edited. That way I can get the unique id, and change the 1/0 field accordingly.
How do I find out the uniqueid of the row that is being changed?
(I only need to know the id when it is being changed, NOT when it is being created for the first time).

Do you have any code you can share for doing this?

Edit: With regards to the stock location changes, these are stored in another table, along with the "unique id" for the row in the order that it refers to. Therefore if I know which unique id is being changed, I was thinking, I can look this id up in the stock location table, and either delete, or create a negative copy (to add the stock back in).

[OBP]

I assume that the UniqueId is on the form when the User inputs the Change?

[OBP]

Basically there are 2 ways to do this, one the a Recordsetclone and the other with a Public Variable.

[ukdane]

It isn't on the userform, but only because I didn't put it there, as it isn't necessary for the user to see. BUT it can be put there, as it is just an autonumber created when the user adds a new line to the dataform.

[ukdane]

Basically there are 2 ways to do this, one the a Recordsetclone and the other with a Public Variable.


Go on...
:doh:

[OBP]

Which one do you want to try?
What is the name of the Unique Key Field on the Form?

[ukdane]

As I'm learning, I'm happy to try both ideas, and see which one works best for me.

(Start with the easiest) .... what's a recordsetclone?

The uniqueid, is called salesorderlineid: you should be able to see it on the relationships table in the other post, on the TblSalesOrderLine.

[OBP]

OK, in the form's Before Update Event Procedure enter

Dim rs As Object
Set rs = Me.RecordsetClone
msgbx "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock
rs.Close
Set rs = Nothing

Where fieldnamewhichhasstock is the actual name of the stock value that is being changed.
The Recordsetclone (rs) value is the one when the record is "current" and the me. value is the new one that will be written to the table.

That should show you how it works.

[ukdane]

m rs As Object
Set rs = Me.RecordsetClone
msgbox "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock
rs.Close
Set rs = Nothing


Tried this, and I'm getting an error in the line:

msgbox "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock the me.fieldnamewhichhasstock (which should be the same as the text i've used as re.fieldnamewhichhasstock) is returning an error: "Sub or function not
defined"


Just to be clear, this is the actual code that I used
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Object
msgbox "For record " & Me.saleorderlineid & " Old value = " & rs.saleorderlineunitrequired & " New Value = " & Me.saleorderlineunitrequired
rs.Close
Set rs = Nothing
End Sub

I removed the " & Me.saleorderlineunitrequired" and I then got a similar error for the "Me.saleorderlineid".
Something isn't right. :-)

Edit: I've just noticed I was missing the line Set rs = Me.RecordsetClone

[ukdane]

My mega post has now been deleted twice! :banghead:

I'll post in short bursts to avoid this happening for a third time.

I changed the Msgbx to Msgbox
so the code MsgBox "For record " & Me.saleorderlineid
this now returns the number of the line that has been changed.

This is good.
I now need to use this number to do two things.
1) Table TblSaleOrderLine contains the following info:

saleorderlineid (this is a unique id created by an autonumber and is the number found above.)
saleorderid (this is the sales order id)
productid (this is the product id)
saleorderlineunitrequired (this is the number of units that are required)
sakeprocessed (this is a tick box it is ticked when a sereis of code/marcos have been run. This needs to be reset to not ticked/0 for the row that has been changed.and post....

[ukdane]

Yes (succes)
The second thing that I need to do is:
Table TblOrderPull contains:

pullid (a unique autonumber id)
productid (the product number)
areaid (the location where the goods have to be taken from)
saleorder id (the number of the sale order)
saleorderline (this is the number that needs to be located, and could occur on this table more than once)
unitstopull (this is the number of units that must be pulled from each location)I need to to one of two things, (unsure which is best)
Either: delete each occurance of the salesorderline (this is probably more accurate with regards to other calculations)
Or: the entire row needs to be copied, and the unitstopull number needs to be negated (so that the total product/area/unitstopull = 0) I'm not so sure this will be as accurate.


Any clues/code/help with both these things problems please?

[ukdane]

With regards to the first question, I came up with the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myconnection As ADODB.Connection
Dim myRecordChange As New ADODB.Recordset
Dim myorderline As Variant
myRecordChange.ActiveConnection = myconnection
myorderline = Me.saleorderlineid
myRecordChange.Open "TblSaleOrderLine", , adOpenKeyset, adLockOptimistic
myRecordChange.MoveFirst
While Not myRecordChange.EOF
If myRecordChange.Fields("saleorderlineid").Value = myorderline Then
myRecordChange.Fields("saleprocessed").Value = 0
Else
End If
Wend
myRecordChange.Close
Set myRecordChange = Nothing
End Sub

BUT, the line myRecordChange.Open "TblSaleOrderLine", , adOpenKeyset, adLockOptimistic is causing a runtime error 3709 "The connection can not be used to perform this operation. It is either closed or invalid in this context."

[ukdane]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid

myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
myRecordChange.Update
End If
myRecordChange.Close
Set myRecordChange = Nothing
End Sub

Tried the above code, but the saleprocessed field doesn't change from ticked to unticked.

[OBP]

Use
myRecordChange.Fields("saleprocessed").Value = 0

[ukdane]

Hi OBP,
Actually I did have it set to 0 to start with.
= false does work... but the tabel doesn't keep the change.
the myRecordChange.update works, and the table changes,
but the change isn't kept, when the sub ends.

[ukdane]

Still not keeping the changes. :dunno

[ukdane]

Can I somehow use this

mySQL = "UPDATE TblSaleOrderLine SET TblSaleOrderLine.saleprocessed = 0" & _
"WHERE (((TblSaleOrderLine.saleorderlineid)= " & myorderline & "))"

within the beforeupdate instead?

[ukdane]

Finally sussed out what was missing wrong.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid

myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
myRecordChange.Update
End If
myRecordChange.Close
Set myRecordChange = Nothing
End Sub

I added Me.[saleprocessed].Value = False

in between the else and end if.... and hey presto, it seems to work! :beerchug:

[OBP]

Well done, sorry I wasn't around, but I have been busy.

[ukdane]

My approach to the second problem has been included in the code below (which also includes the first solution).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Dim checkline As Variant
Dim myconnection As ADODB.Connection
Dim myDeletePull As New ADODB.Recordset
Set myconnection = CurrentProject.Connection
myDeletePull.ActiveConnection = myconnection
myDeletePull.Open "TblOrderPull", , adOpenDynamic

Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid

myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
'updates TblSaleOrderLine!saleprocessed from 1 (true) to 0 (false)
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
Me.[saleprocessed].Value = False
myRecordChange.Update

'deletes any instances in TblOrderPull where salesorderline = myorderline
myDeletePull.MoveFirst
While Not myDeletePull.EOF

checkline = myDeletePull.Fields("salesorderlineid").Value
If checkline = myorderline Then
MsgBox "Found at id: " & myDeletePull.Fields("pullid").Value
myDeletePull.Delete

Else
End If

myDeletePull.MoveNext

Wend
End If

myDeletePull.Close
myRecordChange.Close
Set myDeletePull = Nothing
Set myRecordChange = Nothing
End Sub

The line myDeletePull.Delete needs to delete the current row of data. I thought that it would work, but I get a Run-time error "3251": Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

So what's the solution I need here, in order to delete the current record?

Ah got the problem.

myDeletePull.Open "TblOrderPull", , adOpenKeyset, adLockOptimistic

[OBP]

Is it "Solved" now?

[ukdane]

Almost, which is why I haven't changed the status yet. (That way I can still add posts)

[ukdane]

For example:

I now want to add some buttons on the main form, that control the subform; such as GoToRecord.
I've added buttons that do the following:
DoCmd.GoToRecord , "FrmConsignee", acNext
DoCmd.GoToRecord , "FrmConsignee", acPrevious

with "FrmConsginee" being the name of the subform.
The buttons occur on the main form.
However when I press the buttons, I get the following error:
"The object "FrmConsignee" isn't open."

I've also tried using "Me.FrmConsignee" again without luck.

[OBP]

Try it like this with your Form and field names

Me![WorkHardware Subform].SetFocus

Me![WorkHardware Subform].Form![Combo6].SetFocus

DoCmd.GoToRecord , , acNext

[ukdane]

Not sure I get what you mean.

The cosde I currently have (which doesn't work) is:
Private Sub bttn_gotofirstdelivery_Click()
On Error GoTo Err_bttn_gotofirstdelivery_Click

DoCmd.GoToRecord , "Me.FrmConsignee", acFirst
Exit_bttn_gotofirstdelivery_Click:
Exit Sub
Err_bttn_gotofirstdelivery_Click:
MsgBox Err.Description
Resume Exit_bttn_gotofirstdelivery_Click

End Sub

The parent form (Me) is called: FrmSupplier
The subform is called: FrmConsignee
The buttton to call the above code is called: bttn_gotofirstdelivery
The above code is on the parent forms code.


In your code below, what is Combo6 ?

[OBP]

Combo6 is the first field on my Subform, what the code does is set the focus to the subform and then to the first field on that subform and THEN does the docmd.gotorecord.

[ukdane]

Gotcha, thanks.

Is it possible to create a form that the user can use to perform an advanced filter on the results of another form?

For example, when the user goes from the Main Menu, into the "Edit Records", they are first directed to a form, which includes various fields (I guess they should be dropdowns - and a calendar). If the user completes any of the dropdowns, or selects a date on the calendar then the records are sorted accordingly.

I'm certain this can be done, but having written the above, it seems more complicated than I initially thought.

Suggestions please?

[OBP]

Yes of course, you can do it by Query, Form Filter or by VBA generated SQL recordset.
Take your pick.

[ukdane]

I guess the question should be which is the most effective, and requires the least Mb?

Can you help me with an example (of the most effective method)?

[OBP]

I would suggest the Form Filter method provides a flexible system as the records to be viewed/edited casn be on a subform on the "filtering" form.
Take a look at this example called Search 2000 that I posted on this thread.
http://www.vbaexpress.com/forum/showthread.php?t=25027

[ukdane]

Ecellent, the Allen Browne database seems very helpful, and I like the fact I don't need an extra form, but instead that I can put them directly on the form.

BUT that does lead to another question.
I use the same form, regardless of whether I am adding a new record, or editing an old record.

However, I only want the search criteria to appear on existing records (the ones I can edit)- but not to appear if I am adding a new record (as that could confuse the end user).
How can I do this? How can I tell the form not to show the search criteria if it's showing the new record?

Thanks as always

[OBP]

You use VBA code in the On Current event to set those fields & Combos to Visible or Invisible. It goes like this

If Not me.newrecord then
me.combonumberorname.Visible = True
me.fieldname.Visible = True 'etc
else
me.combonumberorname.Visible = False
me.fieldname.Visible = False 'etc
End if

Where combonumberorname are the actual names of your combos and fieldname are the names of your fields, if you have any.

[ukdane]

Great thanks,
That's a big help.
I'm on holiday until 4th Jan now, so I'll be keeping this thread as unsolved until I'm sure I'm done asking questions about it.
Happy Holidays!
:thumb

[ukdane]

I've got another question about the filter. (I know, I should be on holiday!)

If the filter doesn't return any results, it just shows the new record. (And because of the way I've programmed it, it then won't show the filter options, so I can't then clear the irrelevant filter.

Is there some smart piece of code I can use so that if the filter is run, and no results are shown, it just posts a message stating that no results were found, and then the filter is reset.

(I'm still using Allen Browne's filter system)

Thanks, and again, happy holidays.

[OBP]

You can check the "Current" record's key field and if it null use a msgbox to tell the user and then set Filter off.

[ukdane]

What code would I use to NOT print the first page of a report?