Results 1 to 13 of 13

Thread: VB ?

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB ?

    Hello,

    OK...I will try this question....

    I know that obj.name is the NAME of the object, such as the report, table, or query name, but what is the code I need to refer to if I want to reference a field in the table?

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Are you trying to get data from the table?

    If so, you need to create a record set and then interrogate the field from the recordset.

    In code use the OpenRecordset method to open the recordset and then you can reference the field using any of the following.

    rst(0)
    rst("name")
    rst![name]

    where rst is the recordset and name is the name of the field. 0 is the index of the filed in the recordset.


    I hope this helps

    Richard Aheron
    raheron@hotmail.com
    Richard

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Thanks for the response.

    I guess I need to provide more information.

    Please see below:

    Public Function Open_Log(obj As Object)
    Dim db As Database
    Dim rsLog As Recordset

    Set db = CurrentDb()
    Set rsLog = db.OpenRecordset("tReportLog")
    With rsLog
    .AddNew
    .Fields("WhatReport") = obj.Name 'or whatever your field name is
    '.Fields("PM") = obj.Name
    .Update
    .Close
    End With
    Set rsLog = Nothing
    Set db = Nothing
    End Function

    As you can tell the code here will update tReport Log with specific information.

    The field "What Report" in tReportLog is updated with the name of the Report that printed.

    I am trying to find the correct code to update PM in tReportLog with the PM information that is in the report that printed.

    I know that obj.name will return the name of the report, but what do I need to put in the line of code to return the information in PM?

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    If this code is in the report you can get the data from the report field by using

    me.PM.

    If the code lives outside the report you can use

    reports!ReportName.PM

    as long as the report is open at the time.


    Good luck

    Richard Aheron
    raheron@hotmail.com
    Richard

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Hi Richard,

    OK, I tried putting .fields ("PM")=reports!rProjectInfo!PM right after the line .fields ("WhatReport")=obj.name and it failed....do you have any other suggestions.

    OH, and yes the code lives outside the report. I have Open_Log Me in the onOpen event of the report. Everything works (or it did until I added the other line of code).

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    since the report specified is held in a variable, try something like this:

    .fields("PM")=reports(obj).PM

    (although this will only work if every report passed has the same object (i.e. "PM") on it)

    a better alternative would be to pass two parameters to the function. The report name and the object name

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

    Re: VB ?

    Roberta,

    I have a question for you. Why are you setting two different fields in your code to the same value, i.e., obj.name? You say you want to update a field with the PM value from the report. What is PM, a field in the recordset, a control on the report or what? Unlike forms, reports don't have a current record, so you can't readily read information from them the way you can from a form.

    Syntax like ".fields("PM")=reports(obj).PM " will only work if you're referring to a property of the report object. You can create a custom property for the report that contains whatever PM is supposed to be, but it isn't at all clear what that is.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Hello Everyone,

    Thanks so much for the help.

    I guess I need to be more clear--

    tProjectInfo=table
    tProjectInfo has 3 fields 1)ProjNum, 2)OpenDate and 3)ProjDescr

    tReportLog=table
    tReportLog has 3 fields 1)What Report, 2)SentDate and 3)PM

    fProjectInfo=Form
    fProjectInfo has a several fields one of which is an unbound ComboBox(cboPM) for PM.

    rProjectInfo=Report
    rProjectInfo has several predesigned fields and "PM"

    In the OnClick of a button within fProjectInfo is the code listed in my first post. Once clicked the code runs, it looks at the initials in cboPM determines the email address and then sends rProjectInfo to the email address.

    What I am attempting to do now is log every email that is sent (tReportLog). One piece of information I am interested in is "who" (the PM) was the email sent to. This information is in cboPM which triggers the email. I would like cboPM selection to also be stored in the report log. (please see code for updating tReportLog)

    Public Function Open_Log(obj As Object)
    Dim db As Database
    Dim rsLog As Recordset

    Set db = CurrentDb()
    Set rsLog = db.OpenRecordset("tReortLog")
    With rsLog
    .AddNew
    .Fields("WhatReport") = obj.Name 'or whatever your field name is
    '.Fields("PM") = reports!ReportName.PM
    .Update
    .Close
    End With
    Set rsLog = Nothing
    Set db = Nothing
    End Function

    In the OnClick event of a button in fProjectInfo, the above code runs. Everything works fine except I don't know how to capture the selection from cboPM at the time the report runs(see code for sending email).

    Private Sub Command17_Click()
    strEmail = Me.cboPM.Column(2)
    DoCmd.SendObject acSendReport, "rProjectInfo", asFormatRTF, strEmail, , , "Lead Information Request", , 0

    End Sub

    I think I need to add a piece of code like the following:

    Set db = CurrentDb()
    Set rsLog = db.OpenRecordset("tReortLog")
    Set strEmail=Me.cboPM.Column(2)
    With rsLog
    .AddNew
    .Fields("WhatReport") = obj.Name 'or whatever your field name is
    .Fields("PM") = strEmail
    .Update
    .Close
    End With
    Set rsLog = Nothing
    Set db = Nothing
    End Function

    Hope this is more clear and I don't run everyone off.

    Thanks again for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    With one exception, the code you supplieds under "I think I need to add a piece of code like the following" looks like exactly what you need to add. The one exception is on line 3 of your code. You only need Set with object variables. Since strEmail is a string variable, not an object variable, remove the Set.

    Does this code not work? If not, what does it do or not do?
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Hi Legare,

    I did exactly as you suggested, but when I made the change, it dies at the following line of code:
    strEmail = Me.cboPM.Column(2)
    it says "invalid use of me"
    Any suggestions?

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    Hi Again Legare,

    Thanks for all of your help as well as everyone else. I finally resolved the problem and everyone runs perfectly...did several tests and all were great.

    I changed the following line(3rd) of code:

    strEmail = Forms!fProjectInfo!cboPM.Column(0)

    and

    .Fields("PM") = strEmail (on the 10th line)
    and

    All is well ..... Thanks everyone
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    This may work well as you say, but this code realy belongs behind the form and not in a separate module. The reasoning behind my statement is that you are explicitly saying that only the form "fProjectInfo" can ever be used. If you pass the VALUE of Forms!fProjectInfo!cboPM.Column(0) in your function call, then it would be useful for more than just one form.

    Public Function Open_Log(obj As Object, strEmail as string)
    Dim db As Database
    Dim rsLog As Recordset

    Set db = CurrentDb()
    Set rsLog = db.OpenRecordset("tReortLog")
    With rsLog
    .AddNew
    .Fields("WhatReport") = obj.Name 'or whatever your field name is
    .Fields("PM") = strEmail
    .Update
    .Close
    End With
    Set rsLog = Nothing
    Set db = Nothing
    End Function

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ?

    You have probably realised by now, but repetition is part of learning.
    The '.' is used to reference properties of objects such as 'me.visible'. The '!' is used to reference the controls that you have put onto a form (or report ...) such as 'me!MyControl.visible'.

Posting Permissions

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