Hi: I am hoping someone can help me with this. I've had multiple iterations and I have a function that sometimes works and sometimes does not.
First a little history: I used to use an update query to do this, but sometimes we would get ODBC failures - I could never figure it out, so I thought I would write something like the following function. It worked, and then it didn't, it works with some equipment UID's and not others, or is intermittent with others at the least. Most recently, I added the rs.movefirst, and had some debug statements, it worked, so I made the mde file, tested again, and it failed. Test again from the unchanged access mdb file, and get ODBC -- Update on a linked table 'tblEquipmentMaster' failed.
As you can see in an earlier revision I inserted adLockPessimistic. There is a form open that does a query on 'tblEquipmentMaster', and for the record
Public Function UpdateEquipPM(EquipID As Integer, PMDate As Date) As Boolean
Dim rs As New ADODB.Recordset
Dim query As String
Dim retvalue As Boolean
query = "select * from tblEquipmentMaster where [equipuid]=" & EquipID
rs.Open query, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
rs.Find ("[equipuid]=" & EquipID)
If rs.EOF Then
retvalue = False
rs!LastPMDate = PMDate
retvalue = True
UpdateEquipPM = retvalue
Any ideas or help would be greatly appreciated. Back end is a SQLServer Express Database.
I did a bit more testing . . . I use a series of forms, but in essence, I have a form: "frmCustomerEquipmentLookupforDispatch" that has a source "select * from tblEquipmentMaster" from it, a button "btnPostPM" opens an unbound form from which users enter PM information (FSR Number, Date, TechnicianID). I run two functions when the submit button is depressed, one to insert into a history table - this works fine, and one to update the equipment table.
When I have "frmCustomerEquipmentLookupforDispatch" using a snapshot for data . . . BUT, if I set it to dynaset, this function works "BUT" then I get messages about can't save a record from frmCustomerEquipmentLookupforDispatch. seems like a catch 22.
The solution, as you might have guessed was a simple one. I had tried to create functions that would work whether or not the form was open . . . However when the form was open, it created the problem - so, simple.
if isopen("frmCustomerEquipmentLookupforDispatch") then
. . .
I should have seen this earlier, but I keep trying to move things away from one form modifying another . . .