Add New Record from Navigation Buttons, How Do I get value in text box for new record??

[joel009]

I have a bound form where the record source query prompts the user to enter a value to filter the recordset. The Values are SH or NP. After the user enters a value all the correct records are available for edit. What I want is IF the user clicks on the add new record button to have the text box default to the users choice (SH or NP) for the new record. Hopefully this will not create a new record with only one field populated if the user does not enter any more info.

Using Access 2003 and 2007 interchangebly.

I inherited this project and not given much time or I would rewrite as an unbound form.

Is this possible in a bound form? I could capture the first records Value in a hidden text or the tag rpoperty but I haven't found an event to capture the add new record.

Thanks

Joel

 

[PHV]

I haven't found an event to capture the add new record
In the Current event procedure of the form you may test the Me.NewRecord property.

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

 

[dhookom]

I would use your current form as a subform on an unbound main form. Add a combo box named [cboSHNP] on the main form to select either SH or NP. Then use this combo box in the Link Master property of your subform control. Set the Link Child property to the appropriate field in the subform recordsource.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If you use the combo box idea then in the default value property for your text box you can set the property to
=[YourComboNameHere]
This will also ensure that a new record is not created with a single field.

 

[joel009]

Thanks to all for the suggestions. Here is what I did. The query that is the recordset for the form prompts the user, if nothing is entered records may be added with no ProvType.

Private Sub Form_Current()

    If Me.NewRecord = True Then
        'set txtProvType to SH or NP
        Me.txtProvType = Me.Tag
    End If

End Sub

Private Sub Form_Open(Cancel As Integer)

    If IsNull(Me.txtProvType) = False Then
        Me.Tag = Me.txtProvType
    End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
'clean up table

    DoCmd.SetWarnings False

    DoCmd.RunSQL ("DELETE MasterFile.*, MasterFile.ProvType" _
                    & " FROM MasterFile" _
                    & " WHERE (((MasterFile.ProvType) Is Null));")
    
    DoCmd.RunSQL ("DELETE MasterFile.*, MasterFile.[File Date]" _
                    & " FROM MasterFile" _
                    & " WHERE (((MasterFile.[File Date]) Is Null));")

    DoCmd.SetWarnings True

End Sub

It is not very elegent but only a few users get into this to maintain it. Sloppy but effective. In the unload event I delete anything in the table that may have gotten added.

Thanks again!!!

Joel

 

[dhookom]

You should be able to replace this:

    DoCmd.RunSQL ("DELETE MasterFile.*, MasterFile.ProvType" _
                    & " FROM MasterFile" _
                    & " WHERE (((MasterFile.ProvType) Is Null));")
    
    DoCmd.RunSQL ("DELETE MasterFile.*, MasterFile.[File Date]" _
                    & " FROM MasterFile" _
                    & " WHERE (((MasterFile.[File Date]) Is Null));")

With this:

    DoCmd.RunSQL "DELETE MasterFile.* " _
                    & " FROM MasterFile" _
                    & " WHERE ProvType Is Null or [File Date] Is Null"

Duane
Hook'D on Access
MS Access MVP