DoCmd.GoToRecord , , acGoTo, Screen.ActiveControl

[hnhpak]

DoCmd.SearchForRecord , , acGoTo, "ID =" & Me.CBO_STD_ID

Private Sub CBO_STD_ID_AfterUpdate()

If IsNull([Screen].ActiveControl) Then
DoCmd.GoToControl "[CBO_STD_ID]"
End If

varActContVal = [Screen].ActiveControl
DoCmd.GoToRecord , , acGoTo, Screen.ActiveControl

End Sub

Screen.ActiveControl is the value input by unbounded comboBox.
This the value of Filed ID of the table/Query rawsource of the form.

I want to go to the record where condition ID=[Screen].[ActiveControl]

The above mentioned code works well for first 39 records and then it react strangely and gives wrong results.

On investigation into table it found that ID number form 1-39 are without break, then records having ID 40, 41,44,67,92,97,137,146,191 are deleted from table.



Problem came onward from record having ID more then 39. Results are as under:

on selection from 1-39 go to correct record
on Selection ID 42 it goes to ID 45 two records forward
on Selection ID 45 it goes to ID 48 three records forward
on Selection ID 66 it goes to ID 70 four records forward
on Selection ID 68 it goes to ID 72 five records forward
On Selection ID 93 it goes to ID 104 six records forward .... and so on

On selecting last 8 to 10 records a run time error message says ' You can not go to the specific record'
I am unable to understand the behavior of database in this regards. If you understand please let me know how can i fix it.

ID is the primary key of the table
and records related to ID 40, 41,44,67,92,97,137,146,191 are deleted from table.

What I understands from this behavior of comboBox record selection, it goes to record number serial from top,
and whereas the value of Screen.ActiveControl is the ID number of primary key, which is higher then the serial number of the record due to deleted records in between.


I therefore also had tried the code

DoCmd.SearchForRecord , , acGoTo, "ID =" & Me.CBO_STD_ID
DoCmd.SearchForRecord , , acGoTo, "ID =" [Screen].[ActiveControl]

But all fails, a run time error message shows. Please help in this regards where i am doing mistake.

Thanks and regards

Haseebul Hasan

[NTC]

your post is confusing because you intermingle 'control' and 'record' - - which are 2 very different things. The combo box selects a record. If you then wish to move the focus to a specific control on the form - one typically would use the .SetFocus property.

[hnhpak]

Private Sub CBO_STD_ID_AfterUpdate()

If IsNull([Screen].ActiveControl) Then
DoCmd.GoToControl "[CBO_STD_ID]"
End If

varActContVal = [Screen].ActiveControl
DoCmd.GoToRecord , , acGoTo, Screen.ActiveControl

End Sub

Screen.ActiveControl is the value input by unbounded comboBox.
This the value of Filed ID of the table/Query rawsource of the form.

I want to go to the record where condition ID=[Screen].[ActiveControl]

The above mentioned code works well for first 39 records and then it react strangely and gives wrong results.

On investigation into table it found that ID number form 1-39 are without break, then records having ID 40, 41,44,67,92,97,137,146,191 are deleted from table.

Problem came onward from record having ID more then 39. Results are as under:

on selection from 1-39 go to correct record
on Selection ID 42 it goes to ID 45 two records forward
on Selection ID 45 it goes to ID 48 three records forward
on Selection ID 66 it goes to ID 70 four records forward
on Selection ID 68 it goes to ID 72 five records forward
On Selection ID 93 it goes to ID 104 six records forward .... and so on

On selecting last 8 to 10 records a run time error message says ' You can not go to the specific record'
I am unable to understand the behavior of database in this regards. If you understand please let me know how can i fix it.

Thanks and regards

Haseebul Hasan

[orange]

What is special about the 41st record?

[hnhpak]

What is special about the 41st record?

ID is the primary key of the table
and records related to ID 40, 41,44,67,92,97,137,146,191 are deleted from table.

What I understands from this behavior of comboBox record selection, it goes to record number serial from top,
and whereas the value of Screen.ActiveControl is the ID number of primary key, which is higher then the serial number of the record.

I tried where ID=Screen.ActiveControl but still not successful.

[Missinglinq]

Actually, your post is confusing for a number of reasons. What, exactly, are you trying to do with [Screen].ActiveControl?

If you are in the sub CBO_STD_ID_AfterUpdate(), the [Screen].ActiveControl is the CBO_STD_ID Combobox...and you're already in that Control!

And since the [Screen].ActiveControl is a Combobox

1. You have to enter or change the Value of CBO_STD_ID in order to arrive at the CBO_STD_ID_AfterUpdate event.
2. You cannot select Null from the Combobox Dropdown
3. The only way your code can ever fire, from the the CBO_STD_ID_AfterUpdate event, is if you have a selection made from the Combobox and then delete that selection.

So, once again, what are you trying to do with [Screen].ActiveControl...or maybe a better question is what Control do you think you're referring to with [Screen].ActiveControl?

Linq ;0)>

[canisd]

Forgive my ignorance, as I am still very much a novice in terms of programming. But aside what the OP is trying to do with the control (which I admit I don't fully understand), would not .FindRecord or .SearchForRecord be want is needed not .GoToRecord? Maybe the SearchForRecord with a WhereCondition of ID=Screen.ActiveControl?

My understanding is that GoToRecord goes to a nth record in the table which would be independent of the ID number even if it is an autonumber. This would also explain the why the last few records give and error, as the actual record count of the table is the ID minus the number of deleted records.