I have an access 2000 form linked t

[fwonny]

I have an access 2000 form linked to several mysql tables. These tables have auto increment fields (int,11). However, these auto increment fields do not behave like the access autonumber field. In my bound form, the auto id field will not provide a unique number to the user nor to the child subform. Please tell me there is a quick fix for this! Thanks.

 

[abombss]

No there is not a quick fix. B/C its an ODBC connection the autonumber does not function like it normally would. You need to first store the record in the database, then fetch back the id.

This is why auto numbers are not the best types of fields.

The piece of code below should give you an idea on how to do it. You need to an unbound recordset, which is probably a good idea with linked tables to MySQL anway.

Public function AddRecord() as Long
    Dim rs as DAO.RecordSet
    set rs = currentdb().OpenRecordSet("MyLinkedTable")
    with rs
        .addnew
        !MyColA = "Some Value"
        !MyColB = "Some Other Value"
        .update
        AddRecord = !MyAutoNumber
    end with
    rs.close
    set rs = nothing
end function

abombss