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

Discussion in 'Access Macros' started by krazy darcy, Oct 27, 2005.

  1. krazy darcy

    krazy darcy 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= [table]![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?
    krazy darcy, Oct 27, 2005
    1. Advertisements

  2. krazy darcy

    tina Guest

    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

    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.

    tina, Oct 27, 2005
    1. Advertisements

  3. 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, Oct 27, 2005
  4. krazy darcy

    krazy darcy Guest

    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.
    krazy darcy, Oct 28, 2005
  5. 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.
    Steve Schapel, Oct 28, 2005
  6. krazy darcy

    krazy darcy Guest

    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

    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 darcy, Oct 28, 2005
  7. 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:
    .... and the Props table something like this...

    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

    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, Oct 28, 2005
  8. krazy darcy

    krazy darcy Guest

    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.
    krazy darcy, Oct 29, 2005
  9. I am having a problem with a macro.--

    loveneetsingh, Jun 6, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.