Can'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

flores - 28 Mar 2007 18:48 GMT

I have an access 2002 database. I use a form to display records basing
resultset = SQLstr$. The form essentially displays records for a given user
on a specific week. Each time the user changes a week date, the SQLstr$
changes and the new set of records are displayed. The form has an edit
button that opens an unbound form (frmSelectProject) to collect edits for a
record. After the input form is OKed, I assign the inputs to the current
record of the displaying form. Editing of the record works fine. After I
close all forms and then quit Access, The LDB file closes as it should. The
problem starts right after I try to open the Access DB file for a second
time. When I lunch the DB file, I see the greeting message (msgbox) that I
just click OK, and then the database does not show and I am send back to file
Explorer where I see the LDB file next to the database file. I cannot delete
any of two files (.ldb or .mdb). I use Ctrl+Alt+Del and find the process of
MSACESS.EXE running. I force it to close, and then I can get back into the
database.

I plan to have this database be shared by about 30 users, but currently this
problem is on my test copy that resides in my C drive and I even tried a
split database. I don't want to release this program if after quitting
Access one time the second time the DB file won't open.

I can infer that the problem lies in the editing of the record. I suspect
the recordset.edit or recordset.update along with record locking may have
something to do. Locking is set to "edited record" and seems to work fine on
the display form.

I guess my question is how I can avoid having the DB file being
un-accessible after the first user.

I need this help badly. Thank you in advance.

Private Sub Get_Record(NewRecord As Boolean)
' display dialog form and assign data to bound record.
''''''''''''''''''''''''''''''''''''''''
' Plan to Pass arguments to the frmSelectProject form:
' Load the form first
' Call and pass variables to Initialize procedure on the frmSelectProject
form.
'
'
Dim Cost_Type As Variant
'Cost Types: 1 = [Receiver CCtr]; 2 = [Rec Int order]; 3 = [Rec WBS-E]
Const DefaultCostType = 3 ' Default cost type when all fields are NULL.

If (NewRecord) Then ' new record needs initialization
' jose you need to pass Dialog Title for New record?
Arguments$ = "NewRecord," & DefaultCostType & ",,," &
Round_Date(Me.Parent.Doc_Date) & ",R&DLBR," & ""
Else ' editing record
Me.Recordset.Bookmark = Me.Bookmark ' Syncronize to Form
'TEST FOR LOCKED RECORD GOES HERE (new records should never be locked)
d1 = Me.RecordLocks
d2 = Me.Recordset.EditMode
On Error GoTo ErrGet_Record
Me.Recordset.edit ' This caoused error 3188; locked record can't be
updated.
' d3 = Me.Recordset.LockType ' Error: property not supported.
With Me.Recordset
Cost_Type = (-1 * Not IsNull(.Fields("Receiver CCtr"))) _
- (2 * Not IsNull(.Fields("Rec Int order"))) _
- (3 * Not IsNull(.Fields("Rec WBS-E")))
If Cost_Type = 0 Then Cost_Type = DefaultCostType ' Default cost
type when all fields are NULL.
RecNumber = Me.CurrentRecord
End With
Arguments$ = RecNumber & "," & Cost_Type & "," & cmbProjectNumber &
"," & Quantity & "," & Doc_Date & "," & Activity_type & "," &
Text_max_50_digits
End If
DoCmd.OpenForm "frmSelectProject", , , , , acDialog, Arguments$ '
acDialog halts code execution until the form is dismissed.

'Code continues only after frmSelectProject is Oked or cancelled by the
user.
With Form_frmSelectProject
If FormIsLoaded("frmSelectProject") Then
dfd = Me.Recordset.EditMode
If NewRecord Then
Me.Recordset.AddNew ' Open a new Record
Me.Recordset("Personnel #") = Me.Parent.Emp_ID 'Required field
Me.Recordset("Send CCtr") = Me.Parent.Send_CCtr_head 'Required
field
Me.Recordset("Entry TimeDate") = Now()
' Me.Recordset.Update
' Me.Recordset.Bookmark = Me.Bookmark 'Syncronize to recordset
' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Else ' Open record for editing
Me.Recordset.Bookmark = Me.Bookmark 'Syncronize to form
(Orininally done above)
Me.Recordset.edit
If IsNull(Me.Recordset("Entry TimeDate")) Then
Me.Recordset("Entry TimeDate") = Now()
End If
End If
Assign_to_Project ' For new records Assign_to_Project is to execute
after saving the record.

For xxx = 1 To 10000
'wait loop
Next xxx

'assigns values to current record in Me form.
Me.Recordset("Doc Date") = Round_Date(.Doc_Date)
Set dt = New DateClass
Me.Recordset("Posting date") =
dt.CurrentMonthEnd(Me.Recordset("Doc Date"))
Set dt = Nothing
Me.Recordset("Quantity") = .Qty
Me.Recordset("Activity type") = .Activity_type
Me.Recordset("Text max 50 digits") = .Comments
Me.Recordset.Update
DoCmd.Close acForm, "frmSelectProject", acSaveNo
Me.Recordset.Bookmark = Me.Bookmark 'Syncronize to recordset
Else
' dialog form was canceled
End If
End With
ExitGet_Record:
Me.AllowAdditions = False
Refresh_and_Size (Me.Parent.ActiveControl) ' Passes subform control
Exit Sub

ErrGet_Record:
If Err.Number = 3188 Then 'Error 3188; locked record can't be updated.
Mess$ = "This entry is being edited by another user" & vbCrLf & vbCrLf _
& "System's eror: " & Err.Number & "--" & Err.Description
MsgBox Mess$, vbInformation, "Edit Record"
End If
GoTo ExitGet_Record
End Sub

Reply to this Message

Larry Linson - 30 Mar 2007 05:18 GMT

Frankly, what you are doing seems a complex and convoluted way to accomplish
adding and editing data. While I can't point to the particular problem, I am
not surprised that you are having problems. It would be, IMNSHO, better to
just work in "normal fashion" with bound forms, allowing users to change the
values in the Controls on the Form, and validating either in the Form, or in
Code, or, to the extent possible, in Field validation of the Table.

I know, from (long-)past experience, that some complex and convoluted
approaches _seem_ like a good idea at the time, but I've found they often
turn out to be Not a Good Idea At All. (That is, "been there, done that, got
the t-shirt".)

But, to be honest, I'd expect the problem to occur while your VBA was
working from code with the Form's Recordset. If it is a bound form, why do
you not just set the values into the Control where that Field is displayed,
even if you collect the values from a different (unbound) Form?

Larry Linson
Microsoft Access MVP

>I have an access 2002 database. I use a form to display records basing
> resultset = SQLstr$. The form essentially displays records for a given
[quoted text clipped - 141 lines]
> GoTo ExitGet_Record
> End Sub

Reply to this Message

flores - 30 Mar 2007 20:28 GMT

Larry,

Thank you for all the good insights.

I inherited this bad, un-normal database, and the original creator is the
controller and he still works for the company. I don't want to trash his
original design, although this may be the best thing to do.

The data I am collecting is for time worked on projects (allocating work
time to projects). This data has two columns: Project_ID and Stat_ID. In
essence these two fields should be one, but due to lack DB design knowledge
they have this unnormal data table. So I use an unbound input form that asks
Project or Stat and gets the related data. The input form comes back with
entries and, as you noticed, I use the form's recordset to assign the values
to the record that is being displayed on a form. Because the subform's
record displays on some the fields, I still have other fields that I need to
directly assign values (common fields that should probably belong on a
different table). I use the form's recordset to assign the values.

Is there a way to assign these values to the subform's record without having
to use it directly in the way I am doing it (some alternative like
me.recordset.currentrecord = rst.currentrecord?). I guess the
Me.recordset("field") syntax could be the cause of my problem.

Than you so much for your help.

> Frankly, what you are doing seems a complex and convoluted way to accomplish
> adding and editing data. While I can't point to the particular problem, I am
[quoted text clipped - 161 lines]
> > GoTo ExitGet_Record
> > End Sub

Reply to this Message