Runtime error 2162 - intermittent

[fat controller]

I am really confused now - I have some code which works perfectly well for the vast majority of the time, however once in a while it will throw its toys out of the pram and give me an error message 'Runtime Error 2162' and states that this is due to an error in the FindRecord Statement; when debugged, it highlights the line of code (below) which is the FindRecord Statement:

DoCmd.FindRecord EmpNo, acEntire, , acSearchAll, False, acCurrent

Yet, I can see nothing wrong with this code? Surely if the code was duff, it wouldn't work at all?

The problem seems more likely to happen if the database has not long been opened, if that makes any sort of difference?

I'd be grateful for any help that can be given, as I am not keen on releasing the database into use knowing that its a bit buggy.

 

[fat controller]

I think I have solved it by adding a 1 second pause to the code just as it starts (and well before it gets to the FindRecord command) - although I have no idea why this would make a difference?

EDIT - nah, it didn't. Logged out for a couple of minutes and logged back in and the problem is still there. So frustrating, not least because it is not happening all the time.

 

[MarkK]

One problem with DoCmd is that it doesn't specify an object on which to operate, so it could be that you need to DoCmd.SelectObject or explicitly .SetFocus to something first.

Like, if your DoCmd code is on FormA, and can be (but isn't always) triggered from FormB, then when the DoCmd code runs and FormB is the currently selected object, maybe then it fails. But this common with DoCmd operations, that sometimes the current object is ambiguous or not what you expect.

 

[fat controller]

Thanks @MarkK

I maybe should have shown more of the code to explain more, as the line immediately before the FindRecord line is setting the focus to a bound text box.

Dim EmpNo
Dim T1 As Variant
Dim T2 As Variant

EmpNo = Me.cboSurnameCheck.Column(2)

If DCount("Employee_Number", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'") > 0 Then
MsgBox "An active training record already exists for this employee.  Please select the option to Amend or Edit Existing Training Record.", vbOKOnly

T1 = Now()
T2 = DateAdd("s", 1, T1)
Do Until T2 <= T1
T1 = Now()
Loop

Me.txtEmpSearch.SetFocus

DoCmd.FindRecord EmpNo, acEntire, , acSearchAll, False, acCurrent

Me.lblAmend.Visible = True
Me.cmdAmendYes.Visible = True
Me.cmdAmendNO.Visible = True
Me.cboSurnameCheck = Null

Else
Do a whole load of other stuff with the form

End If
End Sub

I have cut the other bit of the code out as it is quite long and irrelevant to the problem; Although under normal circumstances, there will be at least one more form open, I am currently testing with just the one form open on its own.

For info

• the form's record source is a query that shows records that are not closed (records are deemed closed when a date is entered in a field, therefore the query returns only records where this field is null)
• the combobox me.cboSurnameCheck is unbound
• all of the code above is an AfterUpdate event on the combo

In simple terms, I am trying to get the code to first of all check to see if a record exists for the employee, based upon their unique employee number; if there is already a record existing, I would like that record to be found (and thus populating the field ID (which is the Primary Key for the record in the training table); when the command buttons are revealed, one of them has the code to open a different form showing the same record that was showing on the previous form, and then close the original form.


If there is another way to get the record ID without using FindRecord, that would allow it to be used when the command buttons become available, then I am all ears

 

[fat controller]

Actually, I have an idea - only, I cannot get it to work....... yet.

How about:

Dim EmpNo

EmpNo = Me.cboSurnameCheck.Column(2)

If DCount("Employee_Number", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'") > 0 Then
MsgBox "An active training record already exists for this employee.  Please select the option to Amend or Edit Existing Training Record.", vbOKOnly

Me.lblAmend.Visible = True
Me.cmdAmendYes.Visible = True
Me.cmdAmendNO.Visible = True

Else
Do other stuff which I know already works...

and then on the command button that appears, get it to find the record ID using DLookup?

Dim varID As Variant
Dim EmpNo

varID = DLookup("ID", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'")
EmpNo = Me.cboSurnameCheck.Column(2)

DoCmd.OpenForm "CPC_New_Amend", , , "ID =" & varID
DoCmd.CLOSE acForm, "CPC_Add", acSaveNo

However, I have got the OpenForm line wrong......?

 

[fat controller]

Got it!

Dim EmpNo

EmpNo = Me.cboSurnameCheck.Column(2)

If DCount("Employee_Number", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'") > 0 Then
MsgBox "An active training record already exists for this employee.  Please select the option to Amend or Edit Existing Training Record.", vbOKOnly

Me.lblAmend.Visible = True
Me.cmdAmendYes.Visible = True
Me.cmdAmendNO.Visible = True

Else
Do other stuff which I know already works...

and then on the command button that appears, get it to find the record ID using DLookup?

Dim varID As Integer
Dim EmpNo
EmpNo = Me.cboSurnameCheck.Column(2)
varID = DLookup("ID", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'")


DoCmd.OpenForm "CPC_New_Amend", , , "ID =" & varID
DoCmd.CLOSE acForm, "CPC_Add", acSaveNo

That gets shot of the FindRecord command which was giving the trouble.

 

[MarkK]

I never use DoCmd.FindRecord. Mostly what I do with forms is write a sub like this . . .

Sub GoToID(RecordID As Long)
   With Me.RecordsetClone
      .FindFirst "RecordID = " & RecordID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

. . . which searches a clone of the actual recordset in the form. If it gets results, it goes to the record.

But yeah, you can also re-open the form and apply a filter.

Cheers,

 

[fat controller]

Thanks - as it is opening a different form (albeit linked to the same record in the table) I think I will leave alone now I have it working.

Is there a reason that DoCmd.FindRecord seems to be a bit unstable? I did a bit of Googling a bit later yesterday, and it appears that its quite common?

 

[MarkK]

Access is a collection of softwares, really. Tables and the database engine are provided by Jet. Data access, is provided by DAO or ADODB. Programming is via the VBA IDE. Access, in some ways, is just the Forms and Reports. Everything else you can use independently.

DoCmd is provided by Access, and it does its best to provide a kind of glue amongst the various components, but it was written after the other components in an effort to get them all to play nice.

 

[gemma-the-husky]

Check that empno is not null before undertaking the findrecord. Perhaps that is the problem.

Ignore that. Clearly this isn't the issue.

 

[gemma-the-husky]

I use findrecord, and have never had an issue.

I was on a tablet before, and it was hard to see the posts

this block - what happens if dcount=0? is that the problem.

 If DCount("Employee_Number", "CPC_Incomplete_Training", "Employee_Number='" & EmpNo & "'") > 0 Then
MsgBox "An active training record already exists for this employee. Please select the option to Amend or Edit Existing Training Record.", vbOKOnly

T1 = Now()
T2 = DateAdd("s", 1, T1)
Do Until T2 <= T1
T1 = Now()
Loop

 

[fat controller]

Sorry, I can't seem to get multi-quotes working for some reason.

@MarkK - that makes a bit of sense, as FindRecord worked perfectly well on every other form I have used it on; having said that, I have spent the past day or so altering code to eliminate it wherever possible, as I am aiming to have this database as stable as possible when released into 'the wild'

@gemma-the-husky - I believe that was the block that I had trouble with; EmpNo was definitely not null, as I was searching for known data (only half a dozen records to search through). In fact, if searching for something that wasn't there, it behaved perfectly well.