locking data in subform after record save

[RuralGuy]

I'll see what I can put together for you.

[RuralGuy]

Copy the following to a Standard Module named basTempTable

Option Compare Database
Option Explicit
'-- You can change these to match your names
Const MyTempDB As String = "MyTemp.mdb"
Const MyTable As String = "tmpTransactions"
'-- The next name is the name of the copy of your tblTransactions table
'-- that you have manually copied to another name and deleted all of the records and any AutoNumber field.
Const MyStructure As String = "StructureTransactions"
'  This is designed for Access 2003 or before
Public Sub CreateTempDB()
'   Create a temporary external mdb on the local disk so we can kill it later
'   ...and avoid the bloat that MS Access has been known to exibit.

   On Error GoTo Err_CreateTempDB
   Dim Directory As String
   Dim db As DAO.Database
   Dim dbNew As DAO.Database
   Dim tbl As TableDef
   Set db = CurrentDb()
   '-- Remove the link reference to the Temp table if it exists.
   db.TableDefs.Delete "tmpTransactions"
   '-- Create the string for the path where this program resides.
   Directory = CurrentProject.Path & "\"
   '-- Delete the old TempDB, if it exists.
   If Dir(Directory & MyTempDB) <> vbNullString Then
      Kill Directory & MyTempDB
   End If
   '-- Now create the external new mdb file
   Set dbNew = CreateDatabase(Directory & MyTempDB, dbLangGeneral)
   dbNew.Close
   '-- Copy the local table STRUCTURE to our new temporary database
   '--    This Structure has no records and NO AutoNumber field
   DoCmd.CopyObject Directory & MyTempDB, MyTable, acTable, MyStructure
   '-- Attach the tables from the newly created database
   Set tbl = db.CreateTableDef(MyTable)
   tbl.Connect = (";DATABASE=" & Directory & MyTempDB)
   tbl.SourceTableName = MyTable
   '-- Now add this table as a linked table
   db.TableDefs.Append tbl
   db.Close
Exit_CreateTempDB:
   If Not tbl Is Nothing Then
      Set tbl = Nothing
   End If
   If Not db Is Nothing Then
      db.Close
      Set db = Nothing
   End If
   If Not dbNew Is Nothing Then
      Set dbNew = Nothing
   End If
   Exit Sub
Err_CreateTempDB:
   If Err.Number = 53 Then          '-- File not found
      Resume Next
   ElseIf Err.Number = 3265 Then    '-- Referenced object does not exist in this collection
      Resume Next
   ElseIf Err.Number = 75 Then      '-- Path/File access error
      Resume Next
   Else
      MsgBox "Error No:    " & Err.Number & vbCr & _
             "Description: " & Err.Description
      Resume Exit_CreateTempDB
   End If
End Sub
Public Sub DestroyTempDB()
   On Error GoTo Err_DestroyTempDB
   Dim db As DAO.Database
   Set db = CurrentDb()
   '   Remove the reference to the linked table
   db.TableDefs.Delete MyTable
   '   Delete the created mdb file
   Kill CurrentProject.Path & "\" & MyTempDB
Exit_DestroyTempDB:
   If Not db Is Nothing Then
      db.Close
      Set db = Nothing
   End If
   Exit Sub
Err_DestroyTempDB:
   If Err.Number = 53 Then          '-- File not found
      Resume Next
   ElseIf Err.Number = 3265 Then    '-- Referenced object does not exist in this collection
      Resume Next
   ElseIf Err.Number = 75 Then      '-- Path/File access error
      Resume Next
   Else
      MsgBox "Error No:    " & Err.Number & vbCr & _
             "Description: " & Err.Description
      Resume Exit_DestroyTempDB
   End If
End Sub
Public Sub UpdateFromTempDB()
'-- Update the local table from the Temp table if there are any records
   CurrentDb.Execute "Update tblTransactions FROM MyTable"
End Sub

Let me know if you need additional help. I put some notes at the top of the code.

[RuralGuy]

It is pretty simple from here but I'd be glad to help.

[Nixx1401]

Where exactly am I placing this code. Please walk me through

[RuralGuy]

Create a new Standard Module named basTempTable and put *all* of the code in it.

[Nixx1401]

Hi There,

It been a while since I visited this thread because work tasks did not permit me. I am going to attached the db. I copied the code for the module but dont know what else to do. Can you please assist.

[RuralGuy]

I should have time today to look at it.

[Nixx1401]

Hi

I realize that I did not attach the db. I have done so now can you please look at the db for me please

[RuralGuy]

I should have some time today.

[RuralGuy]

The Lookup ComboBox complicates things. Is it important the user see previous entries?