Getting the record ID early

[mjmartin]

MyODBC and Access - one step closer

Ok this is really bugging me (because I just can't figure it out). I'm using Access with MyODBC (an ODBC driver).

Every time I click the new record button, enter some data in and proceed to enter some more data in a sub form in the same form I get '#Deleted'.

Now I've managed to get around this in existing records by creating a subroutine that instances a Timer and issues a Requery:

Sub QueryTimer(tmpid As Integer, tmpForm As Form)
Dim PauseTime, Start, Finish, TotalTime
    PauseTime = 0.25    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        'DoEvents    ' Yield to other processes.
    Loop '
    Finish = Timer    ' Set end time.
    TotalTime = Finish - Start    ' Calculate total time.
    DBRequery tmpid, tmpForm
End Sub

Public Sub DBRequery(tmpid As Integer, tmpForm As Form)
If SuppressMsgbox = False Then MsgBox tmpid
If tmpid = 0 Then Exit Sub
tmpid = tmpid
tmpForm.Requery
DoCmd.ApplyFilter , "FacilityID = " & tmpid
End Sub

This seems to do the trick for existing records (where I know what the unique identifier is), I use this at the end of all sub forms (in AfterUpdate) but for new records I can't do this because I don't know what the new unique ID will be. Is there anyone out there who can help me on this one. I can show you the database if needs be.

Is there a VBA way of... when the user presses the New Record button, I can create the new record (with nothing in it), save it, and then open it up for the user to edit (thus letting me know what the newly created unique ID is)?

I need the access equivalent of the PHP function:

 $tmpID = mysql_insert_id();

I guess.

 

[The_Doc_Man]

Is there a VBA way of... when the user presses the New Record button, I can create the new record (with nothing in it), save it, and then open it up for the user to edit (thus letting me know what the newly created unique ID is)?

Yes, but you have to be careful of the field validation and key qualifiers. Like, "Zero Length Allowed" and "Required" etc. Plus formatting filters if you have have them, or things in the ValidIf table definitions.

If all "mandatory" fields are filled in and in good formats by the time you are ready to create the record and then go back to it, you can do the .AddNew followed by the .Update, then (without advancing the recordset via another .AddNew) do your .Edit - and at that time, the ID field should be filled in and therefore visible.

 

[mjmartin]

ok, solved the famous #Deleted problem!!!

When I say solved I mean completely bodged around the problem

This lovely thread address most of the issues with MyODBC and Access but I followed everything there and still had the error.

But... thanks to a little bit of VBA bodging and trickery I found a solution. It ain't pretty but it works:

I created a VARCHAR(14) in my table (not a Timestamp but it will be used for storing a timestamp). The reason it's a VARCHAR and not a timestamp is simply because I couldn't get Access working with TIMESTAMP().

In the form Current event I put:

Me!Cstamp = GetTimeStamp ' where Cstamp is the name of the hidden field

In my common functions module I have a public function called GetTimeStamp:

Public Function GetTimeStamp() As String
GetTimeStamp = Format(Now, "yyyymmddhhmmss")
End Function

Then on the first field I wanted the user to enter, in the AfterUpdate event I put in the following code:

FieldRequery Cstamp.value, Me

In that same form I have a Private subroutine called FieldRequery:

Private Sub FieldRequery(tmpStamp As String, tmpForm As Form)
tmpForm.Requery 'Requeries the database
Form.Filter = "Cstamp" & " Like '" & tmpStamp & "'"
Form.FilterOn = True
End Sub

I'm sure you all can improve on that in your own code. but this seemed to do the trick for me. What I'm basically doing is entering something that Access 'knows' so when we requery the database we can retrieve that record using that thing that we 'know'. You can do this with random numbers but I think a timestamp is more trustworthy, you could make it even more unique if you wish. In effect the timestamp is similar to a session key.

I hope this might help some of you budding MyODBC and Access folk out there.

Is it possible to rename this thread: 'MyODBC and Access - one step closer' since it's more relevant that way?