[RESOLVED] [Urgent] Please help me

[brss]

Hello Folk, I have code below it's work but not as it should be. I entered 100 record for each block and blockno generated Auto and rising up but sometimes blockno not working and it's crazy for example when reach block 20 and close form and re-open it block write block 13 and sometimes 10? I did mass up with my data. Is there any way to perform code below and let always record [ Bk + 1 ] when finish entered 100 recordset please? I'm looking forward for your advice.

Edited: Add Form.

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

Dim Nm As Double

Select Case KeyAscii
    Case 8, 13, 46 To 57
    Case Else
        KeyAscii = 0
        MsgBox "Only A to Z allowed"
End Select

  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.Filter = "Number =" & Nm
   If rsReceived.RecordCount = 0 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.Fields(6) = 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
          cn = 0
          txtICCID.Locked = False
          TxCount.Text = cn
          txtICCID.SetFocus
          txtICCID.Text = ""
        End If
          Else
       MsgBox "Number Exist"
       txtICCID.Text = ""
   End If
    Else
      txtICCID.Text = ""
      MessageBoxH Me.hwnd
          MsgBox "Please enter 11 0r 12 digits!", vbCritical + vbMsgBoxRtlReading + vbMsgBoxRight, "ÇÑÞÇã"
           Call OffMsg
    End If
  End If
End Sub

[DataMiser]

Could be that your data is just not in the proper order to get the last block number

rsReceived.Open "Select * From TbStore orderby blockno", dbContact, adOpenStatic, adLockOptimistic

[brss]

Hello my Friend Data, I did try it now and it's not working. Block read 10 while it should be read 17 to continue enter records? Please could you look at Red Code in previous Post. Thank you a lot, I did appreciate your Reply.

[DataMiser]

I can't remember if there needs to be a space in there or not may be order by rather than orderby but in any case that will make sure that the last record is the highest block number which from what you have said is what you want and not what you are getting so that should be the solution.

As for the code in red I can't tell much about it. What you really should do is set a break point and step through the code line by line to see what it is doing and check the values of your BK and CN vars to see if they are correct when the program is running.

[brss]

Yes Should be Space between. I will give a shot and get back to you my friend, And I'm sorry to annoy you with my problem. you Awesome.

[westconn1]

as you do not sort the recordset, you can not tell which record is last in the recordset, so the blocknumber in the last record may not be the highest block number

'this should work, it just calculates everything from the total number of records in the recordset,

Private Sub Form_Load()

Set rsReceived = New ADODB.Recordset
rsReceived.Open "Select * From TbStore", dbContact, adOpenStatic, adLockOptimistic
dtpReceived.Value = Date
rsReceived.MoveLast
 
    totalrecords = rsReceived.recordcount
    msgbox totalrecords      ' check the right count is returned
    bk = totalrecords \ blocksize + 1
    cn = totalrecords mod blocksize +1 ' next record

        TxBlock.Text = Format(bk, "#,##")
        TxTotal.Text = Format(cn, "#,##")
End Sub

[brss]

Hello My Friend West, What about the code in red? I will try it right now and give the result. By the way, Thank you very much

[brss]

Hello My Friend West, Thank you very much, i did test the code and it's work fine but with one issue [TxTotal] not show the actual Total recordset? it's record only [1] while DB and MsgBox shows [550].



Second for code below it's should prevent entered more than 100 recordset right?

cn = totalrecords Mod BlockSize + 1 ' next record

It's not working at all So Please How can I prevent entered more than 100 for each block if form close and re-open? What I'm experiencing now when entered around 89 more or less for block 8! and close the form and re-open it then i should add only 11 record then moved to next block right but the block accept more than 11 and record 189 for block 10? any advice please? Thank you again for your assist

[westconn1]

but with one issue [TxTotal] not show the actual Total recordset

txtotal = totalrecords

while DB and MsgBox shows [550].

should show bk = 6 and cn = 51

but the block accept more than 11 and record 189 for block 10? any advice please?

at the 100th record, make sure to step through the code to find what is happening

use a watch to find if the value of cn changes anywhere apart from where you increment or zero it

[brss]

Thank you again Pete, Really I appreciate, I did add watch and it's highlight on the re code below. Please could you look at red code below and see what makes me the wrong i have? Thanks again

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

Dim Nm As Double

Select Case KeyAscii
    Case 8, 13, 46 To 57
    Case Else
        KeyAscii = 0
        MsgBox "Only A to Z allowed"
End Select

  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


      TxBlock.Text = Format(Bk, "#,##")
      
    rsReceived.Filter = "Number =" & Nm
   If rsReceived.RecordCount = 0 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.Fields(6) = 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.Locked = False
          TxCount.Text = cn
          txtICCID.SetFocus
          txtICCID.Text = ""
        End If
          Else
       MsgBox "Number Exist"
       txtICCID.Text = ""
   End If
    Else
      txtICCID.Text = ""
      MessageBoxH Me.hwnd
          MsgBox "Please enter 11 0r 12 digits!", vbCritical + vbMsgBoxRtlReading + vbMsgBoxRight, "!"
           Call OffMsg
    End If
  End If
End Sub

[westconn1]

i can not see anything wrong with your code, apart from indenting, which makes it extremely hard to read

Nm = txtICCID.Text
txtICCID.Text = Nm

this makes no sense at all, it would make sense if you wanted to automatically increment the iccid after saving

txtICCID.Locked = True

txtICCID.Locked = False

this too seems unnecessary

all changes to textboxes should be done at the end of the procedure, not several times through the code

i would believe you need to reset cn when bk = blocksize

                If cn = BlockSize Then
                      Bk = Bk + 1
                      MsgBox "Block Done"
                      cn = 1
                      TxCount.Text = cn
                      txtICCID.SetFocus
                      txtICCID.Text = ""
                End If

also as you have already incremented cn it would look like you only get 99 records to each block, unless you are working each block from 0 to 99, which i don't believe is the case

[brss]

Thank you Pete, Everything seems to work now BUT only when close form and re-open it's continue record more than blocksize as explain in previous posts? any suggest please? Thank you

Edited: Should we need to add code line to Load form?

[westconn1]

when close form and re-open it's continue record more than blocksize? and suggest please?

i do not see how this can be so, if you implemented the code as suggested, the variables for cn and bk should be initialised correctly on form open, then incremented as required when adding records, i do not see any reason why the block sizes should be incorrect

of course as you are incrementing your counter before testing is cn = blocksize, it would in some cases be possible to miss the 100 count
if you have recordcount of 499, next record would be
block 5 cn 100
cn = cn + 1
therefore cn > blocksize (not = ), so will continue to count upwards, above blocksize

you should test cn for blocksize before incrementing

[brss]

cn = totalrecords mod blocksize +1 ' next record

I use

cn = totalrecords mod blocksize

and code work fine. Thank you very much for your time spending here my friend West