Cant delete last record

[badz2801]

Hello

I am having a problem deleting the last record from a form in my database.

My database is setup with a one-to-many relationship.

Main table:
Autonumber
JobID

JobDetails table:

Autonumber
JobID
ToShop
Submitted
Etc....etc.

The form uses a combo box to select the job and displays the details of the job in a tabbed subform.

The delete button will allow me to delete all jobs (master records) previous to the last job (which is the newest job). However when I want to delete the last job (job at the bottom of the combo box) it wont allow me to do so. There can be jobs above the one I want to delete and it still wont act upon it.

Additionally when I add a new job from a different form, the details located in the subform (for the previously listed job) are carried over to the new one. Consequently the details for the job next to last are lost.

Any idea of how I fix these two issues?

I can zip up the database if you need it.

Thank you for your time.

Ian

 

[badz2801]

Update

Hello

I just tested the form a little more and I am experiencing the following additional issues:

1. When I delete all the records, it wont allow me to delete the very last (or newest in this case).

2. I have a text box near the combo box which displays the job number selected in the combo box. When I select the last record (newest record) it wont display the correct job number. Instead it displays the job number of the previously selected job (provided) it was not the last one.

Any ideas on what the problem is? In c++ it would an n+1 issue, but I am new to access and I cannot figure out how to fix this due to my limited knowledge.

Thanks again

 

[badz2801]

Here is the database as I have it thus far.

 

[IR_Moon]

As far as I can see it is deleting the last record and providing you simply with a blank form. If you look at the table, you will see that the record has been deleted after you press the delete button. It certainly looks like a blank record on the form to me too.

As for the problem with the combo box... The data entry form is open while you add data on another form. The original forms record source needs to be requeried to get the latest date (Me.Requery). I also put in a refresh statement... these both go in the "On Activate" code as follows:

Private Sub Form_Activate()
    Me.Requery  ' refreshes record source to include new records
    Me.Refresh   ' refreshes form
End Sub

HTH

IRMoon

 

[badz2801]

Thank you

I cannot locate an on activate tab.

Which form does this code go into? What control? The combo box?

 

[IR_Moon]

Forgive me if this is too basic a reply for you, I'm not sure how much experience you have, and you said you were new to access

It goes in the form code. If you go to design view, you can select the properties for the form by double clicking on the form selector in the top left of the design view (or right clicking and selecting Properties) (as in Fig 1)

Figure 1​

Click on "On Activate" as per Fig 2 - then on the ellipsis (with the 3 dotted lines) that appears next it.

Figure 2​

When prompted "Choose Builder", select the bottom option, "Code Builder" then hit okay and paste the above code in between the start and end of the Sub shown.

 

[badz2801]

Thank you.

Nope not too basic I had no idea you could do that. This is my third day of using access in this manor.

Just got lucky that database design is similar in MySQL.

Thanks again

Ian

 

[IR_Moon]

Sweet... glad I could help... makes a change actually!! Every other time I've posted I've been ASKING for help not giving it Stick around... you'll learn heaps REAL quick! This site has become one of my most visited sites and my best learning tool.

Good luck with your project!