Adding Record not working consistently

[Linda RQ]

Hi Everyone,

Access 2003. ComboBox with physician names. To add a physician, double
clicking opens up the physician form.

Problem

If I enter a name that is not on the list to my combo box, I get a prompt to
double click to add the physician. If I do that and enter the physician as
a new record then click my Ok button, I get this message "You must save the
current record before you run the requery action." I click ok... then when
back on my main form, the new physician name doesn't show up in the list to
select unless I exit the database and reopen.

If I simply double click on the physician combo box and add a new record and
click ok, the physician name is in the list to select.... works perfectly

I don't understand why having typed a name not on the list would cause this
problem...What part isn't working?

The combobox is on a subform. I have it limited to list.

Properties.
Control Source-PhysID_fk
Row Source-Table/Query
Row Source-qryPhysicians
Column Count-5
Column Widths-0";0";0";0.75";0"
Bound Column-5

sql from my query
SELECT tblPhysicians.PhysLName, tblPhysicians.PhysFName,
tblPhysicians.PhysMI, [PhysLName] & ", " & [PhysFName] & " " & [PhysMI] AS
[Phys L_FName], tblPhysicians.PhysID FROM tblPhysicians ORDER BY
tblPhysicians.PhysLName;

Events

On Not in List-I am using the macro MsbBox
To add an item...Double click in field, add the name.

On Double Click- OpenForm
frmPhysicians

I have no events on the frmPhysicians

 

[Allen Browne]

You cannot use the NotInList event to add the names to your table, as the
NewData does not go in the bound column.

Instead, provide a button alongside the combo to OpenForm so the user can
enter a new physician. Use the AfterUpdate event procedure of that form to
requery the combo.

 

[John W. Vinson]

Events

On Not in List-I am using the macro MsbBox
To add an item...Double click in field, add the name.

On Double Click- OpenForm
frmPhysicians

I have no events on the frmPhysicians

You need one.

In the Form's AfterUpdate event, requery the first form's combo box.

John W. Vinson [MVP]

 

[Linda RQ]

You need one.

In the Form's AfterUpdate event, requery the first form's combo box.

John W. Vinson [MVP]

I went to add a requery event and saw this code in the module for the ok
button I made on the form...shouldn't it save the record?

Apparently my combo box is called PhysID_fk which is weird, I usually name
my combo boxes with a cbo prefix but I did make the combo box with a wizard.

So, would I type this in the code builder?

Forms!PhysID_fk.Requery

-----------------------------------------------------------------------

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

If Me.Dirty Then
DoCmd.RunCommand (acCmdSaveRecord)
Forms!frmMainDataEntry.sfmPtThpy.Form.PhysID_fk.Requery
End If
DoCmd.Close

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click

End Sub

 

[Linda RQ]

Hi Allen,

the NotInList event isn't to add names, it's to let the user know to double
click to add the name. I am trying to get the requery for the combo but I
think something is wrong on my form. I'll keep staring at it and perhaps it
will come to me.

Linda

You cannot use the NotInList event to add the names to your table, as the
NewData does not go in the bound column.

Instead, provide a button alongside the combo to OpenForm so the user can
enter a new physician. Use the AfterUpdate event procedure of that form to
requery the combo.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Everyone,

Access 2003. ComboBox with physician names. To add a physician, double
clicking opens up the physician form.

Problem

If I enter a name that is not on the list to my combo box, I get a prompt
to double click to add the physician. If I do that and enter the
physician as a new record then click my Ok button, I get this message
"You must save the current record before you run the requery action." I
click ok... then when back on my main form, the new physician name
doesn't show up in the list to select unless I exit the database and
reopen.

If I simply double click on the physician combo box and add a new record
and click ok, the physician name is in the list to select.... works
perfectly

I don't understand why having typed a name not on the list would cause
this problem...What part isn't working?

The combobox is on a subform. I have it limited to list.

Properties.
Control Source-PhysID_fk
Row Source-Table/Query
Row Source-qryPhysicians
Column Count-5
Column Widths-0";0";0";0.75";0"
Bound Column-5

sql from my query
SELECT tblPhysicians.PhysLName, tblPhysicians.PhysFName,
tblPhysicians.PhysMI, [PhysLName] & ", " & [PhysFName] & " " & [PhysMI]
AS [Phys L_FName], tblPhysicians.PhysID FROM tblPhysicians ORDER BY
tblPhysicians.PhysLName;

Events

On Not in List-I am using the macro MsbBox
To add an item...Double click in field, add the name.

On Double Click- OpenForm
frmPhysicians

I have no events on the frmPhysicians

 

[Linda RQ]

Ok


My combo box's recordsource is qryPhysicians. The combobox name is
PhysID_fk

sql
SELECT tblPhysicians.PhysLName, tblPhysicians.PhysFName,
tblPhysicians.PhysMI, [PhysLName] & ", " & [PhysFName] & " " & [PhysMI] AS
[Phys L_FName], tblPhysicians.PhysID
FROM tblPhysicians
ORDER BY tblPhysicians.PhysLName;

When I double click on that combobox, it opens up my frmPhysicians which has
a recordsource of qryfrmPhysicians

sql
SELECT tblPhysicians.PhysID, tblPhysicians.PhysServID_fk,
tblPhysicians.PhysLName, tblPhysicians.PhysFName, tblPhysicians.PhysMI
FROM tblPhysicians
ORDER BY tblPhysicians.PhysLName, tblPhysicians.PhysFName,
tblPhysicians.PhysMI;

So now I am thinking I need to do a requery on the frmPhysicians but I don't
think it's this simple?


Private Sub PhysID_fk_AfterUpdate()

Forms!PhysID_fk.Requery

End Sub