get values from 2 forms and create new record in a third table

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

Guest

I am having a problem with a macro.

I have a database for my artworks. props were used in the creation of some
of these.
When I have the details of an artwork on screen (as a form), On that form I
click on a button to see all props from a specific props box. Each prop
listed has a add prop used button. This button launches a macro.

This macro uses the "set" action to take the value of the record number from
the artwork details form and insert in a props-used table. It then takes the
value of prop number from the prop record the button was pressed in and
inserts it in the props-used table.

But when I try it I get an "action failed" screen, and when I click "halt" I
get the following error:
"The object doesn't contain the Automation object 'tables' "

Heres the macro colde:

Action= setvalue
item= [Forms]![artworksdetails]![record number]
expression= [tables]![prop-use]![record number]

Action= setvalue
item= [Forms]![props-in-box-query]![prop-number]
expression=
![prop-use]![prop-number]

The table I am trying to create records in via this macro sequence just had
"record number" and "prop-number" fields (as per the ms access help) as a
artwork can use many props and a prop can be used in more than one artwork.

What am I doing wrong?
Does anyone know how to do what I am trying to do?
 
what you're actually trying to do is *append* a new record to the props-used
table. you can't do that directly with a set value action - you need to use
an Append query.

open a new query in design view, and close the Show Table dialog without
adding any tables or queries.
in the query design view, click Query | Append Query from the menu bar.
in the Append dialog box, choose the prop-use table from the droplist, and
click OK.
in the first column of the design view grid, click in the Append To line and
press F4 or click the down arrow to view the droplist. choose the [record
number] field. in the same column, in the Field line, type
[Forms]![artworksdetails]![record number]

in the next column, choose [prop-number] from the Append To line's droplist.
in the Field line, type
[Forms]![props-in-a-box-query]![prop-number]

to run the Append query in a macro, use the OpenQuery action. note that both
the [artworksdetails] and [props-in-a-box-query] forms must be open when the
query runs.

hth
 
Krazy,

[tables]![prop-use]![record number] is invalid syntax, and means nothing
to Access. That is what the error message is trying to tell you. There
is no such thing as [tables]! which is pretty reasonable when you think
about it. Even if it was possible to refer to a table in this way, it
still wouldn't work, as there is no way you could tell Access which
record in the table you want to refer to.

There is such a thing as an Append Query, which is specifically for the
purpose of adding records to a table. However, I don't think that is
applicable in this case. The easiest (and more standard) approach in
your example would be to make a form, in continuous view, based on the
[prop-use] table, and put it on the atrworksdetails form as a subform.
On this subform, you could have a combobox, bound to the [prop-number]
field, with its Row Source as a table that lists all the Props. If I
were you, I would get rid of the "props box" and the "add prop used"
button, and forget the idea of a macro... way too complicated.

I realise this will probably be a bit of a learning curve to get all
this lined up, but I assure you it will be worth the effort :-)
 
Thanks, I can stop driving my scalp through the brick wall now.

It now appears that I can't have a "look up" form listing the contents of a
specific box and selecting the prop used that way by clicking on an entry (in
the look-up form).
The reason why I was trying to have a "look-up" form is that I can't really
physically add/attach numbers to the various prop objects as I don't know
how/what angle they will be photographed in the future. Numbering baggies
containing each object (bit like in cop tv shows) won't work either
especially when you have a dozen out at once - the bags may get mixed up.

So being able to view the box contents in the look-up form is the easiest
way to see the prop-number. The prop details will also have thumbnail images
of the props so I can visually pick it out as well as through description etc.

It looks like the only way I can do it the way I want is if the lookup form
is seperate and used to manually look up the prop-number, read it off then
keying the number in by hand into the prop-used subform. While it would work,
it is an additional avenue for errors to creep into the system.
Steve Schapel said:
Krazy,

[tables]![prop-use]![record number] is invalid syntax, and means nothing
to Access. That is what the error message is trying to tell you. There
is no such thing as [tables]! which is pretty reasonable when you think
about it. Even if it was possible to refer to a table in this way, it
still wouldn't work, as there is no way you could tell Access which
record in the table you want to refer to.

There is such a thing as an Append Query, which is specifically for the
purpose of adding records to a table. However, I don't think that is
applicable in this case. The easiest (and more standard) approach in
your example would be to make a form, in continuous view, based on the
[prop-use] table, and put it on the atrworksdetails form as a subform.
On this subform, you could have a combobox, bound to the [prop-number]
field, with its Row Source as a table that lists all the Props. If I
were you, I would get rid of the "props box" and the "add prop used"
button, and forget the idea of a macro... way too complicated.

I realise this will probably be a bit of a learning curve to get all
this lined up, but I assure you it will be worth the effort :-)

--
Steve Schapel, Microsoft Access MVP


krazy said:
I am having a problem with a macro.

I have a database for my artworks. props were used in the creation of some
of these.
When I have the details of an artwork on screen (as a form), On that form I
click on a button to see all props from a specific props box. Each prop
listed has a add prop used button. This button launches a macro.

This macro uses the "set" action to take the value of the record number from
the artwork details form and insert in a props-used table. It then takes the
value of prop number from the prop record the button was pressed in and
inserts it in the props-used table.

But when I try it I get an "action failed" screen, and when I click "halt" I
get the following error:
"The object doesn't contain the Automation object 'tables' "

Heres the macro colde:

Action= setvalue
item= [Forms]![artworksdetails]![record number]
expression= [tables]![prop-use]![record number]

Action= setvalue
item= [Forms]![props-in-box-query]![prop-number]
expression=
![prop-use]![prop-number]

The table I am trying to create records in via this macro sequence just had
"record number" and "prop-number" fields (as per the ms access help) as a
artwork can use many props and a prop can be used in more than one artwork.

What am I doing wrong?
Does anyone know how to do what I am trying to do?
Click to expand...
Click to expand...
 
Krazy,

I have read through your message several times, went and had my
breakfast, and came back and read it again, and I'm afraid I just can't
grasp it. So before I try to comment on whether you should walk or swim
without knowing where you're going, maybe I can ask for some specific
examples. Can you describe a scenario of what you are trying to
achieve. Hint: start at the beginning - I don't know what a prop is,
apart from what makes an airplane fly. Thanks.
 
OK,
Just got up (it's 8.30am in New Zealand)

A prop in theatrical/graphics terms is an objcet used in a scene be it
photographic set or stage set such as a telephone sitting on a desk.

Most of my graphics projects to date (and drawings) have been catalouged in
a database). I am also cataloging all of the objects I call props - all sorts
from gas masks to fake cobewbs to a fan heater motor and more.... that I use
in my graphics.

For example image with "record number" 94 used a gasmask,gasmask hose,
intercom box, box brownie (camera), webbing belt, backdrop meshes.

Some of these objects are hard to find a discrete place to inscribe/write a
"prop number" on/in so they are recorded by what box they are in. Therefore
I need to call up a list of props for the specified box to obtain the
"prop-number" value for the specified object. This lookup form will have
thumbnail images of the props to help further distinguish between similar
objects.

With me so far?
My original plan would be to have a button on the lookup table for each prop
that when clicked would get the "prop-number" value for that prop AND the
"record number" for the currently displayed art work record in the details
form, and put those values into the prop used table.

The reason for having a prop used table is that Access's help says to have
such a table when you have a many-many relationship ie 1 artwork can use many
props while 1 prop can be used in many projects.

When I run a query ie keyword search on the artworks, I have a "on-click"
event attached to the "record number" field in the query results form (which
is a continuous form) which when clicked opend a details form for the artwork
related to the specified record number

It is looking like I will hve to manually refer to this lookup form and
manually key the "prop-number" in to a subform like you described yesterday.
 
Krazy,

Well, I'm in New Zealand too, but when your daughter's swimming lesson
starts at 8am, just getting up at 8:30 is a krazy thought. :-) I was
already thinking about your props by 6:30!

Thanks for the further explanation of your project. That helps a lot.

It sounds like you have got your table design pretty much sorted. I
assume your PropsUsed table will have fields something along these lines:
PropUseID
PropID
ProjectID
.... and the Props table something like this...
PropID
BoxNumber
PropName
Description

From the user interface point of view, I understand the challenge is to
browse the Boxes/Props to find the one you want, and then have that
inserted into the right place in the form. Right? Here's how I would
do it...

On the ArtworkDetails form (or whatever you call it), have a continuous
view subform. This subform is bound to the PropsUsed table. It will
have a combobox bound to the PropID field, and a textbox that shows the
related PropName and/or Description fields from the Props table (more
information about how to do that at
http://accesstips.datamanagementsolutions.biz/lookup.htm).

If you know the PropID you can enter it in directly. Or you can look it
up in the combobox drop-down list (if you like you could also filter the
combobox's list according to BoxNumber). Or... your "lookup form". Ok,
so you're entering Props used for a particular Project. Have a button
on the ArtworkDetails form. If you don't know the PropID or you can't
use the combobox on the subform, click the button and it opens the
PropsLookup form. Browse through this to find the Prop you are looking
for. When you have found it, go back to the ArtworkDetails form, and
you have another little command button on the PropsUsed subform, which
will insert the Prop as selected in the PropsLookup. There are several
approaches to making this happen, but if you want to use a SetValue
macro as originally suggested, your command button would need to be in
the Detail section of the subform, and the macro would be like this...
Action: SetValue
Item: [PropID]
Expression: [Forms]![PropLookup]![PropID]
(of course you will need to substitute your own actual
form/control/field names)
 
Thanks.
The reason for stating the time was I assumed you were overseas and we are
just getting up when Londoners are getting ready for their evening out or
dinner at home. Getting up 8-8.30 is a late morning for me - most days it's
6am for the first train to work.

Thanks for the help. I'll work on it during the week - re-arrainging some
shelving at the moment and have work tomorrow (split weekends)

Your instructions should get me where I want to be so to speak.
Again Thanks.
Steve Schapel said:
Krazy,

Well, I'm in New Zealand too, but when your daughter's swimming lesson
starts at 8am, just getting up at 8:30 is a krazy thought. :-) I was
already thinking about your props by 6:30!

Thanks for the further explanation of your project. That helps a lot.

It sounds like you have got your table design pretty much sorted. I
assume your PropsUsed table will have fields something along these lines:
PropUseID
PropID
ProjectID
.... and the Props table something like this...
PropID
BoxNumber
PropName
Description

From the user interface point of view, I understand the challenge is to
browse the Boxes/Props to find the one you want, and then have that
inserted into the right place in the form. Right? Here's how I would
do it...

On the ArtworkDetails form (or whatever you call it), have a continuous
view subform. This subform is bound to the PropsUsed table. It will
have a combobox bound to the PropID field, and a textbox that shows the
related PropName and/or Description fields from the Props table (more
information about how to do that at
http://accesstips.datamanagementsolutions.biz/lookup.htm).

If you know the PropID you can enter it in directly. Or you can look it
up in the combobox drop-down list (if you like you could also filter the
combobox's list according to BoxNumber). Or... your "lookup form". Ok,
so you're entering Props used for a particular Project. Have a button
on the ArtworkDetails form. If you don't know the PropID or you can't
use the combobox on the subform, click the button and it opens the
PropsLookup form. Browse through this to find the Prop you are looking
for. When you have found it, go back to the ArtworkDetails form, and
you have another little command button on the PropsUsed subform, which
will insert the Prop as selected in the PropsLookup. There are several
approaches to making this happen, but if you want to use a SetValue
macro as originally suggested, your command button would need to be in
the Detail section of the subform, and the macro would be like this...
Action: SetValue
Item: [PropID]
Expression: [Forms]![PropLookup]![PropID]
(of course you will need to substitute your own actual
form/control/field names)

--
Steve Schapel, Microsoft Access MVP


krazy said:
OK,
Just got up (it's 8.30am in New Zealand)

A prop in theatrical/graphics terms is an objcet used in a scene be it
photographic set or stage set such as a telephone sitting on a desk.

Most of my graphics projects to date (and drawings) have been catalouged in
a database). I am also cataloging all of the objects I call props - all sorts
from gas masks to fake cobewbs to a fan heater motor and more.... that I use
in my graphics.

For example image with "record number" 94 used a gasmask,gasmask hose,
intercom box, box brownie (camera), webbing belt, backdrop meshes.

Some of these objects are hard to find a discrete place to inscribe/write a
"prop number" on/in so they are recorded by what box they are in. Therefore
I need to call up a list of props for the specified box to obtain the
"prop-number" value for the specified object. This lookup form will have
thumbnail images of the props to help further distinguish between similar
objects.

With me so far?
My original plan would be to have a button on the lookup table for each prop
that when clicked would get the "prop-number" value for that prop AND the
"record number" for the currently displayed art work record in the details
form, and put those values into the prop used table.

The reason for having a prop used table is that Access's help says to have
such a table when you have a many-many relationship ie 1 artwork can use many
props while 1 prop can be used in many projects.

When I run a query ie keyword search on the artworks, I have a "on-click"
event attached to the "record number" field in the query results form (which
is a continuous form) which when clicked opend a details form for the artwork
related to the specified record number

It is looking like I will hve to manually refer to this lookup form and
manually key the "prop-number" in to a subform like you described yesterday.
Click to expand...
Click to expand...
 
I am having a problem with a macro.--
http://google-clone.tk


Steve Schapel said:
Krazy,

[tables]![prop-use]![record number] is invalid syntax, and means nothing
to Access. That is what the error message is trying to tell you. There
is no such thing as [tables]! which is pretty reasonable when you think
about it. Even if it was possible to refer to a table in this way, it
still wouldn't work, as there is no way you could tell Access which
record in the table you want to refer to.

There is such a thing as an Append Query, which is specifically for the
purpose of adding records to a table. However, I don't think that is
applicable in this case. The easiest (and more standard) approach in
your example would be to make a form, in continuous view, based on the
[prop-use] table, and put it on the atrworksdetails form as a subform.
On this subform, you could have a combobox, bound to the [prop-number]
field, with its Row Source as a table that lists all the Props. If I
were you, I would get rid of the "props box" and the "add prop used"
button, and forget the idea of a macro... way too complicated.

I realise this will probably be a bit of a learning curve to get all
this lined up, but I assure you it will be worth the effort :-)

--
Steve Schapel, Microsoft Access MVP


krazy said:
I am having a problem with a macro.

I have a database for my artworks. props were used in the creation of some
of these.
When I have the details of an artwork on screen (as a form), On that form I
click on a button to see all props from a specific props box. Each prop
listed has a add prop used button. This button launches a macro.

This macro uses the "set" action to take the value of the record number from
the artwork details form and insert in a props-used table. It then takes the
value of prop number from the prop record the button was pressed in and
inserts it in the props-used table.

But when I try it I get an "action failed" screen, and when I click "halt" I
get the following error:
"The object doesn't contain the Automation object 'tables' "

Heres the macro colde:

Action= setvalue
item= [Forms]![artworksdetails]![record number]
expression= [tables]![prop-use]![record number]

Action= setvalue
item= [Forms]![props-in-box-query]![prop-number]
expression=
![prop-use]![prop-number]

The table I am trying to create records in via this macro sequence just had
"record number" and "prop-number" fields (as per the ms access help) as a
artwork can use many props and a prop can be used in more than one artwork.

What am I doing wrong?
Does anyone know how to do what I am trying to do?
Click to expand...
Click to expand...
 
Back
Top