How to update a record in a different table (1 Viewer)

[R32chaos]

Hello,
I am creating a donations inventory table. Then I am creating a purchasers table. In a purchases entry form, under the purchaser's record, when I select an item from the inventory as having been purchased, I need a field for that inventory item in the inventory table to be updated automatically with this purchasers record ID. This is needed so that the item purchased is removed from a list of items still available.

I know I can create an update query, but I was hoping for a VB code which will update the record in the inventory table as soon as I finish selecting it as having been purchased.

Any help will be very much appreciated.

Regards, Martin

 

[Uncle Gizmo]

Not exactly the answer you were looking for, however I thought you might find this thread on Allen Brownes web site worth a read.

 

[R32chaos]

Not exactly the answer you were looking for, however I thought you might find this thread on Allen Brownes web site worth a read.

Thank you Uncle Gizmo, I will review it now...Regards, Martin

 

[jal]

Hello,
I am creating a donations inventory table. Then I am creating a purchasers table. In a purchases entry form, under the purchaser's record, when I select an item from the inventory as having been purchased, I need a field for that inventory item in the inventory table to be updated automatically with this purchasers record ID. This is needed so that the item purchased is removed from a list of items still available.

I know I can create an update query, but I was hoping for a VB code which will update the record in the inventory table as soon as I finish selecting it as having been purchased.

Any help will be very much appreciated.

Regards, Martin

Your idea of creating an udpate query might work and let's suppose you name it qryUpdate. You could then have a button on your form called btnMarkThisItemAsPurchqased. In the Click event for that button, you can write

CurrentDB.Execute "qryUpdate"

However, I'm assuming an unbound form. On a bound form you might not even need a query. (I don't use bound forms). It might be as simple as:

Purchased = True

if the column in your table is called "Purchased" and the bound form's current record is the one you wish to mark as purchased.

 

[R32chaos]

Your idea of creating an udpate query might work and let's suppose you name it qryUpdate. You could then have a button on your form called btnMarkThisItemAsPurchqased. In the Click event for that button, you can write

CurrentDB.Execute "qryUpdate"

However, I'm assuming an unbound form. On a bound form you might not even need a query. (I don't use bound forms). It might be as simple as:

Purchased = True

if the column in your table is called "Purchased" and the bound form's current record is the one you wish to mark as purchased.

Jal, Thank you...this is helpful. I appreciate your help.
Regards, Martin

 

[rapsr59]

Hi Martin!

If you are viewing a specific item on a form and you want to update the inventory of a that item when inventory is depleted, or inventory is added using VBA, try using...

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    Set dbs = CurrentDb
    strSQL = "SELECT [ProductID], [Inventory] " & _
    "FROM [Products] " & _
    "WHERE [ProductID] = " & Me.ProductID
    Set rst = dbs.OpenRecordset(strSQL)
 
    With rst
        'ctlqtyDepleted is a control on the form that has
        'the number of products depleted from inventory
        .Edit
        ![Inventory] = ![Inventory] - Me.ctlqtyDepleted
        .Update
    End With
 
    Set rst = Nothing
    Set dbs = Nothing

This will subtract the number of items removed from inventory.

If you want to add products received use...

    With rst
        'ctlqtyAdded is a control on the form that has
        'the number of products added to inventory
        !Edit
        ![Inventory] = ![Inventory] + Me.ctlqtyAdded 
        !Update
    End With

Hope this helps.


Richard

 

[R32chaos]

Hi Martin!

If you are viewing a specific item on a form and you want to update the inventory of a that item when inventory is depleted, or inventory is added using VBA, try using...

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
 
    Set dbs = CurrentDb
    strSQL = "SELECT [ProductID], [Inventory], [PurchaserID] " & _
    "FROM [Products] " & _
    "WHERE [ProductID] = " & Me.ProductID
    Set rst = dbs.OpenRecordset(strSQL)
 
    With rst
        'ctlqtyDepleted is a control on the form that has
        'the number of products depleted from inventory
        .Edit
        ![Inventory] = ![Inventory] - Me.ctlqtyDepleted
        ![PurchaserID] = Me.ctlPurchaserID
        .Update
    End With
 
    Set rst = Nothing
    Set dbs = Nothing

This will subtract the number of items removed from inventory.

If you want to add products received use...

    With rst
        'ctlqtyAdded is a control on the form that has
        'the number of products added to inventory
        !Edit
        ![Inventory] = ![Inventory] + Me.ctlqtyAdded 
        !Update
    End With

Hope this helps.


Richard

Richard Thank you!

I appreciate your detailed help. I think this will be very helpful to me in more ways than one.

Regards, Martin