rs need help

[shobhanauto]

hi
i need another help
I uploaded the database file.

In the forms, the last Sell% shows 3.53%. Then the result should update in the ItemName tables SalesPercents field.

I added the VBA code, but it's not working.
Can anyone help?
Could someone please help me?

 

[theDBguy]

Unfortuntely, calculated columns are not editable. If you must be able to edit the values, then you have to use a regular field.

 

[MajP]

You need to have a field "ManualPrice"
if the manual price <> 0 then display manual price else display [mrp] * [discount]

 

[shobhanauto]

You need to have a field "ManualPrice"
if the manual price <> 0 then display manual price else display [mrp] * [discount]

hmm good idea can you me ff code? <> 0 please

 

[MajP]

RS
=IIf([ManualPrice]>0,[ManualPrice],[MRP]*[DISCOUNT])

 

[shobhanauto]

RS
=IIf([ManualPrice]>0,[ManualPrice],[MRP]*[DISCOUNT])

Thank You Working Good

 

[shobhanauto]

i need another help
I uploaded the database file.

In the forms, the last Sell% shows 3.53%. Then the result should update in the ItemName tables SalesPercents field.

I added the VBA code, but it's not working.
Can anyone help?

 

[Pat Hartman]

I added the VBA code, but it's not working.

What code, Where? We are not mystics.

 

[shobhanauto]

What code, Where? We are not mystics.

Dear Sir i Uploaded database file first page

and here vb code i set SalesPercents

Private Sub SalesPercents_Click()
    On Error GoTo ErrorHandler
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM ItemName", dbOpenDynaset)
    
    Do While Not rs.EOF
        rs.Edit
        rs("SalesPercents") = 1 - (rs("BDDUTY") / rs("RePrice"))
        rs.Update
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub

 

[shobhanauto]

anyone here help please?

 

[arnelgp]

anyone here help please?

what i can see, is a well defined table and form you have.

 

[CJ_London]

why is your code triggered in the detail section of your form? Is the intention you want to update all records or just the one you are on? Why not just use an update query rather than looping through a recordset?

Salespercents is a text field (shouldn't it be numeric?) in your table but a calculated control on your form - so you would need to look in the table to see if your code has worked

BDDuty is a field in your table, but a calculated value on your form and you code is referencing the table version of its value not the form version

And since these are calculated values - why do you want to store them?

Many of the values you use in your calculation are null (or perhaps zls) so will result in a null value.

If you step through your code you can check the values used in your calculation.

 

[shobhanauto]

why is your code triggered in the detail section of your form? Is the intention you want to update all records or just the one you are on? Why not just use an update query rather than looping through a recordset?

Salespercents is a text field (shouldn't it be numeric?) in your table but a calculated control on your form - so you would need to look in the table to see if your code has worked

BDDuty is a field in your table, but a calculated value on your form and you code is referencing the table version of its value not the form version

And since these are calculated values - why do you want to store them?

Many of the values you use in your calculation are null (or perhaps zls) so will result in a null value.

If you step through your code you can check the values used in your calculation.

And since these are calculated values - why do you want to store them?

The reason for storing this is because I have three sales forms for three branches. When I update Salespercents, it will be displayed next to ItemName on these forms. For users generating invoices, the Salespercents I set will prevent selling below that specified percentage.

In my Analysis Form, this setup helps in analyzing items based on the updated Salespercents.

 

[Pat Hartman]

Doesn't the percent change over time? If it does, you need to store the value that was appropriate at the time the record was created and NOT change it after the fact.

 

[shobhanauto]

Doesn't the percent change over time? If it does, you need to store the value that was appropriate at the time the record was created and NOT change it after the fact.

How are you, Pat Hartman?

I'm actually new to Access. If you have a little time for me, could you please update my file if possible?

Thank you

 

[mike60smart]

hi
i need another help
I uploaded the database file.

In the forms, the last Sell% shows 3.53%. Then the result should update in the ItemName tables SalesPercents field.

I added the VBA code, but it's not working.
Can anyone help?
Could someone please help me?

Hi

I would suggest you need to look at Normalisation.

Your 1 table in the attached database contains 81 fields.

Your PK is a Text DataType and I would recommend that it should be an Autonumber PK.

You also have Multiple Lookup fields. See Google for "The Evils of Lookup Fields in Access Tables"

 

[Pat Hartman]

I'm actually new to Access. If you have a little time for me, could you please update my file if possible?

Sorry. This isn't a place you come to to get professionals to do your work for you for free. We provide advice and frequently samples. Sometimes we go further. But there is only one person here who would take the time to update your code to do something that no expert thinks is correct.

 

[shobhanauto]

Sorry. This isn't a place you come to to get professionals to do your work for you for free. We provide advice and frequently samples. Sometimes we go further. But there is only one person here who would take the time to update your code to do something that no expert thinks is correct.

Thank you sir, for providing advice and samples.