Use autonumber value in new record to show/hide tab

[Sendeman]

Hi All,

When I am adding a new record in the form, I want to make a tab visible as soon as the autonumber field in this record gets a value (i.e. after a value has been added to any other field in the form >> the autonumber field is disabled).

Is there an event that is triggered that I can use for this? I already tried foom_dirty, autonumberfield_change and autonumberfield_afterupdate.

I think creating code for the onchange event for every field on the form (other than the autonumber field) might be a workaround, but is seems like a tedious and chaotic way of doing this.

Any other ideas on how to do this? Thanks a lot in advance for any help!

Regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[TheAceMan1]

Hoe are ya Sendeman . . .

You already tried the forms [blue]On Dirty[/blue] event ... you just need to [blue]detect if it was a new record[/blue] that caused the event to fire:

[blue]Private Sub Form_Dirty(Cancel As Integer)
   
   If Me.NewRecord Then
      [green]'make tab visible[/green]
   End If

End Sub[/blue]

Also, an [blue]AutoNumber[/blue] field/control is not editable so you don't need to disable it! Try it!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Sendeman]

Hi AceMan,

I tried your suggestion. I tested with this code:

Private Sub Form_Dirty(Cancel As Integer)
   If Me.NewRecord Then
      MsgBox (Me.BhId)
   End If
End Sub

When I now type a single letter into any of the fields on the form, I get this error message:

Runtime Error 94. Invalid use of Null. (translated from Dutch)

So BhId has not yet received an autonumber when the new record is inserted and so the MsgBox gets a null value. I tested the same code with a MsgBox that returns "Test" instead of BhId. I get the MsgBox now, and I get it just before the textbox for the BhId field is filled with a value.

I want to enable the tabs as soon as the BhId value for the new record is available. Any more ideas?

Regards, Martijn.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[PHV]

Private Sub Form_Dirty(Cancel As Integer)
If Me.NewRecord Then
  If Not IsNull(Me!BhId) Then
    MsgBox (Me!BhId)
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sendeman]

Hi PHV,

Thanks for the reply! I obviously do not get the error message anymore, but I don't get a MsgBox either, because at the time the Forms OnDirty event is triggered, the new record does not have a BhId yet (BhId == Null).

So, I really think I can not get this working using the Forms OnDirty event.

I also tried to run the code on the OnChange and OnUpdate events of the BhId textbox, but this doesn't work either...

I wonder if this is possible at all?

Regards, Martijn.


I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[danvlas]

Private Sub Form_BeforeInsert(Cancel As Integer)
MsgBox "You're done!"
End Sub

HTH


Daniel Vlas
Systems Consultant

 

[Sendeman]

Thanks! But I the same runtime error again:

Runtime Error 94. Invalid use of Null. (translated from Dutch)

So, BhId (autonumber field) doesn't get a value before the new record is inserted, which seems logical to me.

Any more suggestions?

Regards, Martijn.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[danvlas]

You can make the tab visible in the BeforeInsert event.

As soon as you type anything in the form, the AutoNumber GETS a value, but it's not yet commited to the database. This seems specific to Access (generating an ID value for a *possible* record)

The value of the ID is incremented internally, so if you cancel the insertion, that ID is lost.

You can see the value and 'steal' it in the 'BeforeUpdate' event (or any other that fires after BeforeInsert), if you need to use it before saving the record.

However, using it as a foreign key is possible only after the changes are commited to the database.

HTH


Daniel Vlas
Systems Consultant

 

[Sendeman]

Hi again,

Well thanks. That seems to explain why I can't do what I want to do. I guess access doesn't really add the value to BhId or its textbox yet until the new record is comitted to the database.
I don't want to make the tabs visible in the before insert event. It isn't triggered at the right moment.

I think I'll just have to change the design so I don't have to code things this way. I think instead of showing the tabs when the BhId becomes available, I will end up displaying the tabs when the user has filled all the required fields for the new record.

Thanks for all the efforts everyone.

Regards, Martijn.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -