Solved

If statement

Posted on 2014-09-18
17
141 Views
Last Modified: 2014-09-25
I am using ms access 2007 after update property where the user can change the value on Field A and it impact the value on  field B, but if field A is null then I want the user to be able to change field B.  Below is the code I am using but it isn’t working.

If Me.Filed A Is Null Then
Me.Field B (this would be whatever the user wants to type in)  This is where i get my error
Else
If Me.Filed A Is Not Null Then
Me.Field B = Me.Field C
End If
0
Comment
Question by:deefel
  • 7
  • 4
  • 3
  • +2
17 Comments
 
LVL 33

Expert Comment

by:paulmacd
Comment Utility
Try
If Me.Filed A Is Null Then
Me.Field B = ""
Else
If Me.Filed A Is Not Null Then
Me.Field B = Me.Field C
End If
0
 

Author Comment

by:deefel
Comment Utility
i am getting "requires an object" error
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@deefel

why not post the actual NAME of the textboxes instead of using dummy name
it will make it a lot easier for us to give a suggestion to fix your problem

if me.nameoftextboxA & ""="" then
   me.nameoftextboxB=""
else
  me.nameoftextboxA =me.nameoftextboxC

end if
0
 

Author Comment

by:deefel
Comment Utility
Hello - i still get object required error



if Me.Price2 & ""="" then
    Me.Price1=""
 else
   Me.Price1 =me.totalprice

 end if
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
which line is highlighted?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
This is how to do it:

If IsNull(Me!Price2) Then
    ' Do not impact Me!Price1.
Else
    Me!Price1 = Me!TotalPrice
End If

/gustav
0
 

Author Comment

by:deefel
Comment Utility
Once i cleared the extra fields out of the database the error went away.  What is suppose to happen is I can type in a price in the each price field and the form does a calculation and put the price in the Regular price field.  If the each price field is blank then the user can manually change the default price in the Regular price field.  I can enter a price in the Each field but it is not calculating and populating the Regular price field and if the each field is blank when I click on the regular price field to change the number it becomes blank. (see attached)
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Nothing attached.

If Regular turns blank when you enter it, probably some code is running at the OnEnter event of Regular.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:deefel
Comment Utility
sorry ~ file attached
Database7--2-.zip
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Look at sample. Select position with price and without price and try to enter value to field Total
DBPrice.accdb
0
 

Author Comment

by:deefel
Comment Utility
thanks ~ can't download the sample database. Clicking on it opens it up in what appears to be computer language.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Try zip
DBPrice.zip
0
 

Author Comment

by:deefel
Comment Utility
thanks ~ sorry to be so needy but the attached isn’t what I am trying to do.  Maybe I am not explain too good.

When the form opens the Price field has a default value. (see attached)
If a user put a price in the Each Price field ~ and click in the Price Field (Got focus) you will notice the Price field now has that value.  But if the user decides they don’t want the Each Price and deletes it and then go to enter a new price in the Price field the value disappears (the value that was there based on the Each price calculation).  How do I write code so that when the Each Price field’s value gets deleted the Price field is editable without the value disappearing.  
To show what I am talking about ~ Please add the value of .489 in the Each price and click in the Price field.  The value in the Price field should now be .489.  Now delete the value from the Each Price and click in the Price field.  Please note the value has disappeared.
Database7.accdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Use OldValue (and always Option Explicit):

Option Compare Database
Option Explicit

Private Sub price1_GotFocus()

    If IsNull(Me!price2) Then
        Me!price1 = Me!price1.OldValue
    Else
        Me!price1 = Me!eachtotal1
    End If

End Sub

Open in new window

/gustav
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
You can simply remove assignment of Price1 value if there is no value in Price2:
Private Sub price1_GotFocus()
If Not IsNull(Me!price2) Then
     Me!price1 = Me.eachtotal1
End If
End Sub

Open in new window

0
 

Author Comment

by:deefel
Comment Utility
Thanks ~ almost there.  The first record Price1 is 2.60 if I change it to 2.75 and tab off the record it changes Price1 back to 2.60 it don't keep the change.
Database7--3-.zip
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> You can simply remove assignment of Price1 value if there is no value in Price2:

No. That will not restore the previous value in case you blank price2 before moving focus to price1.

> .. it don't keep the change.

That's because you designed it that way. The value is recorded only when price1 gets focus.

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now