Access Database-SQL SERVER backend

[Michael Abrams]

Once again, our lovely IT folks have me pulling what little hair I have left.

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

Here's what I'd like to understand:

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

And then this:

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

Somehow this seems a bit overkill.
In addition, we cannot do a CTL-F in the Record# field. (Edit mode? Little pencil upper left corner?)

Picture1.png

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

[HansV]

Is ISSUES the record source of the form?

[Michael Abrams]

Yes

[HansV]

So I assume that your Access database has a link to the ISSUES table.
In that case. I see no reason to open a connection to the SQL server database in the code. Simply refer to the (linked) table.

Also, it isn't necessary to set focus to a text box to change its value. Instead of for example

Code: Select all

        txtRECORDNUM.SetFocus
        txtRECORDNUM.Text = ""

use

Code: Select all

        txtRECORDNUM = Null

[Michael Abrams]

So, change to :

Code: Select all

Private Sub cboOPENED_BY_LostFocus()

txtRECORDNUM = Null

End Sub

At what point is the Record# populated?

[HansV]

You still have to use code to increment the record number, for example

txtRecordNum = DMax("ISSUE_ID", "ISSUES")+1

[Michael Abrams]

I apologize for doing this again (me not being clear)

This: (Do we get rid of the rest of the code that IT supplied?)

Code: Select all

Private Sub cboOPENED_BY_LostFocus()

txtRecordNum = DMax("ISSUE_ID", "ISSUES")+1

End Sub

and

Code: Select all

Private Sub cmd_CloseForm_Click()

DoCmd.Close acForm, Me.Name

End Sub

And add the error code.

[Michael Abrams]

Geez, almost forgot this:

We cannot filter CTRL-F the record# field.

[HansV]

Why do you want to update the record number field in the lost focus event? I'd do it in the Before Update event.

[HansV]

I have no idea why you cannot search or filter the record number field...

[Michael Abrams]

These SQL pros are killing me. I might as well write my own damn code and just let them translate to the SQL server.

Thank you Hans for your patience.

I am losing mine with the company pros.

The only thing I would like to understand, is why the pencil/edit mode? It seems that may be preventing the filter. Any form properties I should look at?

Sorry for taking up so much of your time.

[HansV]

The Form_Open code modifies the record number, so the record is immediately "dirty".

[Michael Abrams]

I am still messed up with this. I have attached print screens of a smaller database the SQL pros converted.
The first is the one they produced - the second one is the one I gave them to convert.
I supplied the 2 forms with the code behind them.
Sorry I can't let this go

[HansV]

I don't see code that would populate the record number in either of those...

[HansV]

Perhaps Sequential Numbering on the blog of Access MVP Scottgem is useful.

[Michael Abrams]

That's why I have to believe there is a property either in the table or the form that triggers the Record#.
To be honest - every database I've created the Record# is populated.

I mean it is the same exact code and one triggers the Rec# the other doesn't

I'll check out Scottgems link.

Thank you Hans.

[HansV]

Is the record number an AutoNumber field in the Access version?
Or does it have a formula in its Default Value property?

[Michael Abrams]

AutoNumber

No default value

[HansV]

I'm afraid I don't know without seeing the database...