Re calculate automatically

Krays23

Registered User.
Local time
Today, 13:57
Joined
Jun 18, 2015
Messages
46
HI

I have this code that runs if i enter a date into the Txtboxshipactual and then enters a date into txtExpectedDeliveryHUBactual after its added TxtBoxshipactual.Value then it reruns the date diff into TxtDaysdeltatoplan

My issue is that I have to click the check box once to get the date in txtExpectedDeliveryHUBactual then uncheck and check again to get it in TxtDaysdeltatoplan id like it to be clicked only one time and also if possible when unchecked to delete all info in the txt boxes incase I click by mistake

Heres the full code thanks guys

Private Sub ChkBoxship_Click()
If ChkBoxship Then
If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub
 
Hi Dan,

I would look at using your code in the AfterUpdate event for Txtboxshipactual. That way when ever that date changes the other calculations are carried out.
 
Code:
Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
    If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
        Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
    Else
        Me.TxtDaysdeltatoplan = ""
    End If
Else
    Me.TxtDaysdeltatoplan = ""
End If
End Sub
 
One last thing I have all these check boxes but I only want one to be clicked at any one time is there a way you can add a piece of code to say uncheck all other boxes then check this one on click?
 
do you have autonumber field or pk field in your form, if yes we can, just post what is the fieldname/controlname of the auto/pk in your form.
 
do you have autonumber field or pk field in your form, if yes we can, just post what is the fieldname/controlname of the auto/pk in your form.

Hi

Yes I do this one is primary key and autonumber unique to each record

TxtID

Dan
 
using txtID and ID field (i supposed):

Code:
Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
     [COLOR=Blue]Call subUnCheckOtherRecords(Me.txtID)[/COLOR]
    If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
        Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
    Else
        Me.TxtDaysdeltatoplan = ""
    End If
Else
    Me.TxtDaysdeltatoplan = ""
End If
End Sub 


[COLOR=Blue]Private Sub subUnCheckOtherRecords(Byval pk As Variant)
Dim rs As Dao.RecordSet
Set rs = Me.RecordsetClone
With rs
    If Not (.BOF And .EOF) Then .MoveFirst
    While Not .EOF
        IF ![ID] <> pk And !ChkBoxShip=True then
            .Edit
            !ChkBoxShip = False
            .Update
        End If
        .MoveNext
    Wend
    .Close
End With
set rs=Nothing
End Sub[/COLOR]
 
using txtID and ID field (i supposed):

Code:
Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
     [COLOR=blue]Call subUnCheckOtherRecords(Me.txtID)[/COLOR]
    If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
        Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
    Else
        Me.TxtDaysdeltatoplan = ""
    End If
Else
    Me.TxtDaysdeltatoplan = ""
End If
End Sub 


[COLOR=blue]Private Sub subUnCheckOtherRecords(Byval pk As Variant)
Dim rs As Dao.RecordSet
Set rs = Me.RecordsetClone
With rs
    If Not (.BOF And .EOF) Then .MoveFirst
    While Not .EOF
        IF ![ID] <> pk And !ChkBoxShip=True then
            .Edit
            !ChkBoxShip = False
            .Update
        End If
        .MoveNext
    Wend
    .Close
End With
set rs=Nothing
End Sub[/COLOR]



Hi thanks

Sorry im still learning where do I put this new code? also do I have to add Call subUnCheckOtherRecords(Me.txtID) to each of the onclick boxes on the sheet?


Private Sub subUnCheckOtherRecords(Byval pk As Variant)
Dim rs As Dao.RecordSet
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
IF ![ID] <> pk And !ChkBoxShip=True then
.Edit
!ChkBoxShip = False
.Update
End If
.MoveNext
Wend
.Close
End With
set rs=Nothing
End Sub
 
paste it where your your ChkBoxShip click event code is.
 
My understanding of your problem is different to that of Arnelgp. Do you have several checkboxes in the same record (if so, just set ChkBoxOther1, ChkBoxOther2, etc to False), or do you want chkboxship unchecked in every record (which is what I think Arnelgp's solution is aimed at) ?
 
txtID is your primary key. in your table or form?
 
My understanding of your problem is different to that of Arnelgp. Do you have several checkboxes in the same record (if so, just set ChkBoxOther1, ChkBoxOther2, etc to False), or do you want chkboxship unchecked in every record (which is what I think Arnelgp's solution is aimed at) ?

HI

I have 16 check boxes on this form / record and I only want to be able to click one of them at a time.

if one is selected and I click another one id like it to cancel the other chk box and select the one I click on

I have attached photo of the form

Thanks

Dan
 

Attachments

  • chkboxes.jpg
    chkboxes.jpg
    105.2 KB · Views: 87
firstly i suggest renaming your checkboxes to more related to its purpose:
if its for CNC then rename it chkCNC, first letter always "chk".
if you have done the above, then replace your code with the following:

Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
Call subUnCheckOtherRecords
If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
Else
Me.TxtDaysdeltatoplan = ""
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub


private sub subUnCheckOtherRecords()
Dim strActiveControlName as string
Dim c As Control
strActiveControlName = Screen.ActiveControl.Name

For Each c In Me.Controls
If c.Name Like "chk*" And c.Name <> strActiveControlName Then c.Value=False
Next
End Sub
 
That solution requires 16 functions Chk..._Click(), which might well be the best way to do it. However, I would first investigate using an Option Group control.
 
firstly i suggest renaming your checkboxes to more related to its purpose:
if its for CNC then rename it chkCNC, first letter always "chk".
if you have done the above, then replace your code with the following:

Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
Call subUnCheckOtherRecords
If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
Else
Me.TxtDaysdeltatoplan = ""
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub


private sub subUnCheckOtherRecords()
Dim strActiveControlName as string
Dim c As Control
strActiveControlName = Screen.ActiveControl.Name

For Each c In Me.Controls
If c.Name Like "chk*" And c.Name <> strActiveControlName Then c.Value=False
Next
End Sub


Hi

My chk boxes are already named Chk with a caps does that matter?
 
That solution requires 16 functions Chk..._Click(), which might well be the best way to do it. However, I would first investigate using an Option Group control.

How do I do it ?

I cant get Arnels Working
 
firstly i suggest renaming your checkboxes to more related to its purpose:
if its for CNC then rename it chkCNC, first letter always "chk".
if you have done the above, then replace your code with the following:

Private Sub ChkBoxship_Click()
Me.txtExpectedDeliveryHUBactual = Me.TxtBoxshipactual.Value + txtShippingmethod
If ChkBoxship Then
Call subUnCheckOtherRecords
If Trim(Me.TxtBoxshipCRS & "") <> "" And Trim(Me.TxtBoxshipactual) <> "" Then
Me.TxtDaysdeltatoplan = DateDiff("d", Me.txtExpectedDeliveryHUBCRS, Me.txtExpectedDeliveryHUBactual)
Else
Me.TxtDaysdeltatoplan = ""
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub


private sub subUnCheckOtherRecords()
Dim strActiveControlName as string
Dim c As Control
strActiveControlName = Screen.ActiveControl.Name

For Each c In Me.Controls
If c.Name Like "chk*" And c.Name <> strActiveControlName Then c.Value=False
Next
End Sub

Cant get it to work arnel heres my code for each box how do I add to that to make it uncheck all others if check this box and ill copy that to all other 16 functions

Private Sub Chklayup_Click()
If ChkLayup Then
If Trim(Me.txtLayUpCRS & "") <> "" And Trim(Me.txtLayUpactual) <> "" Then
Me.TxtDaysdeltatoplan = NetWorkdays(Me.txtLayUpCRS, Me.txtLayUpactual)
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub
 
Private Sub Chklayup_Click()
If Me.ChkLayup Then
Call subUnCheckOtherRecords
If Trim(Me.txtLayUpCRS & "") <> "" And Trim(Me.txtLayUpactual) <> "" Then
Me.TxtDaysdeltatoplan = NetWorkdays(Me.txtLayUpCRS, Me.txtLayUpactual)
Else
Me.TxtDaysdeltatoplan = ""
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub

insert the blue colored code for each click events of all your checkboxes, ie:

If Me.ChkLayup Then
Call subUnCheckOtherRecords
...
...


another checkbox:

If Me.ChkBoxship Then
Call subUnCheckOtherRecords
...
...

 
Private Sub Chklayup_Click()
If Me.ChkLayup Then
Call subUnCheckOtherRecords
If Trim(Me.txtLayUpCRS & "") <> "" And Trim(Me.txtLayUpactual) <> "" Then
Me.TxtDaysdeltatoplan = NetWorkdays(Me.txtLayUpCRS, Me.txtLayUpactual)
Else
Me.TxtDaysdeltatoplan = ""
End If
Else
Me.TxtDaysdeltatoplan = ""
End If
End Sub

insert the blue colored code for each click events of all your checkboxes, ie:

If Me.ChkLayup Then
Call subUnCheckOtherRecords
...
...


another checkbox:

If Me.ChkBoxship Then
Call subUnCheckOtherRecords
...
...


I donrstand why every thing is based around ChkBoxship its not just if that box is checked

I want only one check box to be allowed to be ticked at any one time on that whole sheet not just if ChkBoxship is ticked
Private Sub subUnCheckOtherRecords(ByVal pk As Variant)
Dim rs As Dao.Recordset
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
If ![ID] <> pk And !ChkBoxship = True Then
.Edit
!ChkBoxship = False
.Update
End If
.MoveNext
Wend
.Close
End With
Set rs = Nothing
End Sub
 

Users who are viewing this thread

Back
Top Bottom