Entering data in a text box.

[bknight]

I am unable to remember the class I took in Access coding, sorry for the memory lapse.
When entering data into a textbox/field there are a number of steps that Access goes through to finally have the data reside in the field.
Once data is entered into the previous field the next field gains focus, IIRC.
Then next data is entered from the keyboard anything in between gaining focus and pressing the enter key is contained in the before update?

When do the validation rules start? As soon as the enter key is pressed?

When are data available to be manipulated? Could any manipulation be accomplished after the enter key is pressed, before moving to the next field? Or before the afterUpdate?

That is all I can visualize without actually entering any data.

[HansV]

While the user is entering data in a text box, you can monitor that using the On Change event of the text box.

The text as being entered is contained in the Text property of the text box. This property is only available while the text box has the focus. If another control has the focus, trying to use the Text property will cause an error.

When the user starts to move to another control, for example by clicking in another control, or by pressing the Tab key, the Value property of the text box is updated, but it can still be canceled.
The Before Update event of the text box occurs; this has a Cancel argument that you can use to cancel the update and keep the focus in the text box.
Example:

Code: Select all

Private Sub TextBox1_BeforeUpdate(Cancel As Integer)
    If Len(Me.TextBox1) < 10 Then
        MsgBox "You must enter at least 10 characters", vbInformation
        Cancel = True
    End If
End Sub

After that, the After Update event occurs. You cannot use this to cancel the update, but you can change the value, for example to change the value to Proper Case:

Code: Select all

Private Sub TextBox1_AfterUpdate()
    Me.TextBox1 = StrConv(Me.TextBox1, vbProperCase)
End Sub

Remark: in these examples, Me.TextBox1 is equivalent to Me.TextBox1.Value, since Value is the so-called default property of a text box.

[bknight]

Code: Select all

Private Sub TextBox1_BeforeUpdate(Cancel As Integer)
    If Len(Me.TextBox1) < 10 Then
        MsgBox "You must enter at least 10 characters", vbInformation
        Cancel = True
    End If
End Sub

Seems like the one I would be interested in working with, as per the finding the apostrophe thread in Excell thread.

[bknight]

One of the reasons I'm using a Db for this account is to prevent entry errors and make sure the records are useable. Suppose I'm entering data with the created form, I normally start with the date entry and proceed though the records as they are laid out although someone else might do it differently.

I have entered a 2 in the quantity field and moved through each field until I reached the amount field.
The new record is still dirty would a function like Dsum("table","quantity',"symbol") have the dirty quantity or is the new record values ignored by Dsum() until it is not dirty?

In a I assume that that a fields value is usable/readable in a form of me.Field(ID), is that correct?

[HansV]

Functions such as DSum look at the stored values, so they will not take changes that have not yet been saved into account.

The value of the ID control in the current record is simply Me.ID or Me!ID

[bknight]

Functions such as DSum look at the stored values, so they will not take changes that have not yet been saved into account.

The value of the ID control in the current record is simply Me.ID or Me!ID

I guess I mistype I'm not looking for a value of ID, but a vlue of a previous record say me.amount(ID), in words the value of me.amount at a previous record (ID-x)

[HansV]

That would be

DMax("ID", "Trades", "ID<" & [ID])

[bknight]

That would be

DMax("ID", "Trades", "ID<" & [ID])

Before you posted this I was attempting to ascertain from the Db the number of contracts open currently, at this posting that number is 0.
Inside a form_After_Entry Event

Code: Select all

Dim I as Long, cntCon as Long
for I = 2 to Me.ID-1
	if Me.Symbol =Me.Sybmbol(I) then
		cntCon = cntCon + Me.Quantity(I)
	End IF
Next I

Failed with invalid property, I believe, it is erased now so I can't show you.
This was my first attempt to obtain values from previous records.
The Dmax would give me the number of the previous records ID. But I attempting to find the sum of all previous records quantity field with matching symbol to the current record.
Let's assume that the top record in the image is the first record and the bottom record is the record being added, Now what I want is the sum of all the Quantity, with Symbol TY prior to the new record. =-1+1+1-1+1-1+1-1+1-1 or 0, I know in my mind it is zero. Now how may I use some appropriate functions inside an event to derive it is zero?

[HansV]

DSum("Quantity", "Trades", "ID<" & [ID] & " AND Symbol='" & [Symbol] & "'")

[bknight]

DSum("Quantity", Trades", "ID<' & [ID] & " AND Symbol='" & [Symbol] & "'")

Gave two errors 1) expected list seperator or ) at Trades", I added " prior to Trades, after that 2) expected expression at Symbol =' added another " in front of Symbol='. Compiles but,
pr = DSum("Quantity", "Trades", "ID<' & [ID] & " And "Symbol='" & [Symbol] & "'") when executed gives type mismatch error

[HansV]

Sorry, that was careless of me

DSum("Quantity", "Trades", "ID<" & [ID] & " AND Symbol='" & [Symbol] & "'")

[bknight]

Works great, now EXTENDING for more cases
DSum("Quantity", "Trades", "ID<" & [ID] & " AND Symbol='" & [Symbol] & "'" & " AND ContractMonth= '" & [ContractMonth] & "'")

[HansV]

Is ContractMonth a text field?

[bknight]

Yes not a date/time.

[bknight]

The next step in my solution of profit calculation is when that DSum is not equal to 0, i. e. there is(are) contract(s) open and the current record close some/all of those open contracts. To do this requires the Db to locate all of the open contracts and determine the amount of each contract to be added to the current records amount.
If no built-in function exists, I am able to write a solution using record sets, but I need to logically fit all the possibilities of that calculation.
It could be trivial one to one or could be one to many.

[bknight]

What would be the code to update the record so that it is not dirty to be used after notes have been entered into the record.
Me.Update?

[HansV]

What would be the code to update the record so that it is not dirty to be used after notes have been entered into the record.
Me.Update?

Weirdly enough:

Me.Dirty = False

This saves the record and sets its Dirty property to False

[HansV]

How can we determine which records are "open" or "closed"?

[bknight]

In the case I was thinking it would be the record being added.

Code: Select all

Me.Dirty = False
CalcProfit (Profit)
End Sub

Update the Db and got to code that will calculate profit, when I finish coding.

[bknight]

What is the code to allow variables to carry values between subs?