Add Items Not In List

[sueb]

This question is related to the database found in How to make a newly-added record show up in an existing form?.

What I have seems like a hot mess to me, while the thing I'm trying to achieve seems fairly simple. Here's how I would like this to behave:

1. The user puts a number in the "Jump To" field. If the number is already in the database, it should just go to that record, and it now does that.

2. If, however, the number is NOT in the database, I want a Yes/No window to pop up displaying the number that was entered, and the question "Add the new chart number?".

3. Clicking "Yes" would add a record to the database, with the new number in the ChartNum field, close the popup, and cause the parent form to go to the new record.

4. Clicking "No" would close the "Yes/No" window, and leave the parent form exactly as it was, with the "Jump To" field blank.

Currently, the user has to re-enter the number if they do want to add it, and if they don't want to add anything, closing the popup window (the current one) generates another "Item not in list" error box. And the user has to go to the new record herself, but it is also not always available to "jump to".

I just seem to have a lot of little scraps of code that don't play well together, and I don't know enough about the different events involved to know where is the best place to do all this. Somehow, it seems that I shouldn't have to have a separate form-based-on-the-database just to add a new record, but I don't know any other way to do that. Here are the different chunks of code, in the order they are happening:

The unbound field Jump To on the form Patient_IUR_Overview has the following 2 routines:

Jump_To_AfterUpdate jumps to the record that matches the user's input, and seems to work correctly:

Private Sub Jump_To_AfterUpdate()
On Error GoTo Err_Jump_To_AfterUpdate
 
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    If Jump_To > "" Then
        rs.FindFirst "[Patient Index] = " & Str(Nz(Me![Jump_To], 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
    Me![Jump_To] = ""
 
Exit_Jump_To_AfterUpdate:
    Exit Sub
 
Err_Jump_To_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Jump_To_AfterUpdate
 
End Sub

Jump_To_NotInList is triggered when the user enters a number not yet in the database, and it calls Add_New_Chart_Click regardless of what the user clicks, so I know I'm not capturing their choice correctly on line 7:

Private Sub Jump_To_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Jump_To_NotInList
 
    Dim AddNew As Boolean
 
    Me![Jump_To] = ""
    AddNew = MsgBox("Add a new chart number?", vbYesNo)
    If AddNew Then Add_New_Chart_Click
    AddNew = False
 
Exit_Jump_To_NotInList:
    Exit Sub
 
Err_Jump_To_NotInList:
'    MsgBox Err.Description
    Resume Exit_Jump_To_NotInList
 
End Sub

The call to Add_New_Chart_Click brings up the Patients_Add_New form, which accepts entry (a second time!) of the chart number, adds the record, and closes itself (I included the OnClose event property code):

Private Sub Default_Invisible_Click()
On Error GoTo err_Default_Invisible_AfterUpdate
 
    Call DoCmd.RunCommand(Command:=acCmdSaveRecord)
    Call DoCmd.Close
 
exit_Default_Invisible_AfterUpdate:
    Exit Sub
 
err_Default_Invisible_AfterUpdate:
'    MsgBox Err.Description
    Resume exit_Default_Invisible_AfterUpdate
 
End Sub
 
Private Sub Form_Close()
    Requery_Open_Forms
End Sub

In a public module:

Function Requery_Open_Forms()
On Error GoTo Err_Requery_Open_Forms
    Dim lngKt As Long
    Dim lngI As Long
    Dim frm As Access.Form
 
'    RequeryACollection clnOverviewClient
    If CurrentProject.AllForms("Patient_IUR_Overview").IsLoaded Then
        For lngI = 1 To lngKt
            Set frm = clnOverviewClient.Item(lngI)
            frm.Requery
        Next
    End If
    RequeryACollection clnIURClient
    RequeryACollection clnGISchedulingClient
    If CurrentProject.AllForms("Clinic_Notification").IsLoaded Then
        Forms("Clinic_Notification").Requery
    End If
 
Exit_Requery_Open_Forms:
   Exit Function
 
Err_Requery_Open_Forms:
    MsgBox Err.Description
    Resume Exit_Requery_Open_Forms
 
End Function

A curious thing is that the new record is not always available to navigate to, although the Jump To field now knows that it is "in the list".

You can see that this is just a tangle. What can I do to correct and streamline it?

[NeoPa]

The approach you suggest is one I like, and it can certainly be implemented (although I wouldn't say it's as straightforward as you seem to think).

With the code working (I need the OCX file for that) I may be able to test it better and it may well be that your code/design isn't too far off. If it doesn't involve too much work I may well help you to resolution of this situation. I'll look again in more detail when I have the file available.

[TheSmileyCoder]

In regards to capturing the user's answer on lines:

AddNew = MsgBox("Add a new chart number?", vbYesNo)
  If AddNew Then Add_New_Chart_Click

if you try to type this in your immediate window:

? vbYes=True

You will see that access returns false. vbYes is a constant with a value of 6. True is a constant with a value of -1. False is 0.

An easier and cleaner approach (in my oppinion) is:

If vbYes=MsgBox("Chart Number: [" & NewData & "] does not exist." & vbnewline & vbnewline & "Would you like to add it now?", vbYesNo,"Chart number not found") Then
  docmd.OpenForm "Patients_Add_New ",acNormal,,,acFormAdd,acDialog,NewData
  Response=acDataErrContinue
Else
  Response=acDataErrContinue
  Me![Jump_to]=Null
End If

About the acDataErrContinue see:

The NotInList event occurs when the user enters a value in the text box portion of a combo box that isn't in the combo box list.

Private Sub object_NotInList(NewData As String, Response As Integer)
Object The name of a combo box control.

NewData A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure.

Response The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants:

Constant Description

• acDataErrDisplay
  (Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list.
  
• acDataErrContinue
  Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue.
  
• acDataErrAdded
  Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.

You can pass the NewData (The new chart number) in the OpenArgs to your form, so that you dont have to retype the information. Examply would be as I have posted above:

docmd.OpenForm "Patients_Add_New ",acNormal,,,acFormAdd,acDialog,NewData

And in your Load Event of Patients_Add_New:

If OpenArgs & ""<>"" then
  Me.tb_ChartNumber=Me.OpenArgs
End If

The next part is when the form closes we want to go to the new record in your main form

'Record Chart Number
Dim lngChart as long
lngChart=Me.tb_ChartNumber
 
Call DoCmd.RunCommand(Command:=acCmdSaveRecord)
    Call DoCmd.Close
    Forms("Frm_YourFormName").Requery
    Forms("Frm_YourFormName").Recordset.FindFirst("ChartNumber=" & lngChart)
 

I think this covers must of what you requested, allthough it might not be my must structured reply to date.... Try implementing it and come back with any issues/questions should they arise.

And NeoPa, sorry for "hi-jacking".

[NeoPa]

Not a Hijack Smiley :-) That's when you change the subject of the thread.

Jumping-in maybe, but I couldn't complain about that after such a good answer (even if I had that right - which I don't). I'll still keep my eye on this thread but I think you've already broken the back of it. I just wanted to make sure Sue appreciated that the approach she suggested is very sensible and workable, if it does take a little bit of coding to implement within Access.

[TheSmileyCoder]

Yes. The main part of the work was allready done by sueb, and described very well in the question.

I've just made slight modifications/suggestions here and there.

[NeoPa]

I'd echo that :-) It seems Sue has progressed quite a bit since starting to post questions here last April (which is no disrespect to your starting position Sue - just that you seem to have progressed somewhat since).

[sueb]

@TheSmileyCoder: Thanks so much for such a detailed response! I might be prevented from trying these suggestions today, but I'll certainly be able to get to it by tomorrow, and will post back how it all went.

@NeoPa: I appreciate the good words! It's true that when I first started posting, I had really never worked with Access before, but I do have a couple of decades of software development in another field and with other tools under my belt, so it's really just a matter of "matching up" Access's ways/vocabulary/etc. with what I already know. The excellent advice on this forum (I no longer try any other) has really boosted this process. It's hard to express how much I owe to you all.

[sueb]

@TheSmileyCoder: I've finally got some time to try implementing your suggestions. I got it all put in, but when I ran it, I got a "Compile error: Method or data member not found" pointing to "tb_ChartNumber" in line 3:

Private Sub Form_Load()
    If OpenArgs & "" <> "" Then
        Me.tb_ChartNumber = Me.OpenArgs
    End If
End Sub
 

and again to the same object in line 5:

Private Sub Form_Close()
'    Requery_Open_Forms
    ' Record Chart Number
    Dim lngChart As Long
    lngChart = Me.tb_ChartNumber
 
    Call DoCmd.RunCommand(Command:=acCmdSaveRecord)
    Call DoCmd.Close
    Forms("Patient_IUR_Overview").Requery
    Forms("Patient_IUR_Overview").Recordset.FindFirst ("ChartNumber=" & lngChart)
 
End Sub
 

I did have a question about what this is. I don't think I have a field named that, so I wondered where it came from

[TheSmileyCoder]

I was presuming you had your chartnumber (whatever that is) in a textbox called tb_chartnumber.

[sueb]

@TheSmileyCoder, no, my field is still (because of my original approach) bound to a field in the database. Should I just change it to an unbound text box or something?

[NeoPa]

I think Smiley is just saying he guessed at your control name. The point here is that you need to tweak the code to match the actual names you are using for your fields and controls.

NB A field is found within a recordset, but a control is found on Form and Report objects. Your sentence is a perfect example of why the two terms shouldn't be mixed up as you use field for both in the same sentence, thus leaving the reader somewhat confused as to what you're actually intending to convey.