New Db building stuff

[HansV]

Context menu is right-click menu.
Move the mouse pointer to the column header of the ID column. the mouse pointer should change to a downwards arrow, indicating that you'll select the entire column.

S2192.png

Then right-click.

S2193.png

[bknight]

To get around the error encountered when in putting a string into a number field, I built a new table and created a form with code. This is my scheme when the field ActionName has had an entry, there is an event code:

Code: Select all

Private Sub ActionID_AfterUpdate()
        If Me.ActionID = 46 Then
            Me.ActionName = "Buy to Open"
        ElseIf Me.ActionID = 47 Then
            Me.ActionName = "Buy to Close"
        ElseIf Me.ActionID = 48 Then
            Me.ActionName = "Sell to Open"Private Sub RawPrice_AfterUpdate()
    Me.Price = Convert2Num(Me.Price)
End Sub
        ElseIf Me.ActionID = 49 Then
            Me.ActionName = "Sell to Close"
        ElseIf Me.ActionID = 99 Then
            Me.ActionID = "Other"
        Else
            MsgBox "You must enter a valid number", vbInformation
        Cancel = True
        End If
        Me.Price.SetFocus
        DoCmd.OpenForm "frmFracCalculator", acFormDS
            'DoCmd.OpenForm "frmTrades", acFormDS
            'Private Sub Price_AfterUpdate()
            Me.Price = Convert2Num(Me.Price)
End Sub

Now I enter string that will be converted to a number, for input to the Price field into the text box.

Code: Select all

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

This leads me to:

Code: Select all

Function Convert2Num(Price As Variant)
    Dim arr() As String, arrL() As String, arrR() As String   
    'If IsNull(Price) Then
       ' Convert2Num = Null
    'Else
        arrL(0) = Left(RawPrice, 3)
        arrR(0) = Right(RawPrice, Len(RawPrice) - 4)
        'If UBound(arr) = 0 Then
            Convert2Num = CDbl(arrL(0))
        'Else
            Convert2Num = arrL(0) + arrR(0) / 32
        'End If
        arr(0) = arrL(0) + arrR(0) / 32
    'End If
End Function

I am using Access 20007 and split is not one of the functions that it recognizes. So I inserted a left and right function: Anyone, including myself figures that this will not work, So what would be the code steps to get Convert2Num () to work and have a finished number to input into the main price field, now if the new table field needs to be renamed that is ok, I plan for the table to one have one record, over and over again. I hope I have described this well enough. This would be an execution across two tables.

[HansV]

Split should work in Access 2007. What is the error message that you get if you try to use it?

[bknight]

Maybe it will, but the above code pieces go to the suc convert2Num prior to entering anything into the raw price and therefore is a null. I need it to go to the sub after entering a string into RawPrice, and after the number is calculated, I'll need to delete record 1 prior to sending the number back to to the main file.

[bknight]

Maybe it will, but the above code pieces go to the suc convert2Num prior to entering anything into the raw price and therefore is a null. I need it to go to the sub after entering a string into RawPrice, and after the number is calculated, I'll need to delete record 1 prior to sending the number back to to the main file.

ETA:I see the problem its my ciding
If Me.Symbol = "TY" Or Me.Symbol = "US" Then
Me.Price = Convert2Num(Me.Price)
End If

I need this to point back to Me.RawPrice instead of going to the convert sub, I'll try that. No need to reply.

[bknight]

This doesn't work

Code: Select all

          If Me.Symbol = "TY" Or Me.Symbol = "US" Then
            frmPriceCalculation.RawPrice = Convert2Num(Me.Price)
        End If

Control of the code still goes to the convert sub without using the PriceCalulation table.

ETA:

Code: Select all

          If Me.Symbol = "TY" Or Me.Symbol = "US" Then
            'frmPriceCalculation.RawPrice = Convert2Num(Me.Price)
            With frmFracCalculator.RawPrice
            .SetFocus
            End With
            

This ends with "Object required" error. I need a method of__> RawPrice to have focus to input that string 2 number.

[HansV]

I'm afraid I can't help you with this, sorry.

[bknight]

OK, then lets try this
Me.Price = some function to convert 114'14.5 to, of course 114+14.5/32. But not a whole sub. There is the string 114'14.5 in Me.RawPrice.

[HansV]

Does this work for you?

Code: Select all

Function Convert2Num(Price As Variant)
    Dim p As Long
    If IsNull(Price) Then
        Convert2Num = Null
    Else
        p = InStr(Price, "'")
        Convert2Num = Left(Price, p - 1) + Mid(Price, p + 1) / 32
    End If
End Function

Use like this:

Code: Select all

Me.Price = Convert2Num(Me.RawPrice)

or in the Control Source of Price:

Code: Select all

=Convert2Num([RawPrice])

[bknight]

Remember Me.price in the old table/form is a number and that is where the error came when trying to input a string. I'm assuming and you know what that means, Me.Price is in the original table, which is a double field and that is where the conversion failed. If you mean in the new table, I'll give it a whirl

[HansV]

Just try it. If it doesn't work, I give up.

[bknight]

Winner, winner chicken dinner. Into the second table, no go still in the first
Now what would be the command to delete a possibly dirty record or a the first/last record?

[HansV]

What is a "dirty" record?

[bknight]

One that still has the little pen/pencil in the left hand margin.

[HansV]

The first record:

Code: Select all

RunCommand acCmdRecordsGoToFirst
RunCommand acCmdDeleteRecord

The last record:

Code: Select all

RunCommand acCmdRecordsGoToLast
RunCommand acCmdDeleteRecord

A dirty record:

Code: Select all

If Me.Dirty Then RunCommand acCmdDeleteRecord

[bknight]

If Me.Dity gives an error: "The command or action 'deleterord' isn't available now. Since it has the dirty pencil/pen, I would have to say a record can't be deleted until/unless it is in the recorset. So, I'll put aside the delete record.
Thanks