Form Problem!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form [weekend_vol] with a subform [human_calc] that keeps track of
volunteers to work the weekend shift. After choosing a weekend date from the
drop down menu, the subform populates...the subform will display how many
people are needed for that weekend([Capacity]), how many spots have already
been taken([Spots_Taken]), and how many spots are available([Capacity -
Spots_Taken]). employees may then add their name and op id and click save to
submit that they want to work that weekend. Here's my problem. As soon as
there are 0 spots available, I do not want people to be able to volunteer for
that weekend. Right now, my form still allows people to volunteer and the
spots_available will display -1 spots available and so forth. Basically, as
soon as there are 0 spots, and someone tries to volunteer for that weekend, i
would like a message box to generate saying there are no more spots available
and i would like for the record NOT to save, so that there isn't a negative
number displayed in the spots_available field. Any help would be
appreciated!!! thanks in advance.
 
Use the AfterUpdate event of the combo box on the main form to test the
value of the "spots available" quantity, and if it's zero, then hide the
subform and display a message to the user.
 
Would you know what code i would use? I am not that great with VB. also,
how do i keep the record from saving when the user clicks the save button on
the main form?

thank you,
DMS

Ken Snell said:
Use the AfterUpdate event of the combo box on the main form to test the
value of the "spots available" quantity, and if it's zero, then hide the
subform and display a message to the user.

--

Ken Snell
<MS ACCESS MVP>

DMS said:
I have a form [weekend_vol] with a subform [human_calc] that keeps track of
volunteers to work the weekend shift. After choosing a weekend date from
the
drop down menu, the subform populates...the subform will display how many
people are needed for that weekend([Capacity]), how many spots have
already
been taken([Spots_Taken]), and how many spots are available([Capacity -
Spots_Taken]). employees may then add their name and op id and click save
to
submit that they want to work that weekend. Here's my problem. As soon
as
there are 0 spots available, I do not want people to be able to volunteer
for
that weekend. Right now, my form still allows people to volunteer and the
spots_available will display -1 spots available and so forth. Basically,
as
soon as there are 0 spots, and someone tries to volunteer for that
weekend, i
would like a message box to generate saying there are no more spots
available
and i would like for the record NOT to save, so that there isn't a
negative
number displayed in the spots_available field. Any help would be
appreciated!!! thanks in advance.
Click to expand...
Click to expand...
 
I'm afraid I don't have enough details about your form's setup nor how the
user will interact with it, so I cannot suggest specific code at the moment.
I can envision two scenarios that you would need to handle:
(1) the user selects the weekend date on the main form and no spots
remain for it already;
(2) the user has entered his/her name into the subform as the last
"spot" and you want to close down the subform.

Any other scenarios that need to be considered?

How do you know the number of spots still open? Do you store a number for
the "spots remaining"? Do you only allow a certain number of signups for
each weekend date? Need to know more about the data and table structure.
--

Ken Snell
<MS ACCESS MVP>



DMS said:
Would you know what code i would use? I am not that great with VB. also,
how do i keep the record from saving when the user clicks the save button
on
the main form?

thank you,
DMS

Ken Snell said:
Use the AfterUpdate event of the combo box on the main form to test the
value of the "spots available" quantity, and if it's zero, then hide the
subform and display a message to the user.

--

Ken Snell
<MS ACCESS MVP>

DMS said:
I have a form [weekend_vol] with a subform [human_calc] that keeps track
of
volunteers to work the weekend shift. After choosing a weekend date
from
the
drop down menu, the subform populates...the subform will display how
many
people are needed for that weekend([Capacity]), how many spots have
already
been taken([Spots_Taken]), and how many spots are available([Capacity -
Spots_Taken]). employees may then add their name and op id and click
save
to
submit that they want to work that weekend. Here's my problem. As
soon
as
there are 0 spots available, I do not want people to be able to
volunteer
for
that weekend. Right now, my form still allows people to volunteer and
the
spots_available will display -1 spots available and so forth.
Basically,
as
soon as there are 0 spots, and someone tries to volunteer for that
weekend, i
would like a message box to generate saying there are no more spots
available
and i would like for the record NOT to save, so that there isn't a
negative
number displayed in the spots_available field. Any help would be
appreciated!!! thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
The manager will enter how many volunteers he needs for that weekend. That
number is the capacity. Spots available = Capacity - the count of people who
have volutneered for that weekend. The scenario that I really need help with
is:

The person chooses the weekend and there are 0 spots available, yet they
still enter their name and click save so it saves the record and now spots
available = -1.

Thanks for all your help thus far.

Darlene

Ken Snell said:
I'm afraid I don't have enough details about your form's setup nor how the
user will interact with it, so I cannot suggest specific code at the moment.
I can envision two scenarios that you would need to handle:
(1) the user selects the weekend date on the main form and no spots
remain for it already;
(2) the user has entered his/her name into the subform as the last
"spot" and you want to close down the subform.

Any other scenarios that need to be considered?

How do you know the number of spots still open? Do you store a number for
the "spots remaining"? Do you only allow a certain number of signups for
each weekend date? Need to know more about the data and table structure.
--

Ken Snell
<MS ACCESS MVP>



DMS said:
Would you know what code i would use? I am not that great with VB. also,
how do i keep the record from saving when the user clicks the save button
on
the main form?

thank you,
DMS

Ken Snell said:
Use the AfterUpdate event of the combo box on the main form to test the
value of the "spots available" quantity, and if it's zero, then hide the
subform and display a message to the user.

--

Ken Snell
<MS ACCESS MVP>

I have a form [weekend_vol] with a subform [human_calc] that keeps track
of
volunteers to work the weekend shift. After choosing a weekend date
from
the
drop down menu, the subform populates...the subform will display how
many
people are needed for that weekend([Capacity]), how many spots have
already
been taken([Spots_Taken]), and how many spots are available([Capacity -
Spots_Taken]). employees may then add their name and op id and click
save
to
submit that they want to work that weekend. Here's my problem. As
soon
as
there are 0 spots available, I do not want people to be able to
volunteer
for
that weekend. Right now, my form still allows people to volunteer and
the
spots_available will display -1 spots available and so forth.
Basically,
as
soon as there are 0 spots, and someone tries to volunteer for that
weekend, i
would like a message box to generate saying there are no more spots
available
and i would like for the record NOT to save, so that there isn't a
negative
number displayed in the spots_available field. Any help would be
appreciated!!! thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Lets try starting here: whats the name of your chose-a-weekend dropdown box,
whats the name of your subform, whats the name of your save button and is it
on the subform or on the main parent form, whats the name of your spots-
remaining field and I assume that it's on your subform. what code is already
associated with your chose-a-weekend dropdown afterupdate event, and what
code is already associated with your save buttons OnClick event. lets start
there and see if we cant figure something out.
 
Weekend drop down box name: weekend
Subform: human_calc
Save button: Save1 (it's on the main form)
Spots remaining field: Sptos_Available (on the subform)
I have no code on my afterupdate when somone chooses a weekend
On my save button, i just have regular code thats generated for the save
function.

Thank you so much for taking the time to look into this.

Darlene
 
OK so your layout is a pinch different than what I envisioned.
I'm guessing then, that your main form is the data entry form and your
subform is used only to display the spots remaining.
whats your main forms record source, your subforms record source, and through
what method does your subform update to reflect info pertinent to your main
form (i.e. is there a LinkChildField, LinkMasterField set on your subform or
does the record source query for your subform use elements of the main form
to filter its records, or something similar) in other words, how does your
subform know what weekend your main form is set to so that it can show the
correct spots remaining. cuz I'm thinking that your save button just needs to
requery then check the value of spots_remaining then take action based on
that value (like if is 0 then do this, if greater than 0 then do that) but
you also need a method of doing the same thing when a date is chosen by the
user so they're not allowed to save, so i'm wondering what method your sub
uses to know what weekend your main form is talking about.
Weekend drop down box name: weekend
Subform: human_calc
Save button: Save1 (it's on the main form)
Spots remaining field: Sptos_Available (on the subform)
I have no code on my afterupdate when somone chooses a weekend
On my save button, i just have regular code thats generated for the save
function.

Thank you so much for taking the time to look into this.

Darlene
Lets try starting here: whats the name of your chose-a-weekend dropdown box,
whats the name of your subform, whats the name of your save button and is it
Click to expand...
[quoted text clipped - 3 lines]
code is already associated with your save buttons OnClick event. lets start
there and see if we cant figure something out.
Click to expand...
Click to expand...
 
what all is in your subform. currently, what all is the subform doing for you.
 
In a nutshell, I'm thinking Ken's reply is pretty much the simple answer. on
your AfterUpdate event for your combobox you'd maybe put some code similar to
this:

weekend_AfterUpdate()
if Human_Calc.form!spots_available=0 then
save1.enabled=false 'disables save button
else
save1.enabled=true 'if it's been turned off then some peice of code has to
turn it back on when appropriate
end if

maybe even after the save1.enabled=false you could put something like
msgbox "there are no more spots for this weekend available."

you'd have to put the same code the save button just incase somebody tries to
come along and save their name to the same weekend as the last person without
changing the weekend combobox. maybe with out the msgbox though.
 
I tried running this code in the after update event; however, there is an
error and it says it does not recognize the spots_available field. But I
double checked it making sure I spelled it right, but it still does not
recognize it..any idea why?

Thank you,
Darlene
 
your subforms definitely called human_calc and your field on that sub is
definitely called spots_available and the save1 button is on the parent form?
the spots_available field is not inside another subform inside the 1st
subform is it?
 
and the weekend field is also on your main form? or is it in the subform on
in some other sub form. the spots_available field, is it an unbound textbox?
does it disappear when the sub goes to a new record or goes to a weekend that
has no info recorded for it yet?
 
The subform "Human_calc" is a query and the control source is the name of the
query. Yes, it does disappear when the sub goes to a new record or to a
weekend that has no info recorded for it yet.
 
sorry about the lag time. my days have suddenly become alot busier than they
were at the beginning of this week. I will put some thought into your issue
given the answers you've gave me and reply when I can. hopefully somebody who
currently has more free time than I now have can jump in so you dont have to
wait around on me.
 
so then, what is the SQL for this human_calc query, what are all the controls
and their control sources for the subform, and what are all the controls and
their controlsources for the main form. also what child-master link does the
subform and the main form have. (when all else fails, we'll just get all the
info about everything and weed out what we dont need later ;) ).
 
Back
Top