Selecting specific record

[sharontodd]

This seems like it should be pretty simple. Many quite varied approaches
have been attempted without success.

In form frmDivisionWinners, the user is entering a value into an unbound
text box [enter data]. I want to make the division matching that number (Div
# in table tblDivisionsWinners) the current record so that all the combo
boxes (so data can be edited) and list boxes displaying data, are getting
their info from the record desired.

 

[Jeanette Cunningham]

Sharon,
If you have tried many things so far, please give a fuller description of
what you are trying to do.
Which version of access?
Is there a form with subform?
Is there a tab control involved?
What else can you tell us about this form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[sharontodd]

I am using Access 2003. There is no subform but two different table are
being used to draw information to this form Division/Winners and
Registrations. This form is being used to enter and edit winners for each
division. The user enters the division they want into unbound text box
"enter data" . A list box shows the details for that division (number, age
group, level, gender) and the winners, first through fifth. A combo box will
show the registration number for each place (this may be blank or already
have an entry. The user can enter or edit here). There is also a list box
to show the rest of information for that person (name, belt, school, etc).

My problem - to get the info for the correct division to showup. When I
enter the division number and tab, the division increments by one and doesn't
go to the desired record.


A friend suggested looping through all records to find a match - seems kind
of primative but should work. But he wasn't sure how to jump out of the loop
when found. We had this and get an error for "break"

Me.Recordset.MoveFirst
Dim I As Integer
For I = 1 To 500
If Me.Recordset.[enter data] = I Then break
Me.Recordset.MoveNext
Next I


Thought a Docmd.findrecord might work but don't know enough about it to
refine it. My programming background isn't in VB and I don't know the
commands too well yet.

Thanks for any help!

Sharontodd

Sharon,
If you have tried many things so far, please give a fuller description of
what you are trying to do.
Which version of access?
Is there a form with subform?
Is there a tab control involved?
What else can you tell us about this form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

This seems like it should be pretty simple. Many quite varied approaches
have been attempted without success.

In form frmDivisionWinners, the user is entering a value into an unbound
text box [enter data]. I want to make the division matching that number
(Div
# in table tblDivisionsWinners) the current record so that all the combo
boxes (so data can be edited) and list boxes displaying data, are getting
their info from the record desired.

 

[Jeanette Cunningham]

The tables and their relationships also control what is possible on your
form.
I don't have the details about what sort of competition you are running -
sport, raffle, something else?

I assume
a table for competitions
a table for divisions
a table for entrants

a competition can have many divisions

can entrants enter more than one division?

can entrants enter more than one competition?

Assuming that your main form already knows the correct competition -
I would put a combo with all the possible divisions for that competition on
the form.
User selects the division,
the listbox shows all the winning entrants for that division,
user selects the winner of that division and saves it back to the
appropriate table and field.

After user makes a selection from the division combo,
you use the value from the combo to filter the query used
as the row source for the listbox so that you only
see the winners for that division in the listobx.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am using Access 2003. There is no subform but two different table are
being used to draw information to this form Division/Winners and
Registrations. This form is being used to enter and edit winners for each
division. The user enters the division they want into unbound text box
"enter data" . A list box shows the details for that division (number,
age
group, level, gender) and the winners, first through fifth. A combo box
will
show the registration number for each place (this may be blank or already
have an entry. The user can enter or edit here). There is also a list
box
to show the rest of information for that person (name, belt, school, etc).

My problem - to get the info for the correct division to showup. When I
enter the division number and tab, the division increments by one and
doesn't
go to the desired record.


A friend suggested looping through all records to find a match - seems
kind
of primative but should work. But he wasn't sure how to jump out of the
loop
when found. We had this and get an error for "break"

Me.Recordset.MoveFirst
Dim I As Integer
For I = 1 To 500
If Me.Recordset.[enter data] = I Then break
Me.Recordset.MoveNext
Next I


Thought a Docmd.findrecord might work but don't know enough about it to
refine it. My programming background isn't in VB and I don't know the
commands too well yet.

Thanks for any help!

Sharontodd

Sharon,
If you have tried many things so far, please give a fuller description of
what you are trying to do.
Which version of access?
Is there a form with subform?
Is there a tab control involved?
What else can you tell us about this form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

This seems like it should be pretty simple. Many quite varied
approaches
have been attempted without success.

In form frmDivisionWinners, the user is entering a value into an
unbound
text box [enter data]. I want to make the division matching that
number
(Div
# in table tblDivisionsWinners) the current record so that all the
combo
boxes (so data can be edited) and list boxes displaying data, are
getting
their info from the record desired.

 

[sharontodd]

The database controls only one competition (Martial Arts). Each competitor
may enter many divisions. There is one table for the division number, their
description and the registration number of each winning registrant - first
through fifth. A second table contains the information for each registrant.

I had a combo box listing all divisions for user to select from and that was
all working fine, but it took too long to scrool through all divisions while
trying to update at the competition. There may be hundreds of divisions at
the event. I want the user to be able to just type in the division number.
I am using an unbound text box for that.

In the form I am working on, the usr types the division number. List box A
displays the division number and description. Combo box B shows first place
registration number (It's a combo box so it will show the number and allow
for editing. I was thinking I could get it to show the correct record using
the row source property in data property but that's not working - That could
probably become a text box if the form selects the correct record to show for
all data). List box C shows first place registrant's personal info. Combo
box D =second place registration number. List box D second place personal
info, etc.


My problem is getting the division they type in to be the one displayed in
the combo or text box. I can get the correct division in a list box, but I
can't edit.

I hope this gives a good picture of what I need.
Thanks!!!
Sharontodd

The tables and their relationships also control what is possible on your
form.
I don't have the details about what sort of competition you are running -
sport, raffle, something else?

I assume
a table for competitions
a table for divisions
a table for entrants

a competition can have many divisions

can entrants enter more than one division?

can entrants enter more than one competition?

Assuming that your main form already knows the correct competition -
I would put a combo with all the possible divisions for that competition on
the form.
User selects the division,
the listbox shows all the winning entrants for that division,
user selects the winner of that division and saves it back to the
appropriate table and field.

After user makes a selection from the division combo,
you use the value from the combo to filter the query used
as the row source for the listbox so that you only
see the winners for that division in the listobx.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am using Access 2003. There is no subform but two different table are
being used to draw information to this form Division/Winners and
Registrations. This form is being used to enter and edit winners for each
division. The user enters the division they want into unbound text box
"enter data" . A list box shows the details for that division (number,
age
group, level, gender) and the winners, first through fifth. A combo box
will
show the registration number for each place (this may be blank or already
have an entry. The user can enter or edit here). There is also a list
box
to show the rest of information for that person (name, belt, school, etc).

My problem - to get the info for the correct division to showup. When I
enter the division number and tab, the division increments by one and
doesn't
go to the desired record.


A friend suggested looping through all records to find a match - seems
kind
of primative but should work. But he wasn't sure how to jump out of the
loop
when found. We had this and get an error for "break"

Me.Recordset.MoveFirst
Dim I As Integer
For I = 1 To 500
If Me.Recordset.[enter data] = I Then break
Me.Recordset.MoveNext
Next I


Thought a Docmd.findrecord might work but don't know enough about it to
refine it. My programming background isn't in VB and I don't know the
commands too well yet.

Thanks for any help!

Sharontodd

Sharon,
If you have tried many things so far, please give a fuller description of
what you are trying to do.
Which version of access?
Is there a form with subform?
Is there a tab control involved?
What else can you tell us about this form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


This seems like it should be pretty simple. Many quite varied
approaches
have been attempted without success.

In form frmDivisionWinners, the user is entering a value into an
unbound
text box [enter data]. I want to make the division matching that
number
(Div
# in table tblDivisionsWinners) the current record so that all the
combo
boxes (so data can be edited) and list boxes displaying data, are
getting
their info from the record desired.

 

[Jeanette Cunningham]

sharontodd,
Summarising to make sure I understand.
User enters div number in text box.
You want to show only the registration number of winners 1 to 5 for each
division.
My suggestion is to make the form a continuous form.
The form's recordsource is a query based on the table with registrant
details and the table with division numbers.

Make the text box for div number unbound.
Use the after update event of the textbox for div number to filter the form
to show the registration numbers and entrant details for winners 1 - 5 for
the selected division.

Here is an example of using filters.
Replace the obvious with your field and control names.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
Me.Filter = "([City] = """ & Me.txtFilterCity & """) "
Me.FilterOn = True
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
Me.Filter = "([MainName] Like ""*" & Me.txtFilterMainName & "*"") "
Me.FilterOn = True
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterLevel) Then
Me.Filter = "([LevelID] = " & Me.cboFilterLevel & ") "
Me.FilterOn = True
End If

You may wish to open a form for editing that shows only the details for the
winner you select from the filtered form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

The database controls only one competition (Martial Arts). Each
competitor
may enter many divisions. There is one table for the division number,
their
description and the registration number of each winning registrant -
first
through fifth. A second table contains the information for each
registrant.

I had a combo box listing all divisions for user to select from and that
was
all working fine, but it took too long to scrool through all divisions
while
trying to update at the competition. There may be hundreds of divisions
at
the event. I want the user to be able to just type in the division
number.
I am using an unbound text box for that.

In the form I am working on, the usr types the division number. List box
A
displays the division number and description. Combo box B shows first
place
registration number (It's a combo box so it will show the number and allow
for editing. I was thinking I could get it to show the correct record
using
the row source property in data property but that's not working - That
could
probably become a text box if the form selects the correct record to show
for
all data). List box C shows first place registrant's personal info.
Combo
box D =second place registration number. List box D second place personal
info, etc.


My problem is getting the division they type in to be the one displayed in
the combo or text box. I can get the correct division in a list box, but
I
can't edit.

I hope this gives a good picture of what I need.
Thanks!!!
Sharontodd

The tables and their relationships also control what is possible on your
form.
I don't have the details about what sort of competition you are running -
sport, raffle, something else?

I assume
a table for competitions
a table for divisions
a table for entrants

a competition can have many divisions

can entrants enter more than one division?

can entrants enter more than one competition?

Assuming that your main form already knows the correct competition -
I would put a combo with all the possible divisions for that competition
on
the form.
User selects the division,
the listbox shows all the winning entrants for that division,
user selects the winner of that division and saves it back to the
appropriate table and field.

After user makes a selection from the division combo,
you use the value from the combo to filter the query used
as the row source for the listbox so that you only
see the winners for that division in the listobx.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am using Access 2003. There is no subform but two different table are
being used to draw information to this form Division/Winners and
Registrations. This form is being used to enter and edit winners for
each
division. The user enters the division they want into unbound text box
"enter data" . A list box shows the details for that division (number,
age
group, level, gender) and the winners, first through fifth. A combo
box
will
show the registration number for each place (this may be blank or
already
have an entry. The user can enter or edit here). There is also a list
box
to show the rest of information for that person (name, belt, school,
etc).

My problem - to get the info for the correct division to showup. When
I
enter the division number and tab, the division increments by one and
doesn't
go to the desired record.


A friend suggested looping through all records to find a match - seems
kind
of primative but should work. But he wasn't sure how to jump out of
the
loop
when found. We had this and get an error for "break"

Me.Recordset.MoveFirst
Dim I As Integer
For I = 1 To 500
If Me.Recordset.[enter data] = I Then break
Me.Recordset.MoveNext
Next I


Thought a Docmd.findrecord might work but don't know enough about it
to
refine it. My programming background isn't in VB and I don't know the
commands too well yet.

Thanks for any help!

Sharontodd




:

Sharon,
If you have tried many things so far, please give a fuller description
of
what you are trying to do.
Which version of access?
Is there a form with subform?
Is there a tab control involved?
What else can you tell us about this form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


This seems like it should be pretty simple. Many quite varied
approaches
have been attempted without success.

In form frmDivisionWinners, the user is entering a value into an
unbound
text box [enter data]. I want to make the division matching that
number
(Div
# in table tblDivisionsWinners) the current record so that all the
combo
boxes (so data can be edited) and list boxes displaying data, are
getting
their info from the record desired.