Run Time Error '2447', form used to work. 1

[Triacona]

Hello everybody!

Thanks for an amazing forum!

I was checking some of my Access Databases and have just recently encountered an error.

Run Time Error '2447' There is an invalid use of the .(dot) or ! operator or invalid parentheses

We have just recently upgraded to Microsoft Office 2010, including MS Access 2010.

On opening the DB a few missing references were listed in VB. I removed those and checked if (what I think) the core references were still intact.
Then the DB opened without missing reference errors.

I then clicked a button on my MainScreen form, which opens another form Questionnaire.

This then gives me the error message:

Run Time Error '2447' There is an invalid use of the .(dot) or ! operator or invalid parentheses

Then I debug and it goes to the following code in the Questionnaire form.
The following code appears:

Private Sub Form_Current()

If Me.NewRecord = True Then
    Me.DateInterview.SetFocus
End If
'FIX!!!!!!!!!!!
[highlight]If txtCurrentRecord = Me.txtTotalRecords Then[/highlight]
    DateInterview.SetFocus
    cmdNextRecord.Enabled = False
    cmdPrevRecord.Enabled = True
    Else
        cmdNextRecord.Enabled = True
    If txtCurrentRecord = 1 Then
        DateInterview.SetFocus
        cmdNextRecord.Enabled = True
        cmdPrevRecord.Enabled = False
    Else
        cmdPrevRecord.Enabled = True
    End If
End If
End Sub

The above (just below Fix!!!) gets highlighted.

What I don't understand is that it used to work, last time I looked, before upgrade to 2010?!

I have tried compact and repair DB but to no avail.
Please help I am really perplexed!
Thank you



Thank you,

Kind regards

Triacona

 

[Triacona]

The above is for record buttons, e.g. Next Record, Previous Record, Last Record, First Reocrd. Rather than use MS's record selectors.
Within the properties of Textbox: txtTotalRecords
Data TAB
Control Source: =Form.RecordsetClone.RecordCount
Text Format: Plain Text

Thank you,

Kind regards

Triacona

 

[PHV]

And what is txtCurrentRecord ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Hi PHV,
Thanks for your help

Within the properties of Textbox: txtCurrentRecord
Data TAB
Control Source: =Form.CurrentRecord
Text Format: Plain Text

This works and displayes the record amount while the txtTotalRecords just says #name

I copied the form and it works if I remove the buttons I created, but I obviously don't want to do that

Thanks again!

Thank you,

Kind regards

Triacona

 

[PHV]

What about replacing this:
If txtCurrentRecord = Me.txtTotalRecords Then
with this (and so on) ?
If Me.CurrentRecord = Me.Recordset.RecordCount Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Thanks so much for your help PHV!
The only problem I have now is that the Textbox: txtTotalRecords does not display the total amount of records?
Is there any way to present this number?
Thanks again!

The code below is working and going from one record to the next
Please see code below...

Private Sub Form_Current()

If Me.NewRecord = True Then
    Me.DateInterview.SetFocus
End If
'FIX!!!!!!!!!!!
If Me.CurrentRecord = Me.Recordset.RecordCount Then
    DateInterview.SetFocus
    cmdNextRecord.Enabled = False
    cmdPrevRecord.Enabled = True
    Else
        cmdNextRecord.Enabled = True
    If Me.txtCurrentRecord = 1 Then
        DateInterview.SetFocus
        cmdNextRecord.Enabled = True
        cmdPrevRecord.Enabled = False
    Else
        cmdPrevRecord.Enabled = True
    End If
End If



End Sub

Private Sub Form_Open(Cancel As Integer)

    DoCmd.GoToRecord , , acLast
    
End Sub

Private Sub QuestionnaireFormClose_Click()
On Error GoTo Err_QuestionnaireFormClose_Click

Dim stDocName As String
    stDocName = "MainScreen"

    DoCmd.Close
    DoCmd.OpenForm stDocName, acNormal
    DoCmd.Restore

Exit_QuestionnaireFormClose_Click:
    Exit Sub

Err_QuestionnaireFormClose_Click:
    MsgBox Err.Description
    Resume Exit_QuestionnaireFormClose_Click
    
End Sub
Private Sub QuestDeleteEntire_Click()
On Error GoTo Err_QuestDeleteEntire_Click



If MsgBox("Are you sure you want to delete this record?", vbYesNo, "DELETE RECORD!!") = vbYes Then

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
   
End If


Exit_QuestDeleteEntire_Click:

    'Me.[Forms.QuestionnaireFormClose].Requery


Exit Sub

Err_QuestDeleteEntire_Click:

    If Err.Number = "2501" Then
        MsgBox "Record not deleted"
     Else
        MsgBox Err.Description
     End If
    
    Resume Exit_QuestDeleteEntire_Click
    
End Sub
Private Sub AddNewRespondent_Click()
On Error GoTo Err_AddNewRespondent_Click


    DoCmd.GoToRecord , , acNewRec
    Me.DateInterview.SetFocus

Exit_AddNewRespondent_Click:
    Exit Sub

Err_AddNewRespondent_Click:
    MsgBox Err.Description
    Resume Exit_AddNewRespondent_Click
    
End Sub
Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNextRecord_Click

    
    Dim msg, style, title, response As String
    msg = "There are no records!"
    style = vbOKOnly
    title = "NO RECORDS!"
    

    If Me.Recordset.RecordCount = 0 Then
     MsgBox msg, vbOKOnly, title
    Else
        DoCmd.SetWarnings False
        DoCmd.GoToRecord , , acNext
        DoCmd.SetWarnings True
    End If
        
    
Exit_cmdNextRecord_Click:
    Exit Sub

Err_cmdNextRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdNextRecord_Click
    
End Sub
Private Sub cmdPrevRecord_Click()
On Error GoTo Err_cmdPrevRecord_Click


    DoCmd.SetWarnings False
    DoCmd.GoToRecord , , acPrevious
    DoCmd.SetWarnings True


Exit_cmdPrevRecord_Click:
    Exit Sub

Err_cmdPrevRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevRecord_Click
    
End Sub
Private Sub cmdFirstRec_Click()
On Error GoTo Err_cmdFirstRec_Click


    DoCmd.GoToRecord , , acFirst

Exit_cmdFirstRec_Click:
    Exit Sub

Err_cmdFirstRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirstRec_Click
    
End Sub
Private Sub cmdLastRec_Click()
On Error GoTo Err_cmdLastRec_Click


    DoCmd.GoToRecord , , acLast

Exit_cmdLastRec_Click:
    Exit Sub

Err_cmdLastRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdLastRec_Click

Thank you,

Kind regards

Triacona

 

[PHV]

I'd get rid of the ControlSource of txtTotalRecords and in the Current event procedure of the form:
Me!txtTotalRecords = Me.Recordset.RecordCount

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Hi PHV!
Thanks for all your help!
I have done as you have guided, and it works!
Just a quick question, has Access 2010 changed how they work with forms?
It used to work in Access 2003.
Must I convert the DB to Access 2007 (latest)?
Is it better practice to declare values in code? (as above?)
Thanks again for all your help!

Thank you,

Kind regards

Triacona