GoTo Next Blank Record 1

[infinitx]

Hi,

I have a table that has a pre-entered autonumber:

Item ID ICL 1
1
2
3
4
5
6
7
8
9
10

I have a form where you add the ICL 1 to the table under an Item ID of 1. When I close the form and I open it again, the record that I have just entered is still visible.

Is there any way to go to the next blank record which in this case would contain an Item ID of 2?

 

[jbodford]

Hi,
I think that your question is much the same as a new thread that I just started. Perhaps we will both benefit from a response to either of our threads.
Good Luck,
jbodford

 

[jbodford]

Dear infinitx,

Did you see the responses to my thread question? If your question is what I thought it was, all you do is set the DataEntry property on your form to "Yes". I just tried it and it worked just fine.

jbodford

 

[PHV]

You may consider to apply a Filter

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[infinitx]

hmmmm....

For some reason when I set the DataEntry to "Yes" and try to add a record, instead of adding it under Item ID of 1, it adds it under Item ID of 25. This is probably due to the fact that I pre-entered the Item ID's.

Is there any way to fix this?

PHV,

Could you elaborate on your idea?

Thanks!

Also, thanks jbodford for keeping me informed!

 

[PHV]

In the Data Tab of your form's property window, press the F1 key in the Filter field

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[infinitx]

I don't think filters will solve my problem but I could be wrong. How does filtering effect what record shows up when the form is opened?

Thanks!

 

[PHV]

You may filter the blank records.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[infinitx]

I don't want to filter the blank records. When the form is open, I want it to go to the first blank record and not show the record that has been entered before.

Thanks!

 

[PHV]

You may filter to show only the blank records.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[infinitx]

Oh, what is the expression to filter to show only blank records?

Thanks!

 

[scriverb]

PHV's response as to filtering and only showing the blank records should work for you as ACCESS will then display the first blank record. But, I question why you would create these blank records in the first place. If you didn't create them and the ID field is an autonumber and you set the forms Data Entry property to Yes, then each time you enter the form a blank record will come up with the next ID number. When you save it then again the next ID number will come up with a blank record. And, on and on. It is better to do it this way than creating a bunch of blank records to start out and then have to manipulate and deal with records that have really not been created and data entered into them.

Post back if you want more discussion.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[infinitx]

The reason for this is when all the ICL's have have been entered, the user selects them from a series of combo boxes. Here's where we run into trouble. If the user does not need some of the ICL's, he has to enter ("") in order to keep the relationships correct. This creates a problem because if the user enters a blank record and tries to choose from the combo boxes, that blank record appears as one of the choices.

In another thread, Tranman suggested using this code:

Select distinct <fieldname> from <tablename> where <fieldname> is not null and <fieldname> <> "";

Which is to disclude Null and ("") values. The Null part of the SQL Statement seems to work fine, but (and <fieldname> <> "" doesn't seem to work.

Any ideas?

Thanks!

 

[scriverb]

Problem is the User can't enter an empty string. It is either a space or tab through the field which would be a null. An empty string can only be set through code or a default value setting.

I am still not understanding the relationship of your table to the selection or entry of data. What is an ICL? Explain the data entry process from a User stand point with any preconceived notions as to how to do it in the database. Explain the tables, fields of concern, and relationship that you have mentioned.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[infinitx]

ICL stands for Item Classification Level

On the Switchboard, there is a Command Button called "Add Clasification". When the user clicks it, a form pops up. By the way, there are four levels of classification. Here is the basic overview of the Classification Level System:

- Level 4
- Level 3
- Level 4


- Level 2


- Level 4
- Level 3
- Level 4

Level 1

- Level 4
- Level 3
- Level 4


- Level 2


- Level 4
- Level 3
- Level 4

Form 1 allows the user to only one Level 1 at a time. The user then clicks "Next" and form 2 opens. In form 2, the user is allowed to enter two Level 2 Classification Levels. The user again clicks "Next" and Form 3 opens which allows the user to enter four Level 3 classification levels. The user then clicks "Next" and the user is allowed to enter eight Level 4 Classifications.

The user is allowed to go through this process a maximum of three times.

I have the following tables:


ICL

Item ID 2
Item ID 4
Item ID 6
Item ID 8


ICL 1

Item ID 2
ICL 1


ICL 2

Item ID 4
ICL 2


ICL 3

Item ID 6
ICL 3


ICL 4

Item ID 8
ICL 4

Tables ICL 1-4 are linked to Table ICL by their respective Item ID's.

Table ICL is where all of the relationships for all of the ICL tables are entered.

ICL looks like this:
Item ID 2 Item ID 4 Item ID 6 Item ID 8
1 1 1 1
1 1 2 2
1 2 1 1
1 2 2 2

This basically means that if the user selects ICL 1 that is under the Item ID 2 of 1, then in combo box 2, his choices would be the items that are under the Item ID 4's of 1 and 2.

Hope this helps!

 

[scriverb]

Well after all of that I am totally confused but that's okay my wife says that is nothing new. But, I think that to solve your problem we just need follow PHV's advise here and create a filter so that only the blank records are displayed thus your form will open to the first blank record. You see you won't be eliminating the blank records but including them exclusively.

The form property Filter needs to be updated to the following:

IsNull([ICL 1]) or [ICL 1]="" [code]

Now execute your query by click the Form View or Datasheet view button in the upper left. You will still see all of the records because the filter is not yet turned on.  From the Form View toolbar click the [b]Filter On]/b] button.  This will apply the filter.  Now save the form.  From now on the filter will be applied when the form is opened and you will not see any of the records that have already been updated.  The first blank record should be the one that has the focus in the form.

Post back if you have any more questions.



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[infinitx]

Thanks!

That worked.

The only problem I have is that the filter does not stay applied after I close the form. Is there a way to turn it on so it is on all of the time?

Thanks again!

 

[scriverb]

Yes, you can. Put the following code in the forms On Open event procedure:

Me.FilterOn = True

Good luck. Thanks for the star.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[infinitx]

Thanks again!

 

[ESquared]

FYI DataEntry mode ONLY allows you to add new records, you can't edit existing ones.