Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sendobject (access97)

    firstly thank you to all the people who contribute to the lounge. I've learnt to do some really fancy things from you. However I think I've missed a basic step along the way in that I can't work out how to reference a value in a table or form using code.

    I want to be able to get a value from an excel table (which has only one record)
    and insert it into an e-mail
    So far I have
    DoCmd.SendObject acSendNoObject, , acFormatTXT, "TheBoss@mywork.com",,,,"the workorder is "& ?????
    The linked table is called tblOutput
    and the value is in the field called Workorder

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: sendobject (access97)

    It is easier to get it from a form than from a table, so have a form open with the field on it.
    Then you refer to the field in code as:
    forms!myform!workorder (where myform is the name of the form)
    So the line of code becomes
    DoCmd.SendObject acSendNoObject, , acFormatTXT, "TheBoss@mywork.com",,,,"the workorder is "& forms!myform!workorder
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sendobject (access97)

    If the value isn't in a form, you'll need to use a function to lookup the value and assign it to a variable, just so you can check to be sure you have a value before you send the email. IN that case, it would probably be simplest to use a DLookup function, which would look something like this:

    Dim varWorkorder As Variant

    varWorkorder = DLookup("Workorder","tblOutput")
    If not isnull(varWorkorder) then
    DoCmd.SendObject acSendNoObject, , acFormatTXT, "TheBoss@mywork.com",,,,"the workorder is "& varWorkorder
    End If

    Normally, you would use a third argument with DLookup to be sure you got a record that matched specific criteria, but in this case if there is only one record in the table, you don't need criteria.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sendobject (access97)

    Thank you John

    I made a form that has all the details on it
    DoCmd.OpenForm "frmFabNewForm"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, "TheBoss@mywork.com",,,,"the workorder is "& forms!frmFabNewForm!workorder
    DoCmd.Close acForm, "frmFabNewForm"

    and it works great!
    Now I can apply what you've taught me to a lot of other things I was stuck with.

    thank you very much

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sendobject (access97)

    Thank you Charlotte

    I've never seen Dlookup before. What a great tool.

    I got both methods to work very quickly and very easily.
    very impressed

    thank you

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sendobject (access97)

    I have been using these two methods (DLOOKUP and getting the information from a form) but I am having a problem with them

    The DLOOKUP and the form sometimes stop referencing the current record from the excel table (which always only has one record) and return values from the previous record that I was working on. If I close the Database and open it again it will return the correct value.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sendobject (access97)

    I don't understand. If the Excel table has only one record, how can there *be* a previous record you were working on?? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sendobject (access97)

    baffling for you too?
    oh no <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I have a macro that fills the excel file with information about a garment style from the database.
    I use the excel file to calculate things and one of the worksheets in the file is a table with one record for that style which has all the information I need to use
    I take the information from the single excel record and do four things with it
    1. I print a report based on a query from a number of tables including the excel table (with the single record) This always works
    2. I run an append query taking information only from the excel table and adding it to a table in the database
    3. I open a form which has a sketch on it, the style number of the sketch is taken from the excel table using DLOOKUP
    4. I create an email using John's form method
    (The last three things don't always work. )
    Then I clear the excel file using a macro that deletes the information of the style I last worked on and then start again from the top with the next style.


    It seams that there is a memory of what was in the excel table even after the table has been changed. The memory is of the last successfully worked with record, once there is a problem it will keep trying to use that same old record even if I change the excel table many times. The excel file has not been saved with that old record.

    Closing the database seams to work most of the time in getting rid of this false memory.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sendobject (access97)

    You have a link to the excel worksheet, right? I'm not clear on why you're using an Excel worksheet in the first place to create reports from Access, especially if you're populating the Excel worksheet from code specifically so you can use it with Access. Why are you using Excel like this anyhow? You are putting a value into the Excel sheet and then appending it from there into an Access table for reasons you haven't explained. It seems like a totally unnecessary step.

    Saying that something doesn't always work isn't specific enough for anyone to be able to identify the problem. For instance, is the macro you're running to populate the Excel "table". something that runs in Excel or in Access? Specifically, *how* are you updating the Excel table? If you look at the contents of the Excel table from Access, which record do you see in it before you run the macro? After you run the macro?

    If you can print a report based on the table, are you saying that the data in the report is not the same as the data in the table?
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sendobject (access97)

    Problem finally solved

    The problem I was having resulted from a formula in the spreadsheet which should return a numeric value. The formula contained an if statement similar to =if(A1>255,"",A1).
    So when the value of "" was returned it wasn't a numeric value. Changing the formula to =if(A1>255,0,A1) fixed the problem.

    Once there had been one record with this problem in it would start returning values (in DLOOKUP and on the form from the one record excel table) from a previous record - the last one that did not have the problem

    As to why I use still use excel?
    The spreadsheet was developed first, I started it a few years ago and then realised that excel had limitations that would be solved by using it in conjunction with access, so I started learning Access. The spreadsheet is big and does a lot of the calculations I need. They probably could all be done in access but I'm not sure if they can and it would be a big job to change it over. So I populated the spreadsheet from an access query, working everything out in excel and sending it back to access to make a report and add the results to a table.

    Thank you for your help <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •