Re: VBA no longer works when linked to SQL server tables

Hi Rick, code is posted below:
ive tried converting it to ado too and i get the same problem, clicking on
the submit button does nothing, no errors, just nothing happens. however i
have similar coding written in DAO on other forms that works fine that uses
recordsets to sum up values from tables and display them in fields, although
they are not updating existing fields as this code is trying to do. Ive tried
dbseechanges too and that makes no difference either, and again no errors.

im confused (.com!)

Thanks,

Rhys.

Private Sub Submit_Click()
On Error GoTo Err_submit_Click

Me.Expense.Enabled = False

Dim rstemp As Recordset
Dim dls As Date
Dim straddress As String
Dim strbody As String
Dim strsubject As String
strsubject = "End of Month Submission from" & " " & loginname & "" & " " &
"for" & " " & Me.submitmonth & " " & Me.submityear
strbody = "Expenses:" & " " & "£" & Me.expenses & vbCrLf & "Available
Hours:" & " " & Me.totalhours & vbCrLf & "Hours Worked:" & " " &
Me.availablehours & vbCrLf & _
"TOIL @ Start of Month:" & " " & Me.toil & vbCrLf & "TOIL Accrued During
Month:" & " " & Me.toilaccduringmonth & vbCrLf & _
"New TOIL Figure:" & " " & Me.toilendofmonth
Set rstemp = CurrentDb.OpenRecordset("tblusers")
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname

If rstemp.NoMatch Then
rstemp.AddNew
rstemp!loginname = loginname
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!toil = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "beverley.sheppard@xxxxxxxxxxxxx", ,
, strsubject, strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.toil = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.toil.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
Else
If rstemp!datelastsubmitted < currentdatesubmit Then
rstemp.edit
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!toil = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "beverley.sheppard@xxxxxxxxxxxxx", ,
, strsubject, strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.toil = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.toil.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
'Else
'response = MsgBox(("You cannot submit a month pior to the last
submission date."), vbOKOnly)
'Me.currentdatesubmit.SetFocus
End If
End If

rstemp.close
Set rstemp = Nothing

Exit_submit_Click:
Exit Sub
Err_submit_Click:
'MsgBox Err.Description
Resume Exit_submit_Click
End Sub


"Rick Brandt" wrote:

Rhys Davies wrote:
Hi Rick, there is a primary key on the table. If i amend information
in a form manually it updates the tables in SQL (i.e. go into a form
and change the date fields that im trying to update viat the code.
however i cant amend the information directly within SQL, after i
execute the changes it sets them back to what they were, i dont know
much about SQL at the moment to know if this is by design. I dont
know if SQL rejects the connection via the code ive written and
subsequently refuses to update the tables, like it does when im
directly in SQL itself?

Thanks for your help, any further ideas would be greatly appreciated!

There are case where Recordsets against ODBC sources need to be opened with
the dbSeeChanges option, but if you need that I would expect you to get an
error message stating that you do.

Do you get any error messages? Have you turned error handling off such that
error messages are not being displayed?

If you can't get it going post your code.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



.

Quantcast