New Db building stuff

[HansV]

1) No, this is not correct. You cannot embed the complete code of a function inside a sub.
I have already posted the code you should use in this earlier reply in this thread.

2) If you specify a fixed value such as 36 or eileen as an argument to a procedure (sub) or function, the rules are:
- Number values can be entered as they are: Sqr(36)
- Text values (strings) must be enclosed in double quotes: UCase("eileen")
- (VBA and Access only) Dates must be enclosed in # characters: Year(#01/14/2023#)

This does not apply when you specify a variable, cell or field as argument:

Code: Select all

Sub Test()
    Const MyNumber = 36
    Const MyString = "eileen"
    Const MyDate = #01/14/2023#

    Debug.Print Sqr(MyNumber)
    Debug.Print UCase(MyString)
    Debug.Print Year(MyDate)
End Sub

[bknight]

I'm really confused and you know what you are saying, but what you are saying has me confused.
Back to the simple hopefully.

Code: Select all

Private Sub Price_AfterUpdate()
    Me.Price = Convert2Num(Me.Price)
End Sub

Is all I need with the exception of the data entry part, which you are indicating the string MUST be entered with parentheses/quotation.
Do I understand you now?

[HansV]

No.

[HansV]

Please carefully read my replies in this thread and your other recent thread. Everything you need is there.

[bknight]

The code that I posted is an event procedure. It will be executed automatically after the Price text box has been updated.
See this reply in one of your other threads.

I have reread all the posts. I'm guessing because I'm still confused--but

Code: Select all

Option Compare Database
Function Convert2Num(Price As Variant)
    Dim arr() As String
    If IsNull(Price) Then
        Convert2Num = Null
    Else
        arr = Split(Price, "'")
        If UBound(arr) = 0 Then
            Convert2Num = CDbl(arr(0))
        Else
            Convert2Num = arr(0) + arr(1) / 32
        End If
    End If
    Me.[Price] = Convert2Num()
End Function

It seems like if this code is entered as above every entry will checked?

The above is Module 3.
ETA:
If I have

Code: Select all

Private Sub Price_AfterUpdate()
    Convert2Num
End Sub

Then it seems only the price gets checked. Correct?

[HansV]

Would you PLEASE read my previous replies slowly and carefully, and then do exactly what I suggested instead of changing it? Thanks in advance!

[bknight]

I have and I'm still confused.

[bknight]

I have created a general module3 as you requested that contains the Convert2Num function.
I have entered a call in the after update of price.
I have entered the string as "114'27.5"
I still get the error you entered text in a numeric field.
Would you like an image of all the codes?

[HansV]

If you wish you can send me the database. I don't think I can help you via this thread.

[bknight]

You have mail.
All I ask is a simple description of what you changed and I will adjust in the original.

[HansV]

OK, I see now. Since Price is a number field, Access does not accept any text values, so it doesn't even get to the Price_AfterUpdate event.
To solve this is going to be far too complicated, we'd never get through it.
So you'll have to enter Price as a valid number such as 16.25 instead of as a string 16'8

[bknight]

I thought we discussed what the field was, but I'm not going through the post.
Ok now to issue number 2 this morning the code for commission, currency.

Code: Select all

Private Sub Commission_BeforeUpdate(Cancel As Integer)
    Dim ComCalc As Double
    If Me.Symbol = "MES" Then
        If Me.ActionID = 46 Or Me.ActionID = 48 Then
            Me.Commission = -Me.Quantity * 0.5
            Me.Commission = Me.Quantity * 0.5
        End If
    ElseIf Me.Symbol = "TY" Then
        If Me.ActionID = 46 Or Me.ActionID = 48 Then
            ComCalc = Me.Quantity * 1.5 * -1
            Me.Commission = ComCalc
        ElseIf Me.ActionID = 47 Or Me.ActionID = 49 Then
            Me.Commission = Me.Quantity * 1.5
        End If
    ElseIf Me.Symbol = "US" Then
        If Me.ActionID = 46 Or Me.ActionID = 48 Then
            Me.Commission = -Me.Quantity * 1.5
            Me.Commission = Me.Quantity * 1.5
        End If
    End If
End Sub

This code is an update to the one imaged earlier only the addition of ComCalc was added to the part where if is true gives the following error. When exiting the form, I get Property not found. Tomorrow if it too late.

[HansV]

You shouldn't try to set the value of Commission in the Before Update event of Commission.
Commission should be a calculated field. Or do you want to be able to edit it manually after it has been calculated from Symbol, ActionID and Quantity?

[bknight]

I figured it out and I am in the task of recoding, I put the same code into and afterUpdate event and it works perfectly.
In answer to your question the goal was have correct commission (different for each symbol) and have that number be negative, that way regardless of the number input it would be updated correctly. The same goes for Fees, but that is almost a cute paste from a Before to an After.

[bknight]

Is there a way to avoid selecting/focusing on key index ID. When entering data in a record, as soon as the last data is entered and the enter key is pressed focus jumps to a new record or the next record but focus is on ID. The tab index is set to 0 and I have attempted to delete the tab number and I get an error.
I would like focus on the next field, TradeDate,

[HansV]

If it is an AutoNumber field, the user doesn't need to enter it, so you can remove the control, or set its Visible property to No.
If you prefer to keep it visible, you can set its Enabled property to No and its Locked property to Yes.

[bknight]

The way my form is set up ID is the first field in a datasheet view, so the first method didn't work and I would have to redesign it to allow the first method to work, The second method worked. Thanks

[bknight]

If it is an AutoNumber field, the user doesn't need to enter it, so you can remove the control, or set its Visible property to No.
If you prefer to keep it visible, you can set its Enabled property to No and its Locked property to Yes.

I've been bust "accounting for the last few days and haven't revisited this issue. The ID still appears, even though it is based on a query that does not have ID as a field. The visible property of D is set to no, yet the form opens with it. Aside from deleting it from the form design is there any way that we haven't discussed to eliminating it form the datasheet view of the query?

[HansV]

Open the form in Datasheet view.
Right-click the ID header.
Select Hide Fields from the context menu.

[bknight]

Dumb question where is the context menu? All I get when right clicking the header is sort choices, eit colum width etc., and conditional formating.