Go to the blank record (Access 2000)

Topic

New Reply

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 ?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

thank you very much indeed !

Reply | Quote

thank you very much indeed !

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote