New Db building stuff

[bknight]

Now that I have some entries I had to redesign the form, delete and create new.
ActionName displays both entries in Action table bound to column 2, but enters column 1 data.
ActionID displays both entries in Action table bound to column 1, and enters column 1 into the test box.

Control source for both is ActionName
Row source for both is Action.

How is it possible for the ActionName to populate the test box with text, not the number?

[HansV]

See the attached version. I removed the ActionName field from the table, since it is redundant.
The Control Source of the ActionName text box is now =[ActionID].[Column](1)
It will automatically display the description belonging to the ActionID selected in the combo box.

FutureTS.zip

[bknight]

I am unable to open the file unrecognized Db format.

Do you know Allen Browne(sp)?

[HansV]

Allen Browne was a Microsoft MVP for Access; I don't know him personally but I have often used his website Allen Browne's tips for Microsoft Access

S2112.png

S2113.png

S2114.png

[bknight]

That's exactly what I did in between posts. Ah that's how I ran onto him.
amusing story in email.

[bknight]

A couple of further questions
Is there a setting that forces focus to go to date on a new page in the form?
Secondly I need to calculate a value for the Amount field =[price]*[Quantity]*constant. The constant several values that may require another table where the constants are paired to the left 2-3 characters of the symbol, in both tables with a join between identical left(x) characters. Your thoughts?

[HansV]

> Is there a setting that forces focus to go to date on a new page in the form?

Do you mean when the form opens? Or when you move to a new record? Or when the user clicks a command button?

> Your thoughts?

Hard to say without knowing the details, but a lookup table is probably the way to go.

[bknight]

First two for sure and maybe the last.

[HansV]

You could create an On Current event procedure for the form.

Code: Select all

 Private Sub Form_Current()
    Me.[Trade date].SetFocus
End Sub

[bknight]

Me.[Trade Date}.SetFocus--or does Trade Date have to be surrounded by "?
Another small issue I have [Price] set to currency 6 decimals both in the form and table, but it keeps reducing to 4 decimals. Is that fixable to 6?

[HansV]

Control names with spaces or punctuation have to be enclosed in square brackets [ ].

The Currency data type has 4 decimal places. From Currency data type:

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right.

If you need more decimal places (which seems weird for currency), use Number > Double instead of Currency.

[bknight]

Control names with spaces or punctuation have to be enclosed in square brackets [ ].

The Currency data type has 4 decimal places. From Currency data type:

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right.

If you need more decimal places (which seems weird for currency), use Number > Double instead of Currency.

Pesky fractions

[bknight]

You could create an On Current event procedure for the form.

Code: Select all

 Private Sub Form_Current()
    Me.[Trade date].SetFocus
End Sub

Refresh my memory of how to get to the form current properties(?) and then
Me.[Trade Date}.SetFocus--or does Trade Date have to be surrounded by "?

Where I put that code give a compile error.

[HansV]

Open the form in design view.
Do not select anything on the form.
Activate the Event tab of the Property Sheet.
Click in the On Current event.
Select [Event Procedure] from the drop-down list in this event.
Click the builder dots (the button with ... to the right of the drop-down arrow)
Make the code look as in my previous reply.
(If your text box has a different name than Trade date, you must use "your" name)

[bknight]

It used to be view code IIRC, but done.
In the text box default value I have
=[Trades]![Quantity]*[Trades]![Price]*1000+[Trades]![Commission]+[Trades]![Fees]
I have'nt doen anything but will this work?

[HansV]

Since Trades is the Record Source of the form, you don't need [Trades]!

However, the Default property will be applied when you create a new record; I assume that Quantity etc. will be empty, so the result of the calculation would be empty too.

[bknight]

Will that change as data is entered? If not then I may need an event after the last of that group is entered.

[HansV]

The value set by the Default property will not be changed when data are entered.

[bknight]

Here is a partial image of the table without the "redundant" data. Perhaps you may see why I wanted it to populate the field.

Is there a meth to populate that field with what I have, or can add?

[HansV]

Remove the ActionName field from the table.
Set the Control Source of the ActionName text box on the form to

=[ActionID].[Column](1)