Required a Dmax or Dcount solution

S

syedadnan

Access Lover
Local time
Today, 18:35
Joined
Mar 27, 2013
Messages
315
Regards

Required a Dmax or Dcount solution which suits best as per my requirement,

I have a table named REG

Fields are:

AUTO
SERIAL

now want to have dmax with default value for SERIAL = 0 with new records will be as +1 based on each AUTO means

if Auto is 5001 then dmax shall start from 0 and adding +1 after entries of records

in short each Serial to be started from 0 and then +1 based criteria is Auto
 
You could achieve it with this code.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  'If Me.SERIAL is empty then get next value
  
  If Len(Me.SERIAL & vbNullString) = 0 Then Me.SERIAL = getNextSerial(Me.AUTO)
End Sub

Private Function getNextSerial(ByVal gnsAuto As Variant)
Dim varTemp As Variant
  
  ' Find largest value of SERIAL in Table REG or -1 if it doesn't exist
  ' WHERE AUTO is equal to the parameter gnsAuto
  
  varTemp = Nz(DMax("SERIAL", "REG", "AUTO = " & Nz(gnsAuto, -1)), -1)
  varTemp = varTemp + 1

  getNextSerial = varTemp
End Function

Result:
Code: 
AUTO	SERIAL
2	0
2	1
2	2
2	3
5000	0
5000	1
1	0
1	1
1	2
1	3
5000	2
5000	3

I have attached an example file.
 

Attachments

nanscombe said:
You could achieve it with this code.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  'If Me.SERIAL is empty then get next value
  
  If Len(Me.SERIAL & vbNullString) = 0 Then Me.SERIAL = getNextSerial(Me.AUTO)
End Sub

Private Function getNextSerial(ByVal gnsAuto As Variant)
Dim varTemp As Variant
  
  ' Find largest value of SERIAL in Table REG or -1 if it doesn't exist
  ' WHERE AUTO is equal to the parameter gnsAuto
  
  varTemp = Nz(DMax("SERIAL", "REG", "AUTO = " & Nz(gnsAuto, -1)), -1)
  varTemp = varTemp + 1

  getNextSerial = varTemp
End Function

Result:
Code: 
AUTO	SERIAL
2	0
2	1
2	2
2	3
5000	0
5000	1
1	0
1	1
1	2
1	3
5000	2
5000	3

I have attached an example file.
Click to expand...


WOW it is fantastic and working great .. but just a problem that might i didn't write at first that i have a save/refresh command button named as cmdsave what my very very basic need is if i selected 5 records with same Auto and hit cmdrefresh then it mus produce Serial 1 as with same Auto then if selecting remaing item through Auto and hit button the serial 2 with same Auto this is the main i badly looking
 
Is the value of AUTO added manually for each record?

Do have records which are allowed to have a value in AUTO but not have a value in SERIAL?

The above code would automatically generate a value for SERIAL based on the value in AUTO if it didn't already have one when the record was saved.
 
nanscombe said:
Is the value of AUTO added manually for each record?

Do have records which are allowed to have a value in AUTO but not have a value in SERIAL?

The above code would automatically generate a value for SERIAL based on the value in AUTO if it didn't already have one when the record was saved.
Click to expand...

Regards,

First of all thank you for being having coopertaion!!!

A DB is attached as your solution is the 90 % answer of my problem but still fighting with the basic purpose..

You may see a form GRN in the DB i select / filter Auto number 5364 and you will see there is a request of four item purchase. You will also see four check boxes also then serial is also here ..

what i want is if having this auto as example like if some one open form GRN and filter 5364 and select first 2 items as purchase done and left 2 items as they are remaining. so here i want a button of any soultion that put the number in serial box like if select 2 items with Auto 5364 then it should produce 1 after clicking of refreshing/save button. suppose after 20 minutes of this click the 1 more item purchased and then first checked the box as yes the click button should produce 2...

short in words i want serial number with button clikcs based on Auto
 

Attachments

How are you "selecting" the records, by using the record selector on the left hand side of the record or using the ForGRN checkbox?

I've added a demo with the functionality connected to double-clicking the Serial field.
 

Attachments

Last edited:
nanscombe said:
How are you "selecting" the records, by using the record selector on the left hand side of the record or using the ForGRN checkbox?

I've added a demo with the functionality connected to double-clicking the Serial field.
Click to expand...

Thanks for the time shared..

Yes I am selecting the records through the ForGRN checkbox, I have checked the DB you attached but still the problem persists, bcz when clicking the serial field it is only generating the number on that field where the cliking is and i want if i select forgrn check box for 3 items out of 5 items then same number to be issued for all forgrn selected item through check box, and same when selecting the remaining items after some time then the number will be +1 as previous.. the basic what i need is to generate number with any command, refresh button, save or submit button to generate number but same number for first click then +1 on different clicks for other selection.. you may use last DB being attached by you..

Thanks again and desperate to have this extremly typical soultion
 
I'm with you now. :)

The attached database should allow you to do that.

Here is the code I'm using (this is the version with comments)

Code: 
Private Sub getSerialForSelectedRecords_WithComments()
' Same code as "getSerialForSelectedRecords" but with added comments.

' Open a clone of the current recordset to work with
' Using "With" means we can shorten the command lines
  With Me.RecordsetClone

' Move to first record
' Without "With" this would be "Me.RecordsetClone.MoveFirst"
    .MoveFirst
  
' Repeat until (E)nd (O)f (F)ile, or records in this case, is reached
    While Not .EOF
    
' Only run code if Me.ForGN is checked AND Me.SERIAL is empty
      If (!ForGRN = True) And (Len(!Serial & vbNullString) = 0) Then

' Edit record in recordset
        .Edit
          
' Get next value of Serial
          !Serial = getNextSerial(!AUTO)
        
' Update record in recordset
        .Update
      
      End If
 
' Move to next record in recordset
      .MoveNext

' Loop until last record in recordset is reached
    Wend
 
  End With
End Sub

And the clean version (without comments)

Code: 
Private Sub getSerialForSelectedRecords()
  With Me.RecordsetClone

    .MoveFirst
  
    While Not .EOF
    
      If (!ForGRN = True) And (Len(!Serial & vbNullString) = 0) Then

        .Edit
          
          !Serial = getNextSerial(!AUTO)
        
        .Update
      
      End If
 
      .MoveNext

    Wend
 
  End With
End Sub
 

Attachments

nanscombe said:
I'm with you now. :)

The attached database should allow you to do that.

Here is the code I'm using (this is the version with comments)

Code: 
Private Sub getSerialForSelectedRecords_WithComments()
' Same code as "getSerialForSelectedRecords" but with added comments.

' Open a clone of the current recordset to work with
' Using "With" means we can shorten the command lines
  With Me.RecordsetClone

' Move to first record
' Without "With" this would be "Me.RecordsetClone.MoveFirst"
    .MoveFirst
  
' Repeat until (E)nd (O)f (F)ile, or records in this case, is reached
    While Not .EOF
    
' Only run code if Me.ForGN is checked AND Me.SERIAL is empty
      If (!ForGRN = True) And (Len(!Serial & vbNullString) = 0) Then

' Edit record in recordset
        .Edit
          
' Get next value of Serial
          !Serial = getNextSerial(!AUTO)
        
' Update record in recordset
        .Update
      
      End If
 
' Move to next record in recordset
      .MoveNext

' Loop until last record in recordset is reached
    Wend
 
  End With
End Sub

And the clean version (without comments)

Code: 
Private Sub getSerialForSelectedRecords()
  With Me.RecordsetClone

    .MoveFirst
  
    While Not .EOF
    
      If (!ForGRN = True) And (Len(!Serial & vbNullString) = 0) Then

        .Edit
          
          !Serial = getNextSerial(!AUTO)
        
        .Update
      
      End If
 
      .MoveNext

    Wend
 
  End With
End Sub
Click to expand...



Great piece of work done.. i will thank you a lot but the problem was resolved by other thread.. no problems will really apprecite the way you support
 
Ok. As long as you've got what you need. :)

Just tuck anything else away in case you need it another time. ;)
 
You must log in or register to reply here.

Similar threads

essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
670
essaytee
essaytee
K
'Next n' Looping rather than moving to the next record
Replies
16
Views
861
arnelgp
arnelgp
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
432
Mike Krailo
Mike Krailo
K
DLookup to return different value on MoveNext
Replies
5
Views
464
ebs17
E
U
Adding up counted records to get a total
Replies
11
Views
819
UltimateNeo
U

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom