How to automatically add a new Record if field is equal to a certain value

Ramlov

Registered User.
Local time
Today, 15:03
Joined
May 26, 2014
Messages
18
It may sound a bit complicated, but I would like following.

A have an OrderDetail form where I add the articles to a specific order. My articles can be ordered with 4 different attributes and one of them cost more than the others. If they want the article that cost more, I would like access to add a new record with some information.

also:
4 different attributes to my articles. (A, B, C, D)
if ArticleAttribute = A then Null
if ArticleAttribute = B then Null
if ArticleAttribute = C then Null
if ArticleAttribute = D then add NewRecord with ArticleId = X

Is this possible?
 
Is this two questions or one.

Can you please rephrase your question in simpler terms.
 
It is one question.

I have a rage of articles. All articles are available in 4 variations. Without any adjustment, 2 standard adjustments and one special adjustment. It is only the last variation that costs extra, and I need it to be specified in the invoice.

So when I choose. Special adjustment. I want access to automatically add a new record whit the article "Special adjustment" as default.

Hope this make more sense.
 
AfterUpdate
Code:
Function ArticleAfterUpdate ()
     With CodeContextObject
        If ArticleAttribute = "D" Then
               DoCmd.SetWarnings False
               DoCmd.OpenQuery "UpdateArticle", acNormal, acEdit
               DoCmd.Close acQuery, "UpdateArticle"
               DoCmd.SetWarnings True
        End If
    End With
End Function
You can use a form Sub and you will need to create the UpdateQuery.

Simon
 
AfterUpdate
Code:
Function ArticleAfterUpdate ()
     With CodeContextObject
        If ArticleAttribute = "D" Then
               DoCmd.SetWarnings False
               DoCmd.OpenQuery "UpdateArticle", acNormal, acEdit
               DoCmd.Close acQuery, "UpdateArticle"
               DoCmd.SetWarnings True
        End If
    End With
End Function
You can use a form Sub and you will need to create the UpdateQuery.

Simon

Thank you!!!

But it wont work for me... Do I need to have all information in the Query?? and dose it not work with all forms?
 
Ramlov

For something completely different. Create a Table and give it an appropriate name, say

Code:
 [SIZE=3][FONT=Calibri]tblArticleCost.[/FONT][/SIZE]

[FONT=Calibri][SIZE=3]AdjustmentCostPK       [/SIZE][SIZE=3]Autonumber and PK[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]AdjustmentType          [/SIZE][SIZE=3]Text[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]AdjustmentCost           [/SIZE][SIZE=3]Currency[/SIZE][/FONT]
When you select the AdjustmentType then the correct Cost will also be selected.

One of the advantages is that this is not hard coded. You don't need a programmer to add another Article type. This can be done by adding a record to the table.

While I am at it, the use of Set Warnings is very poor programing. You could turn Warnings off, then have an error, a spike or one of many other thing that prevent your code from finishing. So then the Warnings is never reversed.
 
Last edited:
Ramlov

For something completely different. Create a Table and give it an appropriate name, say

Code:
 [SIZE=3][FONT=Calibri]tblArticleCost.[/FONT][/SIZE]

[FONT=Calibri][SIZE=3]AdjustmentCostPK       [/SIZE][SIZE=3]Autonumber and PK[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]AdjustmentType          [/SIZE][SIZE=3]Text[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]AdjustmentCost           [/SIZE][SIZE=3]Currency[/SIZE][/FONT]
When you select the AdjustmentType then the correct Cost will also be selected.

One of the advantages is that this is not hard coded. You don't need a programmer to add another Article type. This can be done by adding a record to the table.

While I am at it, the use of Set Warnings is very poor programing. You could turn Warnings off, then have an error, a spike or one of many other thing that prevent your code from finishing. So then the Warnings is never reversed.

Hey RainLover

Thank you very much. I’m very grateful that you all take some of your time to answer my question.


Right now I’m doing something like you describe. I have an article named special adjustment which I manually add when I have chosen the special adjustment for an article. I just want it to come automatically, but I understand that it might be too difficult?


Once again thank you all for your help.
 
Right now I’m doing something like you describe. I have an article named special adjustment which I manually add when I have chosen the special adjustment for an article. I just want it to come automatically, but I understand that it might be too difficult?

My suggest is like falling off a log. Very easy.

Create a table as described. Fill this with the data. The first Field is just an autonumber as the PK. The second is the Type and the third is the cost.

When you select a record you select the correct Primary Key, then the other two fields follow. EG PK 4 could be the Special Type with a Cost of $XXX.00

If you don't understand then create a simple data base with just the One Table of whatever Plus the Table I described.

Create a Form that has a Record Source of the whatever table. Then add a Combo Box to select from the Type Table.

If you can do this in Access 2003 then post it here so I can fine tune the details.

I can also use 2007 but I hate it like a mad dog.

Hope I have not confused too much.
 
My suggest is like falling off a log. Very easy.

Create a table as described. Fill this with the data. The first Field is just an autonumber as the PK. The second is the Type and the third is the cost.

When you select a record you select the correct Primary Key, then the other two fields follow. EG PK 4 could be the Special Type with a Cost of $XXX.00

If you don't understand then create a simple data base with just the One Table of whatever Plus the Table I described.

Create a Form that has a Record Source of the whatever table. Then add a Combo Box to select from the Type Table.

If you can do this in Access 2003 then post it here so I can fine tune the details.

I can also use 2007 but I hate it like a mad dog.

Hope I have not confused too much.

It is not confusing at all, and thank you for your patience :)
I use this method for my articles, and I already have a table for my adjustments. I also know the auto-filling commandos but I want it to come automatically as a new record when I choose the special adjustment. Is that also possible with the auto-filling function?
 
Please explain "special adjustment" and "auto-filling function"

I am confused as to what you want.

You need to explain in simple English.
 
Please explain "special adjustment" and "auto-filling function"

I am confused as to what you want.

You need to explain in simple English.

Special adjustment is just the name of the type. Nothing to do with access.

Auto-filling is when I put in a article number in my order form it automatically take the unit price and fill it in.

What I want is something like this :)
stackoverflow.com/questions/19775349/create-a-new-record-with-certain-values-after-updating-an-existing-record?answertab=votes#tab-top
 
Last edited:
The link is too much to read without directions.

Auto-filling is when I put in a article number in my order form it automatically take the unit price and fill it in.

If you have the two Tables Joined then when you select the Primary Key of the ONE table the balance of information will appear.
 
You may have to knock up a sample database like I suggested previously.
 
The link is too much to read without directions.



If you have the two Tables Joined then when you select the Primary Key of the ONE table the balance of information will appear.

I aint allowed to send links...

You may have to knock up a sample database like I suggested previously.

I will try... How do I send it to you?
 
It needs to be sent as an attachment.

A full explanation is contained within the FAQs

If you don't have enough posts try welcoming a few people to the forum. You would only need to do that three times if you require a total of 10 posts.

It is also a good idea to do a compact and repair first. Then zip and post.

Finally test it by downloading it back onto your machine and open the zip.
 
I aint allowed to send links...

If you don't have a good understanding of English you should say so. Fill out your profile so we can see where you live.

"aint" is not a word. Maybe you should have done a spell check. I know I always do as my spelling is poor.
 
If you don't have a good understanding of English you should say so. Fill out your profile so we can see where you live.

"aint" is not a word. Maybe you should have done a spell check. I know I always do as my spelling is poor.

Sorry, I usually make a spelling check, but I was in a hurry and hoped that you would understand me :)

View attachment Test.accdb

I hope this works as an example. Please tell me if there is anything you need :)
 
Ramlov

Please do a search and look for "The Evils of Lookups" . Have a read of this as it is important for you to understand.

The article on naming conventions will help a lot as well.

I redesigned your database to a point, using the rules as I have just asked you to read.

I have not designed a form as yet. I was hoping you could do that. After all you are very close to solving your own problem.

Relationships are important for you to understand in Access. Especially the part on Enforce Referential Integrity. I always answer yes to that. Cascading is always a no for me. Other people have different reasons for choosing Yes. Suggest you use No for now.

I have attached what I have done for you. It is not a lot at first glance but I suggest you look closely.

Remember, never have lookups in Tables. In Forms is correct not tables.
 

Attachments

Ramlov

Please do a search and look for "The Evils of Lookups" . Have a read of this as it is important for you to understand.

The article on naming conventions will help a lot as well.

I redesigned your database to a point, using the rules as I have just asked you to read.

I have not designed a form as yet. I was hoping you could do that. After all you are very close to solving your own problem.

Relationships are important for you to understand in Access. Especially the part on Enforce Referential Integrity. I always answer yes to that. Cascading is always a no for me. Other people have different reasons for choosing Yes. Suggest you use No for now.

I have attached what I have done for you. It is not a lot at first glance but I suggest you look closely.

Remember, never have lookups in Tables. In Forms is correct not tables.

Hey Rainlover... Thank you once more.

I have read the document, the names in the database was something I just quickly made up, and I use some other prefixes.

I did not know that it was bad to use lookups in table, I have always done that, and never had a problem but I have taken will remember!!

But how do I solve my challenge? I do not see how this makes access add a new record? Only how I make it add the informations to the same record. If you get me? I want access to make a new record with the adjustment as if it was a product I would like to add to the order. [FONT=&quot][/FONT]
 
I don't have 2007, nor am I well versed in it.

I will see what I can do tomorrow.
 

Users who are viewing this thread

Back
Top Bottom