Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Looping through records

    Hi there - I'm doing a database that will need to link with a Paradox database. To accomplish this I need to make a certain field in Access.

    I basically have a table that contains a persons first name and surname.

    I need to create another field called ID Make that takes the first six characters of the first name, adds in a hyphen and then adds in the first character of the surname.

    I can do this easily with a query like:

    Left([First Name],6) & "-" & Left([Surname],1)

    For example the name William Gates would be turned into:

    Willia-G

    Now here comes the tricky bit, if there is another William Gates I would get another Willia-G reference but at this point I need it to add a '1' at the end so I would get:

    Willia-G1

    If there was a third I would need Willia-G2.... and so on.

    What I could do with is some kind of script that looks at each record in the table and then makes the appropriate ID.

    I'd appreciate very much any help anyone can give me.

    At the moment I just have a test table called tbl_test

    Field: First Name
    Field: Surname
    Field: ID Make

    Thanks very much -

  2. #2
    Join Date
    Nov 2009
    Posts
    49
    The easiest way I can think to do this is to create a simple form with 1 textbox for Firstname("txtFirst") and another for Surname ("txtSur"), then a button to add.

    Then you can click on the button with the following coding:

    Dim db as DAO.Database
    Dim rs as Recordset
    Dim strID as String
    Dim x as Integer
    Dim strFirst as String
    Dim strSur as String
    Dim sql as String

    DoCmd.SetWarnings(False)
    Set db = Currentdb
    Set rs = db.OpenRecordset("tbl_test", dbOpenDynaset)

    strID = Left(txtFirst.Value,6) & "-" & Left(txtSur.Value,1)
    x = 0
    rs.MoveFirst

    Do While Not rs.EOF
    If rs![ID Make] = strID Then
    x = x + 1
    End If
    rs.MoveNext
    Loop

    If x > 0 then
    strID = StrID & x
    End If

    strFirst = "'" & txtFirst.value & "'"
    strSur = "'" & txtSur.value & "'"
    strID = "'" & strID & "'"
    sql = CurrentDb.Execute "INSERT INTO tbl_test ([First Name], Surname, [ID Make]) " & _
    "Values(" & strFirst & ", " & strSur & "," & strID & ")"
    DoCmd****nSQL sql

    DoCmd.SetWarnings (True)

    This ofcourse relies upon you using a form. I think this can be modified to fit a table, but I can't seem to get it to work Sorry.

    Phil

  3. #3
    Join Date
    Jan 2010
    Posts
    3

    Thanks !!

    Thanks Phil - much appreciated !!

  4. #4
    Join Date
    Nov 2009
    Posts
    49
    Lol I wouldn't thank me yet, you haven't tried it yet

    I wrote this from memory, so haven't tried it in VB yet. So if you get an error then its probably me.

    Phil

Posting Permissions

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