Re: Change field value in table not in form record source

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

"WCDoan" <WCDoan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9486D73D-F97E-4F7D-9F06-31A8A2FE8456@xxxxxxxxxxxxxxxx
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

"Carl Rapson" wrote:

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

"WCDoan" <WCDoan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B071EC88-8F4F-48BF-805A-8B72A77C17F1@xxxxxxxxxxxxxxxx
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

"Carl Rapson" wrote:

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" <WCDoan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CA838C91-7882-47BC-9229-77FAD16FE9FD@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Retrieving data from user input.
    ... I ran the query by itself to test ... "Carl Rapson" wrote: ... What does your SQL string look like in the Immediate window? ... I used my control name instead of the 'txtPro_ID' but still nothing ...
    (microsoft.public.access.formscoding)
  • Re: Retrieving data from user input.
    ... "Carl Rapson" wrote: ... SQL string in the Immediate window, then copy the SQL statement and paste it ... I used my control name instead of the 'txtPro_ID' but still nothing ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)
  • Re: Retrieving data from user input.
    ... "Carl Rapson" wrote: ... use the new control names: ... Did you try running the query ... What does your SQL string look like in the Immediate ...
    (microsoft.public.access.formscoding)
  • Re: Retrieving data from user input.
    ... What does your SQL string look like in the Immediate window? ... each control) and the field names? ... "Carl Rapson" wrote: ... into an empty Query window. ...
    (microsoft.public.access.formscoding)
  • Re: Change field value in table not in form record source
    ... "Carl Rapson" wrote: ... storing in tbl-boxes, Location-Key or Location-Number? ... The combo box control is Box-Location would this be the ... used for look-ups in combo boxes (tbl-department and tbl-location). ...
    (microsoft.public.access.forms)
Loading