Major Form Problem
- Thread starter mcbass1
- Start date
Yep, "Edited record" is selected on Default record locking, and the "Open databses using record-level locking" is checked.
I cleared out the query, inserted three new records and tested, and two of the records seem to be on the same page - can't modify both at the same time.
Help,
Mike
I cleared out the query, inserted three new records and tested, and two of the records seem to be on the same page - can't modify both at the same time.
Help,
Mike
Are you sure that your database's file format is 2002-2003?
Access 2003 defaults to 2000 file format unless specified otherwise, and you can convert it to 2002-2003; Tools -> Database Utilities -> Convert ...
Not 100% sure if this will fix the problem, though...
Access 2003 defaults to 2000 file format unless specified otherwise, and you can convert it to 2002-2003; Tools -> Database Utilities -> Convert ...
Not 100% sure if this will fix the problem, though...
Well, you've got me beaten.
Sorry.
One more thing... very very unlikely, but did you happen to had multiple version of Access, and therefore multiple version of Jet? If so, maybe remove the old version to force 4.0 version. I'm totally shooting in dark right now.
Sorry.
One more thing... very very unlikely, but did you happen to had multiple version of Access, and therefore multiple version of Jet? If so, maybe remove the old version to force 4.0 version. I'm totally shooting in dark right now.
OK, I added the following code to bind the forms to an ADODB.Recordset Object and feel like I'm close:
Option Compare Database
Private cnn As New ADODB.Connection
Private rst As New ADODB.Recordset
Private Sub Form_Load()
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
End Sub
This code is returning an error when the Set Me.Recordset = rst line is hit. The error: Run-time error '-2147467259 (80004005)': Method 'Recordset' of object'_Form_*Dispatcher Screen (PAR)' failed.
After clicking end on the error, I can pull up the two records that seemed to be joined (1 & 2) and update them both at the same time. But, now records 2 & 3 can't be updated at the same time. Also it's not running the on-current event.
Make sense?
Option Compare Database
Private cnn As New ADODB.Connection
Private rst As New ADODB.Recordset
Private Sub Form_Load()
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
End Sub
This code is returning an error when the Set Me.Recordset = rst line is hit. The error: Run-time error '-2147467259 (80004005)': Method 'Recordset' of object'_Form_*Dispatcher Screen (PAR)' failed.
After clicking end on the error, I can pull up the two records that seemed to be joined (1 & 2) and update them both at the same time. But, now records 2 & 3 can't be updated at the same time. Also it's not running the on-current event.
Make sense?
Last edited:
I see you constructing a recordset but I don't see where it's derived from; you need to have something such as a SQL statement to tell where data comes from to fill in the recordset.
I'm not sure why you were able to update both recordsets; this is totally unexpected, as you should have a empty recordset...
I'm not sure why you were able to update both recordsets; this is totally unexpected, as you should have a empty recordset...
Great, SQL - I know less about that than I do about VB. I'm trying to add a WHERE clause for two fields but keep getting an error.
Private Sub Form_Load()
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
strsvo = "SELECT * FROM [*Manual SVO Table]"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strsvo
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
Private Sub Form_Load()
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
strsvo = "SELECT * FROM [*Manual SVO Table]"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strsvo
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
Does the table really begins with an asterisk? I would think this would cause more headaches...
Anyway:
To use expressions from VBA variables, you would construct SQL like this (be sure to note the spacing!!):
Don't forget the delimiters where required.
Anyway:
Code:
SELECT * FROM TableName WHERE Column1 = Expression1 AND Column2 = Expression2To use expressions from VBA variables, you would construct SQL like this (be sure to note the spacing!!):
Code:
strsvo = "SELECT * FROM TableName WHERE Column1 = " & VBAVariable1 & " AND Column2 = " & VBAVariable2 & ";"Don't forget the delimiters where required.
Is the construction of a recordset what I need to do to enable record level locking? Here is my onload event, but it's giving me an error: no value given for one or more required parameters.
Private Sub Form_Load()
svo = Forms![*Dispatch Panel]![SVO#]
svcdate = Forms![*Dispatch Panel]![Service Date]
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
strsvo = "SELECT * FROM [*Manual SVO Table] WHERE [SVO#] = svo"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strsvo
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
End Sub
Private Sub Form_Load()
svo = Forms![*Dispatch Panel]![SVO#]
svcdate = Forms![*Dispatch Panel]![Service Date]
'specify the OLE DB provider and open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\CCSS (test).mdb", "Admin"
End With
strsvo = "SELECT * FROM [*Manual SVO Table] WHERE [SVO#] = svo"
With rst
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strsvo
End With
'assign rst as the recordset for the form
Set Me.Recordset = rst
End Sub
You're getting that error because Jet couldn't parse the SQL... is [SVO#] a numeric field? If so, you shouldn't be setting svo to that; you need to give an actual number. If svo is a VBA variable holding a number, you need to rewrite it like:
You also need to supply the semicolon at end of every SQL statement.
Code:
strsvo = "SELECT * FROM [*Manual SVO Table] WHERE [SVO#] = " & svo " & ";"You also need to supply the semicolon at end of every SQL statement.
Thank you Banana and Pat for your help, but I think recordsets and connections are beyond my scope at this point. I only have a year and a half of self teaching under my belt.
I've created a work around that seems to have solved at least my record locking problem. I found a post by Shadez that spoke about the RecordLocks property and was unaware that setting a form to Edited Record opens a page of records. I changed the settings of all the forms to no locks and was able to pull up all the calls and update, but of course if I access the record from two forms, I get the msgbox that says it's been updated by another user and gives the option to save or dump. To get around this, I just added code to the on click event (before opening the record) to check the [Call Locked] check box, and if it's checked and someone is viewing the record, it opens the record/form read only. The record is completely locked to the 2nd user that tries to access it, but it tells them it's locked and by who.
I haven't tested this yet with multiple users but it seems to be working well. I'm still worried about getting a "Object invalid or no longer set" error on my dispatch panel, but I'm taking baby steps.
Thanks again, and if another glitch pops up (very likely), I'll repost.
Mike
I've created a work around that seems to have solved at least my record locking problem. I found a post by Shadez that spoke about the RecordLocks property and was unaware that setting a form to Edited Record opens a page of records. I changed the settings of all the forms to no locks and was able to pull up all the calls and update, but of course if I access the record from two forms, I get the msgbox that says it's been updated by another user and gives the option to save or dump. To get around this, I just added code to the on click event (before opening the record) to check the [Call Locked] check box, and if it's checked and someone is viewing the record, it opens the record/form read only. The record is completely locked to the 2nd user that tries to access it, but it tells them it's locked and by who.
I haven't tested this yet with multiple users but it seems to be working well. I'm still worried about getting a "Object invalid or no longer set" error on my dispatch panel, but I'm taking baby steps.
Thanks again, and if another glitch pops up (very likely), I'll repost.
Mike
Last edited:
