deleting last record 2

[kkson]

On my form I can delete a record with a button. If I delete the last record of the table (I know this because I just added it to the table) the fields all go blank and when I try to go to another record I get the index or primary key can't be blank.

Thanks

 

[PRMiller]

In attempting to move to another record, Access is probably assuming that you're trying to save the existing record. With a blank primary key field, it will error out. When you delete the last record, Access should default to a blank first record. Is there any reason that one can't be used?

 

[kkson]

It should go to the first record but it don't. It wants to go into add mode for some reason.

 

[kkson]

It should go to the first record but it don't. It wants to go into add mode for some reason/

 

[MaxEd]

kkson,

Check that your form properties "Data Entry" is set to "No".

-Laughter works miracles.

 

[Edski]

It should go to the first record but it don't. It wants to go into add mode for some reason

Hang on, how have you made the delete button work? Can you post the code? Hopefully you're not just blanking out all the fields (or are you???)

 

[kkson]

here is the delete code i am using. I added the close command and openform command to get around this issue.

If MsgBox("You will be deleting Part #: " & Me![Part#] & " If you want to continue with the deletion select OK.", vbOKCancel, "CONFIRM PART# DELETION") = vbOK Then
Me.AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.AllowDeletions = False
End If
DoCmd.Close
DoCmd.OpenForm "frmAddProducts

 

[Edski]

I can't see anything wrong with THAT code, [red]except[/red] that clicking Cancel will still close the form and open it again (asusming the form with the command button is called "frmAddProducts"). You may want an Else staement in there somewhere.

You'll need to check the form's On Open or On Load events for code that is causing the current record to be a [blue]New Record[/blue] and placing data in another field - causing the record to be "Dirty". The PK will be blank, and moving to another record will fail because a PK cannot be Null.

Understand?

 

[TheAceMan1]

How are ya kkson . . . . . .

[purple]Your trying to close then open the form while your code in the form module is still running . . . .[/purple] [purple]Bad Idea![/purple] I hav'nt tested your code, but I have reasons to believe the [purple]deletion is not complete before you reach DoCmd.Close[/purple]. So . . . [blue]the sequence of the deletion process is out of sync with your code . . .[/blue]

To fix this, remove the following lines from your code:

[blue]   DoCmd.Close
   DoCmd.OpenForm "frmAddProducts"[/blue]

Then in the [purple]AfterDelConfirm[/purple] event of the form, copy/paste the following code:

[blue]   If Status = acDeleteOK Then Me.Requery[/blue]

T[blue]hats it . . . . give it a whirl & let me know . . . .[/blue]

See Ya! . . . . . .

 

[kkson]

Thanks,

the code: DoCmd.Close
DoCmd.OpenForm "frmAddProducts"

was a fix so that I didn't get the error.

TheAceMan1, tried your code and I now get a 'no current record error. It still seems like it don't know how to get to another record.

MaxEd the data entries is set to no.

EdSki there is on on open or onload with this form.

Here is the complete code for the form, maybe that will help. THANKS EVERYONE FOR THE HELP.
---------------------------------------------------------
Option Compare Database 'Use database order for string comparisons

Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click

DoCmd.GoToRecord , , A_NEWREC
Me![cbxProduct].Locked = False
Me!optionColorFam.Locked = False
Me![cbxProduct].TabStop = True
Me!optionColorFam.TabStop = True
Me![Part#] = " "
Me!tmpProdNo = ""
Me!tmpColorNo = ""
Me!ColorLow = ""
Me!ColorHigh = ""
Me!tmpNextColorNo = ""
Me!tmpPrefix = ""
Me!delno = ""
Me!delnow = ""
Me!cbxProduct.SetFocus

Exit_btnAddRecord_Click:
Exit Sub

Err_btnAddRecord_Click:
MsgBox Error$
Resume Exit_btnAddRecord_Click

End Sub

Private Sub btnSave_Click()
On Error GoTo Err_btnSave_Click

DoCmd.RunCommand acCmdSaveRecord

Exit_btnSave_Click:
Exit Sub

Err_btnSave_Click:
MsgBox Error$
Resume Exit_btnSave_Click

End Sub

Private Sub cbxProduct_Exit(Cancel As Integer)
On Error GoTo Err_cbxProduct_Exit

'Check to see if in "Add" mode
If Me![cbxProduct].Locked = False Then

'Make sure components to create a valid part # have been selected.
If Me!tmpProdNo = "" Then
MsgBox "You have not selected a Product. Please select one.", vbInformation, "ERROR, NO PRODUCT SELECTED"
DoCmd.CancelEvent
Me!cbxProduct.SetFocus
End If

End If

Exit_cbxProduct_Exit:
Exit Sub

Err_cbxProduct_Exit:
MsgBox Error$
Resume Exit_cbxProduct_Exit

End Sub

Private Sub cbxProductList_AfterUpdate()
On Error GoTo Err_cbxProductList_AfterUpdate

Me![Part#].SetFocus
DoCmd.FindRecord Me![cbxProductList]
Me![Color].SetFocus

Exit_cbxProductList_AfterUpdate:
Exit Sub

Err_cbxProductList_AfterUpdate:
MsgBox Error$
Resume Exit_cbxProductList_AfterUpdate

End Sub

Private Sub cbxProductList_Enter()
On Error GoTo Err_cbxProductList_Enter

DoCmd.RunCommand acCmdSaveRecord
Me!cbxProductList.Requery
Me!cbxProductList.Dropdown

Exit_cbxProductList_Enter:
Exit Sub

Err_cbxProductList_Enter:
MsgBox Error$
Resume Exit_cbxProductList_Enter

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Me.Requery
End Sub

Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert

If Me!ProductDivision <> "Miscellanous" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryupdatetblnextcolorno"
DoCmd.SetWarnings True
End If
Me![cbxProduct].Locked = True
Me!optionColorFam.Locked = True
Me![cbxProduct].TabStop = False
Me!optionColorFam.TabStop = False
Me!optionColorFam = ""
Me!tmpProdNo = ""
Me!tmpColorNo = ""
Me!ColorLow = ""
Me!ColorHigh = ""
Me!tmpNextColorNo = ""
Me!tmpPrefix = ""
Me!delno = ""
Me!delnow = ""

Exit_Form_AfterInsert:
Exit Sub

Err_Form_AfterInsert:
MsgBox Error$
Resume Exit_Form_AfterInsert

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

If Len(Me![Description]) > "25" Then
MsgBox "Your Product Description is to long! You will have to abbreviate color name and/or abbreviate product name. (If product name needs to be shortened you will need to remove and reAdd the product.)", vbCritical, "ERROR, DESCRIPTION TO LONG"
Exit Sub
Me![Color].SetFocus
End If
Me!Description = Me!cbxProduct & " " & Me!Color & " " & Me![Color#]

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Error$
Resume Exit_Form_BeforeUpdate

End Sub

Private Sub optionColorFam_AfterUpdate()
On Error GoTo Err_optionColorFam_AfterUpdate

If optionColorFam = 1 Then
Me!ColorLow = "000"
Me!ColorHigh = "099"
ElseIf optionColorFam = 2 Then
Me!ColorLow = "100"
Me!ColorHigh = "199"
ElseIf optionColorFam = 3 Then
Me!ColorLow = "200"
Me!ColorHigh = "299"
ElseIf optionColorFam = 4 Then
Me!ColorLow = "300"
Me!ColorHigh = "399"
ElseIf optionColorFam = 5 Then
Me!ColorLow = "400"
Me!ColorHigh = "499"
ElseIf optionColorFam = 6 Then
Me!ColorLow = "500"
Me!ColorHigh = "599"
ElseIf optionColorFam = 7 Then
Me!ColorLow = "600"
Me!ColorHigh = "699"
ElseIf optionColorFam = 8 Then
Me!ColorLow = "700"
Me!ColorHigh = "799"
ElseIf optionColorFam = 9 Then
Me!ColorLow = "800"
Me!ColorHigh = "899"
ElseIf optionColorFam = 10 Then
Me!ColorLow = "900"
Me!ColorHigh = "989"
ElseIf optionColorFam = 11 Then
Me!ColorLow = "990"
Me!ColorHigh = "999"
End If

Me!tmpColorNo = DMin("Number", "qryAvailColorFam")

Exit_optionColorFam_AfterUpdate:
Exit Sub

Err_optionColorFam_AfterUpdate:
MsgBox Error$
Resume Exit_optionColorFam_AfterUpdate

End Sub

Private Sub cbxProduct_AfterUpdate()
On Error GoTo Err_cbxProduct_AfterUpdate

Me!tmpProdNo = Me!cbxProduct.Column(1)
Me!Description = Me!cbxProduct.Column(0)
Me!ProductDivision = Me!cbxProduct.Column(2)
Me!ProductLine = Me!cbxProduct.Column(3)
Me!optionColorFam.SetFocus

Exit_cbxProduct_AfterUpdate:
Exit Sub

Err_cbxProduct_AfterUpdate:
MsgBox Error$
Resume Exit_cbxProduct_AfterUpdate

End Sub

Private Sub Color_Exit(Cancel As Integer)
On Error GoTo Color_Exit_Err

Me!Description = Me!cbxProduct & " " & Me!Color & " " & Me![Color#]

Color_Exit_Exit:
Exit Sub

Color_Exit_Err:
MsgBox Error$
Resume Color_Exit_Exit

End Sub

Private Sub optionColorFam_Exit(Cancel As Integer)
On Error GoTo Err_optionColorFam_Exit

Dim stno As String
Dim stprefix As String

'Check to see if in "Add" mode
If Me![cbxProduct].Locked = False Then

'Make sure components to create a valid part # have been selected.
If Me!tmpProdNo = "" Then
MsgBox "You have not selected a Product. Please select one.", vbInformation, "ERROR, NO PRODUCT SELECTED"
DoCmd.CancelEvent
Me!cbxProduct.SetFocus
ElseIf Me!tmpColorNo = "" Then
MsgBox "You have not selected a Color Family. Please select one.", vbInformation, "ERROR, NO COLOR FAMILY SELECTED"
DoCmd.CancelEvent
Me!optionColorFam.SetFocus
Else
Me![Part#] = Trim(Me![tmpProdNo]) & Trim([tmpColorNo])
Me![AGENTPART#] = Me![Part#]

'Find the next color #
stno = DLookup("NextColorNo", "tblNextColorNo", "[ProductDivision]= forms!frmAddProducts!ProductDivision")
stprefix = DLookup("prefix", "tblNextColorNo", "[ProductDivision]= forms!frmAddProducts!ProductDivision")

If Me!ProductDivision = "Miscellanous" Then
Me![Color#] = " "

ElseIf Me!ProductDivision = "split" Then
Me![Color#] = stno
Me!tmpNextColorNo = stno + 1
Else
Me![Color#] = stprefix & stno
If Me!ProductDivision = "side leather" Then
If stno <> "9999" Then
Me!tmpNextColorNo = stno + 1
Me!tmpNextColorNo = Lpad([tmpNextColorNo], "0", 4)
Me!tmpPrefix = stprefix
Else
Me!tmpNextColorNo = "0000"
Me!tmpPrefix = AutoIncr(stprefix)
End If
ElseIf Me!ProductDivision = "Whole Hide" Then
Me!tmpNextColorNo = stno + 1
Me!tmpPrefix = stprefix
End If
End If
End If
End If

Exit_optionColorFam_Exit:
Exit Sub

Err_optionColorFam_Exit:
MsgBox Error$
Resume Exit_optionColorFam_Exit

End Sub

Private Sub Penetrated_AfterUpdate()
On Error GoTo Err_Penetrated_AfterUpdate

stlong = Len([Color#])

If Me!Penetrated <> True Then
If Right(Trim([Color#]), 1) > "9" Then
Me![Color#] = Left([Color#], stlong - 1)
End If
Else
If Right(Trim([Color#]), 1) <= "9" Then
Me![Color#] = Me![Color#] & "P"
End If
End If

Me!Description = Me!cbxProduct & " " & Me!Color & " " & Me![Color#]

Exit_Penetrated_AfterUpdate:
Exit Sub

Err_Penetrated_AfterUpdate:
MsgBox Error$
Resume Exit_Penetrated_AfterUpdate

End Sub

Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click

Dim stlong As String
Dim stDelno As String
Dim stno2del As String
Dim stDelPrefix As String

If Not IsNull(Me![Color#]) Then
stDelno = DLookup("NextColorNo", "tblNextColorNo", "ProductDivision=forms!frmAddProducts![ProductDivision]")
stDelPrefix = DLookup("prefix", "tblNextColorNo", "[ProductDivision]= forms!frmAddProducts!ProductDivision")
Me!delno = stDelno - 1
stlong = Len([Color#])
If Right(Trim([Color#]), 1) > "9" Then 'is there a P
Me!delnow = Left([Color#], stlong - 1) ' remove the P
Else
Me!delnow = Me![Color#]
End If
If Me![delnow] = stDelPrefix & "0000" Then
MsgBox "You need to inform IT department that you are deleting a 0000 number. They will need to adjust the numbering system BEFORE you add another product", vbInformation, "INFORM IT DEPARTMENT"
Else
If (stDelPrefix & delno) = Me!delnow Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateDecreasetblNextColorNo"
DoCmd.SetWarnings True
End If
End If
End If

If MsgBox("You will be deleting Part #: " & Me![Part#] & " If you want to continue with the deletion select OK.", vbOKCancel, "CONFIRM PART# DELETION") = vbOK Then
Me.AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.AllowDeletions = False
End If
' DoCmd.Close
' DoCmd.OpenForm "frmAddProducts"

Exit_btnDelete_Click:
Exit Sub

Err_btnDelete_Click:
MsgBox Err.Description
Resume Exit_btnDelete_Click

End Sub

----------------------------

Thanks,

 

[TheAceMan1]

kkson . . . . .

I've been going over your post here & there and have eliminated all but a few routines as possible suspect (not really beliving their a problem). Still a little tuff pinning this sucker down . . . .

Question: If you add a record as you did in your post origination, what happens if you [blue]delete it normally[/blue] (that is click the [purple]Record Selector [/purple]and hit the [purple]KeyBoard Delete Button[/purple])?

See Ya! . . . . . .

 

[kkson]

I get the no current record error still. I have the delete it normally set to no. So that I can run the other code using the delete button I made.

Thanks for helping.

 

[TheAceMan1]

kkson . . . . .

Yeah . . . . set AllowDeletions to True and try again . . .

See Ya! . . . . . .

 

[kkson]

I should have told you that I did that before I posted my last reply. Set it to allow deletions and still got the no current record error. How ever instead of going to a blank record it went to a record with data in it.

Thanks

 

[TheAceMan1]

kkson . . . . .

Remove the code in the forms [blue]AfterDelConfirm[/blue] event and try again. Just trying to [purple]verify you can delete normally[/purple] (no intervening code).

See Ya! . . . . . .

 

[kkson]

Removed the code on the AfterDelConfirm and set allow deletions to yes. I can delete the record but if i try to go to another record I get the orignal error 'index or primary key can't be blank."

Thanks

 

[TheAceMan1]

kkson . . . . .

Finally . . . . something concrete to work with . . .

[blue]I get the orignal error [purple]index or primary key can't be blank.[/purple][/blue]

[blue]For the above to occur, [purple]a record has to have been edited (form in edit mode) and/or an attempt to save that record[/purple] occured with a [purple]blank field[/purple] that has its [purple]Required Property set to Yes[/purple] in its respective table.

Note: Writing to controls thru VBA [purple]does not put the form in edit mode.[/purple] Its the same as setting a Default![/blue]

Problem is I've eliminated all the code you posted as suspect!

Is there any other code in the form not posted. On Current event for example?

If no, then all you can do at this point is [blue]set a breakpoint in code[/blue] (at the start of the forms OnDelete & BeforeUpdate events would be good places) and single step thru the code until until you get the error message.

Sorry I can't give you more to go on . . . . let me know what happens with single step . . . .

See Ya! . . . . . .

 

[kkson]

Thanks AceMan1 I will try that.

 

[WillyG]

AceMan1 -

I had a slightly different problem. Using SQL statements I inserted a record from active-members table to dropped-members table and deleted it from active-members table. I was left with an empty record in the form with "deleted#" in every field. This then created a problem in an On Unload event sub that records the primary key of the current record so that On Load the user is returned to the same record he was last viewing.

My problem was solved by putting your suggestion {If Status = acDeleteOK Then Me.Requery} to work, but it did not work in the form After Del Confirm location. It works great at the end of the private sub where the insert/delete takes place. Perhaps kkson should try it that way.

WillyG

 

[TheAceMan1]

How are ya WillyG . . . . .

[blue]The [purple]Status[/purple] arguement is specific to the [purple]AfterDelConfirm[/purple] event![/blue]

Using it in other events (unless there's one I'm missing) is ambiguous . . . .

Which [blue]Private Sub[/blue] are you talking here?

See Ya! . . . . . .