error pulling data from query using a command button on a form

'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the Student_Info table
' The SSN path will be added here to complete the addition tot he record
' This SQL statement is to long for viewing on the screen with scrolling to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting an error?

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not related to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the Student_Info
table
' The SSN path will be added here to complete the addition tot he record
' This SQL statement is to long for viewing on the screen with scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting an
error?

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

I changed it to this but I get the same error
'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub


"Douglas J Steele" wrote:

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not related to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the Student_Info
table
' The SSN path will be added here to complete the addition tot he record
' This SQL statement is to long for viewing on the screen with scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting an
error?

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

You need to put the references to form fields outside of the quotes,
otherwise you get the literal field names in your SQL, not the values which
they contain.

Assuming both of the fields to be updated are numeric, try:

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES( " &
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER]
& ", " &
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] & ")"

However, your references to form fields aren't correct. See whether the
information in http://www.mvps.org/access/forms/frm0031.htm at "The Access
Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
I changed it to this but I get the same error
'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub


"Douglas J Steele" wrote:

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not related
to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the Student_Info
table
' The SSN path will be added here to complete the addition tot he
record
' This SQL statement is to long for viewing on the screen with
scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting an
error?

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries
section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

Thanks Douglas but the part of the code you gave me an answer to was
provifding the correct information for the table.

The code I am having a problem is "L4ssn = Right(DET1PERSONNEL.SSAN, 4)"

The piece of code above cause an "Object riquired" error


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is a tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table
' i have not included the code to insert the last four of the SSN into the
table here yet

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

"Douglas J. Steele" wrote:

You need to put the references to form fields outside of the quotes,
otherwise you get the literal field names in your SQL, not the values which
they contain.

Assuming both of the fields to be updated are numeric, try:

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES( " &
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER]
& ", " &
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] & ")"

However, your references to form fields aren't correct. See whether the
information in http://www.mvps.org/access/forms/frm0031.htm at "The Access
Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
I changed it to this but I get the same error
'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub


"Douglas J Steele" wrote:

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not related
to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the Student_Info
table
' The SSN path will be added here to complete the addition tot he
record
' This SQL statement is to long for viewing on the screen with
scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting an
error?

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries
section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

As I said earlier, you cannot refer to a table/field like that. If there's
only one row in your DET1PERSONNEL table, use

L4ssn = Right(NZ(DLookup("SSAN", "DET1PERSONNEL)," "), 4)

If there's more than one row, how do you determine which row you want?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
Thanks Douglas but the part of the code you gave me an answer to was
provifding the correct information for the table.

The code I am having a problem is "L4ssn = Right(DET1PERSONNEL.SSAN, 4)"

The piece of code above cause an "Object riquired" error


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is a tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table
' i have not included the code to insert the last four of the SSN into the
table here yet

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

"Douglas J. Steele" wrote:

You need to put the references to form fields outside of the quotes,
otherwise you get the literal field names in your SQL, not the values
which
they contain.

Assuming both of the fields to be updated are numeric, try:

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES( " &
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER]
& ", " &
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] &
")"

However, your references to form fields aren't correct. See whether the
information in http://www.mvps.org/access/forms/frm0031.htm at "The
Access
Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
I changed it to this but I get the same error
'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the
Student_Info
table

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub


"Douglas J Steele" wrote:

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not
related
to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the
Student_Info
table
' The SSN path will be added here to complete the addition tot he
record
' This SQL statement is to long for viewing on the screen with
scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting
an
error?

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],

orms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries
section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?

Thanks again UR answer help me see things differently. it is working as I
intended

"Douglas J. Steele" wrote:

As I said earlier, you cannot refer to a table/field like that. If there's
only one row in your DET1PERSONNEL table, use

L4ssn = Right(NZ(DLookup("SSAN", "DET1PERSONNEL)," "), 4)

If there's more than one row, how do you determine which row you want?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
Thanks Douglas but the part of the code you gave me an answer to was
provifding the correct information for the table.

The code I am having a problem is "L4ssn = Right(DET1PERSONNEL.SSAN, 4)"

The piece of code above cause an "Object riquired" error


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is a tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the Student_Info
table
' i have not included the code to insert the last four of the SSN into the
table here yet

DoCmd.RunSQL "INSERT INTO Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

"Douglas J. Steele" wrote:

You need to put the references to form fields outside of the quotes,
otherwise you get the literal field names in your SQL, not the values
which
they contain.

Assuming both of the fields to be updated are numeric, try:

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES( " &
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER]
& ", " &
Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] &
")"

However, your references to form fields aren't correct. See whether the
information in http://www.mvps.org/access/forms/frm0031.htm at "The
Access
Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"AHP" wrote in message
...
I changed it to this but I get the same error
'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click


Dim L4ssn As Integer

'DET1PERSONNEL.SSAN is tablename.fieldname

' I want to retrieve the last four of the SSN from this master table to
store as
' a reference N another table

' This is where I get my error "object required"

' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
' info and determine it is individual they want to add to the table

L4ssn = Right(DET1PERSONNEL.SSAN, 4)

'This SQL statement will INSERT a single record into the
Student_Info
table

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(
Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],Forms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER]
)"

DoCmd.GoToRecord , , acNewRec

Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub


"Douglas J Steele" wrote:

You can't refer to fields in queries that way.

What is DET1PERSONNEL1 Query? How does it relate to your form?

If you're trying to get details from another table or query not
related
to
the recordsource of your form, consider using DLookup.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AHP" wrote in message
...
'I am attempting to pull data from a column in a query.

'I want to pull the last four numbers of the SSN of the individual
selected
form
' the combo box on the form.

'1. The code I have written gives me the error " object required"


'This Sub will add the last four of the SSN and the row number of
'the personnel information being displayed to the Student_Info table

Private Sub UpDateSingleRecord_Click()

On Error GoTo Err_UpDateSingleRecord_Click

Dim num1 As Integer
Dim L4ssn As Integer

' This is where I get my error
' I am attempting to pull the data directly form the queries section
' This piece of code is excuted after I toggle the add record button
' The user will toggle the add record button after they view the
displayed
info and
' determine it is individual they want to add to the table

num1 = InStr(1, Queries![DET1PERSONNEL1 Query]![SSAN], Chr(32))
L4ssn = Left(Queries![DET1PERSONNEL1 Query]![SSAN], (num1 - 1))

'This SQL statement will INSERT a single record into the
Student_Info
table
' The SSN path will be added here to complete the addition tot he
record
' This SQL statement is to long for viewing on the screen with
scrolling
to
the
' right and it will grow longer

' 2. How can I do a contuniation on the next line without getting
an
error?

DoCmd.RunSQL "INSERT INTO
Student_Info(STUDENT_ROW_NUMBER,CLASSNUMBER)
VALUES(

Forms![Course_Information]![ClassDate]![Student_info1]![STUDENT_ROW_NUMBER],

orms![Course_Information]![ClassDate]![Student_info1]![CLASSNUMBER] )"


DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

3. Is it more efficient to pull the data directly from the queries
section
or more efficient to pull it from the form via the combo box?

4. if the latter is more efficient how do I state the path?