Tags: 2465microsoft, access, cant, code, compile, database, error, field, following, microsoft, mysql, office, oracle, receive, run-time, sql

Run-time error 2465

On Database » Microsoft Access

13,158 words with 6 Comments; publish: Wed, 04 Jun 2008 01:24:00 GMT; (25062.50, « »)

I receive run-time error #2465 when I compile the following code :

Run-time error '2465':

Microsoft Office Access can't find the field '|' referred to in your

expression.

Private Sub Command12_Click()

Dim nHours As Integer

Dim nMinutes As Integer

Dim nTotal As Date

Dim nXfer As Integer

Returned.Value = Time

nTotal = CheckedOut.Value - Returned.Value

nHours = DatePart("h", nTotal)

nMinutes = DatePart("n", nTotal)

nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

[Tables]![LearnerInformation]![TotalMinutes] = nXfer

End Sub

The debugger highlights the line

"[Tables]![LearnerInformation]![TotalMinutes] = nXfer"

I'm quite sure that I have spelled everything perfectly and it all

matches the appropriate tables.

If there's not an obvious syntax error here, you can read the rest of

this and perhaps get some insight as to what I'm doing, and maybe even

suggest better code for me.

I have a form (with this button on it) that holds periods of time

people are 'logged in.' The date format (hh:mm) is then converted in to

minutes only, and stored (nXfer). Whenever the logged in time is

updated (done by pressing this button), I want it to shoot the data

(nXfer) over to the user's profile underneath the "TotalMinutes" field

in the LearnerInformation table.

If there's any more info needed, I will gladly provide it.

I appreciate any help, and thank you in advance.

All Comments

Leave a comment...

  • 6 Comments
    • You can't refer to tables in that way.

      You have to open a recordset and update it, or use a SQL Update statement.

      Both of the following assume that the key to your table is LearnerID, and

      that there's a field on your form that will give the LearnerID back to you.

      Method 1:

      Dim rsCurr As DAO.Recordset

      Dim strSQL As String

      strSQL = "SELECT TotalMinutes " & _

      "FROM LearnerInformation " & _

      "WHERE LearnerID = " & Me.LearnerID

      Set rsCurr = CurrentDb().OpenRecordset(strSQL)

      If Not rsCurr.EOF Then

      rsCurr.Edit

      rsCurr![TotalMinutes] = nXfer

      rsCurr.Update

      End If

      Method 2:

      Dim strSQL As String

      strSQL = "UPDATE LearnerInformation " & _

      "SET TotalMinutes = " & nXfer & " " & _

      "WHERE LearnerID = " & Me.LearnerID

      CurrentDb.Execute strSQL, dbFailOnError

      Doug Steele, Microsoft Access MVP

      http://I.Am/DougSteele

      (no private e-mails, please)

      "Rob" <Evendro.ms-access.questionfor.info.gmail.com> wrote in message

      news:1165060265.338445.249720.ms-access.questionfor.info.80g2000cwy.googlegro ups.com...

      >I receive run-time error #2465 when I compile the following code :

      > Run-time error '2465':

      > Microsoft Office Access can't find the field '|' referred to in your

      > expression.

      > --

      > Private Sub Command12_Click()

      > Dim nHours As Integer

      > Dim nMinutes As Integer

      > Dim nTotal As Date

      > Dim nXfer As Integer

      > Returned.Value = Time

      > nTotal = CheckedOut.Value - Returned.Value

      > nHours = DatePart("h", nTotal)

      > nMinutes = DatePart("n", nTotal)

      > nXfer = (nHours * 60) + nMinutes

      > TotalTime.Value = nXfer

      > [Tables]![LearnerInformation]![TotalMinutes] = nXfer

      > End Sub

      > --

      > The debugger highlights the line

      > "[Tables]![LearnerInformation]![TotalMinutes] = nXfer"

      > I'm quite sure that I have spelled everything perfectly and it all

      > matches the appropriate tables.

      > If there's not an obvious syntax error here, you can read the rest of

      > this and perhaps get some insight as to what I'm doing, and maybe even

      > suggest better code for me.

      > I have a form (with this button on it) that holds periods of time

      > people are 'logged in.' The date format (hh:mm) is then converted in to

      > minutes only, and stored (nXfer). Whenever the logged in time is

      > updated (done by pressing this button), I want it to shoot the data

      > (nXfer) over to the user's profile underneath the "TotalMinutes" field

      > in the LearnerInformation table.

      > If there's any more info needed, I will gladly provide it.

      > I appreciate any help, and thank you in advance.

      >

      #1; Wed, 04 Jun 2008 01:26:00 GMT
    • Thank you very much for your assistance, this has fixed my problem.

      Douglas J. Steele wrote:[vbcol=seagreen]

      > You can't refer to tables in that way.

      > You have to open a recordset and update it, or use a SQL Update statement.

      > Both of the following assume that the key to your table is LearnerID, and

      > that there's a field on your form that will give the LearnerID back to you.

      > Method 1:

      > Dim rsCurr As DAO.Recordset

      > Dim strSQL As String

      > strSQL = "SELECT TotalMinutes " & _

      > "FROM LearnerInformation " & _

      > "WHERE LearnerID = " & Me.LearnerID

      > Set rsCurr = CurrentDb().OpenRecordset(strSQL)

      > If Not rsCurr.EOF Then

      > rsCurr.Edit

      > rsCurr![TotalMinutes] = nXfer

      > rsCurr.Update

      > End If

      > Method 2:

      > Dim strSQL As String

      > strSQL = "UPDATE LearnerInformation " & _

      > "SET TotalMinutes = " & nXfer & " " & _

      > "WHERE LearnerID = " & Me.LearnerID

      > CurrentDb.Execute strSQL, dbFailOnError

      > --

      > Doug Steele, Microsoft Access MVP

      > http://I.Am/DougSteele

      > (no private e-mails, please)

      >

      > "Rob" <Evendro.ms-access.questionfor.info.gmail.com> wrote in message

      > news:1165060265.338445.249720.ms-access.questionfor.info.80g2000cwy.googlegro ups.com...

      #2; Wed, 04 Jun 2008 01:27:00 GMT
    • Once again, thank you for your help with this issue. I have another

      problem that surfaced after I implemented this however.

      I've been pulling my hair out over it for the past few days, and I

      decided to try my luck asking here again, since it seems to work so

      far.

      Here is the code that I am using:

      Private Sub Command12_Click()

      Dim rsCurr As DAO.Recordset

      Dim strSQL As String

      Dim nHours As Integer

      Dim nMinutes As Integer

      Dim nTotal As Date

      Dim nXfer As Integer

      Returned.Value = Time

      nTotal = CheckedOut.Value - Returned.Value

      nHours = DatePart("h", nTotal)

      nMinutes = DatePart("n", nTotal)

      nXfer = (nHours * 60) + nMinutes

      TotalTime.Value = nXfer

      TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="

      & IDNumber)

      strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE

      IDNumber = " & Me.IDNumber

      Set rsCurr = CurrentDb().OpenRecordset(strSQL)

      If Not rsCurr.EOF Then

      rsCurr.Edit

      rsCurr![TotalMinutes] = TotalMinBox.Value

      rsCurr.Update

      End If

      End Sub

      The problem I'm running in to, is that the summed total of all the

      times (TotalMinBox) is not adding the most recent entry to the sum

      before sending the data to the table. The sum will only update after

      the current form (on a continuous form subform display) looses focus.

      Since the sum updates after focus is lost, the last entry is not added

      to the total sum and thus I am always one record behind in my table.

      I've attempted to fashion some creative On Change events to get around

      this, but to no avail. If anyone could point me in the right direction

      once again, I would appreciate it very much. Thank you.

      Douglas J. Steele wrote:[vbcol=seagreen]

      > You can't refer to tables in that way.

      > You have to open a recordset and update it, or use a SQL Update statement.

      > Both of the following assume that the key to your table is LearnerID, and

      > that there's a field on your form that will give the LearnerID back to you.

      > Method 1:

      > Dim rsCurr As DAO.Recordset

      > Dim strSQL As String

      > strSQL = "SELECT TotalMinutes " & _

      > "FROM LearnerInformation " & _

      > "WHERE LearnerID = " & Me.LearnerID

      > Set rsCurr = CurrentDb().OpenRecordset(strSQL)

      > If Not rsCurr.EOF Then

      > rsCurr.Edit

      > rsCurr![TotalMinutes] = nXfer

      > rsCurr.Update

      > End If

      > Method 2:

      > Dim strSQL As String

      > strSQL = "UPDATE LearnerInformation " & _

      > "SET TotalMinutes = " & nXfer & " " & _

      > "WHERE LearnerID = " & Me.LearnerID

      > CurrentDb.Execute strSQL, dbFailOnError

      > --

      > Doug Steele, Microsoft Access MVP

      > http://I.Am/DougSteele

      > (no private e-mails, please)

      >

      > "Rob" <Evendro.ms-access.questionfor.info.gmail.com> wrote in message

      > news:1165060265.338445.249720.ms-access.questionfor.info.80g2000cwy.googlegro ups.com...

      #3; Wed, 04 Jun 2008 01:28:00 GMT
    • Rather than getting the value from the control on the form, see whether this

      works any better:

      rsCurr![TotalMinutes] = DSum("TotalTime", "LearnerActivity", "[IDNumber]=" &

      IDNumber)

      (Watch for word-wrap: that's supposed to all be on one line)

      Doug Steele, Microsoft Access MVP

      http://I.Am/DougSteele

      (no e-mails, please!)

      "Rob" <Evendro.ms-access.questionfor.info.gmail.com> wrote in message

      news:1165236835.332532.205880.ms-access.questionfor.info.j72g2000cwa.googlegr oups.com...

      > Once again, thank you for your help with this issue. I have another

      > problem that surfaced after I implemented this however.

      > I've been pulling my hair out over it for the past few days, and I

      > decided to try my luck asking here again, since it seems to work so

      > far.

      > Here is the code that I am using:

      > --

      > Private Sub Command12_Click()

      > Dim rsCurr As DAO.Recordset

      > Dim strSQL As String

      > Dim nHours As Integer

      > Dim nMinutes As Integer

      > Dim nTotal As Date

      > Dim nXfer As Integer

      > Returned.Value = Time

      > nTotal = CheckedOut.Value - Returned.Value

      > nHours = DatePart("h", nTotal)

      > nMinutes = DatePart("n", nTotal)

      > nXfer = (nHours * 60) + nMinutes

      > TotalTime.Value = nXfer

      > TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="

      > & IDNumber)

      > strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE

      > IDNumber = " & Me.IDNumber

      > Set rsCurr = CurrentDb().OpenRecordset(strSQL)

      > If Not rsCurr.EOF Then

      > rsCurr.Edit

      > rsCurr![TotalMinutes] = TotalMinBox.Value

      > rsCurr.Update

      > End If

      >

      > End Sub

      > --

      > The problem I'm running in to, is that the summed total of all the

      > times (TotalMinBox) is not adding the most recent entry to the sum

      > before sending the data to the table. The sum will only update after

      > the current form (on a continuous form subform display) looses focus.

      > Since the sum updates after focus is lost, the last entry is not added

      > to the total sum and thus I am always one record behind in my table.

      > I've attempted to fashion some creative On Change events to get around

      > this, but to no avail. If anyone could point me in the right direction

      > once again, I would appreciate it very much. Thank you.

      >

      >

      > Douglas J. Steele wrote:

      >

      #4; Wed, 04 Jun 2008 01:29:00 GMT
    • Unfortunately I'm running in to the same problem. The data is sent to

      the table, but once again it is sent before the most recent addition is

      added to the sum.

      Is there any way to "force" the update of a text box? For example, on

      this button event, I force the box to update itself (resum?), and then

      send the data to the table?

      Douglas J. Steele wrote:[vbcol=seagreen]

      > Rather than getting the value from the control on the form, see whether this

      > works any better:

      > rsCurr![TotalMinutes] = DSum("TotalTime", "LearnerActivity", "[IDNumber]=" &

      > IDNumber)

      > (Watch for word-wrap: that's supposed to all be on one line)

      >

      > --

      > Doug Steele, Microsoft Access MVP

      > http://I.Am/DougSteele

      > (no e-mails, please!)

      >

      > "Rob" <Evendro.ms-access.questionfor.info.gmail.com> wrote in message

      > news:1165236835.332532.205880.ms-access.questionfor.info.j72g2000cwa.googlegr oups.com...

      #5; Wed, 04 Jun 2008 01:30:00 GMT
    • Perhaps I'm going about this the wrong way. Are there any different

      procedures I should consider to accomplish this goal? Any help would be

      greatly appreciated.

      Thank you.

      Rob wrote:[vbcol=seagreen]

      > Unfortunately I'm running in to the same problem. The data is sent to

      > the table, but once again it is sent before the most recent addition is

      > added to the sum.

      > Is there any way to "force" the update of a text box? For example, on

      > this button event, I force the box to update itself (resum?), and then

      > send the data to the table?

      >

      > Douglas J. Steele wrote:

      #6; Wed, 04 Jun 2008 01:31:00 GMT