Problem locking a transaction record

  • Thread starter Thread starter vandy
  • Start date Start date
V

vandy

I am attempting to lock a transaction record after i key in the first value
in this case the length of a wire recieved into the inventory. The following
are the details of my database.
I have a table Rawmaterial_Items and Rawmaterial_Tran

Table-Rawmaterial_Items

ItemID
ItemNo
Supplier
UOM
Location
Stock No

Table-Rawmaterial_Tran

TransactionID
TranItemID
Qty
DOT (Date of Transaction)
Type ( 1 – Received or -1 Issued)
IssuedTo
Length

Relationship – one to many ItemID ~ TranItemID
Referential Integrity constraint

I have a main form called Rawmaterial Transaction and I am calling a subform
in a datasheet view.
The subform has QOH calculated QOH= sum (qty * type)
Length = sum[length]

In the main forms footer I am calling the QOH value from the subform and
displaying it.
I have a combo box in the main form which selects the item no, stock no and
displays the corresponding details on the main form.

Subform_Final
TransactionID TranItemID Qty DOT IssuedTo Length Type

588 382 11 10-Apr-08 L-floor 0.61 Received
682 382 2 14-Apr-08 yz 0 Issued
707 382 2 01-May-08 xyz 0 Issued

QOH shows 7
Length of wire shows 4.27 km calculated by deducting 4* 0.61 = 2.44
Total wire length = 11* 0.61 = 6.6
Remining wire = 4.2 km

Everything gets calculated ok but is there any way i can lock the user from
entering further values in subsequent cells eg. in TransactionItemID 682 and
707 after the user enters the total length of the wire the first time.

any help will be greatly appreciated.
 
Are TransactionID and TranItemID fields in which the user inputs a value? I
would expect they are not, particularly TranItemID, which seems to be a
linked field. In any case, if you need to display those fields you can lock
or disable the text boxes in which they appear. You can set such properties
in the After Update event for a text box:
If Not IsNull(Me.txtSomeTextBox) Then
Me.txtSomeTextBox.Enable = False
End If

You would need similar code in the form's Current event.
 
Hello Bruce,

Thanks Bruce for your reply. I am attempting to change the Text field Length
wich is not a linked field. I will be using the Afterupdate event to enable
or disable the field. I need some help with the forms current event. Is there
an event like current how do i encorporate this code.
Thanks in advance.

BruceM said:
Are TransactionID and TranItemID fields in which the user inputs a value? I
would expect they are not, particularly TranItemID, which seems to be a
linked field. In any case, if you need to display those fields you can lock
or disable the text boxes in which they appear. You can set such properties
in the After Update event for a text box:
If Not IsNull(Me.txtSomeTextBox) Then
Me.txtSomeTextBox.Enable = False
End If

You would need similar code in the form's Current event.

vandy said:
I am attempting to lock a transaction record after i key in the first value
in this case the length of a wire recieved into the inventory. The
following
are the details of my database.
I have a table Rawmaterial_Items and Rawmaterial_Tran

Table-Rawmaterial_Items

ItemID
ItemNo
Supplier
UOM
Location
Stock No

Table-Rawmaterial_Tran

TransactionID
TranItemID
Qty
DOT (Date of Transaction)
Type ( 1 – Received or -1 Issued)
IssuedTo
Length

Relationship – one to many ItemID ~ TranItemID
Referential Integrity constraint

I have a main form called Rawmaterial Transaction and I am calling a
subform
in a datasheet view.
The subform has QOH calculated QOH= sum (qty * type)
Length = sum[length]

In the main forms footer I am calling the QOH value from the subform and
displaying it.
I have a combo box in the main form which selects the item no, stock no
and
displays the corresponding details on the main form.

Subform_Final
TransactionID TranItemID Qty DOT IssuedTo Length Type

588 382 11 10-Apr-08 L-floor 0.61 Received
682 382 2 14-Apr-08 yz 0 Issued
707 382 2 01-May-08 xyz 0 Issued

QOH shows 7
Length of wire shows 4.27 km calculated by deducting 4* 0.61 = 2.44
Total wire length = 11* 0.61 = 6.6
Remining wire = 4.2 km

Everything gets calculated ok but is there any way i can lock the user
from
entering further values in subsequent cells eg. in TransactionItemID 682
and
707 after the user enters the total length of the wire the first time.

any help will be greatly appreciated.
Click to expand...
Click to expand...
 
After Update of what to enable/disable a "field"? Of the form? A control?
In any case, you enable/disable controls, not fields. If you are talking
about a control, is it supposed to be enabled or disabled when you first
open the form? You should understand that changing the enabled property in
an After Update event means the control will be enabled or disabled for all
records until the After Update event changes it again.
Are you asking how to put code into the Current event?

vandy said:
Hello Bruce,

Thanks Bruce for your reply. I am attempting to change the Text field
Length
wich is not a linked field. I will be using the Afterupdate event to
enable
or disable the field. I need some help with the forms current event. Is
there
an event like current how do i encorporate this code.
Thanks in advance.

BruceM said:
Are TransactionID and TranItemID fields in which the user inputs a value?
I
would expect they are not, particularly TranItemID, which seems to be a
linked field. In any case, if you need to display those fields you can
lock
or disable the text boxes in which they appear. You can set such
properties
in the After Update event for a text box:
If Not IsNull(Me.txtSomeTextBox) Then
Me.txtSomeTextBox.Enable = False
End If

You would need similar code in the form's Current event.

vandy said:
I am attempting to lock a transaction record after i key in the first
value
in this case the length of a wire recieved into the inventory. The
following
are the details of my database.
I have a table Rawmaterial_Items and Rawmaterial_Tran

Table-Rawmaterial_Items

ItemID
ItemNo
Supplier
UOM
Location
Stock No

Table-Rawmaterial_Tran

TransactionID
TranItemID
Qty
DOT (Date of Transaction)
Type ( 1 – Received or -1 Issued)
IssuedTo
Length

Relationship – one to many ItemID ~ TranItemID
Referential Integrity constraint

I have a main form called Rawmaterial Transaction and I am calling a
subform
in a datasheet view.
The subform has QOH calculated QOH= sum (qty * type)
Length = sum[length]

In the main forms footer I am calling the QOH value from the subform
and
displaying it.
I have a combo box in the main form which selects the item no, stock no
and
displays the corresponding details on the main form.

Subform_Final
TransactionID TranItemID Qty DOT IssuedTo Length
Type

588 382 11 10-Apr-08 L-floor 0.61 Received
682 382 2 14-Apr-08 yz 0 Issued
707 382 2 01-May-08 xyz 0 Issued

QOH shows 7
Length of wire shows 4.27 km calculated by deducting 4* 0.61 = 2.44
Total wire length = 11* 0.61 = 6.6
Remining wire = 4.2 km

Everything gets calculated ok but is there any way i can lock the user
from
entering further values in subsequent cells eg. in TransactionItemID
682
and
707 after the user enters the total length of the wire the first time.

any help will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Sorry Bruce, Did not mean to confuse you. Yes I do understand the implication
of using the code in the Afterupdate event . If you look at my example,
Length is stored in a Textbox control and I need to enable or disable it.
What is happening is the code works well the first time I input the length
and the lenght is disabled for subsequent entries which is great. But when I
want to enter the length for another new transaction the Length field remains
disabled.

Referring to your comment "You would need similar code in the form's Current
event" how can this be done, probably this would solve my problem.


BruceM said:
After Update of what to enable/disable a "field"? Of the form? A control?
In any case, you enable/disable controls, not fields. If you are talking
about a control, is it supposed to be enabled or disabled when you first
open the form? You should understand that changing the enabled property in
an After Update event means the control will be enabled or disabled for all
records until the After Update event changes it again.
Are you asking how to put code into the Current event?

vandy said:
Hello Bruce,

Thanks Bruce for your reply. I am attempting to change the Text field
Length
wich is not a linked field. I will be using the Afterupdate event to
enable
or disable the field. I need some help with the forms current event. Is
there
an event like current how do i encorporate this code.
Thanks in advance.

BruceM said:
Are TransactionID and TranItemID fields in which the user inputs a value?
I
would expect they are not, particularly TranItemID, which seems to be a
linked field. In any case, if you need to display those fields you can
lock
or disable the text boxes in which they appear. You can set such
properties
in the After Update event for a text box:
If Not IsNull(Me.txtSomeTextBox) Then
Me.txtSomeTextBox.Enable = False
End If

You would need similar code in the form's Current event.

I am attempting to lock a transaction record after i key in the first
value
in this case the length of a wire recieved into the inventory. The
following
are the details of my database.
I have a table Rawmaterial_Items and Rawmaterial_Tran

Table-Rawmaterial_Items

ItemID
ItemNo
Supplier
UOM
Location
Stock No

Table-Rawmaterial_Tran

TransactionID
TranItemID
Qty
DOT (Date of Transaction)
Type ( 1 – Received or -1 Issued)
IssuedTo
Length

Relationship – one to many ItemID ~ TranItemID
Referential Integrity constraint

I have a main form called Rawmaterial Transaction and I am calling a
subform
in a datasheet view.
The subform has QOH calculated QOH= sum (qty * type)
Length = sum[length]

In the main forms footer I am calling the QOH value from the subform
and
displaying it.
I have a combo box in the main form which selects the item no, stock no
and
displays the corresponding details on the main form.

Subform_Final
TransactionID TranItemID Qty DOT IssuedTo Length
Type

588 382 11 10-Apr-08 L-floor 0.61 Received
682 382 2 14-Apr-08 yz 0 Issued
707 382 2 01-May-08 xyz 0 Issued

QOH shows 7
Length of wire shows 4.27 km calculated by deducting 4* 0.61 = 2.44
Total wire length = 11* 0.61 = 6.6
Remining wire = 4.2 km

Everything gets calculated ok but is there any way i can lock the user
from
entering further values in subsequent cells eg. in TransactionItemID
682
and
707 after the user enters the total length of the wire the first time.

any help will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
To place code into the form's Current event, open the form in design view.
Click View >> Properties, or use whatever other method you prefer to view
the property sheet. Click the Events tab, and click next to On Current.
Click the three dots that appear on the right side, click Code Builder, and
click OK. That should open the VBA editor with the following:
Private Sub Form_Current()

End Sub

The cursor should be blinking between those two lines. Enter the code there
(the code I suggested in an earlier post, adapted for your field and control
names). As you type it will probably give you some help. For instance,
after typing Me. you should see a list of controls, fields, and properties
that are available to the form. When you are done, click Debug >> Compile.

As I mentioned, you will need the code both in the control's After Update
event and in the form's Current event. If it is not in the After Update
event the enable/disable will not work until you move to another record.
This is because the Current event runs only when you arrive at another
record.

vandy said:
Sorry Bruce, Did not mean to confuse you. Yes I do understand the
implication
of using the code in the Afterupdate event . If you look at my example,
Length is stored in a Textbox control and I need to enable or disable it.
What is happening is the code works well the first time I input the length
and the lenght is disabled for subsequent entries which is great. But when
I
want to enter the length for another new transaction the Length field
remains
disabled.

Referring to your comment "You would need similar code in the form's
Current
event" how can this be done, probably this would solve my problem.


BruceM said:
After Update of what to enable/disable a "field"? Of the form? A
control?
In any case, you enable/disable controls, not fields. If you are talking
about a control, is it supposed to be enabled or disabled when you first
open the form? You should understand that changing the enabled property
in
an After Update event means the control will be enabled or disabled for
all
records until the After Update event changes it again.
Are you asking how to put code into the Current event?

vandy said:
Hello Bruce,

Thanks Bruce for your reply. I am attempting to change the Text field
Length
wich is not a linked field. I will be using the Afterupdate event to
enable
or disable the field. I need some help with the forms current event. Is
there
an event like current how do i encorporate this code.
Thanks in advance.

:

Are TransactionID and TranItemID fields in which the user inputs a
value?
I
would expect they are not, particularly TranItemID, which seems to be
a
linked field. In any case, if you need to display those fields you
can
lock
or disable the text boxes in which they appear. You can set such
properties
in the After Update event for a text box:
If Not IsNull(Me.txtSomeTextBox) Then
Me.txtSomeTextBox.Enable = False
End If

You would need similar code in the form's Current event.

I am attempting to lock a transaction record after i key in the first
value
in this case the length of a wire recieved into the inventory. The
following
are the details of my database.
I have a table Rawmaterial_Items and Rawmaterial_Tran

Table-Rawmaterial_Items

ItemID
ItemNo
Supplier
UOM
Location
Stock No

Table-Rawmaterial_Tran

TransactionID
TranItemID
Qty
DOT (Date of Transaction)
Type ( 1 – Received or -1 Issued)
IssuedTo
Length

Relationship – one to many ItemID ~ TranItemID
Referential Integrity constraint

I have a main form called Rawmaterial Transaction and I am calling a
subform
in a datasheet view.
The subform has QOH calculated QOH= sum (qty * type)
Length = sum[length]

In the main forms footer I am calling the QOH value from the subform
and
displaying it.
I have a combo box in the main form which selects the item no, stock
no
and
displays the corresponding details on the main form.

Subform_Final
TransactionID TranItemID Qty DOT IssuedTo Length
Type

588 382 11 10-Apr-08 L-floor 0.61 Received
682 382 2 14-Apr-08 yz 0 Issued
707 382 2 01-May-08 xyz 0 Issued

QOH shows 7
Length of wire shows 4.27 km calculated by deducting 4* 0.61 = 2.44
Total wire length = 11* 0.61 = 6.6
Remining wire = 4.2 km

Everything gets calculated ok but is there any way i can lock the
user
from
entering further values in subsequent cells eg. in TransactionItemID
682
and
707 after the user enters the total length of the wire the first
time.

any help will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top