Table update

[LaughingBull]

I have a form to enter attendance to a class, with a comboBox to select Location.


I have a command button to open the Location form to add a new location.
I want the Location table to update after I close the location form so the new location is on the comboBox location list.
I put the requery code on the close event of the Location form but it only worked once, and yes the location form can be opened independently.


????????????????????????

[June7]

Adding records to 'lookup' table on-the-fly during data entry is customarily done with the combobox NotInList event. http://www.blueclaw-db.com/access_no...ed_example.htm

[LaughingBull]

I have put the code in and it all works except to requery the combo box when close form. also the If ans no stops at GoTo exit.

Public Sub Location_Not_Found(NewData)
Dim ans As Variant
' new Location
gbl_exit_name = False

ans = MsgBox("Do you want to add this Location?", _
vbYesNo, "Add New Location?")
'If ans = vbNo Then
'Me.Location = Null
'DoCmd.GoToControl "Location"
'GoTo exit_frmLocation!Location
'End If
If ans = vbYes Then
DoCmd.OpenForm "frmLocation", acNormal, "", "", , acNormal
DoCmd.GoToRecord , , acNewRec
End If


End Sub
Public Sub Form_Close()
Forms!frmLocation.Location.Requery
End Sub

[June7]

You aren't using the NotInList event.

The BlueClaw example shows the NotInList event calling another Sub but can all be under the NotInList event.

Public Sub Location_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
    Me.Location = Null
    If MsgBox("Do you want to add this Location?", vbYesNo, "Add New Location?") = vbYes Then
        DoCmd.OpenForm "frmLocation", , , , acFormAdd, acDialog, NewData
        Me.Location.Requery
        Me.Location = NewData
    Else
        DoCmd.GoToControl "Location"
    End If
End Sub

Then code behind frmLocation Current event:

If Me.NewRecord Then Me.Location = Me.OpenArgs

[LaughingBull]

almost there after I close the locations form I get this message and debug highlites the Me.Locations = New Data and it does add it to the list even with this error code

[LaughingBull]

when I put Numbers in there it is OK no error. this list combo box was created as a field on the Attendance form as a lookup so when it is all finished it shows it as a number field. can that be changed ?

[June7]

Combobox with lookup alias does complicate. One option is to not populate the combobox with the new data. Let user select the new item. Otherwise, have to capture the new location ID and pass that back to the calling form and populate combobox with it. Gets tricky. One way is to declare a global variable in a general module so both forms can address it and set it with the new location ID in the location form code. Then:

Me.Location = gblLocID

One problem with global variable is that when code execution is interrupted, the variable loses its value. Can make debugging frustrating at times. On the other hand, TempVars do not have that issue.

[LaughingBull]

Here is a code that I found that works well I wanted to share with you.
Private Sub Category_DblClick(Cancel As Integer)

'Open code table
DoCmd.OpenForm "CategoryCodes", acNormal, , , , acDialog
Form_Suppliers!Category.Requery

End Sub

[June7]

Yes, but that doesn't pass the NewData to the form nor does it populate the combobox with the NewData.

Is this thread resolved?

[LaughingBull]

Yes it does pass the new data to the form and populate the combobox

Yes it is solved