Replies

If you do have multiple records on a page, instead of using the On Format event of the page, use the On Format event of the Detail section (right click the detail bar in design view of the report, select properties, click the event tab and you should see the On Format event).

I would tend to use the cdate() function rather than the format() function (although the format function does work)

Just as an alternative really I guess

cdate(Mid$([FullLineField],25,6))

Cheers

Jayden

I’d say that your ‘Invalid Use of Null’ error is occuring when you try to set your ‘field9’ variable (and the value in the rs.field(“Ct-St-Zip”) is null).

To alleviate this you could change the line to

Field9 = Trim(Nz(Rs.Fields(“Ct-St-Zip”), “”))

This type of error shouldn’t occur on the lines for variables field2-8 because you have actually declared these variables as variants rather than strings.

Dim Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9 As string

In the above line you are only setting Field9 to a string variable, and the rest of the variables to the type ‘variant’. This is why you wouldn’t get an error on fields2-8 (because a variant data type can hold a null value).

You need to explicitly sate the variable type for each variable even if they are on the same line… ie.

Dim Field2 as String, Field3 As String, Field4 As String, Field5 As String, Field6 As String, Field7 As String, Field8 As String, Field9 As String

Cheers

Jayden

Yay,

Your comments got me thinking (especially about the Recordsetclone causing the Current Event to fire)…so I then found the problem / cause of my query, thanks. It is certainly the Recordsetclone that is causing Access to run the Current Event again, then return to the place where it left off (thereby giving the double ‘CurrentFinish’). This may certainly explain some of the record locking issues.

You’ve been a great help, thanks.

PS: I would never have though that Access would ‘nest’ a Current Event within another Current Event so to speak….don’t we learn something everyday!

Cheers

Jayden

Hi

This one has me stumped. To cut a long story short, I am having locking issues in an Access database that two users are using at the same time. I can’t seem to find the reason that records are locking, there don’t seem to be any places where they should be being locked. Investigating this problem led me to look at what is happening during the OnCurrent and BeforeUpdate events of the data entry form.

The databases that users are using are distributed MDE files (with linked tables back to a central .mdb file).

In order to try and figure out what is happening between the both of them, at various stages during the OnCurrent and BeforeUpdate routines, I write information to table to try and track where each user is at when the locking occurs.

I’ve noticed however that Access is ‘repeating’ parts of code that are NOT part of a loop (without repeating the parts of code the preceed them. Here is some output from the table that I am writing to.

Only one user (charlotteb) is logged into the database when this is happening.

User	Position	TimeStamp	Order
CharlotteB	CurrentFinish	25/10/2001 17:02:21	31209
CharlotteB	CurrentFinish	25/10/2001 17:02:21	31208
CharlotteB	Current3	25/10/2001 17:02:21	31207
CharlotteB	Current2	25/10/2001 17:02:21	31206
CharlotteB	Current	25/10/2001 17:02:21	31205

This table is showing the output from a function in reverse order (ie. the most recent event at the top).
Notice how the ‘CurrentFinish’ output follows itself twice (without the Current1, 2, 3 etc).
Next I will paste the event subroutine that is writing to the table
———————————————————————————————————-
Private Sub Form_Current()
Dim tempRecBookmark As Long

DebugLog “Current” ‘Degugger for LockedRecords

If IsNull(Me![cboProviderID]) Then
bExistingRecord = False
‘Me![cboProviderID].Locked = False
Else
bExistingRecord = True
‘Me![cboProviderID].Locked = True
End If

DebugLog “Current2” ‘Degugger for LockedRecords

If IsNull(Me![cboDisallowedReason]) Then
Me![txtPSDate].Visible = False
Else
Me![txtPSDate].Visible = True
End If

DebugLog “Current3” ‘Degugger for LockedRecords

‘ this is for going back to the disallowed claim that you just entered
‘ in the beforeUpdate event of the form, assign the [claimID] to lngdisallowRecID
If lngdisallowRecID 0 Then
DebugLog “Current4” ‘De###### for LockedRecords
tempRecBookmark = lngdisallowRecID
lngdisallowRecID = 0
Me.RecordsetClone.FindFirst “[ClaimID] = ” & tempRecBookmark
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

DebugLog “CurrentFinish” ‘Degugger for LockedRecords
End Sub
——————————————————————————————————-

At various points in the subroutine, you will notice that the subroutine calls a function ‘DebugLog’ and passes a string variable. This function writes one line (using DAO) to the table shown above.

Note how it is theoretically impossible for the line ‘CurrentFinish’ to be written twice in a row.

I have no answers for this – and I suspect that Access is doing something that it shouldn’t be. Has anyone got any insight, or experienced something similar?

Your help or opinions are much appreciated.

Cheers

Jayden
Access 2000 SR-2
Jet4 SP5

HI Charlotte,

Thanks for the comments. I completely agree with you. I also apologise for it being hard to follow.

I guess the 2 points that I make are

1. Notce in the table of records that I presented the CurrentFinish string is written to the table two records in a row

2. If the OnCurrent event was firing twice in a row, it would still have to run through the sections of the OnCurrent Event that write Current, Current2, Current3 to the table before it could write CurrentFinish. It doesn’t perform the parts of the OnCurrent Event that it should prior to the ‘CurrentFinish’ stage.

Cheers

Jayden

My solution may not be the most elegant, but it works

1. In the table that contains the data create a new ‘yes/no’ field (called it something like ‘FLAG’)

2. Create a new module

3. Add the following Code to the module (replace the , and with the names of the table you wish to search and the field you wish to search and use as the flag.

4. Run this routine. VIOLA, all the fields that have an * at the end of them are CHECKED TRUE in the flag field!

Sub SearchAst()
Dim rstSearchTable As Recordset
Dim strFieldSearch As String
Dim strFieldFlag As String

Set rstSearchTable = CurrentDb.OpenRecordset(“”, dbopentable)
strFieldSearch = “”
strFieldFlag = “”

With rstSearchTable
Do While Not .EOF
If Right(.Fields(strFieldSearch), 1) = “*” Then
.edit
.Fields(strFieldFlag) = True
.Update
End If
.MoveNext
Loop
End With
End Sub

You are a genius! Thanks, I can’t believe that it was in front of me the whole time.