X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Not In List Producing Incorrect Data, Access 2016    
post May 16 2018, 10:55 AM

Posts: 215
Joined: 15-October 10
From: London, UK

My subform flk:Child_Adult contains a NotInList event for a combo box (bound to chau_au_ID) on the form called cboAdult. The row source of cboAdult is:

SELECT [tau:Adult].au_ID, [au_LastName] & ", " & [au_FirstName] AS Adult, [tau:Adult].au_Email
FROM [tau:Adult]
ORDER BY [au_LastName] & ", " & [au_FirstName];

the code on the NotInList event is:

strMsg = "'" & NewData & "' is not in the adult list. Would you like to add it?"
If MsgBox(strMsg, vbYesNo + vbQuestion, "New adult") = vbNo Then
Response = acDataErrDisplay
'Close the Adult form if it is open, otherwise adding the new data fails
If fnIsLoaded("fau:Adult") = -1 Then
DoCmd.Close acForm, "fau:Adult", acSaveYes
End If
'Open (or re-open) the Adult form
DoCmd.OpenForm ("fau:Adult"), , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If

The data that has been typed into cboAdult is a surname (let us use Jones as an example), and a first name needs to be added as well. When fau:Adult is closed (the new surname, and an accompanying first name, having been added), the field will be updated with the surname if the surname is unique. However, if the surname is not unique (Jones, Alfred; Jones, Bryan,; Jones, Charles), and we have added Jones, Charles, then the surname/first name combination that comes first alphabetically is what gets added (ie Jones, Alfred). This keeps getting me into trouble as I fail to notice.

I feel I need to get the au_ID involved here but I don't know how to do that or whether it is necessary.
Go to the top of the page
Doug Steele
post May 16 2018, 12:47 PM

UtterAccess VIP
Posts: 22,065
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Realistically, since tau:Adult has separate fields for first and last names, it's not reasonable to expect the NotInList event to be able to add the new input (which represents a value that should be split into two separate fields) to the table. Nor, in my opinion, is it reasonable to be able to split the compound name into its components automatically. (How can you know whether Mary Lou Renton should be split to first name Mary and last name Lou Renton or first name Mary Lou and last name Renton? Now ask the same question for Ludwig van Beethoven.)

I would suggest that, when the user has indicated that they want to add a new name to the list, the code should open a maintenance form (in dialog mode, so that the execution of the code associated with the NotInList event stops), the user indicates the proper name then closes the maintenance form. Note that after the maintenance form is closed, you'd still need to check whether the name in NewData exists in tau:Adult, since it's possible they didn't actually add it on the maintenance form.

BTW, do yourself a HUGE favour and stop using semicolons (or any other special characters) in your naming convention!

Oh, and the acSaveYes flag in DoCmd.Close acForm, "fau:Adult", acSaveYes doesn't do what you probably think it does. It indicates whether or not to save changes to the design of the form that's being closed, not whether or not to save data that's been entered on the form.
Go to the top of the page
post May 17 2018, 04:02 AM

Posts: 215
Joined: 15-October 10
From: London, UK

I am suitably advised, and admonished hat_tip.gif Thank you, Doug. I appreciate your help.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd January 2019 - 02:13 AM