Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Go to the blank record (Access 2000)

    How can i go to the next blank record in the table , such a blank record exists ?


    In my form Customers, i have a command button with the following command :
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "CompanyName"

    The above command is OK, but i want to amend it somehow . Do to some reasons the users click the button without entering a new customer,
    and in this case the table contains a customerid without a CompanyName. Therefore, my task is the following :

    If in the table customers there are records having blank CompanyName, then do not go to the next record, but go to the next blank record,
    without CompanyName, in ascending order I imagine my code to do the following :

    If Is Null the first field from the table with blank CompanyName Then
    go to this blank record ( the first in ascending order)
    Else
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "CompanyName"
    End If

    If i succedd, then we will have no blank CompanyNames in our database.

    Is it posible and can somebody help me ?

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

    Re: Go to the blank record (Access 2000)

    Why are you allowing them to save a record with a blank company name in the first place? Use the BeforeUpdate event of the form to check for a value in CompanyName. If there is no value, cancel the update, which will keep them in that record. Or you could use a msgbox to ask whether they want to discard the record. If they do then just use Me.Undo to clear it.
    Charlotte

  3. #3
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to the blank record (Access 2000)

    I have already put that code to exit the function .But thank you for the right suggestion. I do want however to make use of the existing blank fields
    and not just to delete them.I am afraid however i am asking too much from Access, and mabe it is not possible to go to a blank field when aksing for a new record

    regards

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

    Re: Go to the blank record (Access 2000)

    I'm sorry but I don't understand what you want to do that is causing you problems. You can certainly go to a particular field when you move to a record, regardless of whether it's a new record or not. However, if records were already created without a company name, how will the user determine what to enter there? As far as moving to that record, I suspect that whatever code you're using is the reason you're having problems. Why don't you post what isn't working? Remember that you can't test anything with "= Null", so if you're trying that, it will definitely not work.
    Charlotte

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

    Re: Go to the blank record (Access 2000)

    It would be far easier to delete the existing records with a blank Company Name. Since you already have code to prevent the users from leaving it blank in the future, your problem would be solved.

    To do so, open the table, click in a blank Company Name, then press the Filter by Selection button on the toolbar, or select Records | Filter | Filter by Selection. You should now see only records with a blank Company name. Select all records by pressing Ctrl+A, then delete them by pressing Delete.

    If you REALLY want to use the method you ask for, you could do something like this:

    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "CompanyName Is Null"
    If rst.NoMatch = True Then
    RunCommand acCmdRecordsGoToNew
    Else
    Me.Bookmark = rst.Bookmark
    End If
    Me.CompanyName.SetFocus
    Set rst = Nothing

  6. #6
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to the blank record (Access 2000)

    Dear Charlotte

    I appreciate very much your remarks because thanks to them i found out a major error in my codes. Thank you very much for
    your insight. I think it is proper to write this problem in a new thread and i will send it now.I think i have muddled my all codes.

    regards

  7. #7
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Go to the blank record (Access 2000)

    thank you very much indeed !

Posting Permissions

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