Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access a control's data on a tabular form (Access 2002/SP2)

    In VBA for Access 2002, I need to access the data in text boxes on a form which is in tabular view. In single form view where only one row appears at a time, I simply reference the control by its name. On a form in continuous view, however, where several rows are displayed, any reference to a control by name returns a null error message. Please tell me how to access the data in a continuous (tabular or datasheet) view form

  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: Access a control's data on a tabular form (Access 2002/SP2)

    In the continuous form , one of the records will be the current one.
    I think you can still just refer to the control by its name, but what will be returned will the value from the current record.
    In my experiment, I only get a null error if the current record is the new record at the end of the form.
    One way to get access to the contents of the box in which the user has clicked the mouse would be to use an "on got focus" or an "on click" event to set the value of a variable. The "on got focus" event would be preferable as it allows for both keyboard and mouse use.
    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: Access a control's data on a tabular form (Access 2002/SP2)

    In a continuous form, there is really only one set of controls, even though it doesn't look that way. So when you refer to a control (Me!ControlName, etc.), you are always referring to the value of that control for the current record. If you get a null value, it's because there is no value in the control or because Me.NewRecord is true.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Thank you for your reply. At the prospect of trying your patience, let me expand on the second question (the last sentence of my original). A tabular form is displaying several rows. For VBA programming reasons that don

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Something is amiss here - either we don't understand what is going on, or there is some other external factor causing this behavior. We use this kind of form design on a regular basis, and as long as the form has focus, it should not behave the way you describe. So some questions:
    <UL><LI>Is your continuous form on a main form, or is it contained on a sub-form?
    <LI>Is the record selector visible in your continuous form, and if so does it still show the first record selected when you click on another record?
    <LI>Is there code on any other events of the continuous form that could be driving the focus to the first record or some other control?[/list]Hopefully this will trigger some ideas so we can assist you in solving the problem.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Thank you again for your attention.

    To questions one and three: There are two tables in a one-to-many relationship; the one

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

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    If the combobox is on the main form, it is finding matching records in the recordset of the main form. That has nothing to do with the focus in the subform. The current record in the main form is the one you selected using the combobox. Both the form and the subform can have record selectors and navigation buttons. It sounds like you are getting confused over which set of records you're trying to reference. How you do it depends on where you do it from. If you are running code behind the subform, then you can refer directly to the controls and you will reference the current record, which will normally be the first record unless you have clicked on another. If you are trying to refer to it from the parent form, then you need to reference the subform as well, since the controls are not on the main form but on the subform.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    That was quick. I agree with you. The combobox, the one-to-many table and the main form have nothing whatsoever to do with this problem. I included them in the description so that you could see how Access uses the Link Child Fields and Link Master Fields to fill the sub-form. There is no code in either form concerned with filling the subform. And the Record Selector, all positioning and all controls mentioned my numbered steps occur in the sub-form. The only code in the entire sub-form is that MsgBox shown.

    Thanks, John

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Hi John,

    I think your issue is with this code:
    <<
    Me.RecordsetClone.FindFirst "txtCompCd = '" & Me![cboNames] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    >>

    In my experience, RecordsetClone operations don't work the way you expect them to in 2000/2002, and definitely not the same as they did with Access97. The principal culprit seems to be the default ADO data access method. I know the combo-box wizard builds code this way, but I have had problems with it in the past when a subform was involved. Somewhere in the distant path Charlotte and I had an exchange on this issue, but I haven't been able to quickly find it. In any event, we nearly always apply a filter to the main form when the combo box changes (and when the form first opens), which makes this work very nicely, and is equal to or better than the RecordsetClone approach from a performance perspective.
    Wendell

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

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Wendell's remark about recordsetclone if 2000/2002 is to the point. The recordsetclone object belongs to the DAO object model. In ADO, you use the Clone method to create a clone of the recordset, but it isn't the same as the DAO Recordsetclone. If your project is using DAO rather than ADO, then using Recordsetclone navigation should work the way it did in earlier versions. If not, or if you're trying to mix the two models, then I'm not surpirsed you're having problems. Actually, there is no eartly reason I can see to reference the recordsetclone at all. If you want to refer to the field in the current record of the subform, you would simply refer to is as Me!txtWorkCode.

    I'm still confused by the interaction between the main form and subform. If your main form combobox is used to select a main form record, then the subform should display only the related records for that parent record. Why are you doing a find at all in the subform? There must be something more to the combobox on the main form than you have explained. Is it, by any chance, listing *subform* items rather than main form items?
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Thank you again for your continuing attention.

    I took your advice and threw out the two bookmark lines of code and replaced them with:

    Me.Filter = "txtCompCd = '" & Me![cboCreaterNames].Value & "'"
    Me.FilterOn = True

    Remember, these two lines (in cbo_AfterUpdate() in the MAIN form to set the current record in the One table) plus the single MsgBox line (in txtWorkCode_GotFocus() in the SUB-form to display the code that is clicked) are the only lines of code left in the entire project. I

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access a control's data on a tabular form (Access 2002/SP2)

    Problem is solved. By substituting the Boodmark statements (with their reference to RecordsetClone) with the Filter statements, I thought I had thrown out all ADO/DAO stuff. I hadn't. I invertently left the same reference in the MsgBox statement. When I removed the reference and just used "MsgBox Me.txtWorkName", everything worked right.

    Thank you both very much for your time and effort. I've learned a lot of what not to do.

    John Littell (Florence, Oregon)

Posting Permissions

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