Adding Record not working consistently

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda RQ - 14 Apr 2007 03:46 GMT

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

Reply to this Message

Allen Browne - 14 Apr 2007 05:00 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Everyone,
>
[quoted text clipped - 42 lines]
>
> I have no events on the frmPhysicians

Reply to this Message

Linda RQ - 15 Apr 2007 02:32 GMT

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.
[quoted text clipped - 50 lines]
>>
>> I have no events on the frmPhysicians

Reply to this Message

John W. Vinson - 14 Apr 2007 05:08 GMT

>Events
>
[quoted text clipped - 5 lines]
>
>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]

Reply to this Message

Linda RQ - 15 Apr 2007 02:28 GMT

>>Events
>>
[quoted text clipped - 11 lines]
>
> 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

Reply to this Message

Linda RQ - 15 Apr 2007 03:34 GMT

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

> Hi Everyone,
> Access 2003. ComboBox with physician names. To add a physician, double
[quoted text clipped - 41 lines]
>
> I have no events on the frmPhysicians

Reply to this Message