editing a table thru a form

[pat67]

Hi all, I currently have a form I use to enter data into a table for
my pool league. What happens is based on a date I select, the home and
away teams are displayed and i have a that gets populated so i can
pick players off of a list for that team. it is a single form and when
i tab thru the fields, the table gets edited. My question is we play
21 games per night so i would like to show 21 lines on my form that i
can populate and then either thru a submit button or tabbing thru the
last field, whichever, then the table would be updated. How can I do
this? Any ideas? Thanks

 

[ManningFan]

I don't understand what you're asking at all, but have you tried
setting your form Default View property to "Continuous Form"? If it
works with one record on a standard single form, it should do the same
on a continuous form.

 

[pat67]

I don't understand what you're asking at all, but have you tried
setting your form Default View property to "Continuous Form"?  If it
works with one record on a standard single form, it should do the same
on a continuous form.

What I have is this

Date: select from dropdown

Home: fills after date is selected Away: fills after date is
selected

ID Date
Winner Team
Loser Team1
autonumber enter manually dropdown combo box
filled after winner dropdown combo box filled
after winner
background
code selected background
code selected
after date
is selected after date is
selected




it is a single form because if I make it continuosly, the winner's
name fills every box when it is selected once and so on. I know I
would not be able to use a combo box and I would need to use a text
box so wha I am looking for is this


ID Date
Winner Team
Loser Team1
1
2
3
4
5
6
7
8
9
10

Submit

I enter the data for the 10 games or whatever, then press submit to
submit the entire form to a table at once

 

[ManningFan]

K. I'm still not 100% clear on what you're doing (and I apologize, my
head's been in a fog lately...) but I can tell you two things;

1) 99% of the time, you do NOT want to use a bound form. It's just
not a good practice, least of all because it makes error trapping much
more difficult than it should be. So, you just want to set up some
text boxes to hold the data.

2) With the above in mind, I think you want to write the contents of
the text boxes to an array, and then use the array to loop through and
populate your table. Just write the stuff that's going to change
(i.e. team name, winner, loser) to the array, and then read the static
info (date, ID) from the form.

Am I close?

 

[pat67]

K.  I'm still not 100% clear on what you're doing (and I apologize, my
head's been in a fog lately...) but I can tell you two things;

1) 99% of the time, you do NOT want to use a bound form.  It's just
not a good practice, least of all because it makes error trapping much
more difficult than it should be.  So, you just want to set up some
text boxes to hold the data.

2) With the above in mind, I think you want to write the contents of
the text boxes to an array, and then use the array to loop through and
populate your table.  Just write the stuff that's going to change
(i.e. team name, winner, loser) to the array, and then read the static
info (date, ID) from the form.

Am I close?

Yes. I have set up text boxes but when I fill one, it fills every one
below it and i am no sure why.

 

[ManningFan]

Yes. I have set up text boxes but when I fill one, it fills every one
below it and i am no sure why.

Make sure your form is a single form. If no textboxes are bound,
there is no reason why one text box should populate any others.

 

[Access Developer]

Manning Fan, your advice is in error. At least 99% of the time, you SHOULD
be using bound forms with Access, and you should learn how error trapping
works so you won't erroneously think it is "more difficult". If you use
unbound forms you have to provide (write and debug) a mass of infrastructure
that is already included with Access, and has been use-tested since 1992,
and doing so will cost you incredible extra amounts of time and energy in
development.

We have discussed this issue before, and I know others have also discussed
it with you. I'd have thought that surely, by now, you'd have invested the
time and energy to learn how Access works, and be following best practices.

 

[ManningFan]

Manning Fan, your advice is in error.  At least 99% of the time, you SHOULD
be using bound forms with Access, and you should learn how error trapping
works so you won't erroneously think it is "more difficult".  If you use
unbound forms you have to provide (write and debug) a mass of infrastructure
that is already included with Access, and has been use-tested since 1992,
and doing so will cost you incredible extra amounts of time and energy in
development.

We have discussed this issue before, and I know others have also discussed
it with you.  I'd have thought that surely, by now, you'd have investedthe
time and energy to learn how Access works, and be following best practices.

 --
 Larry Linson, Microsoft Office Access MVP

What works for you doesn't work for me. I find Access makes far too
many assumptions (as do ALL Microsux products) and it's easier and you
have more control to do everything yourself. For instance, once you
type into a bound control, you have automatically created a record.
This means you have to do excessive error trapping and write
unnecessary code to delete the record, as the built-in "undo" function
is buggy at best. Putting a "Submit" button on a form is much cleaner
and requires less code.

Thanks for your input.

 

[Douglas J. Steele]

While I agree that it's your perogative to use unbound forms if you want to,
I just wanted to clear up an error in your post.

Typing into a bound control does NOT automatically create a record (although
I will concede that it assigns a value to AutoNumber fields whether or not
the record is actually saved). You must move away from the record before
Access saves it, and you can easily trap this by putting code in the form's
BeforeUpdate event.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

Manning Fan, your advice is in error. At least 99% of the time, you SHOULD
be using bound forms with Access, and you should learn how error trapping
works so you won't erroneously think it is "more difficult". If you use
unbound forms you have to provide (write and debug) a mass of
infrastructure
that is already included with Access, and has been use-tested since 1992,
and doing so will cost you incredible extra amounts of time and energy in
development.

We have discussed this issue before, and I know others have also discussed
it with you. I'd have thought that surely, by now, you'd have invested the
time and energy to learn how Access works, and be following best
practices.

What works for you doesn't work for me. I find Access makes far too
many assumptions (as do ALL Microsux products) and it's easier and you
have more control to do everything yourself. For instance, once you
type into a bound control, you have automatically created a record.
This means you have to do excessive error trapping and write
unnecessary code to delete the record, as the built-in "undo" function
is buggy at best. Putting a "Submit" button on a form is much cleaner
and requires less code.

Thanks for your input.

 

[pat67]

Manning Fan, your advice is in error.  At least 99% of the time, you SHOULD
be using bound forms with Access, and you should learn how error trapping
works so you won't erroneously think it is "more difficult".  If you use
unbound forms you have to provide (write and debug) a mass of infrastructure
that is already included with Access, and has been use-tested since 1992,
and doing so will cost you incredible extra amounts of time and energy in
development.

We have discussed this issue before, and I know others have also discussed
it with you.  I'd have thought that surely, by now, you'd have investedthe
time and energy to learn how Access works, and be following best practices.

 --
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access


 > K.  I'm still not 100% clear on what you're doing (and I apologize, my
 > head's been in a fog lately...) but I can tell you two things;
 >
 > 1) 99% of the time, you do NOT want to use a bound form.  It's just
 > not a good practice, least of all because it makes error trapping much
 > more difficult than it should be.  So, you just want to set up some
 > text boxes to hold the data.

If i use unbound text boxes and combo boxes, then i need to have a
submit button to send to a table. what i need is the code behind that
button

 

[Access Developer]

What works for you doesn't work for
me. I find Access makes far too many
assumptions (as do ALL Microsux products)

You are certainly entitled to your opinion. The vast majority of Access
users/developers have found that they can, relatively easily, learn the
"Access Way" and take advantage of the uncounted hours of development and
testing through even more hours of use by clients. When I first began with
Access, before I undertood how it worked, I thought, as you do, that unbound
forms were easier -- but, the more I learned, the less I used unbound forms,
because I found how much easier it was to take advantage of what Access
provides.

and it's easier and you have more control
to do everything yourself. For instance,
once you type into a bound control, you
have automatically created a record.

As Doug pointed out, this is simply not so. Explicitly doing a Save, moving
off the current record, closing the form, or moving to a subform will save
the record, but code in the Before Update event can be used to prevent the
save.

This means you have to do excessive error
trapping and write unnecessary code to
delete the record,

It is trivial to cancel the update. Access provides all the functionality
needed to do so with far less error trapping and code than required to
handle data with an unbound form. Switchboards and menu forms, of course,
are not handling data and not subject to the "bound/unbound" discussions.

as the built-in "undo" function is buggy
at best. Putting a "Submit" button on a
form is much cleaner and requires less code.

As I said, you are entitled to your opinion. But, when I think you are
misleading someone, I am entitled to express my opinion on that subject.

Thanks for your input.

Thanks for your explanation.

 

[Access Developer]

Pat,

My advice was, and is, *do not use unbound forms* (text boxes and combo
boxes don't exist separately from forms or reports) to handle
(add/update/delete). Learn enough about Access to use bound forms. I do not
offer advice on how to do things in a way that I think is inappropriate,
because sooner or later, the recipient of that advice will suffer for doing
those things in the inappropriate way.

Others may not feel such a responsibility, and may be willing to suggest
code to you.

 

[pat67]

Pat,

My advice was, and is, *do not use unbound forms* (text boxes and combo
boxes don't exist separately from forms or reports) to handle
(add/update/delete). Learn enough about Access to use bound forms. I do not
offer advice on how to do things in a way that I think is inappropriate,
because sooner or later, the recipient of that advice will suffer for doing
those things in the inappropriate way.

Others may not feel such a responsibility, and may be willing to suggest
code to you.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


 > If i use unbound text boxes and combo boxes,
 > then i need to have a submit button to send to
 > a table. what i need is the code behind that
 > button

It doesn't matter to me whether i use bound or undound text boxes. The
issue I am having with using bound boxes is i can only enter 1 line of
data at a time. Unless you can explain it to me differently. The point
is i have 21 line of data to add for each match. I have been doing it
one at a time but i sometimes make entry errors and i don't know this
until after i am done. what i am looking to do is add all 21 lines of
data to the table at one time. This is where i am having difficulty.

 

[John W. Vinson]

Hi all, I currently have a form I use to enter data into a table for
my pool league. What happens is based on a date I select, the home and
away teams are displayed and i have a that gets populated so i can
pick players off of a list for that team. it is a single form and when
i tab thru the fields, the table gets edited. My question is we play
21 games per night so i would like to show 21 lines on my form that i
can populate and then either thru a submit button or tabbing thru the
last field, whichever, then the table would be updated. How can I do
this? Any ideas? Thanks

I would certainly recommend a bound, continuous form... but if you're entering
the *same data* 21 times on the 21 lines, your table structure is evidently
incorrect. You should have separate tables of Teams, Games, and Players;
information about teams should exist only in the Teams table, information
about Games only in the Games table (once per game!!), and so on. It should
not be necessary to reenter *any* data.

What tables do you have? How are they related? I've never been in a pool
league so I'm not sure how the games are set up; but if you have to enter the
same score multiple times (for multiple players?) I suspect your table
structure could be at fault, not the form.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

I would certainly recommend a bound, continuous form... but if you're entering
the *same data* 21 times on the 21 lines, your table structure is evidently
incorrect. You should have separate tables of Teams, Games, and Players;
information about teams should exist only in the Teams table, information
about Games only in the Games table (once per game!!), and so on. It should
not be necessary to reenter *any* data.

What tables do you have? How are they related? I've never been in a pool
league so I'm not sure how the games are set up; but if you have to enterthe
same score multiple times (for multiple players?) I suspect your table
structure could be at fault, not the form.

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

I got it. the problme was because i was using combo boxes for the
player names that i used code to populate. so now i used a continuous
form with the bound text boxes and just used code to populate a list
box in the header for the players. Then i just copy the players into
the text boxes in the form. i then have a button that takes the table
i created for the results that week and puts the data into the overall
results table. it's working fine now. Thanks

 

[pat67]

I got it. the problme was because i was using combo boxes for the
player names that i used code to populate. so now i used a continuous
form with the bound text boxes and just used code to populate a list
box in the header for the players. Then i just copy the players into
the text boxes in the form. i then have a button that takes the table
i created for the results that week and puts the data into the overall
results table. it's working fine now. Thanks- Hide quoted text -

- Show quoted text -

i do have one other question unrelated to this form. but is there a
way to shiow the rank the reuslts from a form. like in a report i can
use a running sum over the report and i wil get 1,2,3,4 etc. is there
a way to do that in a form or even in a query? I can't find anything