Solved

If statement

Posted on 2014-09-18
17
145 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 34

Expert Comment

by:Paul MacDonald
ID: 40331402
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
ID: 40331436
i am getting "requires an object" error
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40331457
@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
ID: 40331604
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
ID: 40331637
which line is highlighted?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40332017
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
ID: 40333140
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
ID: 40333276
Nothing attached.

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

/gustav
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:deefel
ID: 40333321
sorry ~ file attached
Database7--2-.zip
0
 
LVL 39

Expert Comment

by:als315
ID: 40333541
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
ID: 40333724
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
ID: 40334075
Try zip
DBPrice.zip
0
 

Author Comment

by:deefel
ID: 40336254
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
ID: 40336489
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
ID: 40337429
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
ID: 40337430
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
ID: 40337634
> 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 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

21 Experts available now in Live!

Get 1:1 Help Now