New Db building stuff

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by HansV »

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

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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.
You do not have the required permissions to view the files attached to this post.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by bknight »

bknight wrote:
30 Jan 2023, 18:09
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
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by HansV »

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

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by 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])
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by HansV »

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

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by HansV »

What is a "dirty" record?
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by bknight »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Db building stuff

Post by 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
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: New Db building stuff

Post by 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