changing a fieldvalue

[fedum]

I am using a subform where I scan an article that I sell. I would like to change the Stock of this article.
First I use Dlookup() to find this article in the table and read the value of the Stock. Then I would like to change this value into a new one (stock before - number of sell). Is this possible? How can I put the new value into the table???

 

[Remou]

Why not include the stock quantity on the form? You can then show the user current stock, allow them to enter a sales quantity and update the stock to current level-sales.

 

[fedum]

I have tried this but I get a error because the record is still active.
I read out the stock, give it to a variable and then calculate the new stock and put it back into the field stock. Gives a error.

 

[Remou]

Please post your code.

 

[fedum]

This is my code
Private Sub Barcode_LostFocus()

Dim intInStock As Integer

If gRegistratieBarcode = True And Not (IsNull(Barcode))Then
If (PrijsPerEenheid = 0 Or IsNull(PrijsPerEenheid) Or IsMissing(PrijsPerEenheid)) And Prijs = 0 Then
DoCmd.OpenForm "frmPrijsIngave", acNormal, , , , acDialog
Prijs = gIngavePrijs
Else
Prijs = PrijsPerEenheid
End If
intInStock = Me.InStock - Me.Stuks
Me.InStock = intInStock
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Thanks
Marc

 

[Remou]

This Barcode_LostFocus() is not a good event to use. Can you use the After Update event? Also, is a sales quantity already included in Stuks?

I think the code should run like this:
[tt]Enter bar code
Confirm record exits
Set focus to sales quantity
Enter sales quantity
Update In stock
Message to user
Move to next record[/tt]

How do you see it all working?

 

[fedum]

This form is a subform.
I start scanning the barcode and jump to a new record to scan the next article. The standard value of "stuks" is 1 and I can change this by going back to the previous record. So, the "stock" has to be changed if I go to the next record, and if "stuks" is changed.

Thanks,
Marc

 

[Remou]

Is the InStock field on the subform? What is the error that your code gives?

 

[fedum]

Yes InStock is on the subform
Errorcode:
Run-time error '-2147352567 (800200009)
Cannot add record(s);Join key of table tblDimensies not in recordset

If I change the code to where I jump from one record to the other then there is no problem.

Private Sub Barcode_LostFocus()
Dim intInStock As Integer
If gRegistratieBarcode = True And Not (IsNull(Barcode)) Then
If (PrijsPerEenheid = 0 Or IsNull(PrijsPerEenheid) Or IsMissing(PrijsPerEenheid)) And Prijs = 0 Then
DoCmd.OpenForm "frmPrijsIngave", acNormal, , , , acDialog
Prijs = gIngavePrijs
Else
Prijs = PrijsPerEenheid
End If
intInStock = Me.InStock - Me.Stuks
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Me.InStock = intInStock
DoCmd.GoToRecord , , acNext
End If
End Sub

When I change the field Stuks I use the next code without any problem.

Private Sub Stuks_AfterUpdate()

Dim intAantalInStock As Integer
Dim strBarcode As String
Dim intInStock As Integer

intInStock = Me.InStock - Me.Stuks + 1 '+1 because I changed it before in Barcode
Me.InStock = intInStock
End Sub

 

[Remou]

Ok, I see what the problem is, I think. Add in a save:

Private Sub Barcode_LostFocus()
Dim intInStock As Integer
If gRegistratieBarcode = True And Not (IsNull(Barcode)) Then  
 If (PrijsPerEenheid = 0 Or IsNull(PrijsPerEenheid) Or IsMissing(PrijsPerEenheid)) And Prijs = 0 Then
       DoCmd.OpenForm "frmPrijsIngave", acNormal, , , , acDialog
       Prijs = gIngavePrijs 
    Else
        Prijs = PrijsPerEenheid
End If
    DoCmd.RunCommand accmdSaveRecord
    intInStock = Me.InStock - Me.Stuks
    'DoCmd.GoToRecord , , acNewRec
    'DoCmd.GoToRecord , , acPrevious
    Me.InStock = intInStock
    DoCmd.GoToRecord , , acNext
End If
End Sub

 

[fedum]

Ok this works!!!
Thank you for youre help. Hope to meet you again.

Thanks,
Marc