Problem with some VB code??

[RitaMoloney]

Hi,

I have a continuous subform, where records are saved too. The number of parts used are entered in each row and then the new UnitsInStock value is calculated accordingly.
UnitsOnHand:[NumberUsed]-[UnitsInStock].
This UnitsOnHand becomes the new UnitsInStock value which is working fine so far.

I want to put a Delete Button on the subform so that if the user needs to delete a record/row they can and I need the UnitsInStock value to be recalculated.
I have a calculated text box called DeleteRow with the following expression;
=[NumberUsed]+[UnitsInStock]

When a row is deleted I need the value in DeleteRow to be saved to UnitsInStock

I am using the following code is in the event procedure of the Delete Button;

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click

If MsgBox("Are you sure you want to delete the '" & Me.PartName & "' record?", vbQuestion + vbOKCancel, "Delete Current Record") = vbCancel Then

'user clicked Cancel
MsgBox "Delete action aborted!"
Exit Sub
Else 'user clicked OK

UnitsInStock.Value = DeleteRow.Value

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDeleteRecord_Click:
Exit Sub

Err_cmdDeleteRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteRecord_Click

End If
End Sub


This will delete a row but in is not saving the calculation to UnitsInStock. Could someone please help me out with this as I am not very good with vb.

Thanks in advance,
Rita

 

[KenHigg]

Can't you do all this automatically in the form footer using sum()'s?

???
kh

 

[RitaMoloney]

Hi,

Just wondering what exactly do you mean doing the calculation on the form footer?

I need to say if a row is deleted only then add the NumberUsed back to UnitsInStock i.e. DeleteRow=[NumberUsed]+[UnitsInStock]
How do I do this automatically on the form footer? could you explain a little futher to me.

Thanks,
Rita

 

[MStef]

Hello Rita!
Instead of delete record I suggest STORNO TRANSACTIONS.

 

[KenHigg]

What are the relevant flds in the recordsource for thr continous sub form?

kh

 

[RitaMoloney]

The subform's recordsource is made up from two tables as follows;

tblStore
PartNo (PK)
PartName
UnitsInStock
ReOrderLevel
Discontinued
Remark

tblPartsUsed
PartUsedID
JobDetailsID
PartNo (FK)
NumberUsed

The relevant fields would be PartNo, PartName, UnitsInStock, NumberUsed

 

[KenHigg]

The subform's recordsource is made up from two tables as follows;

tblStore
PartNo (PK)
PartName
UnitsInStock
ReOrderLevel
Discontinued
Remark

tblPartsUsed
PartUsedID
JobDetailsID
PartNo (FK)
NumberUsed

The relevant fields would be PartNo, PartName, UnitsInStock, NumberUsed

I will yield to the resident experts who do not think it's a good idea to do flds like 'UnitsInStock' & 'NumberUsed', as they usually chime in and point this out as a major issue...

Maybe one of them will read your post and make a suggestion...

kh

 

[RitaMoloney]

Hi Kh,

Have you no other suggestion other than I haven't named my fields correctly???

 

[KenHigg]

Hum... It was more about storing those values. Some db'rs advise against it at all cost. I just figured we'd get half way through a solution and then someone would point it out...

kh

 

[RitaMoloney]

Hi,

I do realise the complications about sorting these values and I know it's against the rules. But, I really need to store these as other calculations in different parts of the database needs the values, which has all worked out well so far.

There must be a way to save the unbound test box expression (DeleteRow=[NumberUsed]+[UnitsInStock]) value to UnitsInStock before the row is deleted. Please, have you any suggestions. I am even going about it the right way, trying to put it in the Delete Button?

Thanks,
Rita

 

[KenHigg]

Something like :

(psuedo code)

mysubForm!NumberUsed on ChangeEvent

forms!myMainForm!UnitsInStock = dsum(tblPartsUsed!NumberUsed, tblPartsUsed!PartNo = forms!myMainForm!PartNo

End sub

???
kh

 

[RitaMoloney]

Hi,

I put this code in the Form_Delete event as NumberUsed field will change constantly when the user add records they enter the NumberUsed (number of parts) for a job. I only want this code to kick in when a row is deleted.


UnitsInStock = DSum("[NumberUsed]+[UnitsInStock]", tblStore!PartNo = "tblPartsUsed_PartNo")

It's still not working for me but thanks for you help.
Rita