Cannot add a new item

[AC]

Hello,
I have a form that displays records from a table called tbl_item. I
have added some code, shown below, to the Form_Current procedure to
refresh certain fields on the form based on a value selected from a
combo box. It appears that this code below prevents me from adding a
new record. When I select the 'new record' button at the bottom of the
form (that standard access navigation button), it just recalculates and
then goes to the first record.

Could somebody please explain why this is happening and what I need to
change?

Here is the code:
Private Sub Form_Current()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_Number] = '" & Me![txt_ItemNo] & "'"
Me.Bookmark = rs.Bookmark
Me.cbo_packaging.Requery
Me.txt_FtyNo.Locked = True

Me.txt_FtyNo = ""
Me.txt_pack_category = ""
Me.txt_port = ""
Me.txt_FirstCost = ""
Me.txt_ItemItemL = ""
[...]
End Sub


Also, I tried to add a command button to add a new record, but that
generates another error message, saying 'You can't go to the specified
record.'

Any help/solutions are greatly appreciated.

Thanks in advance.
Regards,
A. Crawford

 

[Allen Browne]

You cannot use that code without tying yourself in knots.

The current event fires whenever the form moves to a different record. In
the current event, you are finding another record, and moving to that one
(by setting the form's bookmark property.) Since you moved record, Access
will fire the Current event of the form. So, your code fires again, which
moves record, which fires Form_Current, which ...

Assuming that the code actually does work, do you see the problem here?

It makes no sense to move record in Form_Current.

Similarly it makes no sense to change the value of any bound controls just
because you visited a record.

 

[missinglinq via AccessMonster.com]

Kinda like the legendary bird that flies around in ever tightening circle
until he finally flies up his own a$$!