Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-01-2004, 06:01 AM   #1
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
nuttychick
docmd.runcommand.accmdSaveRecord

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...

__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Old 07-01-2004, 08:45 AM   #2
Rich
Guest
 
Posts: n/a
Use the AfterUpdate event not the on change
  Reply With Quote
Old 07-01-2004, 09:30 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,400
Thanks: 15
Thanked 1,625 Times in 1,543 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-01-2004, 11:34 PM   #4
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
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.
__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Old 07-01-2004, 11:51 PM   #5
Rich
Guest
 
Posts: n/a
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
  Reply With Quote
Old 07-01-2004, 11:58 PM   #6
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
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!!
__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Old 07-02-2004, 12:03 AM   #7
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
nuttychick
Save

To reply to Rich's update

Quote:
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...

__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Old 07-02-2004, 01:22 AM   #8
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
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
__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...

Last edited by nuttychick; 07-02-2004 at 01:23 AM. Reason: addition of thanks
nuttychick is offline   Reply With Quote
Old 07-02-2004, 01:29 AM   #9
Rich
Guest
 
Posts: n/a
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.
  Reply With Quote
Old 07-02-2004, 06:56 AM   #10
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
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

Attached Files
File Type: doc Form screen25.doc (81.0 KB, 173 views)
__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Old 07-02-2004, 07:13 AM   #11
Rich
Guest
 
Posts: n/a
Why are you using an Update statement to insert a value into your Problem table?
  Reply With Quote
Old 07-04-2004, 11:20 PM   #12
nuttychick
Newly Registered User
 
nuttychick's Avatar
 
Join Date: Jan 2004
Location: Telford, England
Posts: 84
Thanks: 0
Thanked 0 Times in 0 Posts
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

__________________
Nicki Smith
CapGemini - ASPIRE

Quote:
Just Keep Swimming...
nuttychick is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 10:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World