docmd.runcommand.accmdSaveRecord

[nuttychick]

I have got a form that updates a sql database table 'Problem'.

One of the combo boxes on the form is called product name which is based on the contents of a view from sql (the view is the table 'products' sorted).
Once a choice is made from the combo (on change) the record is saved using docmd.runcommand accmd SaveRecord

I thought the process was working fine until I attempted to add another entry selecting a product name that I had already used, at this point the on change command of SaveRecord causes all the fields to chage to #deleted

I have tried loads of things to try and get round this, I have found that if a product name is select that has not already been used and then the correct product name is chosen the record is saved.

I do think it is the Access side to blame as I can manually add the entry to SQL and if I remove the docmd.SaveRecord and save manually with the little pencil bar it works fine.

Any ideas, reasons why would be very much appreciated...

 

[Rich]

Use the AfterUpdate event not the on change

 

[Pat Hartman]

You don't need to force Access to save the record if it has been changed. Access will AUTOMATICALLY save the current record if it is dirty when focus moves to another record or the form closes.

There are some cases where you need to force the record to be saved immediately. One case that is especially common is if you want to open a form or report that references the current record. Since the form or report would be reading the values from the underlying table rather than the open form, you need to save the record to ensure that whatever the user is currently looking at is reflected in the newly opened form or report.

Never use the Change event to run code like this. The change event fires multiple times. It fires once for every character typed into the control. So if the user is entering 10 characters, the code will execute 10 times. Use either the AfterUpdate event of the control or the BeforeUpdate event of the form, depending on what you are trying to do.

 

[nuttychick]

Save record

Pat,

The situaiton you have described is exactly what is happening. I need the record to be saved so that another form can be opened (using the problemID), on opening it creates a new record in a different table for that problemID. So naturally if the record is not saved then I get an error saying I have tried to assigned a null value to a none variable data type.

I have tried using the Afterupdate event to save the record. I will do this again, I know there were problems using this process as well.

I will let you know what the situaiton is - thanks for all your help.

 

[Rich]

How are you opening the new form? If it's via a command button then move the save command to the button code. Just make sure it's the first command carried out

 

[nuttychick]

Using AfterUpdate

Ok,

Using the AfterUpdate on the combo box for the product name - to save the record does the following

If I select a product from the list that I know IS already in the problem table after moving from the combo box all the fields turn to #deleted

If I select a product from the list that I know is NOT already in the problem table after moving from the combo box the record is saved and I can continue to the next form. i.e. it all works.

If I select a product from the list that I know is NOT already in the problem table, move from the combo to allow it to save, and then reselect the product name to one that I know IS in the problem table. The record is saved and I can continue.

I really can't get me head around why this is happening.
The relationship of the Product table to the Problem Table is one to many. Each problem can only relate to one product, but a product may have many problems.

There is a subform on the form, and another dropdown based on a query, if this sheds any more light.

Help!!

 

[nuttychick]

Save

To reply to Rich's update

How are you opening the new form? If it's via a command button then move the save command to the button code. Just make sure it's the first command carried out

The new form is opened via a command button, it does contain a save command there are well, I'm pretty sure I have tried to use just that save command but I think that is when I get an error about trying to save a null value to a non variant data type.

I will try it now and let you know...

 

[nuttychick]

Ok,
Bit of corruption with the cmd button. I deleted it and all the code and wrote it again.
Now the Docmd.runcommand accmdSaveRecord is the first thing on the cmd button only (there are dirtyflags on all the other fields if this makes a difference) the form opens by the problemID

Now the problem is this,

If I select a product that already has an entry in the problem table all, when clicking the cmd button all the fields go to #delete and a message about assigning a null value is shown (as the record doesn't save)

I have used the side bar with the pencil on it to test and if I select any product that already has an entry in the problem table the fields go to #deleted (however checking the SQL side they do actually stay in the database)

If I select a product that does not have an entry in the problem table it saves fine.
I can get round this by selecting a product I know is not in the database first, saving it using the bar with the pencil on and then changing it to the one I really want. Of course this is not the best way to do things! but it does save which really confuses me....

Do anyone know what is going on? I've not been into databases for long and this is confusing!

Thanks to everyone for their advice so far

 

[Rich]

There's almost certainly a problem with your structure, either your not saving the Pk as the FK, your relationships are wrong, the bound column is wrong, etc. We need some further details of what your form does in relation to what, etc.

 

[nuttychick]

More info

I've tried to gather some information to explain the structure of the database a little better.

I've attached file..hope it explains things a bit better.

Thanks again

 

[Rich]

Why are you using an Update statement to insert a value into your Problem table?

 

[nuttychick]

The only statement I have is an Insert one for the symptom table...

Which update statement are you refering to? or have I misread your post?

thanks