Subform question on populating and storing 1

[jairiana]

I have a subform that needs to have 2 fields from the TblInventory Master list stored in the TblTransactionsDetails. One the Item ID is no problem as have it bound from a combo box, but the other is the Catprice.

This Catprice can change during the year due to price increases plus we want to be able to change price on an sale by sale basis.

Currently, we display the Catprice and then in field next to it enter the SalesPrice. This is a lot of data entry plus opens up for errors-nothing in it or extra amounts.

I want to populate the SalesPrice with the Catprice but be able to change it, if necessary, without effecting the Catprice; and also be able to change the CatPrice without changing SalesPrices that are already entered.

I reviewed and tried the way the sample Northwest Database has it in the Order subform UnitPrice and AfterUpdate property, but keep getting a Error message "3075 syntax error". I have tried everything I can think of but still get this message.

I have set this combo up using the simplest information but in reality, the combo box would be based on a query which allows us to see our availability. Assumed that getting to work in the simplest form is my first step.

Can anyone help me? There are 3 subforms that are setup basically the same way so if I can get it to work on one the rest should fall into line.

 

[RoyVidar]

Hi!

If I've understood you correctly, you want to have tha value in the CatPrice control populate the SalesPrice control?

If so (assuming of course that SalesPrice and CatPrice are numeric and they might have either a value or 0 - which of course also is a value):

[tt]if me!SalesPrice=0 then
me!SalesPrice=me!CatPrice
endif[/tt]

I'd say you might try it in the on current event of the form.

BTW - If you're having FIELDS in your underlying recordset called SalesPrice and CatPrice, and you also have CONTROLS on your form having the same name, you might be confusing access a bit. Access won't know if you're referring to the fields or the controls. If this is tha case, I'd strongly advice you to rename the controls on the form.

If any of this doesn't help, consider providing your code, what event you're using, and which line provides the error...

HTH Roy-Vidar

 

[jairiana]

Roy-Vidar

Both SalesPrice and CatPrice are numeric and they have either a value or null (not 0. WOuld that make a difference being null and not 0?

Thanks Jairiana

 

[RoyVidar]

Yes, a small (but significant) difference:

[tt]if isnull(me!SalesPrice) then
me!SalesPrice=me!CatPrice
endif[/tt]

Try this one (and as stated, you might put it in the forms on current event), we might be lucky

And - if it doesn't work, please have a look at the suggestions in my previous post regarding naming of fields and controls.

Roy-Vidar

 

[jairiana]

SOrry I got off on other tracks this past week but just was rereading my info and your answers and need to make certain we are both clear on what I am saying.

The field CatPrice is stored in the TblInventory Master List and on the subform is in a text box with Catprice as the control. It is not stored in the TblTransactions Details. It can be null or have an amount

The field SalesPrice is stored in the TblTransactions Details and is the control for the Sales Price textbox.

Under our current subforms, the Catprice populates the textbox and then we data enter the amount we want in the SalesPrice textbox. Usually it is the same amount but occasionally we enter a different amount and some things like are seconds are priced at time of sale.

The SalesPrice is used for extensions and other computations that are used on the form and subform.

In reading your original post you suggest that possibly Acess is confused with controls and fields of the same name.
ARe you refering to Control source on a texbox as thought they had to be named the same as the Field they get their information from.

I am going to provide the code that I have been trying to get to work in another post but at moment don't have access to the computer that is currently on.

I want to make sure I am understanding what you are trying to tell me.
Thanks for your help

 

[RoyVidar]

Hi!

I've been offline for a while myself.

I might have been a bit quick in the earlier explanations. I gather you haven't gotten the expected results?

You're right. Field reside in tables and are available in the forms either tru a table or query as the forms record source. All the thingies on the form (or a report), are referred to as controls (textboxes, combo boxes, lists, check box...).

When using the form wizard, all the controls on a form (or report) are supplied with the exact same name as the fields, which is OK as long as we don't do any manipulationg of/with them.

The two properties we're discussing are:

Control Source (on the 'data' tab):
This must reflect/equal the name of the field where the information in the textbox (or other type of control) stores/retrieves the data.
(Exeption; calculated controls, which might have an expression of some kind '=me!SomeControl * 100' or Unbound controls, to which one might assign a value thru code)

Name (on the 'other' tab):
Control name is what you use to refer to the value currently in the control (or change/retrieve other properties of the control).

It is recommended to change the name (not the control source) of any control you're going to manipulate (use in calculations).

Common naming conventions (you'll see them in threads around this site) is for instance to prefix any text control names with 'txt', combos with 'cbo' etc.

So using the previous example, here, after renaming the controls, it might look like this:

[tt]if isnull(me!txtSalesPrice) then
me!txtSalesPrice=me!txtCatPrice
endif[/tt]

Hope this is a bit more clear. BTW - in my replies, I've just assumed that both fields are present in the sub forms record source.

Roy-Vidar

 

[jairiana]

Hi

It worked!! Tried it on a simple form and it works. Now am in the process of trying it for real. Will keep you posted. Staff here will be so glad and so will their hands and fingers!

Thank you for your excellent help.

Jairiana

 

[jairiana]

Hi again

Well tried it in one of my regular Quote form and subform and it works but there is a time lag between time after you enter the Item ID. It populates the CatPrice immediately but doesn't populate the SoldPrice until you move the cursor up to the previous entry. I would expect it to populate as soon as you move off that record but seems only to populate when you go back to the record before it.

This is what I used that worked:

Private Sub Form_Current()
If IsNull(Me!txtSoldPrice) Then Me!txtSoldPrice = Me!txtCatPrice
End Sub

If I added endif I got an error.

Any suggestions? At least I know it is possible to do. Now as usual I want it to react better.

Thanks again

 

[RoyVidar]

Hi!

Thanx for the star!

First, the If-Then syntax, the most common way to use it, is as I've written it above (only a line of code when true):

[tt]if isnull(me!SalesPrice) then
me!SalesPrice=me!CatPrice
endif[/tt]

Or the "full" If-Then-Else statement

[tt]If <logical expression> Then
' some line(s) of code if true
Else
' some line(s) of code if false
End If[/tt]

But for single line If statements, you can use it as you've done (logical expression and 'one line of code') without end if, all on one line.

The next thing, yes, the On Current event &quot;fires&quot; when you move from from one record to another, therby populating the SoldPrice when leaving the record (I'd thought it should populate the control also when moving to next/new, hmmm....).

I gather then that the item ID is what's used to &quot;filter&quot;/get the correct CatPrice? If so, you might consider moving the code from the forms On Current event to for instance the item ID's after update event (that should provide a value as soon as you enter an item ID).

HTH Roy-Vidar

 

[jairiana]

Roy

I moved the Code to the Item ID's after update event and it works perfectly!

Thanks so much for your help.