Results 1 to 15 of 15

Thread: serial (2k)

  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    serial (2k)

    hello all .
    I have a table contain 3 fealds , feald called id it is numric , feald from it is a text , feald to it is a text . i put that in the table
    id from to
    213 ......... Ga445661 .......... Ga445669
    is there any code or query to make searil from Ga445661 to Ga445669
    i think i shoul add another feald i put in it 10 that count of the numbers
    i hope to expaln right .
    Ashraf

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    I take it that you want to generate serial numbers Ga445661, Ga445662, ..., up to and including Ga445669.

    - Where would you like these serial numbers to be stored? In a new table, or in an existing table?
    - What should the field containing the serial numbers be called?
    - What should be done with the ID field?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    Thanks u Hans
    i want it in a new table . and only thesse feald id , from begaun the first no and from end with the last no . 10th
    Ashraf

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    Hello Ashraf,

    I know that English is difficult for you, but that makes it difficult for us to help you! It is still not clear from your description what you mean, so I have to guess.

    In the following code, I have assumed that
    1) you have created a table tblInfo with fields Id, From and To. This table contains the information about the serial numbers.
    2) you have created a new table tblSerial with fields Id and Serial; this table will be filled.

    You will have to substitute the names you are using, and to adapt the code to your situation. The code uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    Sub CreateSerial()
    Dim dbs As DAO.Database
    Dim rstInfo As DAO.Recordset
    Dim rstTarget As DAO.Recordset
    Dim varId As Variant
    Dim lngFrom As Long
    Dim lngTo As Long
    Dim i As Long

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rstInfo = dbs.OpenRecordset("tblInfo", dbOpenDynaset)
    Set rstTarget = dbs.OpenRecordset("tblSerial", dbOpenDynaset)

    varId = rstInfo!Id
    lngFrom = CLng(Right(rstInfo!From, 6))
    lngTo = CLng(Right(rstInfo!To, 6))

    For i = lngFrom To lngTo
    rstTarget.AddNew
    rstTarget!Id = varId
    rstTarget!Serial = "Ga" & Format(i, "000000")
    rstTarget.Update
    Next i

    ExitHandler:
    On Error Resume Next
    rstInfo.Close
    rstTarget.Close
    Set rstTarget = Nothing
    Set rstInfo = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    Thank u dear
    i do a new tables with ur name that u put , but where i put the code
    Ashraf

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    You can put the code in a module:
    - Activate the Modules tab of the database window.
    - Click New.
    - Paste the code into the module that appears.

    If you want to test the code, first make sure that the names of the tables and fields have been changed to the names of the tables and fields you are using. Then click somewhere in the code and press F5.

    If you want to make the code easy to run, create a form with a command button, and call the procedure from the On Click event of the command button.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    i test it already work but can not call it in a from , nothing apeer ai the expression bulder

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    Don't use the expression builder, but make an event procedure. Say that your command button is named cmdSerial. The On Click event procedure would be

    Private Sub cmdSerial_Click()
    CreateSerial
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    not worl also but i do it
    i put at first
    Function CreateSerial()
    and that
    Exit Function
    it work very good
    many thanks dear Hans
    i know that i make u tird , so thank u very much
    Ashraf

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    MR. HANS
    i know my mistak & prepard it .
    but when i add another line in the table tblInfo like that
    id from to
    213 Ga445661 Ga445669
    214 Rg445661 Rg444669
    when i press at buttom in the from it add the frist line only .
    ant i will be add many lins in another time .
    so i want when i press a buttom it add all the lines first from Ga445661 to Ga445669 then from Rg445661 to Rg444669
    Thanks advanced
    Ashraf

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    Hello Ashraf,

    I have attached a text file with a modified version of the code. It now loops through the records of the table.
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    Thank U very much
    Ashraf

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    sorry dear but there r small mistak in that line
    rstTarget!Serial = " Ga" & Format(i, "000000")
    u will found that Ga will repete with all records
    "Ga " will change like that
    id from to
    213 Ga445661 Ga445669
    215 BD332215 BD332220
    Thanks
    Ashraf

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: serial (2k)

    Ashraf,

    Please excuse me, I had overlooked that detail. I have attached a corrected version of the code. I have assumed that the "prefix" will always consist of 2 characters, and that the rest is numeric.

    I have added a string variable strPrefix. This is set to Left(rstInfo!From, 2), and used in place of "Ga". As usual, you will have to adapt this for your situation.
    Attached Files Attached Files

  15. #15
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: serial (2k)

    u r very clever
    many thanks
    Ashraf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •