I have a Front end database with a SQL Server backend.
All I wanted is for the Record# to be populated on Lost focus of Opened by.
Our SQL folks wrote this code: (this is fine)
Code: Select all
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
[txtDATE_ISSUE_OPENED].SetFocus
[txtDATE_ISSUE_OPENED].Text = Format(Date, "MM/DD/YYYY")
End Sub
Code: Select all
Private Sub cboOPENED_BY_LostFocus()
Dim objRec
Dim objConn
Dim cmdString
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=dcpwdbs623;Initial Catalog=MCA_ELIGIBILITY;Integrated Security=SSPI;"
objConn.Open
cmdString = "SELECT ISNULL(MAX(ISSUE_ID), 0) AS ISSUE_ID FROM ISSUES"
Set objRec = objConn.Execute(cmdString)
If objRec.EOF = True Then
txtRECORDNUM.SetFocus
txtRECORDNUM.Text = ""
cboOPENED_BY.SetFocus
Else
txtRECORDNUM.SetFocus
txtRECORDNUM.Text = objRec!ISSUE_ID + 1
cboOPENED_BY.SetFocus
End If
objConn.Close
cboHMO.SetFocus
End Sub
Code: Select all
Private Sub cmd_CloseForm_Click()
On Error GoTo Err_cmd_CloseForm_Click
Dim objRec
Dim objConn
Dim cmdString
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=dcpwdbs623;Initial Catalog=MCA_ELIGIBILITY;Integrated Security=SSPI;"
objConn.Open
cmdString = "SELECT ISNULL(MAX(ISSUE_ID), 0) AS ISSUE_ID FROM ISSUES"
Set objRec = objConn.Execute(cmdString)
If objRec.EOF = True Then
txtRECORDNUM.SetFocus
txtRECORDNUM.Text = ""
cboOPENED_BY.SetFocus
Else
txtRECORDNUM.SetFocus
txtRECORDNUM.Text = objRec!ISSUE_ID + 1
cboOPENED_BY.SetFocus
End If
objConn.Close
DoCmd.Close acForm, Me.Name
Exit_cmd_CloseForm_Click:
Exit Sub
Err_cmd_CloseForm_Click:
MsgBox Err.Description
Resume Exit_cmd_CloseForm_Click
End Sub
In addition, we cannot do a CTL-F in the Record# field. (Edit mode? Little pencil upper left corner?) I think I was better off doing this myself.
Is there a better way to do this?
Thank you for any input. Since I'll be retiring, the company is turning over all of our Access database to the "SQL Server pros".
Michael