How to go to a specific record within continuous forms

[Franc Sutherland]

Hello everyone,

I have a large table of parts, around 50,000 records. There is a separate
autonumber id and part number. The continuous forms form which is used to
view the data sorts on part number and not autonumber id. I want to make a
quick pop-up tool which will act like Find (CTRL + F), but which will give a
drop-down of the part numbers as they are long and complicated and it is easy
to mis-type them.

I have tried using GoToRecord. It performs the action, but it moves
backwards or forwards from a certain position. I can use the GoToFirst
method in the previous line to give it a consistent point of reference, but I
can't find a way of identifying the position of a certain part within the
recordset.

I also tried FindRecord, but this didn't actually go to the specific record
within the set.

Where am I going wrong?

Thanks,

Franc.

 

[Allen Browne]

Presumably one part number shows up multiple times amongst the 50k records,
so how about you filter the form to the part number?

You could place an unbound combo named cboPart in the Form Header section,
and use its AfterUpdate event procedure to filter the form like this:

Private Sub cboPart_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.cboPart) Then
If Me.FilterOn Then Me.FilterOn = False
Else
strWhere = "[Part] = """ & Me.cboPart & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If Part is actually a Number field (not a Text field), remove the extra
quotes, i.e.:
strWhere = "[Part] = " & Me.cboPart

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Franc Sutherland]

Hi Allen,

All the part numbers should be unique. It's a pricing table, rather than a
transactional table of ins and outs. Thanks for the code you wrote. What
does the .dirty line do?

Thanks,

Franc.

Presumably one part number shows up multiple times amongst the 50k records,
so how about you filter the form to the part number?

You could place an unbound combo named cboPart in the Form Header section,
and use its AfterUpdate event procedure to filter the form like this:

Private Sub cboPart_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.cboPart) Then
If Me.FilterOn Then Me.FilterOn = False
Else
strWhere = "[Part] = """ & Me.cboPart & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If Part is actually a Number field (not a Text field), remove the extra
quotes, i.e.:
strWhere = "[Part] = " & Me.cboPart

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hello everyone,

I have a large table of parts, around 50,000 records. There is a separate
autonumber id and part number. The continuous forms form which is used to
view the data sorts on part number and not autonumber id. I want to make
a
quick pop-up tool which will act like Find (CTRL + F), but which will give
a
drop-down of the part numbers as they are long and complicated and it is
easy
to mis-type them.

I have tried using GoToRecord. It performs the action, but it moves
backwards or forwards from a certain position. I can use the GoToFirst
method in the previous line to give it a consistent point of reference,
but I
can't find a way of identifying the position of a certain part within the
recordset.

I also tried FindRecord, but this didn't actually go to the specific
record
within the set.

Where am I going wrong?

Thanks,

Franc.

 

[Allen Browne]

When you start editing a record in a bound form, Access sets the form's
Dirty property to True. Setting it to False forces Access to save the
record. This is one of the few techniques that works in all 32-bit versions
of Access, whether the form has focus or not, and gives an trappable error
message if the save fails. (It notifies you that the propety could not be
set.)

I'm not sure about the wisdom of loading 50k records into a combo, but if
you want to do it that way, this code will take you to the particular record
instead of filtering the form:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If you are comfortable with VBA code, delay-loading the combo could work:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi Allen,

All the part numbers should be unique. It's a pricing table, rather than
a
transactional table of ins and outs. Thanks for the code you wrote. What
does the .dirty line do?

Thanks,

Franc.

Presumably one part number shows up multiple times amongst the 50k
records,
so how about you filter the form to the part number?

You could place an unbound combo named cboPart in the Form Header
section,
and use its AfterUpdate event procedure to filter the form like this:

Private Sub cboPart_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.cboPart) Then
If Me.FilterOn Then Me.FilterOn = False
Else
strWhere = "[Part] = """ & Me.cboPart & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If Part is actually a Number field (not a Text field), remove the extra
quotes, i.e.:
strWhere = "[Part] = " & Me.cboPart

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hello everyone,

I have a large table of parts, around 50,000 records. There is a
separate
autonumber id and part number. The continuous forms form which is used
to
view the data sorts on part number and not autonumber id. I want to
make
a
quick pop-up tool which will act like Find (CTRL + F), but which will
give
a
drop-down of the part numbers as they are long and complicated and it
is
easy
to mis-type them.

I have tried using GoToRecord. It performs the action, but it moves
backwards or forwards from a certain position. I can use the GoToFirst
method in the previous line to give it a consistent point of reference,
but I
can't find a way of identifying the position of a certain part within
the
recordset.

I also tried FindRecord, but this didn't actually go to the specific
record
within the set.

Where am I going wrong?

Thanks,

Franc.

 

[Franc Sutherland]

Hi Allen,

Thanks for that, that's excellent. I'm afraid I can't use the filter as the
user wants to see the records around it as well, as though he were looking at
a spreadsheet.

I like the idea of the re-setting the Dirty property to false to force a
save. What kind of situations would one use this in? I realise this is
QUITE a vague question, but I'm not managing to picture the appropriate
scenarios, and definitely won't picture them all.

I've been looking at your website as well, thanks for the links! It's a
really good website. Very good flow of information.

Regards,

Franc.

When you start editing a record in a bound form, Access sets the form's
Dirty property to True. Setting it to False forces Access to save the
record. This is one of the few techniques that works in all 32-bit versions
of Access, whether the form has focus or not, and gives an trappable error
message if the save fails. (It notifies you that the propety could not be
set.)

I'm not sure about the wisdom of loading 50k records into a combo, but if
you want to do it that way, this code will take you to the particular record
instead of filtering the form:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If you are comfortable with VBA code, delay-loading the combo could work:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi Allen,

All the part numbers should be unique. It's a pricing table, rather than
a
transactional table of ins and outs. Thanks for the code you wrote. What
does the .dirty line do?

Thanks,

Franc.

Presumably one part number shows up multiple times amongst the 50k
records,
so how about you filter the form to the part number?

You could place an unbound combo named cboPart in the Form Header
section,
and use its AfterUpdate event procedure to filter the form like this:

Private Sub cboPart_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.cboPart) Then
If Me.FilterOn Then Me.FilterOn = False
Else
strWhere = "[Part] = """ & Me.cboPart & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If Part is actually a Number field (not a Text field), remove the extra
quotes, i.e.:
strWhere = "[Part] = " & Me.cboPart

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hello everyone,

I have a large table of parts, around 50,000 records. There is a
separate
autonumber id and part number. The continuous forms form which is used
to
view the data sorts on part number and not autonumber id. I want to
make
a
quick pop-up tool which will act like Find (CTRL + F), but which will
give
a
drop-down of the part numbers as they are long and complicated and it
is
easy
to mis-type them.

I have tried using GoToRecord. It performs the action, but it moves
backwards or forwards from a certain position. I can use the GoToFirst
method in the previous line to give it a consistent point of reference,
but I
can't find a way of identifying the position of a certain part within
the
recordset.

I also tried FindRecord, but this didn't actually go to the specific
record
within the set.

Where am I going wrong?

Thanks,

Franc.

 

[Allen Browne]

Feedback appreciated.

As you probably realize, Access saves the current record automatically in
many scenarios, e.g. when you tab past the last control, close the form,
apply a filter, change the sort, requery the form, etc. I recommend that you
explicitly save when you programmatically do anything that would require
Access to save the record.

What this does is to clear the queue of interacting events in a sensible
way, and give you a meaningful error message if the save fails for any
reason (e.g. a required field is missing.) This avoids seemingly weird
errors that otherwise make little sense when you try to debug them: things
like "update without edit", or "cancelled previous operation", or "no
current record", and other things the cause of which is abundantly unclear.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi Allen,

Thanks for that, that's excellent. I'm afraid I can't use the filter as
the
user wants to see the records around it as well, as though he were looking
at
a spreadsheet.

I like the idea of the re-setting the Dirty property to false to force a
save. What kind of situations would one use this in? I realise this is
QUITE a vague question, but I'm not managing to picture the appropriate
scenarios, and definitely won't picture them all.

I've been looking at your website as well, thanks for the links! It's a
really good website. Very good flow of information.

Regards,

Franc.

When you start editing a record in a bound form, Access sets the form's
Dirty property to True. Setting it to False forces Access to save the
record. This is one of the few techniques that works in all 32-bit
versions
of Access, whether the form has focus or not, and gives an trappable
error
message if the save fails. (It notifies you that the propety could not be
set.)

I'm not sure about the wisdom of loading 50k records into a combo, but if
you want to do it that way, this code will take you to the particular
record
instead of filtering the form:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

If you are comfortable with VBA code, delay-loading the combo could work:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html

message

Hi Allen,

All the part numbers should be unique. It's a pricing table, rather
than
a
transactional table of ins and outs. Thanks for the code you wrote.
What
does the .dirty line do?

Thanks,

Franc.

:

Presumably one part number shows up multiple times amongst the 50k
records,
so how about you filter the form to the part number?

You could place an unbound combo named cboPart in the Form Header
section,
and use its AfterUpdate event procedure to filter the form like this:

Private Sub cboPart_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.cboPart) Then
If Me.FilterOn Then Me.FilterOn = False
Else
strWhere = "[Part] = """ & Me.cboPart & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If Part is actually a Number field (not a Text field), remove the
extra
quotes, i.e.:
strWhere = "[Part] = " & Me.cboPart

in
message
Hello everyone,

I have a large table of parts, around 50,000 records. There is a
separate
autonumber id and part number. The continuous forms form which is
used
to
view the data sorts on part number and not autonumber id. I want
to
make
a
quick pop-up tool which will act like Find (CTRL + F), but which
will
give
a
drop-down of the part numbers as they are long and complicated and
it
is
easy
to mis-type them.

I have tried using GoToRecord. It performs the action, but it moves
backwards or forwards from a certain position. I can use the
GoToFirst
method in the previous line to give it a consistent point of
reference,
but I
can't find a way of identifying the position of a certain part
within
the
recordset.

I also tried FindRecord, but this didn't actually go to the specific
record
within the set.