Combo Box 1

[drek01]

i donot know whether i should be posting this here.

i have form( its VBA) which displays different field froma table.In every field ( almost all) there are combo box, which dispalys different grading( variables). field in combo box are populated from a field of relevant tables. when i change the variable in combo box for that row, i want it to give option if i wanna change the varaible for every row, and changes will be applied if i want to, else just for that row.

wich i could upload the form.

 

[CaptainD]

Go to the bottom of the VBA editor and paste the code below the last section. Then, in your combobox after update event type in "Call UpdateValues " and the intellisense will show you the fields to fill in. They are the ones inside the parenthesis. That is where you plug in the values to define what records to update. If you noticed, I used the ones from your select statment in the stored procedure. That way, you update the same records that you are looking at in your form. Then, below the "Call UpdateValues" enter "me.requery" and that should update your form.

 

[drek01]

gave me error saying "can't find macro name "Call UpdatesValues".

 

[CaptainD]

How are you calling it, post your code.

Thanks
D

 

[drek01]

Option Compare Database
Option Explicit
Private Sub cmddetail_Click()
On Error GoTo Err_cmddetail_Click

DoCmd.Openform "fsubTranWrkSheet"

Exit_cmddetail_Click:
Exit Sub

Err_cmddetail_Click:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_cmddetail_Click

End Sub



Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert

'Call activitylog("Asset Quality Review", "General info./Exposure")

Exit_Form_AfterInsert:
Exit Sub

Err_Form_AfterInsert:
MsgBox Err.Description, vbCritical
Resume Exit_Form_AfterInsert

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate

Call activitylog("Asset Quality Review", "General info./Exposure")

Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description, vbCritical
Resume Exit_Form_AfterUpdate

End Sub
Private Sub InternalTransactionGrade_Change()
Dim rs As ADODB.Recordset

Dim varBookmark As Variant, varValue As Variant
If MsgBox("Apply to all", vbYesNo) = vbYes Then
varBookmark = Me.CurrentRecord
varValue = Me.InternalTransactionGrade
Do
Me.InternalTransactionGrade = varValue
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
Me.Recordset.MoveFirst
Do While Me.CurrentRecord <> varBookmark
Me.InternalTransactionGrade = varValue
Me.Recordset.MoveNext
Loop
End If


End Sub
Private Sub UpdateValues(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
'error haandling here
Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb.Connection 'I think that is right

strSql = "UPDATE tblMaster SET tblMaster.YourField = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

db.Execute strSql

Exit Sub
Error message
End Sub




still gives me error or "Me.recordset.movenext"

 

[drek01]

one this i might need to clear this up is that, my combo boxex whenever i changes the value in it, and closes the form it updates the values in database.( this is already there, )

so, all i need it to make it easier if i change top row it will change all the rows.

hope i am naot confusing you.

THanks
D

 

[CaptainD]

I understand that, other then to say that using a stored procedure to populate the form and still be bound to the database does not make since to me. But, that aside, the update statement in the sub routine will make the changes to all the records and then requerying the form will give you all the updated records. So, show the code you used to run the sub routine so we can see why you are getting the error.

I would suggest for the moment having the combobox change event run the sub. You'll need to pass it the same information that you pass to the stored procedure at the form load event. (You have not shown how you are doing that)

Sample code:

Private Sub Combo4_Change()
    Call ChangeValues(YourValuesToPass)
    Me.Requery
End Sub

Also, I hope you are working on a sample database and not a live one until you get the bugs worked out.

 

[drek01]

i am so confused D.

the changed value on combo box gets updated on database once i close the form.

please bear with me here.


THis is the code to load main form, however i have subform inside this main form, which has combo boxes( code for that is posted in previous postings and store procedure as well.)


Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim bflag As Boolean
Dim sStr As String

bflag = bUserPrivileges(Me.Form)
If bflag = False Then
MsgBox "You don't have enough privilege for accessing the data of this form"
GoTo Exit_Form_Load
End If
sStr = " select * from tblborrowerinformation where (tblborrowerinformation.borrowername = '') and (tblborrowerinformation.examname = '" & Form_frmMainMenu.cmbexamname.Value & "') and (tblborrowerinformation.examasofdate = '" & Form_frmMainMenu.cmbasofdate & "')"
Form_frmCreditWrkSheet.RecordSource = sStr

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
GoTo Exit_Form_Load

End Sub


right now i am so confused. since my combo box already saves( loads) changed value if i made a changes of grades in every rows. so i was trying to do the same thing, sometime the all rows has to be changed for same grade, so if that couldbe applied.

Hope i am making sense to you. As i said plz, bear with me.
so i guess as CautionMp gave me idea, if that is possible to apply cahnge on one comboboxes ( of one column) so the same changes will take palce in all the combo boxes of all the rows of same colum.

same should be done to other combo boxex of differt cloumn.

Hope this is not confusing you,

 

[CaptainD]

Are you using this to open the sub forms?

Private Sub cmddetail_Click()
On Error GoTo Err_cmddetail_Click

DoCmd.Openform "fsubTranWrkSheet"

Exit_cmddetail_Click:
Exit Sub

If so, let me see the values or sql statment that populates the sub form.
This:

Alter Procedure Spr_fsubCreditMaster
(@ParExamName varchar(50),@ParAsofDate smalldatetime,@ParBorrowerName varchar(255))
as
Begin
SELECT tblMaster.InstrumentID, tblMaster.TypeOfCredit,
(tblMaster.ApprovedCrLine) / 1000 as ApprovedCrLine,(tblMaster.TotalExposure) / 1000 as TotalExposure,
(tblMaster.Loans) / 1000 as Loans, (tblMaster.TradeFinance) / 1000 as TradeFinance,
(tblMaster.Commitments) / 1000 as Commitments,(tblMaster.LCs) / 1000 as LCs,
(tblmaster.participationsold) / 1000 as participationsold,tblMaster.BookingOffice,
tblMaster.InternalTransactionGrade, tblMaster.CeaTransactionRating, tblMaster.SystemGuarantyFlag, tblMaster.SecurityFlag
FROM tblMaster
where (tblmaster.borrowername = @ParBorrowerName) and (tblmaster.orgunit = @ParExamName) and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd =@ParAsofDate
End

takes parameters that desinguish the records you are viewing. By using those parameters and how you are getting them you can run the update statment. By running the update statment, you can change all the chosen values.

 

[drek01]

this is how my subform "fsubCreditMaster" is updated, that is what i have in my event after update, event procedure has

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate

Call activitylog("Asset Quality Review", "General info./Exposure")

Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description, vbCritical
Resume Exit_Form_AfterUpdate

End Sub




this is the subform which has cobo boxes.
hope you got the point,

Derek

 

[CaptainD]

I've seen the after update information, it calls a sub routine called "activitylog" that, based off it's name, just writes to a log. It might help if you posted that routine if it does otherwise.

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate

Call activitylog("Asset Quality Review", "General info./Exposure")

Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description, vbCritical
Resume Exit_Form_AfterUpdate

End Sub

that is not what I was asking for. On your main forms form load event, you query "tblborrowerinformation". The Record source for you sub form is a stored procedure that you posted.

Alter Procedure Spr_fsubCreditMaster
(@ParExamName varchar(50),@ParAsofDate smalldatetime,@ParBorrowerName varchar(255))
as
Begin
SELECT tblMaster.InstrumentID, tblMaster.TypeOfCredit,
(tblMaster.ApprovedCrLine) / 1000 as ApprovedCrLine,(tblMaster.TotalExposure) / 1000 as TotalExposure,
(tblMaster.Loans) / 1000 as Loans, (tblMaster.TradeFinance) / 1000 as TradeFinance,
(tblMaster.Commitments) / 1000 as Commitments,(tblMaster.LCs) / 1000 as LCs,
(tblmaster.participationsold) / 1000 as participationsold,tblMaster.BookingOffice,
tblMaster.InternalTransactionGrade, tblMaster.CeaTransactionRating, tblMaster.SystemGuarantyFlag, tblMaster.SecurityFlag
FROM tblMaster
where (tblmaster.borrowername = @ParBorrowerName) and (tblmaster.orgunit = @ParExamName) and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd =@ParAsofDate
End

This stored precedure takes parameters to retrieve it's values from a different table.

What we need to know is, "how are the parameters passed to the stored procedure when the sub form loads" I assume the sub forms form_load event or the form_initialize event passes the parameters to the stored procedure.

Once we find out where the parameter values come from we can run the update statement.

Another question. Do you know how to debug and step through your code as it runs?

 

[drek01]

Sooru i do not know how to debug and step through code as it runs.
can u tell me how?
thanks

 

[drek01]

the main form has drop down box where there are borrower information, once you click the borrower name, it loads data in the sub form.
here is code of main form.

Private Sub cmbborrower_AfterUpdate()
On Error GoTo Err_cmbborrower_AfterUpdate

Me.Form.RecordSource = "select * from tblborrowerinformation " & _
"where (tblborrowerinformation.borrowername = '" & Me![cmbborrower] & "') and (tblborrowerinformation.examname = '" & Forms!frmMainMenu!cmbexamname & "')" & _
" and (tblborrowerinformation.examasofdate = '" & Forms!frmMainMenu!cmbasofdate & "')"

Me.fsubcountryrisk.Form.RecordSource = "select borrowercountryrisk, borrowerbtmcountryrat, borrowermoodyscountryrat, " & _
" borrowerspcountryrat from tblborrowerinformation where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate & "'"

Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"

Exit_cmbborrower_AfterUpdate:
Exit Sub

Err_cmbborrower_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_cmbborrower_AfterUpdate

End Sub

Private Sub cmbborrower_Enter()
On Error GoTo Err_cmbborrower_Enter

Me.cmbborrower.RowSource = "SELECT [tblborrowerinformation].[borrowername]" & _
" FROM tblborrowerinformation where (tblborrowerinformation.examname = '" & Forms!frmMainMenu!cmbexamname & "')" & _
" and (tblborrowerinformation.examasofdate = '" & Forms!frmMainMenu!cmbasofdate & "') ORDER BY tblborrowerinformation.borrowername"

Exit_cmbborrower_Enter:
Exit Sub

Err_cmbborrower_Enter:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_cmbborrower_Enter

End Sub




AND form to load form,



Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim bflag As Boolean
Dim sStr As String

bflag = bUserPrivileges(Me.Form)
If bflag = False Then
MsgBox "You don't have enough privilege for accessing the data of this form"
GoTo Exit_Form_Load
End If
sStr = " select * from tblborrowerinformation where (tblborrowerinformation.borrowername = '') and (tblborrowerinformation.examname = '" & Form_frmMainMenu.cmbexamname.Value & "') and (tblborrowerinformation.examasofdate = '" & Form_frmMainMenu.cmbasofdate & "')"
Form_frmCreditWrkSheet.RecordSource = sStr

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
GoTo Exit_Form_Load

End Sub

 

[CaptainD]

First off, stepping through your code. Just to the left of the code window is a verticle bar. If you click in that bar next to a section of code, you should see a little red dot appear. That is called a break point. When you have break points set and activate that portion of code, the code "Stops" there and that section is highlighted. (usually in yellow). Depending on where you set the break point, portions of code will show their value as you hover over it.

Example:

Private Sub cmdTest_Click()
Dim i as integer
<Break Point Here> i = 4+2
MsgBox i
Hover the mouse over i as you hit F8 the yellow highlight will step down one line and you should see the value 6 in a little intellisense popup
Hit F8 again and the message box will pop up reading 6

End Sub

You can use that to find errors, make corrections etc.

Play with it, it is very helpful in learning how your program works and flows.

 

[CaptainD]

Ok, your stored procedures have changed.

Alter Procedure Spr_fsubCreditMaster

to

Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"

That asside, we're working with on table now, at least I assume that since I don't have the information on the other stored procedure.

Looking at both forms, they are both taking the same parameters.

Private Sub cmbborrower_AfterUpdate()
On Error GoTo Err_cmbborrower_AfterUpdate

Me.Form.RecordSource = "select * from tblborrowerinformation " & _
"where (tblborrowerinformation.borrowername = '" & Me![cmbborrower] & "') and (tblborrowerinformation.examname = '" & Forms!frmMainMenu!cmbexamname & "')" & _
" and (tblborrowerinformation.examasofdate = '" & Forms!frmMainMenu!cmbasofdate & "')"

Me.fsubcountryrisk.Form.RecordSource = "select borrowercountryrisk, borrowerbtmcountryrat, borrowermoodyscountryrat, " & _
" borrowerspcountryrat from tblborrowerinformation where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate & "'"

Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"

Exit_cmbborrower_AfterUpdate:
Exit Sub

Err_cmbborrower_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_cmbborrower_AfterUpdate
Form_frmMain.Requery
End Sub

Now we just need to know what fields you want to update. Are they in the main form where you used "SELECT *" or are they in the sub form? Either way, you need to change the update statment I showed you earlier to take those parameters.

strSql = "UPDATE ttblborrowerinformation SET tblborrowerinformation.YourField = '" & sValue & "' where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate & "'"
'Used db.Execute(strSql) if you are use ADO
 or 
DoCmd.RunSql(StrSql)
Me.Form.Requery

Like I had mentioned before, if you try to run the code in the combobox_afterupdate event, then each one will try to run the code, and you don't want that. So I would come up with another means of activating the update statment and then requery the form with the new data. (command button or something. like that )

 

[drek01]

Ok, your stored procedures have changed.

Quote:

Alter Procedure Spr_fsubCreditMaster


to


Quote:


Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"


this is the store procedure to load the main form actually, not the sub form. And those arethe update statement of the grade of the main borrower, actually i need to update the
grade of the bussiness unit of the borrower in subform.


i know i am confusing you.
how do i post the screen shot of the form, so it would make
lot easier to get the idea.

 

[CaptainD]

Ok, We know what you need to update, we know what records to filter for the update, so why the confusion?

Have you ever ran an update statement and / or understand how to run an update statement?

http://www.sql-tutorial.net/SQL-UPDATE.asp

 

[drek01]

Private Sub UpdateValues(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb.Connection 'I think that is right

strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

db.Execute strSql
Me.Form.Requery
Exit Sub
Error message

End Sub

this statement should i imply as event afterupdate on my combo box or just in subform as sub updatevalues().

 

[CaptainD]

As I mentioned before, I think each combobox is going to fire the same after_update event. If that is not a problem for you, then place it there and alter the statement to fit the proper table and fields.

If you want to test it, instead of actually firing the update statement. Create the sub, call it from where you want, and instead of using

db.execute(strSql) or DoCmd.RunSql(strSql)

put a message box to show you the statement so you can look it over and make sure it's right and runs the way you want it to.

Private Sub UpdateValues(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb.Connection 'I think that is right

strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

'db.Execute strSql
'DoCmd.RunSql(strSql)

[COLOR=red]MsgBox strSql[/color]

Me.Form.Requery
Exit Sub
Error message

End Sub

 

[drek01]

its not just doing anything.when you change the values of one rows, it just changes only for that row. fired the update event for that combo box and put the above code with some modification;

 

[CaptainD]

If this is what you are still running in the update event, then it will only change one record. You are not calling the sub routine

Private Sub cmbborrower_AfterUpdate()
On Error GoTo Err_cmbborrower_AfterUpdate

Me.Form.RecordSource = "select * from tblborrowerinformation " & _
"where (tblborrowerinformation.borrowername = '" & Me![cmbborrower] & "') and (tblborrowerinformation.examname = '" & Forms!frmMainMenu!cmbexamname & "')" & _
" and (tblborrowerinformation.examasofdate = '" & Forms!frmMainMenu!cmbasofdate & "')"

Me.fsubcountryrisk.Form.RecordSource = "select borrowercountryrisk, borrowerbtmcountryrat, borrowermoodyscountryrat, " & _
" borrowerspcountryrat from tblborrowerinformation where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate & "'"

Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"

Exit_cmbborrower_AfterUpdate:
Exit Sub

Err_cmbborrower_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_cmbborrower_AfterUpdate
Form_frmMain.Requery
End Sub

The samples I posted are not functional because I don't have the field name that you want updated in the list. I don't know what field(s) you want to update. You need to look at your table(s) to see what field or fields you want to update and change the update statement to fit.

Once that is done, you need to call the sub routine from the event area you want to use it in and test it as I suggested.