How to refer to a specific field record in a subform?

Posted on 2004-11-15
Last Modified: 2008-02-01

I have inserted a continuous subform in a form.
The subform is a query and is used to view all records from the query and also enter information through certain fields in the query back down to the related table.

The problem is when I try to disable certain subform field records depending on corresponding subform field records, the whole field gets disabled.
In the following piece of code when I update a record in the 'Ignored' field and its answer is 'Yes', then I want to disable the corresponding record in the 'Reference' field.
The code I used behind the subform was

Private Sub .......
    If Ignored.Value = "Yes" Then
       Reference.Enabled = False
        Reference.Enabled = True
    End If
End Sub

This code disabled the whole 'Reference' field instead of just a specific record in the field.

I think the code I am trying to run would look something like the following;

Private Sub .......
    Dim RecNum As Integer
    RecNum = Me.CurrentRecord
    If Ignored.Value(RecNum) = "Yes" Then
       Reference.Enabled(RecNum) = False
        Reference.Enabled(RecNum) = True
    End If
End Sub

I would appreciate your help,
Question by:Alan OBrien
    LVL 17

    Accepted Solution

    THe control is enabled ( for all records) or disabled (for all records).

    You don't say where you have your existing code: you might need it to run in a couple of different cases:

    1 - when changing value of Ignored
    2 - when moving to a new record

    LVL 4

    Author Comment

    by:Alan OBrien
    I'm not sure if you fully understood what I meant by the above question.

    Basically I want to be able to refer to a specific field of a record  for example

    on a dataset subform i select(place the cursor in) column 3 row 4. i.e the 4th record of a field
    what is the correct syntax in VBA to say the value of the selected field
     for  example i thought it would be      


    but this does not compile.

    LVL 3

    Expert Comment

    On a continuous form, I don't know of any way to specify the row.   There is one 'active' row and when you refer to the fieldnames, you always are referring to the active row.  

    If you move your cursor to the 4th row, and any VBA Code runs, it will always run against the 4th row (until you move your cursor to another row).  By putting your cursor there, you've made that row the 'active' row.   In code on your subform, you can say me.fieldname.value.

    Hope that helps.
    LVL 3

    Expert Comment

    Sorry, I re-read my comment and this is unclear:
    "On a continuous form, I don't know of any way to specify the row."
    Should read:
    "On a continuous form, I don't know of any way to specify the row in VBA Code."

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now