On Current command for a new record

[SOMSteve]

I am running MS ACCESS 2003 SP3. I have run into a (probably very easy) problem that I couldn't find an answer to when searching the posts. I am using the "On Current" event to try and detect if the item being entered is a new item. Because I don't know any other way to tell it "On new record" i am having it look at whether the Item# (ItemNum is an Autonumber) Is Null. If it is, then I want a message box to ask the user if they would like to add a new item. If they click yes, then I want a few lines of code to run. Below is my code, currently I am having problems with the first line that states:


If you have any suggestions I would greatly appreciate them, thanks!

Private Sub Form_Current()
'Prompt to add new item in order to fill in all subforms
'This is where I am also running into problems, with the Is Null statement
    If Me![ItemNum] Is Null Then
        Dim strMsg As String

        strMsg = "Would you like to add a new item/sale to the Database?"
        If MsgBox(strMsg, vbOKCancel + vbQuestion, "Add New Rate?") = vbOK Then
            Me!Quantity = 1
            Me!txtItemCount = Me!Quantity
            Me.[subfrmSalesInternet2].Form.txtQuantity = Me![Quantity]
            Me.[subfrmSalesInternet2].Form.txtItemNum = Me![ItemNum]
            Me.[subfrmSalesInternet3].Form.TransferNum = Me![TransferNum]
        Else
            Response = acDataErrContinue
        End If
    Else
            Response = acDataErrContinue
    End If
End Sub

-Steve

 

[PHV]

What about replacing this ?
If Me![ItemNum] Is Null Then
with this ?
If Me.NewRecord Then

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

 

[SOMSteve]

Works great! I knew there had to be a simple way to tell if it is a new record. Thanks again PH.

 

[TheAceMan1]

How are ya SOMSteve . . .

You can easily get this solved with PHV's help, however if your copying MainForm Data to subForm data (as you've4 shown) you have a [blue]normalization problem.[/blue] Have a look at:

Normalizing Tables

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

Table Relationships

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

See Ya! . . . . . .

Be sure to see FAQ219-2884: