not in list open form in add mode - code not working

[boydhiatt]

I have the following code in a not in list event and when it opens the form Add_New_Part, it opens the form to the first record and overwrites the first Part Number record. I want it to open to a blank record to add a new one, not change an existing one. Other than this problem, this code is doing exactly what I want it to do. Oh, and I am opening the form with the field cboPartNumber in add mode - it is actually a subform. Can somebody tell me what I need to do?

Private Sub cboPartNumber_NotInList(newdata As String, Response As Integer)
Response = acDataErrContinue
Call PartNumber_Not_Found(newdata)
End Sub



Public Sub PartNumber_Not_Found(newdata)
Dim ans As Variant
' new part
gbl_exit_name = False

ans = MsgBox("Do you want to add this part?", _
vbYesNo, "Add New part?")

If ans = vbNo Then
Me.cboPartNumber = Null
DoCmd.GoToControl "cboPartNumber"
GoTo exit_it
End If

' add part

DoCmd.OpenForm ("Add_New_Part")
Form_Add_New_Part.PartNumber = newdata

Me.cboPartNumber = Null

DoCmd.GoToControl "PartNumber"
exit_it:
End Sub



Thanks!!

Lara

 

[Terry_S]

At a guess I would say to open Add_New_Part form with DataEntry=True, other than that I always use a Recordset to write the new record and then requery the original Form/ComboBox.

 

[boydhiatt]

I am afraid I don't know what you mean by use a Recordset. I can add a new record on Add_New_Part when it opens but it automatically opens to the first record and overwrites PartNumber with the record that prompted the not in list event.

Thanks,

Lara

 

[Terry_S]

If you set the DataEntry property to True the form should open at a new record. Because you are using a bound form to add a new record it will normally open at the first record in the recordset.

Another way to add your new part is to open a recordset based on the table that holds your parts and use the .AddNew method to add the new part. Then it is a simple matter of requerying your calling form to ensure the new part is added into it's recordset so that it may be displayed. You do not need to have another form unless you have more than one piece of information to get from a user.

Assuming DAO is used and something similar to the following is added into the calling forms general module as a sub:

Dim db as DAO.Database, rs as DAO.Recordset
Set db = CurrentDB
Set rs=db.OpenRecordset("MyPartsTable",dbOpenDynaset)
rs.AddNew
rs!PartNumber=newdata
rs.Update
rs.Close
Set rs=Nothing
Set db=Nothing
Me.Requery

There is no error checking performed in the above as well as no Workspace methods such as CommitTrans and RollbackTrans. You will also be able to use the .Findfirst and .Bookmark methods of the forms RecordsetClone to position the form at the newly entered part.

 

[boydhiatt]

Thanks! I thought I had the DataEntry property set to true but when I checked again, I hadn't. That fixed it. Sometimes I just need someone to point out the obvious to me!