Form Help: Trying to add new record...(detailed)

[AccessApprentice]

I finally thought I'd set up a form that would allow me to 1) enter new records, and 2) jump to an existing record if I entered an existing Item Number (primary key). I tried it first using a simple test form with an ItemNumber field that is Auto-Numbered...I inserted a combo box that would jump to an existing record using the wizard, and it generated what I call the infamous Me.Bookmark code (Set rs = Me.Recordset.Clone, etc, etc.).

It actually worked (even though it hadn't for me before). However, I ran into a little problem as I was tabbing through the fields and cycling through the records. When I tabbed out of the last field in the record back to the first (resulting in the next record being displayed), the number in the ItemNumber field didn't change...the first record's number stayed in it (even though the contents of all the other fields changed to accurately match the current record). To fix that, I inserted a new, invisible combo box that was actually bound to the ItemNumber field, experimented a bit, and came up with the following code:

Private Sub Combo22_GotFocus()
[Combo22] = [ItemNumberMain]
End Sub

This way, whenever I get to a different record and tab to the visible, unbound ItemNumber combobox, it would update itself properly using the bound field.

Now my problem comes with adding a new record. It was no problem with the test database, where the ItemNumber was set up as an AutoNumber field. When I got to a new record, the ItemNumber field stayed blank and waited until I entered something in the second field on the form, then it would add an ItemNumber and allow me to tab out of (and thus save) that record and go to a new blank one.

With the actual database I want (not the test one), the ItemNumber field is NOT an AutoNumber field...I want to be able to enter the ItemNumbers myself. However, when I reach the end of the database and I'm at a blank record, instead of allowing me to enter a new item number and tab to the other records and enter new data, the ItemNumber field hops right back to the first record in the database when I try to tab out of it. I can't add a new record.

I may have to just use two forms (one just for adding new records, another just for editing), but that's so cumbersome and I want to be able to use one form for both. If anyone has any ideas...HELLLLLLLLLLP! I'm drowning in Access! (And they say this thing is easy to use...)

THANKS!

 

[BlackKnight]

Hi,

Generally, there is rareey a need for the user to see an autonumbered field. I'd suggest the following: I assume your table that the form is bound is similar to this:

tblItem
-------
ItemID autonumber
ItemNumber text
...other fields

Your form has its controls bound to each field EXCEPT the ItemNumber combobox. This unbound combobox will be used to find an existing record that has the selected item number.

Name = cboItemNumber
rowsource = SELECT ItemID, ItemNumber FROM tblItem ORDER BY Itemumber ASC

Column Count = 2
Column Widths = 0;1

Limit To List = Yes

In its ON Click event you would set the form's Filter property like:

Me.Filter = "ItemNumber='" & cboItemNumber.ItemData & _(cboItemNumber.ListIndex) & "'"
Me.FilterOn = true


Create a command button called cmdNew so the user can click click on it to add a new record. In its ON Click event you will have some code like:


dim Resp as integer

if Me.dirty then
Resp = msgbox("Save changes to this record?&quot
if Resp = vbyes then
DoCmd.RunCommand acCmdSaveRecord
else
Me.undo
end if
docmd.GoToRecord acDataForm, Me.Name, acNewRec
end if

Have a good one!
BK

 

[froag]

Or simply leave an AutoNumber field in the table and add a "Record Number" or "Customer Number" field to the table.
Let Access use the AutoNumber for creating new records, and use your new field to add YOUR data.
You can have the AutoNumber field on a form and "Visible" set to "No".

Tony