Form auto save problem

[deepaksharma]

hii all experts....

I have created this program, inside it I have created offer entry and offer register. On double clicking on any offer number in the offer register, that offer opens in the form to be edited.
To prevent data from being auto saved and offer numbers to be auto generated I have done some coding in the current form, on load form and unload form and data entry is set to yes in the property of the form and it is working well.
The problem arises when I double click on the last made offer in the offer register and open it for editing, then it gets deleted.
I have been trying hard for many days to solve this problem but my mind has become completely crazy but I have not found any solution for it yet. I wish any of you can tell me the solution.
With this, if I use the search box created in the offer entry form, a new empty record is created in the gray offer table.

[mike60smart]

Why are you using the following code on the DoubleClick Event?

All you need is a DoCmd Open Form where ID = Me.ID

Private Sub Form_Load()

Set DB = CurrentDb
Set RST = DB.OpenRecordset("GREY_OFFER")
's


DoCmd.RefreshRecord


'DoCmd.RunCommand (acCmdRecordsGoToNew)
If Me.TXT0 < DMax("Offer_ID", "GREY_OFFER") Then GoTo ad
If RST.EOF Then Me.TXT0 = 1
If DMax("Offer_ID", "GREY_OFFER") >= 1 Then Me.TXT0 = DMax("Offer_ID", "GREY_OFFER") + 1


GoTo ab


ad:
MsgBox "You are Editing Offer Number " & Offer_ID, vbInformation, "Information"


ab:
DoCmd.RefreshRecord
'DoCmd.GoToRecord , , acNewRec


End Sub

[deepaksharma]

Why are you using the following code on the DoubleClick Event?

All you need is a DoCmd Open Form where ID = Me.ID

Private Sub Form_Load()

Set DB = CurrentDb
Set RST = DB.OpenRecordset("GREY_OFFER")
's


DoCmd.RefreshRecord


'DoCmd.RunCommand (acCmdRecordsGoToNew)
If Me.TXT0 < DMax("Offer_ID", "GREY_OFFER") Then GoTo ad
If RST.EOF Then Me.TXT0 = 1
If DMax("Offer_ID", "GREY_OFFER") >= 1 Then Me.TXT0 = DMax("Offer_ID", "GREY_OFFER") + 1


GoTo ab


ad:
MsgBox "You are Editing Offer Number " & Offer_ID, vbInformation, "Information"


ab:
DoCmd.RefreshRecord
'DoCmd.GoToRecord , , acNewRec


End Sub

This is a form load event

[mike60smart]

I give up as nothing appears to be sinking in

[deepaksharma]

I give up as nothing appears to be sinking in

this is very difficult to solve

[mike60smart]

The idea of a List of previous records like your "Offer Register" is to quickly open a Form to display the record selected.

Please explain what it is you are trying to do with the Load Event ?

[Micron]

The problem arises when I double click on the last made offer in the offer register and open it for editing, then it gets deleted.

So that means if you open the offer to edit the record(s) about that offer get deleted?

if I use the search box created in the offer entry form, a new empty record is created in the gray offer table.

Or is the problem that when you use the search box (no real idea what that means) you end up with a new offer.

If you are going to expect help from forum members, you have to state what the problem is, what makes it happen and what you want to have happen. You have to write like you are directing 2 year old kids - not because that's our mentality but because you're asking us to create an understanding based on those 3 things without the benefit of your insight. If you don't, you end up getting a lot of questions.

I'm away right now so don't expect too much from me for a couple of days.

[Micron]

I think I understand what the issue is now but I dunno what caused the record to be deleted. I commented out your Docmd.RefreshRecords because I see no reason for that and I don't think I've ever used it. If you are opening a form and filtering in the load event (which you seem to be doing) there is no reason to do anything with the records unless you've modified them in the process. Even then, I would refresh (not requery) the form, not the record. You even try to refresh the record when there is no record - because the form hasn't even loaded anything yet (it is only the 3rd line of code).

What I ended up with now is the error message "The command or action "RefreshRecord" isn't available now when I re-enabled those lines to see if the record would once again be deleted. Now that stepping through the code is generating that error I don't know what to suggest other than to either to comment out those lines and/or substitute Me.Refresh because I'm out of time for today.

[mike60smart]

Micron has come to the same conclusion as me. The Code needs to be deleted.

Your process is you want to open a specific record using your Off Register Form. That is it. The record opens and you either
Edit it in some way or Close the Record.

[deepaksharma]

The idea of a List of previous records like your "Offer Register" is to quickly open a Form to display the record selected.

Please explain what it is you are trying to do with the Load Event ?

In the form load event, I have coded to auto generate the new offer number so that whenever the form is opened, a new offer number is shown in it and I have also done the same coding in the save button so that as soon as the offer is saved, the offer number is shown in the box. A new offer number shows up.
If I want, I can set the offer number field to auto number but the problem with that is that on deleting an offer, that number is lost forever.

[deepaksharma]

I think I understand what the issue is now but I dunno what caused the record to be deleted. I commented out your Docmd.RefreshRecords because I see no reason for that and I don't think I've ever used it. If you are opening a form and filtering in the load event (which you seem to be doing) there is no reason to do anything with the records unless you've modified them in the process. Even then, I would refresh (not requery) the form, not the record. You even try to refresh the record when there is no record - because the form hasn't even loaded anything yet (it is only the 3rd line of code).

What I ended up with now is the error message "The command or action "RefreshRecord" isn't available now when I re-enabled those lines to see if the record would once again be deleted. Now that stepping through the code is generating that error I don't know what to suggest other than to either to comment out those lines and/or substitute Me.Refresh because I'm out of time for today.

I have coded in form unload event that if offer number is less than dmax number of gray offer table then nothing should happen but if offer number is greater than or equal to dmax offer id of gray offer table and click on save button If it is deleted, the data may be deleted and not saved in the table.
Similarly when an old record is opened from the form offer register by double clicking then nothing happens due to unload event but if any last record is opened then it triggers the coding of unload form that it is dmax offer id. Is equal to so it deletes it.
If I change the form unload event to not be deleted if the offer number is equal or more than the Dmax offer ID, then the last record opened from the offer register will not be deleted but each new record will be auto saved while entering in the current form. It will take place.

[mike60smart]

Hi Deepak

Well your thought process is all wrong.

Step 1 - You have a Form for creating new Grey Orders
In this process you are using the following OnLoad Event

If DMax("Offer_ID", "GREY_OFFER") >= 1 Then Me.TXT0 = DMax("Offer_ID", "GREY_OFFER") + 1

This is absolutely the WRONG way to do this.
You are using the Primary Key "Offer_ID" to generate a Unique Number when Access will already do this Automatically
when you add data to the Form.

You should have a field in the Grey_Offer table named OrderNumber and then use the DMax Funcrion to generate a Unique OrderNumber

Step 2 - At some time in the future you need to edit/look at a specific OrderNumber by using your OnClick Event to open the "Offer_Registry" Form.

You do not need any code whatsoever in the On Load of this Form as the Record has already been created with a Unique "OrderNumber"

[deepaksharma]

Hi Deepak

Well your thought process is all wrong.

Step 1 - You have a Form for creating new Grey Orders
In this process you are using the following OnLoad Event

If DMax("Offer_ID", "GREY_OFFER") >= 1 Then Me.TXT0 = DMax("Offer_ID", "GREY_OFFER") + 1

This is absolutely the WRONG way to do this.
You are using the Primary Key "Offer_ID" to generate a Unique Number when Access will already do this Automatically
when you add data to the Form.

You should have a field in the Grey_Offer table named OrderNumber and then use the DMax Funcrion to generate a Unique OrderNumber

Step 2 - At some time in the future you need to edit/look at a specific OrderNumber by using your OnClick Event to open the "Offer_Registry" Form.

You do not need any code whatsoever in the On Load of this Form as the Record has already been created with a Unique "OrderNumber"

What you mean to say is that two fields should be created in the gray offer table, first is the offer ID which is set to auto number and second is the order number which is numeric and the primary key is set on it.
And dmax function should be used in on load form on order number

[mike60smart]

Hi Deepak

Main field 1 is the Primary Key set to Autonumber
Field OrderNumber - Number - LongInteger

You can then set the Before Update of the Form to use the DMax Function to Add new OrderNumbers

[Micron]

If I want, I can set the offer number field to auto number but the problem with that is that on deleting an offer, that number is lost forever.

Unless you have a reason that you must have consecutive numbers, then so what? If you can't have "missing" numbers, then sometimes those records are just saved without data, or flagged as "Rejected" or something that identifies them as being discarded. This can also point out where there may be issues with users using the system haphazardly.

You might be better off creating any numbers (assuming you can't just use an autonumber) at the end of the process; i.e. open the form without the number, create it upon save. This is usually the best method if the db is used in a shared environment.