Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

If Statement - not working

Posted on 2005-04-19
9
Medium Priority
?
4,435 Views
Last Modified: 2011-09-20
I am a novice user of access and not a programmer.  

I have a form named frmDeals and when I change the status of my combo box on frmDeals to "New Deals" I want a text box on another form (with underlying table tblLetterOfCredit) to update automatically with the current date of this new deal.  If there is alread a date in the field then  I do not want to override it.  

Here are some deatails that i think you would need for the code:

combo box name on frmDeals that contains the status:  Status
text box name on frmLetterOfCredit that needs updating:  NewDealsDate  and DateLineOpened
form that has the text box NewDealsDate:  frmLetterOfCredit
underlying table of frmLetterofCredit:  tblLetterOfCredit

The first part of the code below works for the status Like"New Deals*" but the one below it does not work for some reason.  The code below "New Deals*"  section that starts at like "Funded*" is to do the exact same thing as what the "New Deals*" part does.  "New Deals*"  section works good but the section below does not.  I believe that you are to remove the dims and just change the names of the fields that I want to change.  The only change is NewDealsDate to DateLineOpened.  

Here it is:  

Private Sub Status_AfterUpdate()

If Me.Status.Value Like "New Deals*" Then
   
    Dim intDealID As Variant
    Dim rsdeals As DAO.Recordset

    Set intDealID = Forms!frmDeals!DealID

    Set rsdeals = CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit ")
 
        rsdeals.MoveFirst
        rsdeals.FindFirst "[DealID] =" & intDealID
    If Not rsdeals.NoMatch Then
    If IsNull(rsdeals!NewDealsDate) Then
            rsdeals.Edit
            rsdeals!newdeals = True  'this will check mark the check box
            rsdeals!NewDealsDate = Date
            rsdeals.Update
        End If
        End If
   rsdeals.Close
   
   
   NEXT PART:
   
   
   If Me.Status.Value Like "Funded*" Then
   
 'this code is not working for some reason.  The one above does work
    Set intDealID = Forms!frmDeals!DealID

    Set rsdeals = CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit ")
 
        rsdeals.MoveFirst
        rsdeals.FindFirst "[DealID] =" & intDealID
    If Not rsdeals.NoMatch Then
    If IsNull(rsdeals!DateLineOpened) Then
        rsdeals.Edit
        rsdeals!Funded = True
        rsdeals!DateLineOpened = Date
        rsdeals.Update
 End If
End If
   rsdeals.Close

 End Sub
0
Comment
Question by:Petrobras
  • 4
  • 3
  • 2
9 Comments
 
LVL 18

Expert Comment

by:Billystyx
ID: 13815326
try doing the whole code like this:
Private Sub Status_AfterUpdate()

If Me.Status.Value Like "New Deals*" Then
   
    Dim intDealID As Variant
    Dim rsdeals As DAO.Recordset

    Set intDealID = Forms!frmDeals!DealID

    Set rsdeals = CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit ")
 
        rsdeals.MoveFirst
        rsdeals.FindFirst "[DealID] =" & intDealID
    If Not rsdeals.NoMatch Then
    If IsNull(rsdeals!NewDealsDate) Then
            rsdeals.Edit
            rsdeals!newdeals = True  'this will check mark the check box
            rsdeals!NewDealsDate = Date
            rsdeals.Update
        End If
If IsNull(rsdeals!DateLineOpened) Then
        rsdeals.Edit
        rsdeals!Funded = True
        rsdeals!DateLineOpened = Date
        rsdeals.Update
 End If

        End If
   rsdeals.Close

 end sub

Billystyx
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13815358
First, verify that intDealID holds the correct value. You should use:
intDealID=Forms!frmDeals!DealID

In other words, remove the Set. This is used to set object variables and is not needed.

Instead of doing a search, why not just open the recordset at the record you are interested in?

Set rsdeals=CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit WHERE DealID=" & intDealID)
If Not rsdeals.EOF Then
   If IsNull(rsdeals!DateLineOpened) Then
      ....etc....
0
 
LVL 18

Expert Comment

by:Billystyx
ID: 13815364
Sorry about last post - like this:

Private Sub Status_AfterUpdate()

If Me.Status.Value Like "New Deals*" Then
   
    Dim intDealID As Variant
    Dim rsdeals As DAO.Recordset

    Set intDealID = Forms!frmDeals!DealID

    Set rsdeals = CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit ")
 
        rsdeals.MoveFirst
        rsdeals.FindFirst "[DealID] =" & intDealID
    If Not rsdeals.NoMatch Then
    If Me.Status.Value Like "New Deals*"  and IsNull(rsdeals!NewDealsDate) Then
            rsdeals.Edit
            rsdeals!newdeals = True  'this will check mark the check box
            rsdeals!NewDealsDate = Date
            rsdeals.Update
        elseif Me.Status.Value Like "Funded*" and If IsNull(rsdeals!DateLineOpened) Then
        rsdeals.Edit
        rsdeals!Funded = True
        rsdeals!DateLineOpened = Date
        rsdeals.Update
 End If

        End If
   rsdeals.Close

 end sub


Billystyx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Petrobras
ID: 13816731
Billy, there seems to be a syntax error with:
elseif Me.Status.Value Like "Funded*" and If IsNull(rsdeals!DateLineOpened) Then

I am going to need to repeat this code many more times and wondering where I would need to start the copy from?

Thank you
0
 

Author Comment

by:Petrobras
ID: 13816763
Shane, I could try the code but as I am not a programmer and the slighest thing could go wrong and I would get nowhere.  If you could display the code then I could try it.  I am going to need to repeat this code many more times for other Like "XXX" and wondering where I would need to start the copy from?  Thanks!
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 13816944
Basically, like this:

Dim intDealID As Long
Dim rsdeals As DAO.Recordset

intDealID = Forms!frmDeals!DealID

Set rsdeals=CurrentDb.OpenRecordset("SELECT * FROM tblLetterOfCredit WHERE DealID=" & intDealID)
If Not rsdeals.EOF Then
   If IsNull(rsdeals!NewDealsDate) Then
      rsdeals.Edit
      rsdeals!newdeals = True  'this will check mark the check box
      rsdeals!NewDealsDate = Date
      rsdeals.Update
   End If
   If IsNull(rsdeals!DateLineOpened) Then
      rsdeals.Edit
      rsdeals!Funded = True
      rsdeals!DateLineOpened = Date
      rsdeals.Update
   End If
End If
rsdeals.Close
Set rsdeals=Nothing

Note that you should use specifically typed variables instead of Variants where possible, since they operate faster.
0
 

Author Comment

by:Petrobras
ID: 13817108
Shane, that was awesome!  Worked great.  My system is getting better and better each day.  Thanks Billy too!

One question though.  I dont see the like statement there anymore like Billy's code had.  How does it know?  i have to copy the code many more times and I guess I would start the copy after the first end if anbd just change the names (Funded and Date Line Opened)?

Thanks!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13817136
Sorry, I omitted that part entirely. You can wrap the code I gave you in an If statement:

If Me.Status.Value Like "Funded*" Then
   Set rsdeals.....

    ...etc....

   Set rstdeals=Nothing
End If
0
 

Author Comment

by:Petrobras
ID: 13817423
Shane, I have many like "XXX*" not only "Funded*"
Where do you suggest I put the status like "XXX*" at?

eg for the like "Funded*", I have to change the DateLineOpened (which is already in the code)
for the like "Denied*", I have to change the DeniedDate (not already in the code)


IN the code now it is set up for two different status': (1) LIke New Deals (enters in the NewDealsDate current date)
                                                                            (2) Like Funded (enters in the DateLineOpened current date)

I have many more like "XXX*" to code and since the code is set up for two status' now I am not sure where the likes go?  I know the first one goes where you said but what about the second one.  I hope this is not confusing.  

Thanks guy!




0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

580 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