Add Record Pop-Up - Combo Box Requery

[Haymarket]

Hi - My first time here, and I am fairly new to Access, but getting on OK. So as ever, be kind.

I have spent several hours reading this forum and learnt a few things, and been completely confused by other things. My Job is IT but Software, Networks etc configuration rather than programming.

I am trying to help a charity out by building a database for their Sportsday.

I have a Parent Form Called Team, with Sub forms in Tabs for Team Details, Competitors etc.

In the Team Details, They can Choose the Hospital and Team Manager.

I have a Combo box with a lookup query, which looks up the Manager. However, rather than just display a last name, I have changed the SQL behind the (Properties - Data - Row Source), so that I get a concatenation of the Surname and First name:

SELECT tblCompetitor.CompetitorID, [surname] & " " & [Firstname] AS Fullname FROM tblCompetitor ORDER BY tblCompetitor.Surname;

All Works fine

I know I can use a "Not In the List" command, but initially they want a button to open an Add Manager Form.

No Problem except when I close and try to enter the New Managers Details in the Combo Box and requery.

I have a button to close the Add Manager Form and requery the Manager Combo in the originating Form

Private Sub CloseCompetitorsEditPopUp_Click()
On Error GoTo Err_CloseCompetitorsEditPopUp_Click

Forms![frmTeam]![ChildTransplantTeam].Form![Manager] = Me.CompetitorID
(the Manager field is a lookup from the Competitor table)
Forms![frmTeam]![ChildTransplantTeam].Form![Manager].Requery
DoCmd.Close

Exit_CloseCompetitorsEditPopUp_Click:
Exit Sub

Err_CloseCompetitorsEditPopUp_Click:
MsgBox Err.Description
Resume Exit_CloseCompetitorsEditPopUp_Click

End Sub

This code works fine if I just use the Managers Surname as the Data Field, but leaves a blank field in the Combo Box if I am displaying concatenation, until I do some sort of refresh.

Any Ideas if I can do this seamlesly as it is driving me potty.

 

[MarkK]

It doesn't appear that your code references a combo, but rather it references the whole "ChildTransplantTeam" suform on the "frmTeam" form.
My read on what you want to do is this: You have a pop-up form in which you enter a new manager. Once this is complete you want to show this new manager in a combo on a different form.
If this is so, then try something along these lines...

Private Sub CloseCompetitorsEditPopUp_Click()
[COLOR=Green]   'make sure current edits in the current form are saved to the table[/COLOR]
   Me.Refresh
[COLOR=Green]   'refer to a combo on the sub-form of a form[/COLOR]
   With Forms!frmTeam.ChildTransplantTeam.Form.[COLOR=DarkRed]cboSomeCombo[/COLOR]
[COLOR=Green]      'requery the combo so newly added record appears there
[/COLOR]      .Requery
[COLOR=Green]      'set the value of the combo[/COLOR]
      .Value = Me.CompetitorID
   End With
[COLOR=Green]   'close the form by name, so there is no confusion[/COLOR]
   DoCmd.Close acForm, Me.Name

End Sub

 

[Haymarket]

Thanks very much - tried the cose and it works fine.

Also may I thank you for the explanation that went with your example as that helped me understand what you were doing.

Phil