Run-time error 2465

[Rob]

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.

 

[Douglas J. Steele]

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

 

[Rob]

Thank you very much for your assistance, this has fixed my problem.

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

(no private e-mails, please)

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.

 

[Rob]

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.

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

(no private e-mails, please)

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.

 

[Douglas J. Steele]

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

(no e-mails, please!)

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.

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

(no private e-mails, please)

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.

 

[Rob]

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?

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

(no e-mails, please!)

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.

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

(no private e-mails, please)


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.

 

[Rob]

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.

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?

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

(no e-mails, please!)

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:
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

(no private e-mails, please)


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.