After requery, how do I move to desired recordset on subform

[Guest]

I have a subform. Before Insert, I create a recordest to one of the
underlying tables to add a new record. I then update some field values and
capture the primary key values (Entity ID and Contact Id). I update the form
fields with these values and do a requery. (If I don't take these steps, I
get an error message that the record can't be created due to null values.)

The problem is, once I requery, Access defaults to the first record in the
recordset, when I want it to go to the last. I've tried docmd.gotorecord,
I've tried setfocus and a thousand other different attempts with no success.
How can I get to the last record in my subform?

With rstContact
.Source = "Select * from Contact"
.ActiveConnection = conContact
.LockType = adLockOptimistic
.Open
.AddNew
![Contact Type] = "Address"
![Notes ID] = 3
.Update
End With

Me.[Entity ID] = intEntityID
Me.[Contact ID] = intContactID
Me.Requery

 

[Guest]

If this code run from the main form, try this after the requery

Me.[WriteHereTheSubFormControlName].SetFocus
DoCmd.GoToRecord , , acLast

 

[Guest]

Thanks so much for responding -- I'm running the code from the subform
(Before Insert event).

Just tried to use your code, and I'm getting a run-time error 2499 "can't
run this command from design view" even though I'm not in design view. I've
even tried exiting from Access and running it again ... same message.

If this code run from the main form, try this after the requery

Me.[WriteHereTheSubFormControlName].SetFocus
DoCmd.GoToRecord , , acLast

--
Good Luck
BS"D

I have a subform. Before Insert, I create a recordest to one of the
underlying tables to add a new record. I then update some field values and
capture the primary key values (Entity ID and Contact Id). I update the form
fields with these values and do a requery. (If I don't take these steps, I
get an error message that the record can't be created due to null values.)

The problem is, once I requery, Access defaults to the first record in the
recordset, when I want it to go to the last. I've tried docmd.gotorecord,
I've tried setfocus and a thousand other different attempts with no success.
How can I get to the last record in my subform?

With rstContact
.Source = "Select * from Contact"
.ActiveConnection = conContact
.LockType = adLockOptimistic
.Open
.AddNew
![Contact Type] = "Address"
![Notes ID] = 3
.Update
End With

Me.[Entity ID] = intEntityID
Me.[Contact ID] = intContactID
Me.Requery

 

[Guest]

Move the two lines

Me.Requery
DoCmd.GoToRecord , , acLast

To the after insert event of the sub form

--
Good Luck
BS"D

Thanks so much for responding -- I'm running the code from the subform
(Before Insert event).

Just tried to use your code, and I'm getting a run-time error 2499 "can't
run this command from design view" even though I'm not in design view. I've
even tried exiting from Access and running it again ... same message.

If this code run from the main form, try this after the requery

Me.[WriteHereTheSubFormControlName].SetFocus
DoCmd.GoToRecord , , acLast

--
Good Luck
BS"D

I have a subform. Before Insert, I create a recordest to one of the
underlying tables to add a new record. I then update some field values and
capture the primary key values (Entity ID and Contact Id). I update the form
fields with these values and do a requery. (If I don't take these steps, I
get an error message that the record can't be created due to null values.)

The problem is, once I requery, Access defaults to the first record in the
recordset, when I want it to go to the last. I've tried docmd.gotorecord,
I've tried setfocus and a thousand other different attempts with no success.
How can I get to the last record in my subform?

With rstContact
.Source = "Select * from Contact"
.ActiveConnection = conContact
.LockType = adLockOptimistic
.Open
.AddNew
![Contact Type] = "Address"
![Notes ID] = 3
.Update
End With

Me.[Entity ID] = intEntityID
Me.[Contact ID] = intContactID
Me.Requery

 

[Guest]

Tried that ... still getting the same error message.

Move the two lines

Me.Requery
DoCmd.GoToRecord , , acLast

To the after insert event of the sub form

--
Good Luck
BS"D

Thanks so much for responding -- I'm running the code from the subform
(Before Insert event).

Just tried to use your code, and I'm getting a run-time error 2499 "can't
run this command from design view" even though I'm not in design view. I've
even tried exiting from Access and running it again ... same message.

If this code run from the main form, try this after the requery

Me.[WriteHereTheSubFormControlName].SetFocus
DoCmd.GoToRecord , , acLast

--
Good Luck
BS"D


:

I have a subform. Before Insert, I create a recordest to one of the
underlying tables to add a new record. I then update some field values and
capture the primary key values (Entity ID and Contact Id). I update the form
fields with these values and do a requery. (If I don't take these steps, I
get an error message that the record can't be created due to null values.)

The problem is, once I requery, Access defaults to the first record in the
recordset, when I want it to go to the last. I've tried docmd.gotorecord,
I've tried setfocus and a thousand other different attempts with no success.
How can I get to the last record in my subform?

With rstContact
.Source = "Select * from Contact"
.ActiveConnection = conContact
.LockType = adLockOptimistic
.Open
.AddNew
![Contact Type] = "Address"
![Notes ID] = 3
.Update
End With

Me.[Entity ID] = intEntityID
Me.[Contact ID] = intContactID
Me.Requery

 

[RoyVidar]

Anita wrote in message

I have a subform. Before Insert, I create a recordest to one of the
underlying tables to add a new record. I then update some field
values and capture the primary key values (Entity ID and Contact
Id). I update the form fields with these values and do a requery.
(If I don't take these steps, I get an error message that the record
can't be created due to null values.)

The problem is, once I requery, Access defaults to the first record
in the recordset, when I want it to go to the last. I've tried
docmd.gotorecord, I've tried setfocus and a thousand other different
attempts with no success. How can I get to the last record in my
subform?

With rstContact
.Source = "Select * from Contact"
.ActiveConnection = conContact
.LockType = adLockOptimistic
.Open
.AddNew
![Contact Type] = "Address"
![Notes ID] = 3
.Update
End With

Me.[Entity ID] = intEntityID
Me.[Contact ID] = intContactID
Me.Requery

The before insert event of a form, invokes at the moment you start
typing information in a new record, right? If you during that event,
save the record, and try to movelast, I think you're confusing Access
a little.

Why not either
1 - ensure all the relevant fields are within the forms recordsource -
they need not all have controls bound to them on the form, then add
the information directly to the controls/fields in the recordscource

Me![Entity ID] = intEntityID
Me![Contact ID] = intContactID
Me![Contact Type] = "Address"
Me![Notes ID] = 3

2 - use "Address" and 3 as default value for the relevant fields in the
table design, and assign only to the entity and contact id controls in
the before insert event.