How to craet a buttom can add 50 data in the table (1 Viewer)

[bigbigi]

how to write the code?
for example, the field "ID number" A1200 in the last record
how can i creat a buttom "Add 50" to add 50 record in the field??
than means when i click the buttom
it creat
A1201
...
...
...
A1250
in the table.

 

[aziz rasul]

You can create a recordset and add new records assuming your table was "tblEmployees":-

Dim rst as DAO.Recordset
Dim i as Integer

Set rst = CurrentDb.OpenRecordset("tblEmployees")

For i=1 to 50
rst.AddNew
rst.Update
Next i

Set rst = Nothing

 

[Tim L]

The following code will do what you require:

[COLOR="blue"]Private Sub[/COLOR] btnAddRecords_Click()
[COLOR="blue"]On Error GoTo[/COLOR] Err_btnAddRecords_Click

    [COLOR="blue"]Dim [/COLOR]intCounter [COLOR="blue"]As Integer[/COLOR]
    [COLOR="blue"]Dim [/COLOR]intCurrentMax [COLOR="blue"]As Integer[/COLOR] [COLOR="Green"]'Missed out of original post[/COLOR]
    
[COLOR="green"]    ' Find out what the current maximum record ID Number is
    ' the Nz statement is required for when there are currently no records in the table[/COLOR]
    intCurrentMax = Nz(Mid(DMax("[ID Number]", "[B]tblAutoAddRecordsTest[/B]"), 2, [B]4[/B]), 0)

 [COLOR="green"]   ' Loop to add 50 records
    ' Starting at the highest number plus 1[/COLOR]
    [COLOR="blue"]For [/COLOR]intCounter = intCurrentMax + 1 To intCurrentMax + 50
        [COLOR="green"]' Create a new record[/COLOR]
        DoCmd.GoToRecord , , acNewRec
       [COLOR="green"] 'Apply the required value to the ID field[/COLOR]
        Me.[ID Number] = "A" & Format(intCounter, [B]"0000"[/B])
    [COLOR="blue"]Next [/COLOR]intCounter
[COLOR="Green"]    ' Save the last added record
    ' The other records will be automatically saved by the New Record action.[/COLOR]
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_btnAddRecords_Click:
    [COLOR="blue"]Exit Sub[/COLOR]

Err_btnAddRecords_Click:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Error Adding Records"
    [COLOR="blue"]Resume [/COLOR]Exit_btnAddRecords_Click
    
[COLOR="blue"]End Sub[/COLOR]

Providing you place the code in a button called btnAddRecords.

You will need to change tblAutoAddRecordsTest to the same name as your table.

This code should work for numbers from 1 to 9999, if you want higher numbers you should alter the other code in bold so that it reflects the length of the highest number you intend to use.

If you want to also increment the letter you should examine how the code works and adapt the procedure accordingly.

Note: You should avoid using spaces in field and control names, your ID Number field should be renamed to either IDNumber or ID_Number so that it is easier to reference.

That said, I have no idea why you would actually want to create so many records like this.

HTH

Tim

 

[bigbigi]

thz for help
but

how can i add record according to the record on the form
say
i am pointing to MCB/EQ/050 i want to add up tp MCB/EQ/100
but i have record in below
how can i add it?

 

[Tim L]

Apart from apparently changing your requirement you have still not clarified why you want to add so many blank records.

That said, I'm also not sure if I have fully understood your requirement, rewording your request and taking more time over it would probably have helped.

As you've changed the format of the ID Number from your original request I've formulated a more elaborate routine which will handle varying length of both the prefix (everything before and including the final /) and suffix (everything after the final /) of the ID Numbers.

There are some caveats:

The actual component of [ID Number] to be incremented must be numeric and it must be after the final /.

The routine will not handle numbers rolling over to new units. For example, if your lowest ID Number is AA/BB/1, it fails to add the leading zero to any of the previously entered numbers; so you would have AA/BB/1 ... AA/BB/9and not AA/BB/01 ... AA/BB/09. The impact this has is that the records numbered AA/BB/11 and AA/BB/111 would appear in the sort order before AA/BB/2. The same would be true for any other numbers that add another level of units. This means that you will need to decided on what the likely maximum number of units you are going to use is (which is just good database design theory anyway), and ensure that the first numbers entered have sufficient leading zeros.

The routine will also detect the highest 'ID Number' according to the alphabetic component, so if you have AA/BB/9999 and Z/A/1 the next record to be added will be Z/A/2, because the first letter "Z" is 'higher' in the alphabet than "A".

Private Sub btnAddRecords_Click()
On Error GoTo Err_btnAddRecords_Click

    Dim intCounter As Integer
    Dim strCurrentMax As String
    Dim strPrefix As String
    Dim strCounter As String
    Dim strFormat As String
    
    ' Find out what the current maximum record ID Number is
    ' the Nz statement is required for when there are currently no records in the table
    strCurrentMax = Nz(DMax("[ID Number]", "tblAutoAddRecordsTest"), 0)
    strCounter = Nz(Right(strCurrentMax, (Len(strCurrentMax) - InStrRev(strCurrentMax, "/"))), 0)

    ' make sure that the counter is acually a number, if not warn user and exit from the subroutine
    If Not IsNumeric(strCounter) Then
            MsgBox "The counter component is not numeric"
            Exit Sub
        End If

    ' Find the prefix
    strPrefix = Left(strCurrentMax, Len(strCurrentMax) - Len(strCounter))
    
    ' Create the format string
    strFormat = ""
    For intCounter = 1 To Len(strCounter)
        strFormat = strFormat & "0"
    Next intCounter
        
    'intCounter = strCounter ' Set the counter to the ID Number of the next record to be added

    ' Loop to add 50 records
    ' Starting at the highest number plus 1
    For intCounter = strCounter + 1 To strCounter + 50
        ' Create a new record
        DoCmd.GoToRecord , , acNewRec
        'Apply the required value to the ID field
        Me.[ID Number] = strPrefix & Format(intCounter, strFormat)
    Next intCounter
    ' Save the last added record
    ' The other records will be automatically saved by the New Record action.
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_btnAddRecords_Click:
    Exit Sub

Err_btnAddRecords_Click:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Error Adding Records"
    Resume Exit_btnAddRecords_Click
    
End Sub

As before you will need to change the reference to "tblAutoAddRecordsTest" to the name of your own table.

As alluded to, the routine will not increment the alphabetic part of the ID Number, if you intend to do that as well, start a new thread specifically about 'Incrementing Alphabetic ID Numbers", and include a link to this thread, because the subject, and complexity, would be going way beyond the scope of the original question.

You've not provided sufficient information about how your implementing the ID Number field, but it may be possible that you should have used several fields, two text and one numeric, instead of a combined text field which, as you can see, will lead to all sorts of problems.

If you need any more help on this matter please ensure that you explain your requirements clearly. Using punctuation helps. Providing a copy of your db also helps; you should remove all of the unnecessary components and any sensitive data, before using Compact and Repair and then zipping up the copy. Providing background information about the use of fields that relate to your question is also useful to anyone attempting to help you.

HTH

Tim

 

[bigbigi]

really thz for that
the database is sourced form an excel
the action is dont to show how much spear cable we left
where blank column with a number mean it is not in use

this shit work almost ten years.....
as a summer student helper
what i can do is to do what i can do
but fix it

anyway
i really thz for that
if you guys come to Hong Kong
tell me i will have a meal for you ^_^

 

[Tim L]

You're welcome to the help. I hope the code is working for you.

Regards,

Tim