How do I update data related to a read-only query on a form?

[JMac]

Hi all, I'm so tired of looking... please help me!!

I have an Access Data Access Page based on a query which is not updatable.

The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back.

(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)

I want the user to be able to update the Amount field, so e.g. if it is currently set to 50 to be able to change it to 40, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.

I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the form/DAP so that Amount shows the newly updated/inserted value.

I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
(This is actually for a charity helping people suffering from emergencies in developing countries, not OfficeProducts, so your spot in heaven will be reserved if you can help Thanks)

Jen

 

[Laurentech]

I would do it something like this:

Private Sub NewAmount_AfterUpdate()

Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM OfficeProducts WHERE OfficeProduct = '" & Me.OfficeProduct & "'", Application.CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Not rst.EOF Then 'Product already exists in table

     rst!Amount = Me.NewAmount 'Update the amount in the table

Else 'Product doesn't exist in table yet

    rst.addnew 'Add a new product to the table

    rst!OfficeID = Me.OfficeID
    rst!ProductID = Me.ProductID
    rst!Amount = Me.Amount

End If

rst.update 'Save the new data to the table

If Not rst Is Nothing Then rst.Close 'Close Out The Recordset
Set rst = Nothing

End Sub

Well I hope that works for you. I didn't test this code, so I hope I got it right! Let me know how it goes.

Larry

 

[JMac]

Thanks Larry, that looks like exactly what I need to do.

Do you know how to write the same thing in VBScript? I've created this form in Data Access Pages so it doesn't like the syntax.

Thanks again,
Jen

 

[JMac]

does anyone know how to write this in VBScript?

Does anyone know how to convert the above code to VBSCript so I can use it in Data Access Pages?

Jenny

 

[Laurentech]

Sorry Jen, I don't know anything about VBScript. Hopefully another user can help you with that.

Larry