Solved Textbox re-uses auto-number ProductID problem?

[Babycat]

Hi Everyone

The table PRODUCT has field image to store its image name (supposed i already have folder with images). The ProductID is an auto-number field
The default image is "Default.JPG", when I edit anything on form, the image field will be set to ProductID.JPG such as 1.JPG, 2.JPG... This is accomplished by AfterUpdate event. It looks fairly fine. However It is not working on new record.
For example, I am on record ProductID = 9, I click newrecord on navigation bar then type the name into ProductName textbox. Now I navigate to other record, AfterUpdate event triggered as expect.
I thought at this moment newrecord (ID=10) already saved into table and i am now on record 10. Thus, I try to save its image name by

rs!Image = rs!ProductID & ".JPG"

But strange is that rs!ProductID = 9, but not 10.
So It failed to save image name of ID 10.

May anyone help to explain and advise a solution?

 

[theDBguy]

Hmm, why use rs? Why not just Me? For example:

Me.Image = Me.ProductID & ".jpg"

PS. You might want to use the BeforeInsert event instead of AfterUpdate.

 

[Pat Hartman]

I think the DBGuy meant the form's BeforeUpdate event

The form's AfterUpdate event runs AFTER the record is saved. In earlier versions of Access code that updates the form would send Access into a tight loop. The screen would flicker wildly and the only way out was cntl-alt-delt. Cureent versions of Access escape gracefully when the stack gets too deep. That doesn't mean you should use the AfterUpdate event to update the current record though

 

[Babycat]

Hmm, why use rs? Why not just Me? For example:

Me.Image = Me.ProductID & ".jpg"

PS. You might want to use the BeforeInsert event instead of AfterUpdate.

I dont think the new record is saved to table on BeforeUpdate event.
Anyway, I have followed your suggestion that using "Me.", the result surprised me:
I set: rs = Me.Recordset
then I got:
rs!ProductID = 9,
but
me.ProductID = 10.

Why is difference btw rs!ProductID and Me.ProductID?

Anyway, I am fine with me.ProductID = 10, this value is what I want.

 

[theDBguy]

Anyway, I am fine with me.ProductID = 10, this value is what I want.

Glad to hear that worked out for you.

Why is difference btw rs!ProductID and Me.ProductID?

Me. refers to the "current" record; whereas, rs! may have been pointing to the first record.

 

[Babycat]

Me. refers to the "current" record;

Is that the one displaying on my form?
To manipulate current record on form, I normally have to access via Me.Recordset!ProductID (seem this is a critical mistake of me), or control Textbox_ProductID if this control bounds to field ProductID.
I dont even know Me.ProductID is a valid syntax.

Feeling dizzy now... )

 

[Pat Hartman]

I dont think the new record is saved to table on BeforeUpdate event.

Technically it is saved BETWEEN the form's BeforeUpdate and AfterUpdate events.

Me. refers to the form's CURRENT record.
rst. refers to the recordset so it only includes SAVED records. That is why the ID is different.

 

[theDBguy]

Is that the one displaying on my form?

Yes.

 

[Pat Hartman]

Also, the AfterUpdate event does NOT advance to the next record.

 

[Babycat]

Technically it is saved BETWEEN the form's BeforeUpdate and AfterUpdate events.

Me. refers to the form's CURRENT record.
rst. refers to the recordset so it only includes SAVED records. That is why the ID is different.

Yeah it is clear now. I have learnt new thing for today.

Btw, you said: AfterUpdate event does NOT advance to the next record.
What did you mean by "next record"? Could you please more elaborate. I did not really get it.

 

[Pat Hartman]

What did you mean by "next record"?

You seemed to think that the AfterUpdate event should scroll the recordset to the next record. It is not clear at all to me why you were trying to get the PK from the form's recordset rather than the form controls but that is neither here nor there. Based on your code in the AfterUpdate event, it appears that the recordset does not advance to the newly added record until the AfterInsert event.

 

[Cotswold]

Personally I wouldn't use an AutoNumber as a system Code or ID. If your database is ever corrupted you may well lose the field with that individual number and you can never recreate or recall it. Once lost all related fields in your database may well not show and appear lost. (which they arent)

If you must use an AutoNumber to create codes, at least add a field (maybe) called CodeID and insert the AutoNumber content into it on save. You can then use that to link to other tables rather than the AutoNumber ProductID. Then if you do lose records you can simply add the Product again and change the CodeID to the original number. Your system will then continue to work.

 

[Babycat]

You seemed to think that the AfterUpdate event should scroll the recordset to the next record. It is not clear at all to me why you were trying to get the PK from the form's recordset rather than the form controls but that is neither here nor there. Based on your code in the AfterUpdate event, it appears that the recordset does not advance to the newly added record until the AfterInsert event.

Hi Pat
Nothing special, it is just because I was not well-aware about the recordset. I thought once new-record inserted, form's recordset points to the newest one.
Anyway, I got what i want with Me.ProductID.

Thank everyone for your supports.