[RESOLVED]Const BlockSize Issue

Hi folk, How are you All? I have below code it's work just fine by entered 100 record for each block and it's work fine but if i entered less than 100 record and close form and re-open it it's record new block even the previous block not completed entered. I'm looking forward for your help to prevent record new block number in case not completed the 100 record? and i try code in red but unfortunately it's not working. Thank you All.

Code:

---

Const BlockSize = 100
Dim rsReceived As ADODB.Recordset
Dim cn As Long, Tn As Long, Bn As Long, Bk As Long
----------------------------------------------------------------------
Private Sub txtICCID_KeyPress(KeyAscii As Integer)
'This check that all information has been entered and the saves the record

Dim Nm As Double

  If KeyAscii = 13 And Not txtICCID.Locked Then
  If Len(txtICCID.Text) = 11 Or Len(txtICCID.Text) = 12 Then
      Nm = txtICCID.Text
      txtICCID.Text = Nm
      If Bk = 0 Then Bk = 1
      TxBlock.Text = Format(Bk, "#,##")
    rsReceived.Find "number = '" & txtICCID.Text & "'"
  If rsReceived.EOF Then
 
rsReceived.AddNew

  rsReceived.Fields(0) = txtICCID.Text
  rsReceived.Fields(1) = TxBlock.Text
  rsReceived.Fields(3) = dtpReceived.Value
  rsReceived.Fields(2) = 0
  rsReceived.Fields(4) = 0
  rsReceived.Fields(5) = 0
  rsReceived.Update

cn = cn + 1
        TxCount.Text = cn
        txtICCID.Text = ""
        txtICCID.SetFocus
        If cn = BlockSize Then
          Bk = Bk + 1
          MsgBox "Block Done"
          txtICCID.Locked = True
          txtICCID.SetFocus
          Else
        Bk = Bk
        End If
       
 Else
      MsgBox "Repeated"
      txtICCID.Text = ""
  End If
    Else
      txtICCID.Text = ""
      MessageBoxH Me.hwnd
          MsgBox "please enter 11 or 12 number!", vbCritical + vbMsgBoxRtlReading + vbMsgBoxRight, "Warning"
          Call OffMsg
    End If
  End If
 
Exit Sub
End Sub

---

on opening, you need to figure how many records are in the last block, and put into variable cn

Dear Westconn1, Could you please provide an example? I would be very appreciate. Thank you in advance

P.S I did use in Load form code below

cn = 0
TxCount.Text = cn

Quote:

---

cn = 0
TxCount.Text = cn

---

this is good if no previous entries in the last block

get the last used row number and use mod to get the partial block size

Code:

---

lastrow = cells(rows.count, 1).end(xlup).row
firstrow = 2    ' whatever row the first block starts on
cn = (lastrow - firstrow) mod 100

---

this assumes there is no gaps between blocks, if you have gaps between blocks you will have to modify to suit

Hello Again my friend, I thank you for your time spending here, I did test the Code as attachment below shows. Thank you in advance

Edited: I use Dim For Lastrow and Firstrow and same error msg.

you need to fully qualify rows and cells to your worksheet object
as you are using ADO, possibly you can use the recordcount property of the recordset instead, to find how many records are after the last full block

Thank you Pete for your informative. I would be very glad if you provide simple example? Thank you for your patience with me

Any suggest please? Thank you

It is pretty simple

Code:

---

Dim LastBockSize As Integer
LastBlockSize = RS.RecordCount Mod 100

---

Here if the last block would be last than 100 records then the number of records would be in the lastblocksize variable
If the last block would have 100 records in it then the var would be = 0

Hello Friend DataMiser, I really glad for your stopping by, I did use your formula but no hope it's still record Bk + 1 if the previous block has less than 100. And I did come up with recordcount but no hope either, Please look at it and see if you can help me out. Thank you in Advance.

Code:

---

Private Sub Form_Load()
'This opens the recordset
Set rsReceived = New ADODB.Recordset
rsReceived.Open "Select * From TbStore", dbContact, adOpenStatic, adLockOptimistic
dtpReceived.Value = Date

cn = 0

  If rsReceived.RecordCount <> 0 Then
    rsReceived.MoveLast
    TxBlock.Text = Format(rsReceived.Fields("BlockNo") + 1, "#,##")
    TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
  Else
    TxBlock.Text = 0
    TxTotal.Text = 0
  End If
  Bk = TxBlock.Text
  Tn = TxTotal.Text
  TxCount.Text = 0

End Sub

---

That formula will tell you how many there are in the last odd block.
So if you have 458 records it would give you 58 but I have a feeling you are not really looking for the last block of data but something else related to your block number IDs and I am not clear on what you are doing so not much I can do.

That little piece of code you just posted sheds no light on what you are trying to do nor the problem you are having.

I don't have any problem with above code, it's only when i working on block and reach any record less than 100 and close form and re-open it it's record new BLOCK!!!, I'm looking for code like yours posted in post #9 to prevent record new block incase we have uncompleted block but unfortunately it's not working. So please how we can develop code above to reach my requirment? And i apologize for confusing. Your help appreciated if issue resolved or not. Thank you again for your time. Brss

Edited: I have as i mention in code above txtTotal show me total records in DB and txtblock to show me the block i reach.

Ok I looked at your code again and I have a little better idea of what you are trying to do.

This code that you added

Code:

---

Else
  Bk = Bk

---

serves no purpose and should be removed

In your form load code you should check the total records present and see if it is evenly divisible by 100 and add 1 only if it is. Something like

Code:

---

If rsReceived.RecordCount > 0 Then
    rsReceived.MoveLast
    If rsReceived.RecordCount Mod 100 > 0 then 'Is not evenly divisible by 100
        TxBlock.Text = Format(rsReceived.Fields("BlockNo") , "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    Else
        TxBlock.Text = Format(rsReceived.Fields("BlockNo") + 1, "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    End If
Else
    TxBlock.Text = 0
    TxTotal.Text = 0
End If

---

This way if your record count was say 500 then it would add 1 to the last block number whereas if the recordcount was 498 it would use the last block number and not add 1 to it

Hello And good day DataMiser, I would take a time to thank you for your precious time spending with me. now code you mention is fantastic and i really like the idea, when used it it's just record only one (1) to BlockNo. so i made little change to code line below and it's work just fine

Code:

---

If rsReceived.RecordCount <> 0 Then

---

But I'm facing serious problem, When add record to for example let's say i'm working on blockno 1 add 45 record and ofcourse it's less than 100 and close form and re-open it to add the rest 55 record to blockno 1 then when finish 100 record move to next block, block remain and that's good but i start over and TxBlock not stop on 55 record to jump to next block it's continue to 56, 57 to 100 and entered 100 record and Total would be 145 to one block! I would love if you advice and ofcourse would be appreciate. Thank you For your fantastic help.

Here is code i used to save

Code:

---

Private Sub txtICCID_KeyPress(KeyAscii As Integer)
'This check that all information has been entered and the saves the record

Dim Nm As Double

  If KeyAscii = 13 And Not txtICCID.Locked Then
  If Len(txtICCID.Text) = 11 Or Len(txtICCID.Text) = 12 Then
      Nm = txtICCID.Text
      txtICCID.Text = Nm
      If Bk = 0 Then Bk = 1
      TxBlock.Text = Format(Bk, "#,##")
    rsReceived.Find "number = '" & txtICCID.Text & "'"
  If rsReceived.EOF Then
 
rsReceived.AddNew

  rsReceived.Fields(0) = txtICCID.Text
  rsReceived.Fields(1) = TxBlock.Text
  rsReceived.Fields(3) = dtpReceived.Value
  rsReceived.Fields(2) = 0
  rsReceived.Fields(4) = 0
  rsReceived.Fields(5) = 0
  rsReceived.Update

cn = cn + 1
        TxCount.Text = cn
        txtICCID.Text = ""
        txtICCID.SetFocus

        If cn = BlockSize Then
          Bk = Bk + 1
          MsgBox "Block Done"
          txtICCID.Locked = True
          txtICCID.SetFocus
        End If       
 Else
      MsgBox "Repeated"
      txtICCID.Text = ""
  End If
    Else
      txtICCID.Text = ""
      MessageBoxH Me.hwnd
          MsgBox "please enter 11 or 12 number!", vbCritical + vbMsgBoxRtlReading + vbMsgBoxRight, "Warning"
          Call OffMsg
    End If
  End If
 
Exit Sub
End Sub

---

Code:

---

If rsReceived.RecordCount <> 0 Then

---

This line would stop it from working properly. It does not check hown many records were returned. Only that it is not 0 so I would expect if the last block had 100 in it then it would still try to re-use that block. The way I showed you would use a new block if there were 100 records per block in the data and use the last block number if not.

You need to use the MOD as I showed you. If that did not work then there is another issue.

As for the other problem it is the cn you are making it go to 100 but not takign into account how many you start with

Code:

---


If rsReceived.RecordCount > 0 Then
    rsReceived.MoveLast
    cn=rsReceived.RecordCount Mod 100
    If cn > 0 then 'Is not evenly divisible by 100
        TxBlock.Text = Format(rsReceived.Fields("BlockNo") , "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    Else
        TxBlock.Text = Format(rsReceived.Fields("BlockNo") + 1, "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    End If
Else
    TxBlock.Text = 0
    TxTotal.Text = 0
End If

---

Hello DataMiser, Thank you for your advice and your hard work reall i got amazed by you. Every things work fine but I'm facing problem after I made your recommendation as well, when entered blocks code work like charm, but when sell blocks, TxBlock reset, For example: If I entered 3 blocks and Txcount should be 4 when close form, Now when I sell block 1 to dealer and return back to continue entered blocks i found that Txcount = 2 and in fact it should be 4 or if sell block 2 Txcount = 3. As you can see in below attachment. Any advice please? Thank you in Advance

I think we need to add some line to Load Form to prevent this issue.

Code:

---

Private Sub Form_Load()
'This opens the recordset

Set rsReceived = New ADODB.Recordset
rsReceived.Open "Select * From TbStore", dbContact, adOpenStatic, adLockOptimistic
dtpReceived.Value = Date

cn = 0
 
If rsReceived.RecordCount > 0 Then
    rsReceived.MoveLast
    cn = rsReceived.RecordCount Mod 100
   
  If cn > 0 Then 'Is not evenly divisible by 100

        TxBlock.Text = Format(rsReceived.Fields("BlockNo"), "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    Else
        TxBlock.Text = Format(rsReceived.Fields("BlockNo") + 1, "#,##")
        TxTotal.Text = Format(rsReceived.RecordCount, "#,##")
    End If
Else
    TxBlock.Text = 0
    TxTotal.Text = 0
End If
  Bk = TxBlock.Text
  Tn = TxTotal.Text
  TxCount.Text = 0

End Sub

---

Code to enerted block mention below.

Code:

---

Private Sub txtICCID_KeyPress(KeyAscii As Integer)
'This check that all information has been entered and the saves the record

Dim Nm As Double

  If KeyAscii = 13 And Not txtICCID.Locked Then
  If Len(txtICCID.Text) = 11 Or Len(txtICCID.Text) = 12 Then
      Nm = txtICCID.Text
      txtICCID.Text = Nm
      If Bk = 0 Then Bk = 1
      TxBlock.Text = Format(Bk, "#,##")
    rsReceived.Find "number = '" & txtICCID.Text & "'"
  If rsReceived.EOF Then
 
rsReceived.AddNew

  rsReceived.Fields(0) = txtICCID.Text
  rsReceived.Fields(1) = TxBlock.Text
  rsReceived.Fields(3) = dtpReceived.Value
  rsReceived.Fields(2) = 0                            ' CmActiveAgent.Text    : Dealer Name
  rsReceived.Fields(4) = 0                            ' dtpReceived    : Sell Date
  rsReceived.Fields(5) = 0
  rsReceived.Update

cn = cn + 1
        TxCount.Text = cn
        txtICCID.Text = ""
        txtICCID.SetFocus
        If cn = BlockSize Then
          Bk = Bk + 1
          MsgBox "Block Done"
          txtICCID.Locked = True
          txtICCID.SetFocus
        End If
       
 Else
      MsgBox "Repeated"
      txtICCID.Text = ""
  End If
    Else
      txtICCID.Text = ""
      MessageBoxH Me.hwnd
          MsgBox "please enter 11 or 12 number!", vbCritical + vbMsgBoxRtlReading + vbMsgBoxRight, "Warning"
          Call OffMsg
    End If
  End If
 
Exit Sub
End Sub

---

Code to Sell blocks as below described:

Code:

---

Private Sub CmdSell_Click()

  If TxActiveBlock.Text <> "" And CmActiveAgent.Text <> "" Then
    rsReceived.Filter = "DealerName='" & CmActiveAgent.Text & "'"
    rsReceived.Filter = "BlockNo=" & TxActiveBlock.Text
   
    If rsReceived.RecordCount <> 0 Then
      rsReceived.MoveFirst
      Do While Not rsReceived.EOF
        rsReceived.Fields(2) = CmActiveAgent.Text
        rsReceived.Fields(4) = dtpReceived
        rsReceived.Update
        rsReceived.MoveNext
      Loop
     
      MsgBox "Block Sell"
    End If
    'CmdBlock.Enabled = False
    TxActiveBlock.Text = ""
    CmActiveAgent.Text = ""

 Else
    MsgBox "Please chose block and dealer name"
  End If
End Sub

---

Code:

---

Private Sub CmActiveBlock_GotFocus()

Dim Tx

CmActiveBlock.Clear

Set City_str = New Recordset
City_str.Open "SELECT DISTINCT BlockNo, DealerName FROM TbStore", dbContact, adOpenStatic, adLockOptimistic
City_str.Filter = "DealerName=0"
  If City_str.RecordCount <> 0 Then
    Tx = 0
    Do While Not City_str.EOF
      If Tx <> City_str.Fields("BlockNo") Then CmActiveBlock.AddItem City_str.Fields("BlockNo")
      Tx = City_str.Fields("BlockNo")
      City_str.MoveNext
    Loop
  End If
End Sub

---

Edited: Dear DataMiser, I apologize, I do have this problem before i made your update. Looking forward for your advice. Thank you

I really have no idea what you are talking about in that last post.

Sorry for not explain this issue straight forward;). When start record blocks and entered 3 blocks the TxBlock should record last BlockNo would be 4 as [ ("BlockNo") + 1 ]right. Now when assign block to dealer and return back to continue record blocks i did found the TxBlock not hold the last block i left behind Fox example if i assign block 1 to dealer DataMiser the last TxBlock record 2 while it should be 4 as i left it behind, or if assign 2 the last TxBlock 3. I did spend enough time and i did figure out solution i create field for DealerName and empty previous DealerName and change field name and this field will record only zero value and when assign block to dealer the txBlock still hold the last Block Number. Thank you DataMiser and All of you to share me and spend with me your precious time.