Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    10

    Unanswered: Auto Number issue

    Ok guys and girls, I have 2 problems. 1) I work for this company that had me update and convert their database (Filemaker Pro) to Access. Here's the problem, each record has to have the same number in Access as it did in Filemaker Pro, that's easy. The hard part is that each record in Filemaker Pro is not in sequential order because they were deleted or what not (For example, 1 2 3 4 6 8 12 13 14 21). When I transfer them into Access they are the same number, BUT, on the form where they are able to create a new record, I want the autonumber to work. Maybe check for the last number in the table then add 1. I am not very good at VB so if anyone could help, it would be greatly appreciated.

    2) I want to send an email to someone when a record has been deleted. I am able to send and entire table, 1200 record, but i can't figure out how to send just the one record.

    Thanks guys/girls I know I came to the right place.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe something like this?
    - not autonumber.
    - provides sequential numbers
    - simultaneous-call safe in a multiuser environment
    (it is also DAO, but the process is the same in ADO)

    the demo does some fluff-stuff showing references in a form. the sequential number code is in the module.

    for the mail - if simple text is all you need try recordset -> textstring -> mail.
    DAO-how (but ADO equivalent exists) and assuming your db 'knows' the ID of the deletion
    dim dabs as dao.database
    dim recs as dao.recordset
    dim strSQL as string
    dim strMsg as string
    strSQL = "SELECT this, that FROM yourTable WHERE ID = " & theKnownID & ";"
    set dabs = currentdb
    set recs = dabs.openrecordset(strSQL)
    with recs
    strMsg = "Someone deleted " & the knownID & vbcrlf & "this=" & !this & vbcrlf & "that=" & !that
    end with
    set recs = nothing
    set dabs = nothing

    izy
    Last edited by izyrider; 07-09-06 at 06:22.
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You can introduce an autonumber to replace your current current field as follows:

    - Create a new table with the same structure as your old but with an extra new field which is an autonumber field.

    - Add a load of records to this table equal to the last record number in your original data. So in your example you should add 21 records. Make sure you only fill in data for the autonumber field. I find using Excel to generate a block of data then copying and pasting is a quick way to populate your table. In fact you can simply create a single column on 1's in Excel (21 for your example) and copy them, then paste by right-clicking on the record selector at the left of the table row (and pasting). The 1's will be converted to the autonumber sequence.

    -Create and run a query to match your old and new table (match the new auto number field with the old key field) and update the relevant records in the new table with the value from your data

    -create and run a query to delete the records in the new table where your old key field is null (thus deleting records 5,7,9,10... in your example).

    -delete the old key field from the new table

    -rename the autonumber field to that of your choice.

    Your autonumber field will then continue as you would expect of an autonumber and not use previously deleted values.

    It guess there's an easier way but this is pretty straight forward I think.

    hth
    Chris

  4. #4
    Join Date
    Jun 2006
    Posts
    10
    You guys are awesome, I fixed the random number problem, but I can't fix the email problem. I have a form named Active Database (Close), and a query named Active Database. On the form there is a button named CLOSE, when I press CLOSE I want it to send that record ONLY, to an email. The problem is that is sends the entire query to the email.
    Thanks

  5. #5
    Join Date
    Jun 2006
    Posts
    10
    Anyone know?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you say "that record"
    ?? which record.
    the record on the form ??

    then maybe loop thru the controls:
    Code:
        Dim aCtl As Control
        Dim aFrm As Form
        Dim aStr As String
        Set aFrm = Me
        For Each aCtl In aFrm.Controls
            If aCtl.ControlType = acTextBox Then
                aStr = aStr & aCtl.Name & "=" & aCtl & vbCrLf
            End If
        Next
        MsgBox aStr
    apart from txtboxes, other potentially interesting control contants include:
    Code:
    acCheckBox	Check box
    acComboBox	Combo box
    acListBox 	List box
    acOptionButton	Option button
    acOptionGroup	Option group
    acTextBox	Text box
    izy
    currently using SS 2008R2

Posting Permissions

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