Open List Items Edit form on new record (1 Viewer)

[mishash]

In frmOrders, when adding notinlist value in combobox "CustomerID" a List Items Edit data property triggers frmCustomers form to open.
It opens on the 1st existing record (CustomerID=1).
I need it to open on new record (preferably inheriting the entered new customer's name, but this is less important).
I don't want to put DoCmd.GoToRecord , , acNewRec comand in On Open or On Load event of frmCustomers , as in other scenarios I open frmCustomers double-clicking on the customer's name (so it opens on selected record).
What can be done with as few code as possible?
Thanks in advance.

 

[Gasman]

This is one way ?

open form, add new record, and set a value in that new record

I have a button(cmd1) on a form(Form1) that I want to have open a different form(Form2) to add more detailed information, add a new record, and set a field in Form2 equal to the primary key of Form1 without having to add Form2 as a subform. Anyone know of any ideas or have code for that?

www.access-programmers.co.uk

Open form in add mode

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

Also look at Similar threads below

 

[mishash]

This is one way ?

open form, add new record, and set a value in that new record

I have a button(cmd1) on a form(Form1) that I want to have open a different form(Form2) to add more detailed information, add a new record, and set a field in Form2 equal to the primary key of Form1 without having to add Form2 as a subform. Anyone know of any ideas or have code for that?

www.access-programmers.co.uk

Open form in add mode

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

Also look at Similar threads below

Thank you for your reply.
Unfortunately none of your suggestions seems to address the problem I've described. The form opening trigger is executed automatically (so I don't know where to add DoCmd.OpenForm method) and I cannot add On Load event for the reasons I've mentioned.

 

[Gasman]

Thank you for your reply.
Unfortunately none of your suggestions seems to address the problem I've described. The form opening trigger is executed automatically (so I don't know where to add DoCmd.OpenForm method) and I cannot add On Load event for the reasons I've mentioned.

Not quite.

I use that method to add a new client if it is not in the combo?
I open the form frmClient with an OpenArgs of the client number.
In frmClient, only if the OpenArgs parameter is set, do I add a new record.

On other occasions I open frmClient, there is no OpenArgs paremeter, so it opens normally.
And if you do not believe me.

Private Sub CMS_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Set ctl = Me.CMS

If MsgBox("CMS ref is not in client table? Add it", vbOKCancel) = vbOK Then
    DoCmd.OpenForm "frmClient", , , , , acDialog, Me.CMS.Text
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
    ctl.Undo
End If

Set ctl = Nothing

End Sub

and in the frmClient form

' If opened from elsewhere via a form, we need a new record with passed CMS reference
If Not IsNull(Me.OpenArgs) Then
    DoCmd.GoToRecord , , acNewRec
    Me.ClientCMS = Me.OpenArgs
End If
End Sub

 

[theDBguy]

Hi. Pardon me for jumping in. If you're using frmCustomers for other purposes, I would recommend creating a copy of it and set the copy's Data Entry property to Yes. Then, update your List Items Edit Form combo to use the copy. Hope that helps...

 

[mishash]

Hi. Pardon me for jumping in. If you're using frmCustomers for other purposes, I would recommend creating a copy of it and set the copy's Data Entry property to Yes. Then, update your List Items Edit Form combo to use the copy. Hope that helps...

Thought of this too. I was hoping to find a simple solution without multiplying DB objects unnecessarily.

 

[theDBguy]

Thought of this too. I was hoping to find a simple solution without multiplying DB objects unnecessarily.

The other option is to set the Data Entry property of frmCustomers to Yes and just manipulate it when you have to use the same form other than for the Combobox.

 

[Dreamweaver]

I do this with the not in list event although It creates a new record then opens that record so the remaining items like address Etc can be added.

'=======Add Under Option Explicit===================================================================
Private m_rst As Recordset 'Used for all my List filling functions which have worked without fault for 18 years
Private m_IntNew As Integer 'used to test the response from message box
Private Const m_strMsg As String = " Is Not An Item In The List Would You Like To Add It" 'Part Of all list filling funtions message
Private Const m_strUndoMsg As String = " Is Not In the List Undoing Entry" 'Part of the list filling function undo message
Private Const m_strTitle As String = "Undoing Invalid Entry" 'Undo title
'==========================================================================================
 
Public Function FillListsOneExt(StrItem As String, StrTable As String, _
                            strField As String, StrForm As String, IDField As String) As Integer
'=================================================================
'Description:Added Items To A Combo And opens a form sent in
'Called By: NotInList
'Calling:
    'Response = FillListsOneExt(NewData, "TableName", "FieldName", "FormToOpen", "IDField")
    '    If Response = acDataErrContinue Then
    '        DoCmd.RunCommand acCmdUndo
    '    End If
'Parameters: StrItem As String, StrTable As String, strField As String, StrForm As String, IDField As String
'Returns: acDataErrContinue  or acDataErrAdded
'Author: Michael Javes, Database Dreams
'Editor(s) :
'Date Created: 2004-2008
'Rev. History:
'Requirements: FormToOpen
'=================================================================
Dim NewID As Long
On Error GoTo Err_HandleErr
    m_IntNew = MsgBox(StrItem & m_strMsg, vbInformation + vbYesNo, "Item Not In List")
    If m_IntNew = vbYes Then
        Set m_rst = CurrentDb.OpenRecordset(StrTable)
        m_rst.AddNew
        m_rst(strField) = StrItem
        NewID = m_rst(IDField)
        m_rst.Update
        FillListsOneExt = acDataErrAdded
        m_rst.Close
        Set m_rst = Nothing
        DoCmd.OpenForm StrForm, , , "[" & IDField & "]=" & NewID, , acDialog, "Adding"
    Else
        MsgBox StrItem & m_strUndoMsg, , m_strTitle
            FillListsOneExt = acDataErrContinue
    End If
    
Exit_HandleErr:
    Exit Function
    
Err_HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
            Resume Exit_HandleErr
        Resume
    End Select
End Function

 

[mishash]

The other option is to set the Data Entry property of frmCustomers to Yes and just manipulate it when you have to use the same form other than for the Combobox.

How do i edit this properly:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID
Forms!frmCustomer.DataEntry = False

End Sub

So I pass the argument and switch the DataEntry to False in one click?

 

[theDBguy]

How do i edit this properly:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID
Forms!frmCustomer.DataEntry = False

End Sub

So I pass the argument and switch the DataEntry to False in one click?

Looks like that should have worked. If not, you could try it like this:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID, acFormEdit
Forms!frmCustomer.DataEntry = False

End Sub

If that doesn't work, you could try it this way:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID, acFormEdit, acHidden
Forms!frmCustomer.DataEntry = False
Forms!frmCustomer.Visible = True

End Sub

And yet, another option is to use the OpenArgs parameter.

 

[mishash]

Looks like that should have worked. If not, you could try it like this:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID, acFormEdit
Forms!frmCustomer.DataEntry = False

End Sub

If that doesn't work, you could try it this way:

Private Sub CustomerID_Click()

DoCmd.OpenForm "frmCustomer", , , "CustomerID = " & Me!CustomerID, acFormEdit, acHidden
Forms!frmCustomer.DataEntry = False
Forms!frmCustomer.Visible = True

End Sub

And yet, another option is to use the OpenArgs parameter.

All 3 options open frmCustomer unfiltered (on 1st existing record). I guess, the DataEntry property should be turned off before the argument "CustomerID = " & Me!CustomerID is passed, nor after.

 

[theDBguy]

All 3 options open frmCustomer unfiltered (on 1st existing record). I guess, the DataEntry property should be turned off before the argument "CustomerID = " & Me!CustomerID is passed, nor after.

Maybe you can just reapply the filter after you open the form. Like, passing the filter to the OpenArgs too.