Newbie at Access needs help

[gti_jobert]

Hi all,

I'm just having a dabble in VBA for Access so I'm fairly newbish!

I have a field [Reference Number] that increments in the table by 1 everytime - but the user has to enter this manually (its not auto-incrementing). How can I do the following: when the user selects a new row in the table it will automatically look in the table for the last entered [Reference Number], increment it, and put that value in the Userforms TextBox??

Hope you understand this,

TIA

 

[Krij]

Choose the property sheet->Data tab and the default value field. Write:

=Nz(DMax("[aField]";"tblSomething"),1)+1

Hi all,

I'm just having a dabble in VBA for Access so I'm fairly newbish!

I have a field [Reference Number] that increments in the table by 1 everytime - but the user has to enter this manually (its not auto-incrementing). How can I do the following: when the user selects a new row in the table it will automatically look in the table for the last entered [Reference Number], increment it, and put that value in the Userforms TextBox??

Hope you understand this,

TIA

 

[gti_jobert]

Hi thanks for quick response!

I'm Selected my Reference Number Textbox on my form >> Properties >> Data Tab >> Default Value >> =Nz(DMax("[Reference Number]";"tblData"),1)+1

Doing the following above, select another text box and go back to Ref Number properties and the default value text has been lost? am I doing the right thing here? Sorry for being so newbish!!

TIA

 

[gti_jobert]

Managed to find and use the following code that works just how I wanted it to...

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
    Me.Reference_Number.DefaultValue = Nz(DMax("[Reference Number]", "tblData"), 0) + 1
End If
End Sub

 

[Krij]

Make sure you save any changes before you move on to a new post. Do this by adding:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

in the 'Exit' or 'LostFocus' event of the textbox. Sometimes Access play its own tricks

Hi thanks for quick response!

I'm Selected my Reference Number Textbox on my form >> Properties >> Data Tab >> Default Value >> =Nz(DMax("[Reference Number]";"tblData"),1)+1

Doing the following above, select another text box and go back to Ref Number properties and the default value text has been lost? am I doing the right thing here? Sorry for being so newbish!!

TIA

 

[Banana]

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This is now obsolete, though Wizard like to use that sort of code. Instead of this use-

DoCmd.RunCommand acCmdSaveRecord