Hi,</SPAN>
i am new in VBA and Access. i am trying to develop application using forum help. your help is much appreciated.</SPAN>
i am fetching data from MS access table in excel using IPR_ID and displaying it in Userform which works fine but at the same time i would like to update AD_State for same IPR_ID with LOCKED status to lock records for others to View or Edit fetched records.</SPAN>
Once changes are updated in UserForm update IPR_ID with other changes from excel to Access with AD_State UNLOCKED . Sr No field is set as Primary Key in Database.</SPAN>
Below is the Find Records click button code in Find_Rec userform </SPAN>
i am new in VBA and Access. i am trying to develop application using forum help. your help is much appreciated.</SPAN>
i am fetching data from MS access table in excel using IPR_ID and displaying it in Userform which works fine but at the same time i would like to update AD_State for same IPR_ID with LOCKED status to lock records for others to View or Edit fetched records.</SPAN>
Once changes are updated in UserForm update IPR_ID with other changes from excel to Access with AD_State UNLOCKED . Sr No field is set as Primary Key in Database.</SPAN>
Below is the Find Records click button code in Find_Rec userform </SPAN>
Code:
Sub CommandButton1_Click()</SPAN>
Dim cnt As ADODB.Connection</SPAN>
Dim rst1 As ADODB.Recordset</SPAN>
Dim wsSheet1 As Worksheet</SPAN>
'Instantiate the ADO-objects.</SPAN>
Set cnt = New ADODB.Connection</SPAN>
Set rst1 = New ADODB.Recordset</SPAN>
Set wbBook = ThisWorkbook</SPAN>
Set wsSheet1 = wbBook.Worksheets(1)</SPAN>
'Path to the database.</SPAN>
stDB = "R:\Claims\MS Access Database\Claims.accdb"</SPAN>
'Create the connectionstring.</SPAN>
stConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & stDB & ";"</SPAN>
'The 1st raw SQL-statement to be executed.</SPAN>
stSQL1 = "SELECT * FROM Payables_Output where IPR_ID = '" & TextBox1 & "' And AD_State = 'UnLocked'"</SPAN>
'Clear the worksheet.</SPAN>
ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Offset(1).Clear</SPAN>
With cnt</SPAN>
.Open (stConn) 'Open the connection.</SPAN>
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.</SPAN>
End With</SPAN>
With rst1</SPAN>
.Open stSQL1, cnt 'Create the recordset.</SPAN>
Set .ActiveConnection = Nothing 'Disconnect the recordset.</SPAN>
End With</SPAN>
With ThisWorkbook.Sheets(1)</SPAN>
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.</SPAN>
End With</SPAN>
'Release objects from the memory.</SPAN>
rst1.Close</SPAN>
Set rst1 = Nothing</SPAN>
' rst2.Close</SPAN>
' Set rst2 = Nothing</SPAN>
cnt.Close</SPAN>
Set cnt = Nothing</SPAN>
'update AD Status to lock records for others to view and update</SPAN>
stSQL2 = "update Payables_output set Ad_State = 'Locked' where IPR_ID= '" & TextBox1 & "'"</SPAN>
If Not Range("A2").Value = "" Then</SPAN>
ThisWorkbook.Sheets(1).Range("A2").Select</SPAN>
FindRec.Hide</SPAN>
Module3.show_records</SPAN>
Module3.label_Header</SPAN>
Rec_Viewer.Show</SPAN>
Else</SPAN>
MsgBox "No Records Found for IPR " & TextBox1</SPAN>
ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Offset(1).Clear</SPAN>
FindRec.Show</SPAN>
End If</SPAN>
End Sub</SPAN>
