Change field value in table not in form record source

[WCDoan]

I'm not sure if my subject description explains what I need and not sure I
can explain my problem clearly enough, but here goes. I have a form that has
1 table (tbl-boxes) as its record source. It has two other tables that are
used for look-ups in combo boxes (tbl-department and tbl-location). The
tbl-location has a field in it that shows availablity. Once a location is
used I need to mark this location as unavailable by changing the value from
yes to no. How do I change the value in the other table that is used for
look-up purposes? Will I have to add it to the form or is there another way?
This location problem arose after the form had been completed and is an
afterthought that I have to now allow for, c'est la vie. Thanks for any
help anyone can give.
RandyM

 

[Carl Rapson]

In the AfterUpdate event of the control that contains the location, update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use your own
field and control names.

Carl Rapson

 

[WCDoan]

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's text),
and Location-Used. In the code you gave me- Location-Used would go where
[Available] is? The combo box control is Box-Location would this be the
Me.txtLocation in the code? And would Location-Number go where [location] is
in the code. Unforturnately, where I work I wear several hats and so I
haven't had to do anything in Access in about 9 months so, as the old saying
goes, "if you don't use it, you lose it." I've lost it. Thanks again for
your help, I really do appreciate it.
RandyM

In the AfterUpdate event of the control that contains the location, update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use your own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and not sure I
can explain my problem clearly enough, but here goes. I have a form that
has
1 table (tbl-boxes) as its record source. It has two other tables that are
used for look-ups in combo boxes (tbl-department and tbl-location). The
tbl-location has a field in it that shows availablity. Once a location is
used I need to mark this location as unavailable by changing the value
from
yes to no. How do I change the value in the other table that is used for
look-up purposes? Will I have to add it to the form or is there another
way?
This location problem arose after the form had been completed and is an
afterthought that I have to now allow for, c'est la vie. Thanks for any
help anyone can give.
RandyM

 

[Carl Rapson]

It depends on how you're setting the location in tbl-boxes. What are you
storing in tbl-boxes, Location-Key or Location-Number? What is in the bound
column of your location combo box (Box-Location)?

Let's make some assumptions. You should be storing Location-Key in
tbl-boxes, so let's assume you are doing so. Furthermore, Location-Key
should also be the bound column on Box-Location, although you can show only
Location-Number. If those assumptions are correct, then you would use:

UPDATE [tbl-location] SET [Location-Used]=True WHERE [Location-Key]=" &
Me.Box-Location

If the assumptions aren't true, maybe you can glean enough from this example
to see what you need to change.

Carl Rapson

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's text),
and Location-Used. In the code you gave me- Location-Used would go where
[Available] is? The combo box control is Box-Location would this be the
Me.txtLocation in the code? And would Location-Number go where [location]
is
in the code. Unforturnately, where I work I wear several hats and so I
haven't had to do anything in Access in about 9 months so, as the old
saying
goes, "if you don't use it, you lose it." I've lost it. Thanks again
for
your help, I really do appreciate it.
RandyM

In the AfterUpdate event of the control that contains the location,
update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use your
own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and not
sure I
can explain my problem clearly enough, but here goes. I have a form
that
has
1 table (tbl-boxes) as its record source. It has two other tables that
are
used for look-ups in combo boxes (tbl-department and tbl-location). The
tbl-location has a field in it that shows availablity. Once a location
is
used I need to mark this location as unavailable by changing the value
from
yes to no. How do I change the value in the other table that is used
for
look-up purposes? Will I have to add it to the form or is there another
way?
This location problem arose after the form had been completed and is an
afterthought that I have to now allow for, c'est la vie. Thanks for
any
help anyone can give.
RandyM

 

[WCDoan]

Carl,
That did it! Thanks so much. Somethign I don't understand is why you have
to have the quote and the ampersand before the Me.Box_Location. I thought the
whole SQL statment had to be included in quotes, but this way it's just up to
the equal sign. It's working like I want it too, but I was just curious as to
why it's done the way it is. Anyhoo, thanks so much for your expert help.
RandyM

It depends on how you're setting the location in tbl-boxes. What are you
storing in tbl-boxes, Location-Key or Location-Number? What is in the bound
column of your location combo box (Box-Location)?

Let's make some assumptions. You should be storing Location-Key in
tbl-boxes, so let's assume you are doing so. Furthermore, Location-Key
should also be the bound column on Box-Location, although you can show only
Location-Number. If those assumptions are correct, then you would use:

UPDATE [tbl-location] SET [Location-Used]=True WHERE [Location-Key]=" &
Me.Box-Location

If the assumptions aren't true, maybe you can glean enough from this example
to see what you need to change.

Carl Rapson

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's text),
and Location-Used. In the code you gave me- Location-Used would go where
[Available] is? The combo box control is Box-Location would this be the
Me.txtLocation in the code? And would Location-Number go where [location]
is
in the code. Unforturnately, where I work I wear several hats and so I
haven't had to do anything in Access in about 9 months so, as the old
saying
goes, "if you don't use it, you lose it." I've lost it. Thanks again
for
your help, I really do appreciate it.
RandyM

In the AfterUpdate event of the control that contains the location,
update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use your
own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and not
sure I
can explain my problem clearly enough, but here goes. I have a form
that
has
1 table (tbl-boxes) as its record source. It has two other tables that
are
used for look-ups in combo boxes (tbl-department and tbl-location). The
tbl-location has a field in it that shows availablity. Once a location
is
used I need to mark this location as unavailable by changing the value
from
yes to no. How do I change the value in the other table that is used
for
look-up purposes? Will I have to add it to the form or is there another
way?
This location problem arose after the form had been completed and is an
afterthought that I have to now allow for, c'est la vie. Thanks for
any
help anyone can give.
RandyM

 

[Carl Rapson]

You're building the SQL string by concatenating the value from Box_Location
onto the rest of it. Any time you need to include the contents of a control
in a SQL string that's how you'll do it (in VBA code, anyway).

I'm glad it's working.

Carl Rapson

Carl,
That did it! Thanks so much. Somethign I don't understand is why you have
to have the quote and the ampersand before the Me.Box_Location. I thought
the
whole SQL statment had to be included in quotes, but this way it's just up
to
the equal sign. It's working like I want it too, but I was just curious as
to
why it's done the way it is. Anyhoo, thanks so much for your expert help.
RandyM

It depends on how you're setting the location in tbl-boxes. What are you
storing in tbl-boxes, Location-Key or Location-Number? What is in the
bound
column of your location combo box (Box-Location)?

Let's make some assumptions. You should be storing Location-Key in
tbl-boxes, so let's assume you are doing so. Furthermore, Location-Key
should also be the bound column on Box-Location, although you can show
only
Location-Number. If those assumptions are correct, then you would use:

UPDATE [tbl-location] SET [Location-Used]=True WHERE [Location-Key]="
&
Me.Box-Location

If the assumptions aren't true, maybe you can glean enough from this
example
to see what you need to change.

Carl Rapson

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's
text),
and Location-Used. In the code you gave me- Location-Used would go
where
[Available] is? The combo box control is Box-Location would this be the
Me.txtLocation in the code? And would Location-Number go where
[location]
is
in the code. Unforturnately, where I work I wear several hats and so I
haven't had to do anything in Access in about 9 months so, as the old
saying
goes, "if you don't use it, you lose it." I've lost it. Thanks again
for
your help, I really do appreciate it.
RandyM

:

In the AfterUpdate event of the control that contains the location,
update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use
your
own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and not
sure I
can explain my problem clearly enough, but here goes. I have a form
that
has
1 table (tbl-boxes) as its record source. It has two other tables
that
are
used for look-ups in combo boxes (tbl-department and tbl-location).
The
tbl-location has a field in it that shows availablity. Once a
location
is
used I need to mark this location as unavailable by changing the
value
from
yes to no. How do I change the value in the other table that is used
for
look-up purposes? Will I have to add it to the form or is there
another
way?
This location problem arose after the form had been completed and is
an
afterthought that I have to now allow for, c'est la vie. Thanks
for
any
help anyone can give.
RandyM

 

[WCDoan]

Thanks for the answer. I guess I'm a semi-newbie. Last year about this time I
had to build 2 databases for the small town that I work for and I had got to
where I understood a good bit, not as much as you and the others here who so
graciously help people like me, but enogh to do what I needed to do. However,
working for a small town you have to do a lot of other things, I'm the city's
grant writer, web-site content manager, access, powerpoint, word, etc...so I
guess I'm the jack-of-all-trades master of none. One other thing if you
don't mind, is there anyway to stop the question that is asked each time it
changes that field in the table. Each time I make a change it says something
to the effect that I'm about to change a row do I want to? I don't think the
people who are going to be using this are going to want to do that. Is there
a way to eliminate that question? And Carl, thanks again for your time,
patience and help.
RandyM

You're building the SQL string by concatenating the value from Box_Location
onto the rest of it. Any time you need to include the contents of a control
in a SQL string that's how you'll do it (in VBA code, anyway).

I'm glad it's working.

Carl Rapson

Carl,
That did it! Thanks so much. Somethign I don't understand is why you have
to have the quote and the ampersand before the Me.Box_Location. I thought
the
whole SQL statment had to be included in quotes, but this way it's just up
to
the equal sign. It's working like I want it too, but I was just curious as
to
why it's done the way it is. Anyhoo, thanks so much for your expert help.
RandyM

It depends on how you're setting the location in tbl-boxes. What are you
storing in tbl-boxes, Location-Key or Location-Number? What is in the
bound
column of your location combo box (Box-Location)?

Let's make some assumptions. You should be storing Location-Key in
tbl-boxes, so let's assume you are doing so. Furthermore, Location-Key
should also be the bound column on Box-Location, although you can show
only
Location-Number. If those assumptions are correct, then you would use:

UPDATE [tbl-location] SET [Location-Used]=True WHERE [Location-Key]="
&
Me.Box-Location

If the assumptions aren't true, maybe you can glean enough from this
example
to see what you need to change.

Carl Rapson

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's
text),
and Location-Used. In the code you gave me- Location-Used would go
where
[Available] is? The combo box control is Box-Location would this be the
Me.txtLocation in the code? And would Location-Number go where
[location]
is
in the code. Unforturnately, where I work I wear several hats and so I
haven't had to do anything in Access in about 9 months so, as the old
saying
goes, "if you don't use it, you lose it." I've lost it. Thanks again
for
your help, I really do appreciate it.
RandyM

:

In the AfterUpdate event of the control that contains the location,
update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use
your
own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and not
sure I
can explain my problem clearly enough, but here goes. I have a form
that
has
1 table (tbl-boxes) as its record source. It has two other tables
that
are
used for look-ups in combo boxes (tbl-department and tbl-location).
The
tbl-location has a field in it that shows availablity. Once a
location
is
used I need to mark this location as unavailable by changing the
value
from
yes to no. How do I change the value in the other table that is used
for
look-up purposes? Will I have to add it to the form or is there
another
way?
This location problem arose after the form had been completed and is
an
afterthought that I have to now allow for, c'est la vie. Thanks
for
any
help anyone can give.
RandyM

 

[Carl Rapson]

Yes, there is. Under Tools->Options, on the Edit/Find tab, there is a
Confirm section (at least, that's where it is for Access 2002/2003; I'm not
sure about Access 2007). Un-check any of the options you don't want to see
prompts for. This is a property of the Access environment itself, so it will
have to be done on each machine.

Congratulations on your progress. Be sure to pass the knowledge along to
others when you can.

Carl Rapson

Thanks for the answer. I guess I'm a semi-newbie. Last year about this
time I
had to build 2 databases for the small town that I work for and I had got
to
where I understood a good bit, not as much as you and the others here who
so
graciously help people like me, but enogh to do what I needed to do.
However,
working for a small town you have to do a lot of other things, I'm the
city's
grant writer, web-site content manager, access, powerpoint, word, etc...so
I
guess I'm the jack-of-all-trades master of none. One other thing if you
don't mind, is there anyway to stop the question that is asked each time
it
changes that field in the table. Each time I make a change it says
something
to the effect that I'm about to change a row do I want to? I don't think
the
people who are going to be using this are going to want to do that. Is
there
a way to eliminate that question? And Carl, thanks again for your time,
patience and help.
RandyM

You're building the SQL string by concatenating the value from
Box_Location
onto the rest of it. Any time you need to include the contents of a
control
in a SQL string that's how you'll do it (in VBA code, anyway).

I'm glad it's working.

Carl Rapson

Carl,
That did it! Thanks so much. Somethign I don't understand is why you
have
to have the quote and the ampersand before the Me.Box_Location. I
thought
the
whole SQL statment had to be included in quotes, but this way it's just
up
to
the equal sign. It's working like I want it too, but I was just curious
as
to
why it's done the way it is. Anyhoo, thanks so much for your expert
help.
RandyM

:

It depends on how you're setting the location in tbl-boxes. What are
you
storing in tbl-boxes, Location-Key or Location-Number? What is in the
bound
column of your location combo box (Box-Location)?

Let's make some assumptions. You should be storing Location-Key in
tbl-boxes, so let's assume you are doing so. Furthermore, Location-Key
should also be the bound column on Box-Location, although you can show
only
Location-Number. If those assumptions are correct, then you would use:

UPDATE [tbl-location] SET [Location-Used]=True WHERE
[Location-Key]="
&
Me.Box-Location

If the assumptions aren't true, maybe you can glean enough from this
example
to see what you need to change.

Carl Rapson

Thanks for replying Carl. I'm not exactly sure what goes where. The
tbl-location table has 3 fields: Location-Key, Location-Number(it's
text),
and Location-Used. In the code you gave me- Location-Used would go
where
[Available] is? The combo box control is Box-Location would this be
the
Me.txtLocation in the code? And would Location-Number go where
[location]
is
in the code. Unforturnately, where I work I wear several hats and so
I
haven't had to do anything in Access in about 9 months so, as the
old
saying
goes, "if you don't use it, you lose it." I've lost it. Thanks
again
for
your help, I really do appreciate it.
RandyM

:

In the AfterUpdate event of the control that contains the location,
update
tbl-location with something like:

DoCmd.RunSQL "UPDATE [tbl-location] SET [Available]=False WHERE
[location]='" & Me.txtLocation & "'"

This assumes location is a text value (not numeric). Be sure to use
your
own
field and control names.

Carl Rapson

I'm not sure if my subject description explains what I need and
not
sure I
can explain my problem clearly enough, but here goes. I have a
form
that
has
1 table (tbl-boxes) as its record source. It has two other tables
that
are
used for look-ups in combo boxes (tbl-department and
tbl-location).
The
tbl-location has a field in it that shows availablity. Once a
location
is
used I need to mark this location as unavailable by changing the
value
from
yes to no. How do I change the value in the other table that is
used
for
look-up purposes? Will I have to add it to the form or is there
another
way?
This location problem arose after the form had been completed and
is
an
afterthought that I have to now allow for, c'est la vie.
Thanks
for
any
help anyone can give.
RandyM