Continuous Form, how to add/update records with external connection
- by Mohgeroth
EDIT After some more research I found that I cannot use a continuous form with an unbound form since it can only reference a single record at a time. Given that I've altered my question...
I have a sample form that pulls out data to enter into a table as an intermediary.
Initially the form is unbound and I open connections to two main recordsets. I set the listbox's recordset equal to one of them and the forms recordset equal to the other.
The problem is that I cannot add records or update existing ones. Attempting to key into the fields does nothing almost as if the field was locked (Which it is not). Settings of the recordsets are OpenKeyset and LockPessimistic.
Tables are not linked, they come from an outside access database seperate from this project and must remain that way. I am using an adodb connection to get the data. Could the separation of the data from the project be causing this?
Sample Code from the Form
Option Compare Database
Option Explicit
Private conn As CRobbers_Connections
Private exception As CError_Trapping
Private mClient_Translations As ADODB.Recordset
Private mUnmatched_Clients As ADODB.Recordset
Private mExcluded_Clients As ADODB.Recordset
//Construction
Private Sub Form_Open(Cancel As Integer)
Set conn = New CRobbers_Connections
Set exception = New CError_Trapping
Set mClient_Translations = New ADODB.Recordset
Set mUnmatched_Clients = New ADODB.Recordset
Set mExcluded_Clients = New ADODB.Recordset
mClient_Translations.Open "SELECT * FROM Client_Translation" _
, conn.RBRS_Conn, adOpenKeyset, adLockPessimistic
mUnmatched_Clients.Open "SELECT DISTINCT(a.Client) as Client" _
& " FROM Master_Projections a " _
& " WHERE Client NOT IN ( " _
& " SELECT DISTINCT ClientID " _
& " FROM Client_Translation);" _
, conn.RBRS_Conn, adOpenKeyset, adLockPessimistic
mExcluded_Clients.Open "SELECT * FROM Clients_Excluded" _
, conn.RBRS_Conn, adOpenKeyset, adLockPessimistic
End Sub
//Add new record to the client translations
Private Sub cmdAddNew_Click()
If lstUnconfirmed <> "" Then
AddRecord
End If
End Sub
Private Function AddRecord()
With mClient_Translations
.AddNew
.Fields("ClientID") = Me.lstUnconfirmed
.Fields("ClientAbbr") = Me.txtTmpShort
.Fields("ClientName") = Me.txtTmpLong
.Update
End With
UpdateRecords
End Function
Private Function UpdateRecords()
Me.lstUnconfirmed.Requery
End Function
//Load events (After construction)
Private Sub Form_Load()
Set lstUnconfirmed.Recordset = mUnmatched_Clients //Link recordset into listbox
Set Me.Recordset = mClient_Translations
End Sub
//Destruction method
Private Sub Form_Close()
Set conn = Nothing
Set exception = Nothing
Set lstUnconfirmed.Recordset = Nothing
Set Me.Recordset = Nothing
Set mUnmatched_Clients = Nothing
Set mExcluded_Clients = Nothing
Set mClient_Translations = Nothing
End Sub
