Oddies with combo box as 1st field on form

  • Thread starter Thread starter Joan Edington
  • Start date Start date
J

Joan Edington

Hi,

I feel I'm getting into this Access VBA thing, thanks to help from
various folk on this NG, but have once again struck a problem I can't
get round.

I have a form to hold helpdesk call details from members of staff. The
first field is the name of the person calling which until the other
day was typed free-hand. All was normal with the Access world.

Since there are not too many members of staff and I also had a table
of details such as telephone numbers, location etc for everyone, I
changed the Name field on my call form to a combo box looking up my
staff details table.

My problem arises when I delete the last record. I've always known
Access to happily delete the record and leave an empty record, with no
allocated autofill number, ready for the next entry. That is what
happened until I made the first field a combo box anyway.

Since then, if I delete the last record, a new record is opened, the
autofill number allocated and the cursor left on the combo box.

Even odder is the fact that ,if I delete the last record in datasheet
view by selecting only that line, the same thing happens but, if I
select the record and the blank unallocated autofill line, the delete
works OK as expected.

As it stands, if I then create a new record, the oddly created one
stays in the database, blank. If I use that one for the new entry next
time round it is no longer a new record so doesn't carry out some of
the processing required.

Is there some way to get round this automatic creation of a new record
when I delete the last one?

Puzzled,

Joan.
 
If I use that one for the new entry next
time round it is no longer a new record so doesn't carry out some of
the processing required.


The above means that you likely changed some code that in fact does dirty
the record? What event are you using on the form for this processing? If you
have any code that sets a field value, then the record is dirty, and the
autonumber will occur. I have almost always given my users a add record
button anyway. Opening a form that is just bound to a table without a
"where" clause is a terrible design decision anyway.
 
time round it is no longer a new record so doesn't carry out some of
the processing required.


The above means that you likely changed some code that in fact does dirty
the record? What event are you using on the form for this processing? If you
have any code that sets a field value, then the record is dirty, and the
autonumber will occur.
Click to expand...

In the Form_OnCurrent I check if it is a new record and set a flag.

In the StaffName_AfterUpdate, if it is a new record, I use DLookups to
fill in some further fields on the form. I don't want this to happen
if it's an existing record. StaffName is the combo box that is the
first field on the form apart from the autonumber which doesn't get
focus.

I do not use the OnOpen or OnLoad events so neither of them could be
setting fields.

If I put a dummy text field before StaffName, and set focus on it, I
have no problem, so it must be the setting focus on a combo box that
is doing it, although the only event for that field is the AfterUpdate
which should not have been actioned.
I have almost always given my users a add record
button anyway. Opening a form that is just bound to a table without a
"where" clause is a terrible design decision anyway.
Click to expand...

I'll accept that since I know very little VB. So little in fact that I
don't even know what you mean :-)

Thanks anyway.

Joan
 
Back
Top