Set Value from one field on main form to field on subform

[burrina]

I need to choose a value, which will be a number from one field (TaxRate) and have it update a field on my subform which also is a number (TaxRate)



TaxRate set subform TaxRate to value chosen

main form is named AddProducts and subform is named AddProductsSubform

[Rod]

Do you want to update a specific row in the subform or all rows in the subform?

Are the fields in a subform row locked or disabled?

[burrina]

Do you want to update a specific row in the subform or all rows in the subform?

Are the fields in a subform row locked or disabled?

I want to update just the one field. I am having design issues not knowing which is better. Use tax rate by category, by item or simply by a rate on main form.

thanks,

Dave

[Rod]

Ah, not exactly the answer to my question. I know you want to update one field, but do you want to do the single-field update for every row in the subform?

[burrina]

when a product is entered then it should update that record, so I guess the answer is ALL records.


Thanks,

[Rod]

Hi,

Sorry to be such a pedant but it's all a matter of the best way to fulfil your requirement. I now see two situations:

1. When entering a new product on the subform, the tax rate should be automatically copied from the main form. (What do you want to happen if the tax rate is null? I assume a zero rate is permissible.)
2. When changing the tax rate on the main form, each and every product on the subform should have its tax rate adjusted to the new value.

For number 1 above, the easiest way is to include a small VBA routine that runs at the Before Update event for the subform. This has the advantage that the tax rate is checked for existing lines (when changed) as well as for new lines. The disadvantage is that you don't see the tax rate for new product records until that record is saved.

For 2, there should be a routine that runs upon the Before/After Update event for the main form's tax rate. This routine calls another routine in the subform that updates every line.

If you want to see immediately the change to the subform's tax rate than some other technique is needed involving some form of user action - a command button, double-clicking, etc.

OK, the VBA routine to place into the subform's Before Update event is:

Me.TaxRate = Nz(Me.Parent.TaxRate, 0)

Once this is working for you, post back and I'll give you the instructions for number 2 above.

[burrina]

That works great, Thanks. Yes, I have one Category where everything is NonTaxable and the TaxRate is set to 0. ALL other categories are Taxable and have a TaxRate.

[Rod]

OK, now for that mass update. I don't know all your object names so change my names to yours as necessary.

First include a public sub procedure in your subform.

Public Sub UpdateTaxRate(curTaxRate as Currency)
    'Use a clone to avoid disturbing current pointer.
    Dim rstClone As DAO.Recordset
    Set rstClone = Me.RecordsetClone
    With rstClone
        .MoveFirst
        Do Until .EOF
            .Edit
                !TaxRate = curTaxRate
            .Update
            .MoveNext
        Loop
    End With
    Set rstClone = Nothing
End Sub

I don't know the data type of TaxRate; I have assumed it is Currency but if it's something else then change the procedures argument definition accordingly (highlighted in red above). Also the recordset update should be enclosed in a BeginTrans - CommitTrans pair but that's a little beyond this example.

Now in the main form for the TaxRate control's Before Update event, code:

Me.sfrProductList.Form.UpdateTaxRate Me.TaxRate

Change the blue text to whatever you have named the subform control on the main form.

All should now work. Let us know how you get on.

[burrina]

Both controls on forms are named TaxRate.. I am lost when you say create a public sub procedure. what does that mean?

I added the Public code and saved in a module and called it form my form named AddProductsSubform, is that what you meant?

I also added this code to my main form named AddProducts but it did not work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.AddProductsSubform.Form.UpdateTaxRate Me.TaxRate
End Sub

[Rod]

Yes, I realise both the rate controls are named TaxRate but the two things I don't know are:

1. The name of the control on the main form that contains the subform. If you used an Access wizard to place the subform then the control is probably named the same as the name of the subform.
2. The data type of TaxRate.

Anyway we can proceed without these for now; it means you may have to substitute your own names in my code samples.

I'll take this step by step - please forgive me if you already know all this.

First though, a note about procedures. All VBA (Visual Basic for Applications) code is written in modules. There are two types of module: standard and class. However the way of writing code is practically the same for both.

VBA modules consist of three sections. At the top there appear the Option statements. These are followed by the module declaration section where module level variables and such like are specified. Following the declaration section are any number of procedures.

There are two types of procedure: sub procedures and function procedures. The difference is that function procedures return a value whereas sub procedures simply execute code. Each procedure may be declared as public or private. Private procedures are only visible to the code in the module in which they are declared. You will notice that Access/VBA declares the event handling procedures as private sub procedures.

Enough of the theory and before I get a raft of replies from the experienced gurus, yes I know I have simplified this somewhat.

I notice you are using v2002. It's a long time since I used that version so I hope my instructions apply.

First open your subform in design mode and go to the 'Other' tab in the Property Sheet. Making sure it is the form selected, there is a property called, 'Has Module.' Make sure this is set to 'Yes.'

To get to the VBA coding window, type Alt+F11 from the Access window. The VBA window consists of a number of panes. In the upper left-hand pane you will see a hierarchy of modules. If not expanded, click on the '+' sign underneath Micosoft Office Access Class Objects. Listed will be all your Access design objects that have modules prefixed with Form_, etc. Double click on the entry correponding to your subform and any code will be displayed in the main pane.



The Access Class Objects List

Now type my code in the main pane. It should end up looking like this.



If it does not already exist then it is good practice to have an 'Option Explicit' statement immediately following the 'Option Compare Database' statement.

Procedures may be entered anywhere in the procedures section but not between Sub/End Sub and Function/End Function pairs.

To check your typing click 'Debug' on the menu bar and then 'Compile ...' Any syntax errors and typos will be immediately identified.

OK, that's how you enter a procedure.

PS Rather than type my procedure you could copy and paste, then change names as appropriate.

[burrina]

Now that I have changed the table structure as suggested my code for the subform name TimeAndCatProdSub located on the form TimeCards does not work.

Choose your quantity,category but then when you try and choose a Products from the dropdown list you get the error shown on the pic I uploaded.

The lookup code is screwed up now. Not sure if I can fix it by ,myself.

I have to downsize the db to upload again. Size limitation prevents me from uploading in complete state.

[burrina]

Ok, here is modified version

[Rod]

Now that I have changed the table structure as suggested ...

Did I tell you to change the table structure? If you have changed something then I suggest you reverse the change immediately. The run time error you are getting is generated by the SQL engine and is complaining that it can't find the named table and/or one or more of the named fields.

Meanwhile I'll have a look at your sample db but I see that there are only forms, no tables whatsoever.

[burrina]

I thought I sent the necessary tables and forms. Here is the attached db in 2 parts. These are the tables.

[burrina]

attached is the queries for the database.