Link to home
Start Free TrialLog in
Avatar of Petrobras
PetrobrasFlag for United States of America

asked on

If Statement - not working

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
Avatar of Billystyx
Billystyx

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
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....
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
Avatar of Petrobras

ASKER

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
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!
ASKER CERTIFIED SOLUTION
Avatar of shanesuebsahakarn
shanesuebsahakarn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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
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!