You must save the current field before you run the Requery action.

[jjake]

hi,

Im having an issue with a not in list event for a combo box.

i have frm1 with subfrm1

subfrm1 has a combo box with a list of names. if the name is not in the table then trigger not in list event and open form 2.

User enters name etc in form 2 then hits submit. i'm trying to requery the combo box to show the name.

Private Sub cmdSubmitName_Click()

If Me.Dirty = True Then
Me.Dirty = False

Forms!frmDailyLog!frmVisitorLog.Form!VisitorName.Requery

DoCmd.Close acForm, "frmVisitorInfo"

end if
End Sub

I get the following error,

Run-time error'2118':
You must save the current field before you run the Requery action.

 

[theDBguy]

Hi. Just to be sure, can you please post the Not In List code? Thanks.

 

[Deleted member 147267]

You will have a record in edit mode in the subform, but you're trying to requery the sub form control on that record. Not sure why you'd take this approach because the Not In List event allows you to just add the value according to the prompt. The new value should become available right away (next drop down should show it) allowing the user to select it. Don't see a need for an additional form.

 

[jjake]

Hi. Just to be sure, can you please post the Not In List code? Thanks.

Private Sub VisitorName_NotInList(NewData As String, Response As Integer)

    Response = MsgBox("This visitor has never signed in before, Would you like to create a new profile?", vbYesNo + vbQuestion, "Client Prompt")
If Response = vbYes Then
    DoCmd.OpenForm "frmvisitorinfo", , , , acAdd, acDialog
    End If
  
    
If Response = vbNo Then

Me.VisitorName.Value = ""

End If

End Sub

 

[jjake]

You will have a record in edit mode in the subform, but you're trying to requery the sub form control on that record. Not sure why you'd take this approach because the Not In List event allows you to just add the value according to the prompt. The new value should become available right away (next drop down should show it) allowing the user to select it. Don't see a need for an additional form.

i'm using multiple fields on the persons profile which aren't always necessary.

My combo box is 4 fields, VisitorID, VisitorName, VisitorOrganization, VisitorTrainingDate.

if the person is a visitor then the name is the only thing required. there is different criteria if it is a contractor.

if its a new contractor without a profile, then its assumed they will need training, a profile will need to be generated by opening another form so multiple data can be input then closed again then the combo box needs to be re queried to show the new contractor. then if the training (column).(3) is still null after adding that contractor another prompt follows.

 

[Deleted member 147267]

If I'm understanding the situation, then you probably need to requery the combo rather than the whole form. Not sure by your reply that you understood what I'm saying, which is that (I think) you're in the middle of a record edit when interacting with the combo, and trying to requery the form from the other form is what's causing the message. So just requery the combo from the second form.

 

[jjake]

Ok got it,

I copied the combo box to the 2nd form and put this on my submit button for the form.

Private Sub Command25_Click()

Me.cbovisitorname.Requery
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "frmVisitorInfo"

End Sub

So now when the form closes and i'm back to my first form, if the original text that was input into the first form does not match what was typed in the 2nd form then it asks to add it again. is it possible to pass the new value back to the combo on the first form?

 

[Deleted member 147267]

You put a combo on the second form and requery it there? That's not what I suggested, and it's not going to "refresh" the list in the combo on the first form. That's like saying "I polished my left shoe but my right shoe is still dirty". There's no connection between the 2 in either case.

In post 3 I said I don't see why you need a 2nd form, and rather than address that you add more controls to it instead? Sorry but I just think you're over complicating the whole thing. The not in list event will allow you to add the value without a 2nd form so why have it?

 

[mike60smart]

Hi

When Form 2 Closes why not just use the ON Enter event of the Combobox

Me.NameofComboBox.Requery

 

[jjake]

Your right. i misunderstood what you said. i went through i started over using the following concept

frm1 w/ subfrm1

Private Sub VisitorName_NotInList(NewData As String, Response As Integer)

    Dim oRS As DAO.Recordset, i As Integer, sMsg As String
    Dim oRSClone As DAO.Recordset

    Response = acDataErrContinue

    If MsgBox("Add new visitor profile?", vbYesNo) = vbYes Then
        Set oRS = CurrentDb.OpenRecordset("tblVisitorInfo", dbOpenDynaset)
        oRS.AddNew
        oRS.Fields(1) = NewData
        For i = 2 To oRS.Fields.Count - 1
            sMsg = "Please enter data for " & oRS(i).Name
            oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
        Next i
        oRS.Update
        VisitorName = Null
        VisitorName.Requery
     End If

End Sub

This works fine to add data to the table and refresh the combo without an additional form.

 

[Deleted member 147267]

Hi
When Form 2 Closes why not just use the ON Enter event of the Combobox
Me.NameofComboBox.Requery

I think user is already in the control since a new entry in it is what causes the not in list event to fire in the first place.

Am I missing an important point here in this whole thread? I'm saying

Scenario1 - user enters a value in control that's not in list. NIL event fires and user clicks Yes to add the value. The NIL code requeries the control (not the form) on the first form if Yes. Value is now in the list. NIL code could set the value of the combo to the new value if you want to make this automatic for the user. Done.

Scenario2 - NIL event fires. Open another form to add the new value to a copy of the control, requery the second form combo (which does nothing for the first one's list) then you still have to requery the first form's combo and go back to first form only to find that value is not only not selected but may not even be in the list yet (if the control isn't requeried)? One form, one procedure OR an additional unnecessary form and code on it that isn't required. I would pick the first one - unless maybe I'm missing the whole point.

EDIT - seems things can change a lot in 5 minutes. I will have to review what you've got. Was going to say don't see the need for a recordset at first glance. However, you want to prompt the user several times and present an input box several times to add a value to each of several fields?? What if they fail to enter a value and submit the empty input box? What if the value is bogus or of the wrong data type?

 

[Deleted member 147267]

What led me to think you had one value to enter

if the name is not in the table...
requery the combo box to show the name...
the name is the only thing required...

What I missed or forgot

My combo box is 4 fields

I guess you do need the recordset. If you wanted to keep the 2nd form and do validation on it, then in it's close or NIL event you have to requery the 1st form combo (again, not the form).

 

[jjake]

What led me to think you had one value to enterWhat I missed or forgot I guess you do need the recordset. If you wanted to keep the 2nd form and do validation on it, then in it's close or NIL event you have to requery the 1st form combo (again, not the form).

I ended up putting the additional fields in an unbound combo box tied to another query which update as i type in the name. it turned out better this way anyway.