What am I doing wrong?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to send a field value from a query to a field in a table.

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.RunMacro ("mcr_cumroll") 'runs a query and doesn't close it
DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = [qry_cumrollforward]![ec_cum1].Value


this is not working, I get an error that says :
"You can't go to the specified record"
Is there another way that I could do this?
 
so when you click on NewCum (a command button?), you want to move to a new
record in the form, and set the value of one of the fields in the new record
to the value returned by the query?

assuming that the query only returns one record, suggest you use the
DLookup() function to retrieve the value from the query, as

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = DLookup("ec_cum1", "qry_cumrollforward")

note that the code above does *not* have an "open query" action in it - the
DLookup() function will handle that automatically. see the DLookup Function
topic in Help for details.

hth
 
Tina,
It works, almost.
I need to change the following:

DoCmd.GoToRecord , , acNewRec

to copy the record instead of adding a new one because the query that
dlookup is working with using fields on the record as a parameter.


tina said:
so when you click on NewCum (a command button?), you want to move to a new
record in the form, and set the value of one of the fields in the new record
to the value returned by the query?

assuming that the query only returns one record, suggest you use the
DLookup() function to retrieve the value from the query, as

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = DLookup("ec_cum1", "qry_cumrollforward")

note that the code above does *not* have an "open query" action in it - the
DLookup() function will handle that automatically. see the DLookup Function
topic in Help for details.

hth


TimT said:
I'm trying to send a field value from a query to a field in a table.

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.RunMacro ("mcr_cumroll") 'runs a query and doesn't close it
DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = [qry_cumrollforward]![ec_cum1].Value


this is not working, I get an error that says :
"You can't go to the specified record"
Is there another way that I could do this?
Click to expand...
Click to expand...
 
I got it...

' To copy the current record to a new record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Thank you Tina so much for your help. I think I love you.


tina said:
so when you click on NewCum (a command button?), you want to move to a new
record in the form, and set the value of one of the fields in the new record
to the value returned by the query?

assuming that the query only returns one record, suggest you use the
DLookup() function to retrieve the value from the query, as

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = DLookup("ec_cum1", "qry_cumrollforward")

note that the code above does *not* have an "open query" action in it - the
DLookup() function will handle that automatically. see the DLookup Function
topic in Help for details.

hth


TimT said:
I'm trying to send a field value from a query to a field in a table.

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.RunMacro ("mcr_cumroll") 'runs a query and doesn't close it
DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = [qry_cumrollforward]![ec_cum1].Value


this is not working, I get an error that says :
"You can't go to the specified record"
Is there another way that I could do this?
Click to expand...
Click to expand...
 
you're welcome :)


TimT said:
I got it...

' To copy the current record to a new record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Thank you Tina so much for your help. I think I love you.


tina said:
so when you click on NewCum (a command button?), you want to move to a new
record in the form, and set the value of one of the fields in the new record
to the value returned by the query?

assuming that the query only returns one record, suggest you use the
DLookup() function to retrieve the value from the query, as

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = DLookup("ec_cum1", "qry_cumrollforward")

note that the code above does *not* have an "open query" action in it - the
DLookup() function will handle that automatically. see the DLookup Function
topic in Help for details.

hth


TimT said:
I'm trying to send a field value from a query to a field in a table.

Private Sub NewCum_Click()
On Error GoTo Err_NewCum_Click

DoCmd.RunMacro ("mcr_cumroll") 'runs a query and doesn't close it
DoCmd.GoToRecord , , acNewRec
Me.BF_CUM_1 = [qry_cumrollforward]![ec_cum1].Value


this is not working, I get an error that says :
"You can't go to the specified record"
Is there another way that I could do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top