UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Switch From Ado To Dao Recordset, Listbox Doesn't Requery, Access 2016    
 
   
dutsnekcirf
post Feb 1 2018, 11:25 AM
Post#1



Posts: 185
Joined: 31-October 05
From: Las Vegas, NV


I have an unbound form which used to use ADO and SQL to update/modify the database. Since we've upgraded to Access 2016 we're getting "class not registered" errors and in an effort to avoid these issues I've decided to move away from ADO and go back to DAO. I'm making the assumption that since DAO is the default data access method I won't run into class registration issues. So I've updated my code to do so and the new record gets inserted into the table as expected. But I've run into a weird issue. On the form I have a listbox that gets requeried to show the new record. This worked fine before but now it doesn't requery or refresh to show the new record. There is one exception to this though. If I put a break point somewhere in the code and step through to see where it's failing, only then does it actually refresh and I see the new record in the listbox! How on earth could it behave different if I'm debugging as opposed to allowing it to run as written?

Here's the code I'm using. As you can see it's a form that allows users to enter their military experience, but that's not really important here.

CODE
Private Sub cmdMilAdd_Click()

On Error GoTo Err_cmdMilAdd_Click

Dim db As dao.Database
Dim rs As dao.Recordset
Dim strBranch As String
Dim strRank As String
Dim dteJoinDate As Date
Dim dteSeparationDate As Date
Dim strMOS As String

If IsNull(Me.cboBranch) Or IsNull(Me.txtMilJoinDate) Or IsNull(Me.txtMilLastRank) Or IsNull(Me.txtMilMOS) Then
    MsgBox "Please ensure you have provided the Branch, Last Rank, Join Date and MOS before adding your military experience.", vbCritical, "Need information."
    Exit Sub
End If

If CDate(Me.txtMilJoinDate) > Date Then
    MsgBox "Please enter an issue date that falls prior to today's date.", vbCritical, "Incorrect join date."
    Me.txtMilJoinDate.SetFocus
    Exit Sub
End If

If Not IsNull(Me.txtMilSeparationDate) Then
    If CDate(Me.txtMilSeparationDate) > Date Then
        MsgBox "Please enter a seperation date that falls prior to today's date.", vbCritical, "Incorrect separation date."
        Me.txtMilSeparationDate.SetFocus
        Exit Sub
    End If
End If

dteJoinDate = CDate(Me.txtMilJoinDate)
dteSeparationDate = CDate(Me.txtMilSeparationDate)
strRank = Me.txtMilLastRank
strMOS = Me.txtMilMOS
strBranch = Me.cboBranch

Set db = OpenDatabase("FSRDatabase.accdb")
Set rs = db.OpenRecordset("tblMilExperience", dbOpenDynaset)

With rs
    .AddNew
    !EmpNum = Me.EmpNum.Value
    !JoinDate = dteJoinDate
    !SeparationDate = dteSeparationDate
    !LastRank = strRank
    !MOS = strMOS
    !Branch = strBranch
    .Update
    .Bookmark = .LastModified
End With

rs.Close
db.Close

Me.lstMilitaryExperience.Requery  <---Right here is where the listbox is supposed to refresh to show the new record.
Me.cboBranch = Null
Me.txtMilJoinDate = Null
Me.txtMilLastRank = Null
Me.txtMilSeparationDate = Null
Me.txtMilMOS = Null

Exit_cmdMilAdd_Click:
    Exit Sub
    
Err_cmdMilAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdMilAdd_Click

End Sub


If you're interested in the ADO code I was using before here it is:

CODE
Private Sub cmdMilAdd_Click()

On Error GoTo Err_cmdMilAdd_Click

Dim strBranch As String
Dim strRank As String
Dim dteJoinDate As Date
Dim dteSeparationDate As Date
Dim strMOS As String
Dim strSql As String
Dim db As adodb.Connection

If IsNull(Me.cboBranch) Or IsNull(Me.txtMilJoinDate) Or IsNull(Me.txtMilLastRank) Or IsNull(Me.txtMilMOS) Then
    MsgBox "Please ensure you have provided the Branch, Last Rank, Join Date and MOS before adding your military experience.", vbCritical, "Need information."
    Exit Sub
End If

If CDate(Me.txtMilJoinDate) > Date Then
    MsgBox "Please enter an issue date that falls prior to today's date.", vbCritical, "Incorrect join date."
    Me.txtMilJoinDate.SetFocus
    Exit Sub
End If

If Not IsNull(Me.txtMilSeparationDate) Then
    If CDate(Me.txtMilSeparationDate) > Date Then
        MsgBox "Please enter a seperation date that falls prior to today's date.", vbCritical, "Incorrect separation date."
        Me.txtMilSeparationDate.SetFocus
        Exit Sub
    End If
End If

strBranch = Me.cboBranch
strRank = CleanSQLValue(Me.txtMilLastRank)
dteJoinDate = CDate(Me.txtMilJoinDate)
strMOS = CleanSQLValue(Me.txtMilMOS)

If Not IsNull(Me.txtMilSeparationDate) Then
    dteSeparationDate = CDate(Me.txtMilSeparationDate)

    strSql = "INSERT INTO tblMilExperience (EmpNum,  JoinDate, SeparationDate, LastRank, MOS, Branch) " & _
    "SELECT " & Me.EmpNum.Value & ", '" & dteJoinDate & "', '" & dteSeparationDate & "', '" & strRank & "', '" & strMOS & "', '" & strBranch & "';"

Else

    strSql = "INSERT INTO tblMilExperience (EmpNum,  JoinDate, LastRank, MOS, Branch) " & _
    "SELECT " & Me.EmpNum.Value & ", '" & dteJoinDate & "', '" & strRank & "', '" & strMOS & "', '" & strBranch & "';"

End If

Set db = CurrentProject.Connection
db.Execute strSql
Set db = Nothing

Me.lstMilitaryExperience.Requery
Me.cboBranch = Null
Me.txtMilJoinDate = Null
Me.txtMilLastRank = Null
Me.txtMilSeparationDate = Null
Me.txtMilMOS = Null

Exit_cmdMilAdd_Click:
    Exit Sub
    
Err_cmdMilAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdMilAdd_Click

End Sub

This post has been edited by dutsnekcirf: Feb 1 2018, 11:54 AM

--------------------
-------------------------------------------------
Jonathan
-The only way to coast is downhill.
Go to the top of the page
 
cheekybuddha
post Feb 1 2018, 12:13 PM
Post#2


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


Why don't you use the same Insert/Update query in the new version?

Try either:
CODE
' ...
  Dim strSQL As String
' ...
strMOS = CleanSQLValue(Me.txtMilMOS)

If Not IsNull(Me.txtMilSeparationDate) Then
    dteSeparationDate = CDate(Me.txtMilSeparationDate)

    strSql = "INSERT INTO tblMilExperience (EmpNum,  JoinDate, SeparationDate, LastRank, MOS, Branch) " & _
    "SELECT " & Me.EmpNum.Value & ", '" & dteJoinDate & "', '" & dteSeparationDate & "', '" & strRank & "', '" & strMOS & "', '" & strBranch & "';"

Else

    strSql = "INSERT INTO tblMilExperience (EmpNum,  JoinDate, LastRank, MOS, Branch) " & _
    "SELECT " & Me.EmpNum.Value & ", '" & dteJoinDate & "', '" & strRank & "', '" & strMOS & "', '" & strBranch & "';"

End If

CurrentDb.Execute strSQL, dbFailOnError

Me.lstMilitaryExperience.Requery
' ...


Or if you want to use the recordset method:
CODE
strBranch = Me.cboBranch

Set rs = CurrentDb.OpenRecordset("tblMilExperience", dbOpenDynaset)

With rs
    .AddNew
    !EmpNum = Me.EmpNum.Value
    !JoinDate = dteJoinDate
    !SeparationDate = dteSeparationDate
    !LastRank = strRank
    !MOS = strMOS
    !Branch = strBranch
    .Update
    .Bookmark = .LastModified    ' <---What does this do???
End With

rs.Close

Me.lstMilitaryExperience.Requery  
' ...


I'm assuming all this happens within FSRDatabase.accdb. If not then we'll have to do things a little differently.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dutsnekcirf
post Feb 1 2018, 12:44 PM
Post#3



Posts: 185
Joined: 31-October 05
From: Las Vegas, NV


Well that worked. In fact, both of your suggestions worked.

When I was trying to switch back to DAO I was using Microsoft's MSDN library as a code reference: https://msdn.microsoft.com/en-us/library/of...e/ff845624.aspx

You also asked what the ".Bookmark = .LastModified" does. That website has the following to say about it:
QUOTE
The record that was current before you used AddNew remains current. If you want to make the new record current, you can set the Bookmark property to the bookmark identified by the LastModified property setting.
I don't really think I need that in there for my particular case.

I think the reason why it wasn't working for me though is because of the way it was opening the database. Rather than using Currentdb it was probably "reopening" the database in memory outside of the fact that I had it open already. I'm not sure if that makes sense.

Anyway, your code works great. I appreciate it.

UtterAccess saves my bacon again.

--------------------
-------------------------------------------------
Jonathan
-The only way to coast is downhill.
Go to the top of the page
 
cheekybuddha
post Feb 1 2018, 01:01 PM
Post#4


UtterAccess VIP
Posts: 10,347
Joined: 6-December 03
From: Telegraph Hill


yw.gif

>> it wasn't working for me though is because of the way it was opening the database. Rather than using Currentdb it was probably "reopening" the database in memory outside of the fact that I had it open already. <<

Yes, that is what I think too.

But the issue was a timing issue. Since you opened effectively another copy of the database in memory it took more time for the changes to filter back through to your instance from which the code was run.

If you had added DoEvents (perhaps more than once) before the Me.lstMilitaryExperience.Requery line would probably have worked too.

Of course, this is not the preferred way to do it, since there is no need to open the same database again in memory!

The code referenced in the link you mentioned is more generically for DAO, and would be what you want if you were trying to update the db from, for instance, Excel.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 04:56 AM