Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referring to a field on a subform (A2k-SR1)

    Hi,

    I have inserted the following code into the "Got Focus" event procedure for a combobox called <font color=red>Info1</font color=red> to make it dynamically change the items in the drop-down list depending on a few factors.

    Private Sub Info1_GotFocus()

    Dim strSQL As String

    With Me!Info1

    If Me![Name] = 2 Then ' NameID = 2 implies "Dump Slag"
    strSQL = "SELECT DISTINCTROW [Dump Slag Receipts].[SampleID] FROM [Dump Slag Receipts]"
    strSQL = strSQL & " WHERE [Dump Slag Receipts].ReceiptDate=Forms![sfrmSamples]![Sample Date]"
    strSQL = strSQL & " AND [Dump Slag Receipts].HasAssay = False;"
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .LimitToList = True

    Else
    strSQL = ""
    .RowSourceType = "Table/Query"
    .RowSource = strSQL
    .LimitToList = False
    End If

    End With

    End Sub

    This code works perfectly when I load the sub-form <font color=red>sfrmSamples</font color=red> on it's own in datasheet view and click on the <font color=red>Info1</font color=red> combobox. I actually want to use this form as a sub-form on a main form that has a tab control with two pages. When I load the main form and click on the <font color=red>Info1</font color=red> combobox within this subform, I get a dialog box entitled "Enter parameter value" and am prompted with <font color=red>Forms!sfrmSamples!Sample Date</font color=red>.

    I have read several old posts about how to refer to controls on tab controls, and it seems one doesn't need to refer to the name of the tab control at all, only the sub-form itself. I have tried <font color=red>Me![sfrmSamples]![Sample Date]</font color=red> and a few other means of referencing the subform, but no luck.

    Any ideas as to what I am doing wrong ?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Hello Adrian,

    This doesn't have anything to do with Tab controls. They are just a handy way to group and display controls, but control names still have to be unique on a form.

    The reason you have problems with Forms!sfrmSamples is that the Forms collection contains only the forms that have been opened - that means opened independently. A subform on an open form is not considered to be "open" itself.

    You could use

    ...
    strSQL = strSQL & " WHERE [Dump Slag Receipts].ReceiptDate=Forms![frmMain]![sfrmSamples]![Sample Date]"
    ...

    where frmMain is the name of the main form. You won't be able to use sfrmSamples as a standalone form any more.

    Or you can put the reference to the Sample Date outside the quotes, so that it it passed as a literal date. In that case, there is no need to use Forms!...

    ...
    strSQL = strSQL & " WHERE [Dump Slag Receipts].ReceiptDate=#" & Format([Sample Date], "m/d/yyyy") & "#"
    ...

    The Format(..., "m/d/yyyy") is to make absolutely sure that the date is in US format - SQL can't handle international date formats. Now you should be able to open sfrmSamples as a standalone form and as subform.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Hans is right in the last part of his post - you need to refer to the control on the subform outside of the quotes. Though it's not perfectly clear it seems the combobox is on the main form and you are trying to refer to a date that is in a control on the subform. If this is the case you need to use the name of the subform control on the main form, not the name of the subform.

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Hans, baie dankie !

    Both methods worked perfectly. The first method now makes sense. Because the <font color=red>sfrmSamples</font color=red> was not part of the open forms collection, one has to refer to it as a subform on the main form which IS open.

    I am confused as to why the second method works. Is it because the <font color=red>Sample Date</font color=red> control is referenced in the code related to the subform in question, so one doesn't have to refer to any forms in the control reference to make it find the right control ? Sort of like an implied "Me" reference to the current form in front of the <font color=red>Sample Date</font color=red> reference in the code ?

    Am I correct in my interpretation of the two methods ?

    As a matter of interest, I am using some techniques gleaned from "Roger's Access Library" and Helen Feddema's "Access Archon" archives here and am amazed what can be done with minimal code. It is a very powerful feature that allows the combobox to change it's row source based on other fields in the same record.

    One further question relating to the code that I originally posted . . . I have repeated the relevant portion below :

    If Me![Name] = 2 Then ' NameID = 2 implies "Dump Slag"
    strSQL = "SELECT DISTINCTROW [Dump Slag Receipts].[SampleID] FROM [Dump Slag Receipts]"
    . . . . .
    Else
    strSQL = ""
    . . . .
    End If

    In the first line of code above, <font color=red>Me![Name]</font color=red> refers to the textbox control called <font color=red>Name</font color=red> which is bound to the <font color=red>NameID</font color=red> field in the <font color=red>tblSamples</font color=red> table.

    The <font color=red>tblSamples</font color=red> table and the <font color=red>tblSampleNames</font color=red> table are related through the <font color=red>NameID</font color=red> field which is the pk in the <font color=red>tblSampleNames</font color=red> table (autonumber type) and the fk in the <font color=red>tblSamples</font color=red> table.

    I want to replace the <font color=red>If</font color=red> with a <font color=red>Case</font color=red> statement that will cater for maybe up to five different sample names, but instead of testing for the meaningless autonumber <font color=red>NameID</font color=red>, I want to test for the cases of the more descriptive <font color=red>Name</font color=red> fields from the <font color=red>tblSampleNames</font color=red> table. Is there an easy way to replace the integer <font color=red>NameID</font color=red> (autonumber) referred to in the criteria with the text <font color=red>Name</font color=red> criteria ?

    TIA again,

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Hello Paul,

    As you will see from my reply to Hans, both methods he suggested worked perfectly.

    The combobox and the date field are both on the subform, so in this case I don't need to reference the subform control itself.

    I will bear your tip in mind when I am dealing with a control on the main form that needs to "get info" from a control on a subform.

    Thanks,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Hello Adrian,

    In the second method, there is indeed an implicit reference to Me. If you refer to a control without specifying its location, Access assumes it is on the instance of the form that is executing the code. Instead of

    Format([Sample Date], "m/d/yyyy")

    you could also use

    Format(Me![Sample Date], "m/d/yyyy")
    or
    Format(Me.[Sample Date], "m/d/yyyy")

    <img src=/w3timages/blueline.gif width=33% height=2>

    It is possible to use descriptions instead of ID's in a Case statement, but I wouldn't do that myself - a matter of personal preference. I usually add comments to the cases, just as you do now:

    Select Case [Name]
    Case 1 ' something
    ' Code goes here
    Case 2 ' Dump Slag
    ' Code goes here
    ...
    End Select

    If you *do* want to use the descriptions, there are several possibilities:

    Add tblSampleNames to the record source of the form and add the description field.

    Or put a combo box cboSampleName on the form bound to NameID; set row source to tblSampleNames, column count to 2 and column widths to 0;1. You can then refer to the description as cboSampleName.Column(1).

    Or even use a DLookup in the Select Case. Probably inefficient.

    Regards,
    Hans

    (P.S. I can't answer in Afrikaans - I can understand and read it more or less, but not speak or write it)

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    OK I will stick to the integer criteria and use comments - much simpler I agree.

    Where you suggested that one could add <font color=red>tblSampleNames</font color=red> to the record source of the form, I presume that would mean either making and saving a query which one would then refer to in the record source property of the form, or (equivalently) inserting SQL code like :

    SELECT tblSamples.*, tblSampleNames.Name
    FROM tblSampleNames INNER JOIN tblSamples ON tblSampleNames.NameID = tblSamples.NameID;

    into the forms' record source property ?

    In that case, would the saved query option be a more efficient method ?

    Thanks for your help,

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to a field on a subform (A2k-SR1)

    Adrian,

    In general, a saved query is better. Access can optimize the execution of saved queries to a large extent.
    If you put a SQL statement into the Record Source property of a form or report, it is just a string, and Access must evaluate that string each time the form (or report) is opened.

    Regards,
    Hans

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

    Re: Referring to a field on a subform (A2k-SR1)

    Actually, the way I do it is to declare constants in the routine and use those constants in the case statement. That makes it self documenting and easier to understand that the integers, with or without comments. So I might do this:

    Const DUMP_SLAG = 2

    Then in the case statement, I would do this:

    Select Case [Name]

    Case DUMP_SLAG

    etc....
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks - nice idea

    Thanks - nice idea

Posting Permissions

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