Petrobras
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("S ELECT * FROM tblLetterOfCredit ")
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If IsNull(rsdeals!NewDealsDat e) 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("S ELECT * FROM tblLetterOfCredit ")
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If IsNull(rsdeals!DateLineOpe ned) Then
rsdeals.Edit
rsdeals!Funded = True
rsdeals!DateLineOpened = Date
rsdeals.Update
End If
End If
rsdeals.Close
End Sub
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("S
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If IsNull(rsdeals!NewDealsDat
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("S
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If IsNull(rsdeals!DateLineOpe
rsdeals.Edit
rsdeals!Funded = True
rsdeals!DateLineOpened = Date
rsdeals.Update
End If
End If
rsdeals.Close
End Sub
First, verify that intDealID holds the correct value. You should use:
intDealID=Forms!frmDeals!D ealID
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.OpenReco rdset("SEL ECT * FROM tblLetterOfCredit WHERE DealID=" & intDealID)
If Not rsdeals.EOF Then
If IsNull(rsdeals!DateLineOpe ned) Then
....etc....
intDealID=Forms!frmDeals!D
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.OpenReco
If Not rsdeals.EOF Then
If IsNull(rsdeals!DateLineOpe
....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("S ELECT * FROM tblLetterOfCredit ")
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If Me.Status.Value Like "New Deals*" and IsNull(rsdeals!NewDealsDat e) 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!DateLineOpe ned) Then
rsdeals.Edit
rsdeals!Funded = True
rsdeals!DateLineOpened = Date
rsdeals.Update
End If
End If
rsdeals.Close
end sub
Billystyx
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("S
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If Me.Status.Value Like "New Deals*" and IsNull(rsdeals!NewDealsDat
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!DateLineOpe
rsdeals.Edit
rsdeals!Funded = True
rsdeals!DateLineOpened = Date
rsdeals.Update
End If
End If
rsdeals.Close
end sub
Billystyx
ASKER
Billy, there seems to be a syntax error with:
elseif Me.Status.Value Like "Funded*" and If IsNull(rsdeals!DateLineOpe ned) 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
elseif Me.Status.Value Like "Funded*" and If IsNull(rsdeals!DateLineOpe
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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
If Me.Status.Value Like "Funded*" Then
Set rsdeals.....
...etc....
Set rstdeals=Nothing
End If
ASKER
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!
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!
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("S
rsdeals.MoveFirst
rsdeals.FindFirst "[DealID] =" & intDealID
If Not rsdeals.NoMatch Then
If IsNull(rsdeals!NewDealsDat
rsdeals.Edit
rsdeals!newdeals = True 'this will check mark the check box
rsdeals!NewDealsDate = Date
rsdeals.Update
End If
If IsNull(rsdeals!DateLineOpe
rsdeals.Edit
rsdeals!Funded = True
rsdeals!DateLineOpened = Date
rsdeals.Update
End If
End If
rsdeals.Close
end sub
Billystyx