requery multi-value fields (1 Viewer)

[brastedhouse]

good morning, i am trying to requery a combox based on a multi-value field for a not-in-list situation. i usually add to the list as:

    Dim intAnswer As Integer
    Dim strSQL As String
    
    intAnswer = MsgBox("The location " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "CFD Operations")
    
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO tblCallsLocationsLU([callLocation],[callDrill]) VALUES ('" & NewData & "', 1);"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        MsgBox "The new location has been added to the list.", vbInformation, "CFD Operations"
        Response = acDataErrAdded
        DoCmd.SetWarnings True
    Else
        MsgBox "Please choose a location from the list.", vbInformation, "CFD Operations"
        Response = acDataErrContinue
    End If

i cannot use this in a combo box based on a multi-value field because it will not autofill. you have to manually check each choice. so I use a form to add the record to the table. the update works, but the requery will not. i can close the form and reopen it and get the new record, but no permuation of requery will update the list in the combo box.

i have tried Forms!Formname!Comboboxname.requery in several locations in the code to no avail.

does anyone have any experience in multivaue fields and requery? can requery work on a combo box with a multi-value field?

Thanks, Scott

 

[arnelgp]

multivalue fields are recordset (actually recordset2).
you need a code to edit the table, add new record to the multivalue field (as recordset2).
after that is done, then you requery.

 

[brastedhouse]

first, thanks for the quick reply. second, while i understand what you are referrring to, i do not know how to do that. can you give a quick example?

thanks, Scott

 

[arnelgp]

the form is tblTransaction.
when you go to ProductID control, you can press F2 to add new Product.
after adding the product, close this data entry form.

 

[brastedhouse]

Hi, sorry this took so long to get back to, but real life intruded.

So, I have used your code example to requery the data. The new record is added, but i get an error when i close the form. The error happens here:

Set rsParent = Forms!frmCallsOnly.Form.RecordsetClone
With rsParent
HERE . . . .FindFirst "id = " & Forms!frmCallsOnly.Form!callID

I have tried the primary key to the parent form and the primary key to the child form. the one above is the parent form primary key. Obviously I am doing something wrong. Any thoughts?

Thanks, Scott

 

[arnelgp]

do you have callID in your recordset, then use it:

.FindFirst "callID = " & Forms!frmCallsOnly.Form!callID

 

[brastedhouse]

Hi again, I think I'm surrounding it.

It now stops at:

If Not .NoMatch Then
'get the last id from tblCallsOtherTypeLU
id = DMax("otherCallTypeID", "tblCallsOtherTypeLU")
'set child recordset
HERE . . . Set rsChild = .Fields("otherCallTypeID").Value

So, does the id DMAx "id" need to be set to a real field? Do you see why I am stoping here? I sort of uderstand how this should work. But my coding skill are not great. Thanks.

 

[arnelgp]

you should substitute the multivalue fieldname in your parent recordset:
id is just a variable there, that will be saved in the child recordset

Set rsChild = .Fields("multiValueFieldName").Value

substitute the blue-colored with correct field name.

 

[brastedhouse]

First, thank you for your patience. The good news is the After Update code is now working. Yeah! The bad news is that the Form Unload code is not. Here are the two ways I have tried to do it:

Forms!frmCallsOnly.Form!otherCallTypeID.Requery
otherCallTypeID is primary key for the table of the combobox to requery
Forms!frmsCallsOnly.cboOtherType.Requery
Forms!frmsCallsOnly!cboOtherType.Requery
cboOtherType is the nameof the combobox to requery

Any thoughts on this one? Many Thanks.

 

[brastedhouse]

Hurray! Light finally dawned on old marblehead. Thank you very much for all your help. Best, Scott