Want to got to a form On Not in List and return to original form and update the combo box

[catluvr]

Hi all!



You were SO VERY HELPFUL with my last request ... everything is working beautifully! You were even helpful on things you didn't know about because as i was typing out my post, I got a few ideas and worked it out myself! So, thanks for those, too!

This time, it's a database for dear hubby.

I've done something very similar in the past that worked perfectly. This time, I think it's due to the sub-form ... maybe?

There are installation details that use certain parts and a particular quantity of that part.

I have a parts table, an detail table (hubby insisted on calling it that) and a link table ... this is all very much like parts and invoices or a recipe and ingredients.

On the Install Detail form, there is a sub-form for the parts. The sub-form has a combo box for the part number, a box to enter the quantity and two locked fields showing the description and size of the part so the user knows it's the correct one.

If the user enters a part number that isn't in the parts table, a message box pops up and asks the user if they want to enter the new part. if they answer "no", another message box prompts them to choose from the list. If they answer "yes", then the Parts form opens with the new part number already populating the Item No. field. But then, a message appears saying "The text you entered isn't an item in the list" ... this is an Access message, not one I programmed. If I could figure out where that was being triggered, I'd add a line to disable the error messages.

After the new part is entered and the Parts form is closed, I'd like the combo-box in the sub-form to requery. But, it's unhappy about that, too!

I've attached the database, but here's the code in case something obvious jumps out:

This is from the sub form when the part number isn't in the list:

Private Sub Item_No_NotInList(NewData As String, Response As Integer)    On Error GoTo Item_No_NotInList_Err
        Dim intAnswer As Integer
        Dim strNew As String     'didn't end up using this
       
        DoCmd.SetWarnings False


        intAnswer = MsgBox("The part number " & Chr(34) & NewData & _
            Chr(34) & " is not currently listed." & vbCrLf & _
            "Would you like to open the part number form in order to add it now?" _
            , vbQuestion + vbYesNo, "B&V BOQ")
        If intAnswer = vbYes Then
            'open the parts form in add new mode and pass the NewData to the form
            DoCmd.OpenForm "frm_Parts", , , , acFormAdd, , NewData
            Response = acDataErrAdded
        Else
            MsgBox "Please choose a part number from the list." _
                , vbInformation, "B&V BOQ"
            Response = acDataErrContinue
        End If
Item_No_NotInList_Exit:
        DoCmd.SetWarnings True


        Exit Sub
Item_No_NotInList_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume Item_No_NotInList_Exit
End Sub

On the parts form, this is in the On Load event:

Private Sub Form_Load()    'set the cursor in the item_No text box
    Me.Item_No.SetFocus
    'check to see if an openargs value was passed from the Parts subform
    If Not IsNull(Me.OpenArgs) Then
        'if there IS an openargs value, then set the Item_No to equal the NewData
        Item_No = OpenArgs
        Else
        'if there is no openargs value, set the cursor in the Item
        Me.Item_No.SetFocus
    End If
End Sub

Should I disable the regular Access warnings? if so, where?

I tried code to requery the the Item_No combo box in the sub-form when it had focus, but it wasn't happy with that, so I took it out. Can I requery a sub-form in it's entirety? Yes, I can, but It's still upset that the text in the Item_No combo box isn't in the last ... although with the requery, it actually IS.

When the parts form is closed, I do want the Instal_Detail form to still be on the same Detail.

(and a note: the part numbers and the detail numbers are the primary keys because dear hubby swears up and down there will be no duplicates and he and the other users get a bit anxious when an AutoID number shows up in a field where they were expecting a text part number or detail number. So don't fuss at me about that! )

Thank you!

Susie
Kansas

[moke123]

If intAnswer = vbYes Then
dim NewID as long
NewID = fGetNewID(NewData)

'open the parts form in dialog mode to halt code
            DoCmd.OpenForm "frm_Parts", , , "PartID = " & NewID, , acDialog
            Response = acDataErrAdded

Private Function fGetNewID(ND As String) As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strOut As String
    strSql = "select * from  tbl_Parts where PartID = 0"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    rs.AddNew
    rs!Item_No = ND
     fGetNewID = rs.Fields(0)
    rs.Update

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Function

First I would use an autonumber Primary Key.

You need to add the part to the table first, get the primary key in return and then open the form using the where arguement. Then you can enter any of the additional info in the form.

(and a note: the part numbers and the detail numbers are the primary keys because dear hubby swears up and down there will be no duplicates and he and the other users get a bit anxious when an AutoID number shows up in a field where they were expecting a text part number or detail number. So don't fuss at me about that! )

Tell hubby that a primary key is supposed to be meaningless and should never even be seen by a user. Why does hubby want to torment the developer?

[davegri]

BOQ_AGG-davegri-v01.zip

See attached. This just fixes your existing code. No schema changes.

[moke123]

if you want to save yourself a lot of coding use a public function in a standard module. It has the option to use a form or not.

Public Function AddNewToList(NewData As String, stTable As String, _
                                   stFieldName As String, strPlural As String, _
                                   Optional strNewForm As String) As Integer
On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record
    
    'Call with:
    'Response = AddNewToList(....


    'Declare variables
    Dim rst As DAo.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String
    
    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."


    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)


        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog
  
        AddNewToList = acDataErrAdded                'Set response 'Data added'
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If
    
exit_proc:
On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function


err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc


End Function
'Notes:
'1. The Primary Key field must be numeric (long integer) and must always be the first field in the table.
'2. The 'Limit to List' property of the combo box must be set to 'Yes'
'3. strNewForm is opened in edit mode as the new record is added first and the form then opened to that record. A consequence of this is that other fields in the table must have their 'Required' property set to 'No' or a (valid) default property value set in the table design.
'4. FieldNamePlural is there simply to make the message grammatically correct; in the AddNewToList code the message box string generated as: strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) ..... would result in the warning message (e.g.):
'London' is not in the current list. "
'Do you want to add it to the list of Cities?
'(Please check the entry before proceeding).
'5. If an edit form is opened (strNewForm <> "") then the field that is displayed in the combo box should be in a locked control on the form, alternatively remove the acDialog argument and ensure the combo box is re-queried when the form is closed.
'6. The form 'strNewForm' should have it's 'Allow Additions' and 'Data Entry' properties set to 'No' to prevent users from adding additional entries to the entry requested by the Not In List event.
'Enjoy!
'Edit1: 18/02/2013: Added Note 4
'Edit2: 02/03/2014: Added Notes 5 & 6.

[catluvr]

Tell hubby that a primary key is supposed to be meaningless and should never even be seen by a user. Why does hubby want to torment the developer?

Because when he and other users design reports, they get discombobulated when a number shows up instead of the data they were expecting ... wanting the part numbers associated with an installation, so they use the link table and get integers instead of the actual part numbers!

I know, I tried!

Thank for your response!

[catluvr]

Thank you davegri! it's just what I needed!


Susie
Kansas

[davegri]

Thank you davegri! it's just what I needed!

Happy to help and thanks for the star!