save data from form to text file.

I have about 14 concurrent user using an access form on their desktop.  They keep locking each other out of the database. The database is split with the frontend ( forms, queries, reports and some tables used to feed dropdown lists) on the PC.  The data tables reside on the network.  I form is currently bound to the backend tables.  I would like to make the form unbound with a submissions that would insert the form record info the a text file,  then link the text file to the backend.  This should eliminate the concurrent user conflicts.   The form only has 4 elements so I think that is the best solution.


Thoughts
eyes59Asked:
Who is Participating?
 
milduraitCommented:
You would need to create VBA subs to read/write records, and fire getRecord from a listbox and save record from a command button.
Example below using genering table/control names.

Sub GetRecord()        
        Dim rs as dao.recordset
        set rs = currentdb.openrecordset("SELECT * FROM tblRecord WHERE RecID=" & NZ(me.lboRecordID,0))
        With rs
             If .eof then
                     me.txtRecordName.value = .fields("sRecordName").value
                     me.txtRecordNo.value = .fields("sRecordNo").value
                     me.txtRecordDate.value = .fields("dRecordDate").value
                     me.chkRecordActive.value = .fields("bRecordActive").value
             Else
                     me.txtRecordName.value = .fields("sRecordName").value
                     me.txtRecordNo.value = .fields("iRecordNo").value
                     me.txtRecordDate.value = .fields("dRecordDate").value
                     me.chkRecordActive.value = .fields("bRecordActive").value
             End If
         End with
         rs.close
         set rs = nothing
End Sub

Sub SaveRecord()
         Dim rs as dao.recordset
        set rs = currentdb.openrecordset("SELECT * FROM tblRecord WHERE RecID=" & NZ(me.lboRecordID,0),dbOpenDynaset,dbSeeChanges)
        With rs
              If .eof then .addnew else .edit
              .fields("sRecordName").value = me.txtRecordName.value
              .fields("iRecordNo").value = me.txtRecordNo.value
              .fields("dRecordDate").value = me.txtRecordDate.value
              .fields("bRecordActive").value = me.chkRecordActive.value
              .update
         End with
         rs.close
         set rs = nothing
         me.lboRecordID.requery
         me.lboRecordID.value = null
End Sub
                     


0
 
eyes59Author Commented:
THANKS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.