insertting a record

[madrav72]

hi all

when i insert a record into my subform it works fine but since my subform is driven off a query i need to do a requery command. now the problem with this is that it goes back to the first record in the set rather than the recently added one. how can this be corrected??

thanhks

 

[alastair69]

I think if the sub form is linked to a main form via the childed & the master child then you should not have a problem, if you post your Database i will have a look at it for you and have some suggestions as to a way round this.

Alastair

 

[RuralGuy]

Hi madrav72,
PMFJI but I believe all you need do is set the focus to a control on your SubForm and issue:
DoCmd.RunCommand acCmdRecordsGoToLast

 

[madrav72]

hi guys thanks for the response

ruralguy i tried doing the DoCmd.RunCommand acCmdRecordsGoToLast
but it keeps going back to the first record in the set

befiore this i am doing a requery

let me know you think might be wrong

 

[RuralGuy]

If you comment out the requery what happens? It sounds like you have another requery somewhere later on.

 

[madrav72]

basically commenting that bit out causes the form to stay with the new record that has just been created but nohting is updated to show the data that has just been inserted.

 

[RuralGuy]

Un-comment the requery and insert a DoEvents between the requery and the DoCmd.RunCommand acCmdRecordsGoToLast, although it should not be required. At least we know there is not another requery somewhere.

 

[madrav72]

im not sure why you think there is another requery being done else where. As far as i know the form itself is bound to a query because there is none. the doevent is showing zero value.

i have a popup form which has a a listbox and a updated button which when pressed does the insert. but because the main form is bound to the query still it doesnt updated until a requery is carried out. once this is done i need the main form page to display the new record just inserted.

 

[RuralGuy]

Do you bring up the PopUp form with the acDialog argument of the OpenForm command?

 

[madrav72]

hi RG i noticed that it seems to be going to the right record on the main form but for some strange reason the beahviour is acting like this ...

before .... record num 122 (record 1 of 1)

now adding new record 123

after .... record num 123 (record 1 of 2)
record num 122 (record 2 of 2)

what causing this???/

thanks

 

[RuralGuy]

Are these real Record Numbers? If so, where do they come from since Jet has no Record NUmbers? Are you using a filter in the Form?

 

[madrav72]

the expression used to work out the X of Y logic is......

="Application " & [CurrentRecord] & " of " & IIf([NewRecord],[CurrentRecord],DCount("*","ApplicationQuery","[ApplicantID]=" & [ApplicantID]))

 

[RuralGuy]

Is [ApplicantID] an AutoNumber field? Is your problem that it seems to be backwards? Do you have a sort order on the recordset? If not then Jet will hand you the records in the most efficient way it determines. Unless you specify the order then you will get an unordered set of records.

 

[madrav72]

ApplicantID is not an autonumber it is manually entered - but this is fine. I also came to the conclusion that the sorting needs to be fixed. Ive tried fixing it in the query and also via the subform sort property but it doesnt seem to work. ive used another field which is a date field that is automatically genereated to the current date since that would be the best method of sorting since the number above is not an autonumber.

 

[madrav72]

ok it seems to behave correcfly now that ive sorted by date but now its the issue of going to the last record when a new record is inserted

 

[madrav72]

i have a button on the main form which goes to the last record but for some strange reason it wont let me call the command for clicking htat button from the popup box when i try mainform.commandbutton_click etc it says method or data member not found.

edit: i fixed this by making the command button method public

 

[RuralGuy]

Glad to hear you got everything sorted out.