Results 1 to 6 of 6
  1. #1
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65

    Selecting a specific cell in a table

    Q: how to select a specific cell in order to enter data directly using vba.
    Warning - this is likely to offend the purists!!
    The following code sits behind a button on a form which itself calls a particular report. This button allows me to inspect and/or edit the raw data in the table, whilst allowing me to see the data for the surrounding records.
    Code:
    Private Sub OpenItemsButton_Click()
    ' opens DataItems table for general edit
        strTblName = "DataItems"
        idFilter = Me.ItemSelector  'ItemSelector is a combo on the controlling form
        
        DoCmd.OpenTable strTblName, acViewNormal, acEdit
        DoCmd.FindRecord (idFilter)
        On Error GoTo WayOut
        DoCmd.GoToRecord , , acNext, 10
        DoCmd.GoToRecord , , acPrevious, 10
    WayOut:
    In effect, this opens the table in a normal view and then moves the cursor so that the required record is visible in the context of its neighbours (ie, +/- 10 adjacent records)
    Fine so far!


    The main purpose for this devious practice is to add a date into the sixth column of the record, so the basic question is - can I add some code to move the cursor to the sixth column without having to do it manually?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You dont select cells like in Excel.
    just filter ALL the data. (not go to some records) This way ALL records are the correct ones.

    Code:
    If IsNull(cboBox) Then
      Me.FilterOn = False
    Else
      Me.Filter = "[panel]='" & cboBox & "'"
      Me.FilterOn = True
    End If
    or you can EDIT 1 record by opening a form with that record only. you use the keys....
    docmd.openform "myForm",,,"[id]=" & txtID

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would not ever open a table, but it sounds like this is for your own use (I hope).

    Try adding this line:
    Code:
    <snip>
        DoCmd.GoToRecord , , acNext, 10
        DoCmd.GoToRecord , , acPrevious, 10
        DoCmd.GoToControl ("Name_Of_Field")
    <snip>
    Change "Name_Of_Field" to the name of the field you want to select.

  4. #4
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Hi Guys, thanks for your prompt response and, yes, this is strictly for my own use as administrator.
    I agree wholeheartedly that this is not good practice and that it should not be copied without a good reason. Before retiring, I managed databases used to analyse traffic behaviour in London, using procedures that were very much subject to public scrutiny. This present application is very different, with data from several, largely incompatible, sources. Most of it is in paper format and I have a front end with proper forms and reports. However, one colleague (who doesn't speak English) uses Word and Excel solely as a way of preparing pretty printouts and she will often change or re-use cross references without any regard for data integrity. Since those references correspond to my foreign keys I have to go through a process of checking "what has she changed this time?" before integrating the data into my standard procedures. To do this I need to be able to compare any suspect record with similar records that are already established - hence the need to be able to view the raw data directly.
    Repeat - a proper database with reliable inputs should not require this type of procedure!

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad you have it sorted out....

    Good luck with your project........sounds like it could be frustrating at times.

  6. #6
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Yes, there are times when analysing 40,000 traffic contraventions per month seems to be easy in comparison.

    BTW, Discovery TV in Romania is running a series about the Alaskan Railroad - some impressive scenery you have there!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-12-2017, 09:10 AM
  2. Replies: 4
    Last Post: 09-19-2016, 05:02 PM
  3. Replies: 1
    Last Post: 11-20-2014, 08:34 AM
  4. selecting specific records from table into sub form
    By Terry Sumpter in forum Forms
    Replies: 3
    Last Post: 12-19-2013, 06:37 PM
  5. error while selecting last cell from access vba
    By allenjasonbrown@gmail.com in forum Access
    Replies: 1
    Last Post: 06-30-2011, 04:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums